Magento 2 GraphQL Performance Alert: The Product Count N+1 Bottleneck

Unmasking Magento 2 GraphQL's Hidden Performance Drain: The Product Count N+1 Issue

As e-commerce platforms increasingly rely on modern headless architectures and Progressive Web Apps (PWAs), GraphQL has become a cornerstone for delivering dynamic content. However, even the most advanced systems can harbor subtle performance bottlenecks. A critical issue recently surfaced on the Magento 2 GitHub, highlighting a severe N+1 problem within the GraphQL product_count resolver that can significantly degrade storefront performance, especially for large catalogs and PWA frontends.

The Problem: N+1 Queries for Product Counts

The core of the issue lies in how Magento 2's GraphQL product_count resolver calculates the number of products for each category. Instead of efficiently retrieving this data, it executes a separate, resource-intensive database query for every single category where product_count is requested. This 'N+1' pattern leads to a cascade of redundant queries, severely impacting response times.

The problem manifests when queries like categoryList or product listings request product_count across multiple nesting levels:

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

And similarly for product listings requesting categories:

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

The Database Strain: A Deep Dive

The Magento 2.4.x resolver, Magento\CatalogGraphQl\Model\Resolver\Category\ProductsCount, inefficiently builds a full product collection for each category and then calls getSize(). This triggers a complex COUNT(DISTINCT e.entity_id) SQL query involving five joins:

• catalog_product_entity
• catalog_category_product_index_store
• cataloginventory_stock_status
• catalog_product_entity_int (for status and visibility)
• catalog_product_website

This heavy query is executed repeatedly, without any batching or reuse, even for categories shared across products. On a vanilla Magento install, a query requesting 31 product_count fields resulted in 31 such COUNT(DISTINCT) queries. For a product listing, 40 product_count fields generated 40 queries, consuming nearly half of the total SQL execution time for the request.

Real-World Impact: Hours of Database Time

The severity of this issue escalates dramatically on production Adobe Commerce stores with large catalogs. One reported instance on a store with ~60,000 products and a PWA frontend saw this single query averaging 0.6 seconds and executing approximately 98,000 times in just seven days from a single product detail GraphQL operation. This translates to roughly 16 hours of database time per week dedicated to calculating product counts for categories – a staggering waste of resources.

The Root Cause and Proposed Solutions

The inefficiency stems from ProductsCount::resolve() method, which relies on $category->getProductCollection()->getSize(). This forces the system to construct and filter a full product collection for each category before counting. The issue is related to a broader family of Catalog N+1 problems (e.g., #40700).

The good news is that the GitHub issue itself proposes clear, actionable solutions:

1. Leverage the Category Product Index: The catalog_category_product_index_store already contains the necessary filtered product data (enabled, in-website, visibility). A simple COUNT(*) query against this index, filtered by category and store, would be significantly faster.
2. Implement Batch Resolving: By making the resolver implement BatchResolverInterface, all requested categories could be counted in a single, optimized query using GROUP BY category_id. This approach could also efficiently handle stock status filtering by joining cataloginventory_stock_status once for the entire batch.

Community Response and Next Steps

The issue, reported by lbajsarowicz, has been confirmed by Magento engineering (engcom-Bravo) on the latest 2.4-develop instance. The author has also indicated they are working on a fix, which is a positive sign for future Magento releases. This highlights the crucial role of the Magento community in identifying and addressing critical performance bottlenecks.

For merchants and developers running Magento 2 stores with GraphQL frontends, particularly PWAs, understanding this issue is vital. While awaiting an official patch, developers might consider implementing custom overrides or workarounds based on the proposed solutions to mitigate the performance impact on their live sites.