[Performance] GraphQL category product_count runs a five-join COUNT(DISTINCT) query per category (N+1)

Preconditions and environment

• Magento 2.4-develop (f8405be831e); the resolver is unchanged since 2.4.2 apart from PHP 8.4 compatibility commits, so every 2.4.x release is affected
• Any catalog; the effect scales with catalog size and number of categories

Steps to reproduce

1. Enable the DB query log: bin/magento dev:query-log:enable
2. Send a GraphQL categoryList query that requests product_count on a few nesting levels:

{ categoryList { children { id product_count children { id product_count children { id product_count } } } } }

1. Send a product listing query that requests the categories of each item:

{ products(filter: {category_id: {eq: "3"}}, pageSize: 12) { items { sku categories { id product_count } } } }

1. Count the queries in var/debug/db.log:

grep -c "COUNT(DISTINCT e.entity_id)" var/debug/db.log

Expected result

product_count is resolved with one cheap query per request, or at most one per category, against the category product index.

Actual result

Magento\CatalogGraphQl\Model\Resolver\Category\ProductsCount builds a full product collection for every category and calls getSize() on it. Each call runs:

SELECT COUNT(DISTINCT e.entity_id) FROM catalog_product_entity AS e
 INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id = e.entity_id AND cat_index.store_id = 1 AND cat_index.visibility IN (3, 2, 4) AND cat_index.category_id = 3 AND cat_index.is_parent = 1
 INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
 INNER JOIN catalog_product_entity_int AS at_status_default ON at_status_default.entity_id = e.entity_id AND at_status_default.attribute_id = 97 AND at_status_default.store_id = 0
 INNER JOIN catalog_product_entity_int AS at_visibility_default ON at_visibility_default.entity_id = e.entity_id AND at_visibility_default.attribute_id = 99 AND at_visibility_default.store_id = 0
 INNER JOIN catalog_product_website AS product_website ON product_website.product_id = e.entity_id AND product_website.website_id IN (1)
WHERE stock_status_index.stock_status = 1

Measured on a vanilla install with 1200 products and 33 categories:

One COUNT(DISTINCT) per product_count field, no batching, no reuse between items that share a category. On the listing query it is 48% of all SQL for the request.

On a production store with a large catalog (Adobe Commerce, ~60k products, PWA frontend) the same query averages 0.6 s and ran ~98,000 times in 7 days from a single product detail GraphQL operation, roughly 16 hours of DB time per week for one field. Numbers from New Relic, aggregated; the query shape is identical to the one above plus the staging created_in/updated_in predicates.

Additional information

Root cause: ProductsCount::resolve() does

$productsCollection = $category->getProductCollection();
$productsCollection->setVisibility($this->catalogProductVisibility->getVisibleInSiteIds());
$productsCollection = $this->collectionProcessor->process($productsCollection, $this->searchCriteria, [], $context);
$size = $productsCollection->getSize();

The collection processors add the status, visibility, website and stock joins, then getSize() counts through all of them for every category.

The category product index already contains only enabled, in-website products with their visibility per store, so COUNT(*) FROM catalog_category_product_index_store{N} WHERE category_id = ? AND store_id = ? AND visibility IN (...) gives the same number without the four joins. Better still, the resolver can be a BatchResolverInterface and count all requested categories with one GROUP BY category_id query.

The stock join only matters when "Display Out of Stock Products" is disabled; the batch count can keep that behaviour by joining cataloginventory_stock_status once for the whole set.

Related: #40700 (Catalog N+1 family). This report is specific to the GraphQL product_count resolver and comes with production measurements.