split_materialized re-evaluates a 38-row derived table once per outer row, 30x slower than materializing it

With the default optimizer_switch, a query whose derived table returns 38 rows is executed as LATERAL DERIVED and re-evaluated for each of the 8277 rows of the outer join. Turning split_materialized off makes the same query return the same rows about 30 times faster.

This is not a synthetic case. The query is generated by the faceted search (layered navigation) feature of PrestaShop, an e-commerce platform, so it runs on a large number of production shops. On our servers it is currently the single most common entry in the slow query log.

Measurements

Measured on one shop, catalog of roughly 9500 products:

All variants return identical results. Under real traffic the same query has been logged at 245 seconds.

EXPLAIN shows LATERAL DERIVED for the derived table in the slow case.

Forcing materialization with the usual LIMIT 18446744073709551615 trick has no effect here - measured at 27.5 s, i.e. the same as the default plan.

Affected versions

We went back through rotated slow query logs from four servers and checked the server version recorded in each log header. Logs written exclusively under 11.8.7 already contain thousands of these queries above the 10 s slow query threshold - on one server 4248, 3796, 3857, 4068, 4358 and 5898 in six consecutive rotated logs.

The split_materialized flag exists from 10.3 onwards, so any release from 10.3 up is a candidate. We have measured 11.8.7 and 12.3.3.

Query

Table prefix and filter values vary per installation; this is the shape:

SELECT pac.id_attribute, COUNT(DISTINCT p.id_product) c

FROM (

    SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) AS quantity,

           p.condition, p.weight, p.price, psales.quantity AS sales, p.on_sale,

           p.date_add, pa.id_product_attribute

    FROM ps_product p

    LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

    LEFT JOIN ps_product_attribute_combination pac

           ON (pa.id_product_attribute = pac.id_product_attribute)

    LEFT JOIN ps_stock_available sa

           ON (p.id_product = sa.id_product

               AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute

               AND sa.id_shop = 1 AND sa.id_shop_group = 0)

    LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product)

    INNER JOIN ps_product_shop ps

           ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE)

    INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)

    INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active = 1)

    LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category)

    WHERE ps.id_shop = '1'

      AND ps.visibility IN ('both', 'catalog')

      AND cg.id_group = '1'

      AND cp.id_category = '512'

    GROUP BY p.id_product

) p

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_product_attribute_combination pac

       ON (pa.id_product_attribute = pac.id_product_attribute)

INNER JOIN ps_attribute a ON (a.id_attribute = pac.id_attribute)

WHERE ((a.id_attribute_group = 10))

GROUP BY pac.id_attribute

How to reproduce

1. A PrestaShop 1.7 or 8 catalog with a few thousand products, attributes and an active attribute group. Any installation of that size reproduces it.
2. Run the query above, adjusting prefix, category id and attribute group id.
3. Run it again prefixed with SET STATEMENT optimizer_switch='split_materialized=off' FOR
4. Compare timings and EXPLAIN output.

Why it matters beyond one shop

Faceted search URLs are indexed by search engines and replayed by crawlers, so a shop receives a high share of requests with filter combinations that have never been seen before. Each of those bypasses the application level cache and executes this query. On one account we measured 3094 filter requests in 30 minutes, of which 98.7 % used five or more filter values.

The pathological plan is therefore not hit occasionally - it is hit thousands of times per hour, on many shops at once.

Current workaround

Prefixing that single statement with SET STATEMENT optimizer_switch='split_materialized=off' FOR, guarded by a server version check because the flag does not exist before 10.3 or on MySQL.

Related

Reported to PrestaShop from the application side: https://github.com/PrestaShop/ps_facetedsearch/issues/1309