Magento 2 Performance Deep Dive: Unraveling the Catalog Products List Widget Bottleneck

The Magento 2 ecosystem is constantly evolving, and with that comes the ongoing effort to optimize performance and developer experience. A recent GitHub issue, #41666, sheds light on a significant performance bottleneck related to the Catalog Products List widget, particularly when dealing with configurable products and large catalogs.

The Core Problem: Inefficient Product ID Fetching

The issue, reported by lbajsarowicz, describes how the Catalog Products List widget, when configured with a specific products_count (e.g., to display only 5 products), paradoxically fetches every single matching product ID from the database. This occurs due to an oversight in the Magento\ConfigurableProduct\Plugin\CatalogWidget\Block\Product\ProductsListPlugin::afterCreateCollection() method.

Specifically, the plugin's calls to getAllIds() on both the widget collection and the base collection are problematic. The getAllIds() method, by design, resets any LIMIT and OFFSET clauses, leading to a full scan of the catalog_product_entity table. This means that even if you intend to display only a handful of products, the system might be querying thousands of product IDs into PHP memory, only to discard most of them.

Impact on Performance

The consequences of this behavior are substantial, especially for Magento 2 stores with extensive catalogs and when block caches are cold. The issue author provided compelling data:

• On a production store, the plugin consumed approximately 490 ms out of 516 ms spent in createCollection() during uncached widget renders.
• The number of IDs bound into the catalog_product_super_link query scales directly with the catalog size, not the desired products_count. For instance, a base catalog might bind 103 IDs, while adding 200 products could escalate this to 303 IDs, even if only 5 are ultimately rendered.

This unnecessary data fetching translates directly into slower page load times, increased database load, and higher memory consumption, diminishing the overall user experience and potentially impacting SEO.

Reproducing the Issue

Developers can easily replicate this behavior by setting up a CMS page with a Catalog Products List widget, enabling the query log, and observing the database queries:

{{widget type="Magento\CatalogWidget\Block\Product\ProductsList" show_pager="0" products_count="5" template="Magento_CatalogWidget::product/widget/content/grid.phtml" c}}

The key observation is the execution of SELECT DISTINCT e.entity_id FROM catalog_product_entity ... statements without a LIMIT clause, followed by an IN (...) clause in the catalog_product_super_link query containing all fetched IDs.

Proposed Solutions and Active Development

The issue author not only identified the problem but also suggested concrete optimizations:

1. Eliminate the first getAllIds() call on the widget collection, as its IDs are always a subset of the base collection's IDs, making the call redundant.
2. Pass the base collection's ID select as a subquery directly to the catalog_product_super_link lookup, rather than loading all IDs into PHP memory. This would significantly reduce memory footprint and database interaction.

Encouragingly, the author, lbajsarowicz, confirmed they are actively working on a fix, indicating that a resolution for this performance bottleneck is in the pipeline for future Magento 2.4.x releases. This highlights the proactive nature of the Magento community and core contributors in enhancing the platform's efficiency.

For Magento developers and merchants, understanding such nuances is crucial. While this specific bug is being addressed, it serves as a reminder to always monitor query logs and profile performance, especially when dealing with widgets and large datasets. Keeping your Magento Open Source or Adobe Commerce instance updated will ensure you benefit from these critical performance improvements.