Subquery optimization and execution
A subquery is a SELECT statement nested inside the WHERE or HAVING clause of a query. Writing subqueries in distributed SQL often leads to performance problems that are hard to diagnose. PolarDB-X automatically converts many subqueries into joins to avoid nested iteration over large datasets — a technique called unnesting. This topic explains how PolarDB-X classifies and executes subqueries, when unnesting applies, and how to rewrite queries that cannot be unnested.
Subquery types
PolarDB-X classifies subqueries into two types based on whether they reference values from the outer query.
| Type | Definition | Execution |
|---|---|---|
| Non-correlated subquery | Does not reference variables from the outer query | In most cases, executed once, independent of the outer query |
| Correlated subquery | References variables from the outer query | Executed once per input row of the outer query |
/* Non-correlated subquery: l_partkey is compared against a fixed result set from part. */
SELECT * FROM lineitem WHERE l_partkey IN (SELECT p_partkey FROM part);
/* Correlated subquery: l_suppkey is referenced from the outer query (lineitem). */
SELECT * FROM lineitem WHERE l_partkey IN (SELECT ps_partkey FROM partsupp WHERE ps_suppkey = l_suppkey);PolarDB-X supports most SQL subqueries. For details on supported and unsupported syntax, see SQL limits.
How PolarDB-X optimizes subqueries
Subquery unnesting
PolarDB-X converts common subqueries into SEMIJOIN or JOIN statements to improve execution efficiency. This technique, called unnesting, avoids repeated nested iteration over large datasets and significantly reduces execution cost.
The following example shows a correlated subquery that PolarDB-X successfully unnests. The EXPLAIN output shows the subquery replaced by a HashJoin, with both sides of the join executed concurrently across shards.
> EXPLAIN SELECT p_partkey, (
SELECT COUNT(ps_partkey) FROM partsupp WHERE ps_suppkey = p_partkey
) supplier_count FROM part;
Project(p_partkey="p_partkey", supplier_count="CASE(IS NULL($10), 0, $9)", cor=[$cor0])
HashJoin(condition="p_partkey = ps_suppkey", type="left")
Gather(concurrent=true)
LogicalView(tables="part_[0-7]", shardCount=8, sql="SELECT * FROM `part` AS `part`")
Project(count(ps_partkey)="count(ps_partkey)", ps_suppkey="ps_suppkey", count(ps_partkey)2="count(ps_partkey)")
HashAgg(group="ps_suppkey", count(ps_partkey)="SUM(count(ps_partkey))")
Gather(concurrent=true)
LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT `ps_suppkey`, COUNT(`ps_partkey`) AS `count(ps_partkey)` FROM `partsupp` AS `partsupp` GROUP BY `ps_suppkey`")When unnesting cannot be applied
PolarDB-X cannot unnest subqueries in all scenarios. When unnesting is not possible, the subquery runs as a nested iteration: the outer query runs first, then the subquery is evaluated separately for each matching row. For large outer datasets, this is significantly slower.
In the following example, the OR condition prevents unnesting. The EXPLAIN output shows the subquery listed separately as an individual correlate subquery, which PolarDB-X evaluates row by row.
> EXPLAIN SELECT * FROM lineitem WHERE l_partkey IN (SELECT ps_partkey FROM partsupp WHERE ps_suppkey = l_suppkey) OR l_partkey IS NOT
Filter(condition="IS(in,[$1])[29612489] OR l_partkey < ?0")
Gather(concurrent=true)
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.lineitem_[0-7]", shardCount=8, sql="SELECT * FROM `lineitem` AS `lineitem`")
>> individual correlate subquery : 29612489
Gather(concurrent=true)
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.partsupp_[0-7]", shardCount=8, sql="SELECT * FROM (SELECT `ps_partkey` FROM `partsupp` AS `partsupp` WHERE (`ps_suppkey` = `l_suppkey`)) AS `t0` WHERE (((`l_partkey` = `ps_partkey`) OR (`l_partkey` IS NULL)) OR (`ps_partkey` IS NULL))")To enable unnesting, remove the OR condition and rewrite the SQL statement.