Slow Product Listing From N+1 EAV Loads
A category page renders 48 configurable products, each with 3 simple children. The listing shows name, price, a custom material attribute, and stock status. Profiling shows 1,900+ queries per page load. The material attribute is a text attribute stored in catalog_product_entity_varchar. Stock status comes from the stock index. Prices are resolved per product in the template via $product->getPrice(). The team added a custom block that loops the collection and calls getAttributeText('material') inside the loop. Page TTFB is 4.2s uncached.
1. Identify the query sources and explain how EAV loading causes the explosion.
Read the worked answer
The collection is loaded, then the template and custom block call model methods that trigger lazy loads. getAttributeText('material') on each product, if the attribute was not added to the collection select, triggers a per-product load of that attribute (or a full product load). $product->getPrice() on a configurable child or without the price index joined can trigger additional loads. Stock status per product, if not pulled from the index in the collection, causes per-product stock lookups.
With 48 parents + 144 children, per-product loads multiply: 192 products × several lazy loads each easily reaches 1,900 queries. EAV makes this worse because each attribute lives in a value table keyed by attribute_id and store_id, so unselected attributes require separate queries rather than being part of the base row.
2. Describe the collection changes that reduce this to a small constant number of queries.
Read the worked answer
Build the listing collection with the needed attributes joined up front: add material to the select via addAttributeToSelect('material') so its value table is joined once for the whole collection. Join the price index (addPriceData / price index table) so getPrice() reads a joined column instead of lazy-loading. Join the stock status index table (or use Magento\CatalogInventory\Model\ResourceModel\Stock\Status collection join) so stock comes from the index in the same query. Ensure the custom block reads attributes from the already-loaded collection items rather than calling model loaders.
Result: one query for the product collection (with joins) plus a handful for configurable child relations and options, instead of ~1,900. Verify with the query log that counts stay flat as page size grows - that is the regression guard.