PolarDB:Subquery decorrelation

How it works

The following figure shows the structure of a query that contains a subquery.

T1, T2, and T3 each represent a collection of one or more tables and views. T2 (inside the subquery) is nested with T3, as shown by the dotted line. T1 is in the outer query and is not nested with T2.

The window function strategy applies when all of the following conditions are met:

• The scalar subquery contains no LIMIT or DISTINCT clause, and its output is an aggregate function.

• The table in the subquery is a subset of the tables in the outer query.

• The subquery is connected to the outer query by an equi join. The outer query contains join conditions with the same semantics and filter conditions for common tables in the subquery.

• The join column in the subquery is a primary key or unique key column.

• Neither the subquery nor the outer query contains custom functions or random functions.

After unnesting, the window function computes the aggregate in groups and attaches the result to each row, eliminating the need to re-execute the subquery.

Example

The following example uses TPC-H Q2 (Minimum Cost Supplier Query), which finds the supplier with the lowest price among all suppliers in a region who sell components of a specific type and size.

In MySQL Community Edition, the outer query first retrieves all matching suppliers. The subquery then runs on each row to find the minimum supply cost. For large datasets, this means the subquery executes once per supplier row.

Original query:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
 s_address, s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
   AND s_suppkey = ps_suppkey
   AND p_size = 30
   AND p_type LIKE '%STEEL'
   AND s_nationkey = n_nationkey
   AND n_regionkey = r_regionkey
   AND r_name = 'ASIA'
   AND ps_supplycost = (
       SELECT MIN(ps_supplycost)
       FROM partsupp, supplier, nation, region
       WHERE p_partkey = ps_partkey
           AND s_suppkey = ps_suppkey
           AND s_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA'
   )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

PolarDB rewrites this using MIN() OVER(PARTITION BY ps_partkey) to compute the minimum cost per part in a single pass, then filters rows where the supply cost matches the group minimum.

Rewritten query:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM part, partsupp, supplier, nation, region
    WHERE p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND p_size = 30
      AND p_type LIKE '%STEEL'
      AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

Performance improvement

At TPC-H scale factor 10:

• Q2: 1.54x improvement

• Q17: 4.91x improvement

How it works

The following figure shows the structure of a query that contains a subquery.

The GROUP BY strategy applies when all of the following conditions are met:

• The scalar subquery contains no GROUP BY or LIMIT clause, and its output is an aggregate function.

• The scalar subquery appears in a JOIN, WHERE, or SELECT condition.

• The subquery is connected to the outer query by an equi join, with conditions joined by AND.

• The scalar subquery contains no custom functions or random functions.

After unnesting, the subquery becomes a derived table that pre-aggregates results grouped by the join key. The outer query then joins this derived table once, replacing repeated subquery execution.

Example

The following example retrieves orders where the quantity exceeds 10% of the total purchase amount for the same product.

Original query:

SELECT *
FROM sale_lineitem sl
WHERE sl.sl_quantity >
    (SELECT 0.1 * SUM(pl.pl_quantity)
     FROM purchase_lineitem pl
     WHERE pl.pl_objectkey = sl.sl_objectkey);

Without unnesting, the database iterates every row in sale_lineitem, reads sl_objectkey, and re-executes the subquery for that value. The subquery runs as many times as there are rows in sale_lineitem. Because sl_objectkey typically contains many duplicate values, the same aggregation on purchase_lineitem repeats many times — even when an index exists on pl_objectkey.

PolarDB rewrites this as a LEFT JOIN against a pre-aggregated derived table. The purchase_lineitem table is scanned only once.

Rewritten query:

SELECT *
FROM sale_lineitem sl
LEFT JOIN
  (SELECT (0.1 * sum(pl.pl_quantity)) AS Name_exp_1,
          pl.pl_objectkey AS Name_exp_2
   FROM purchase_lineitem pl
   GROUP BY pl.pl_objectkey) derived ON derived.Name_exp_2 = sl.sl_objectkey
WHERE sl.sl_quantity > derived.name_exp_1;

The optimizer can further improve execution by reordering the join based on cost estimates.