PolarDB:Subquery folding

AND operator

OR operator

AND operator

OR operator

AND condition

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subquery 1
  AND EXISTS (SELECT 1 FROM t2);               -- Subquery 2

Subquery 1 is a subset of subquery 2 → subquery 2 is redundant under AND → subquery 2 removed.

-- After
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0);

OR condition

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subquery 1
   OR EXISTS (SELECT 1 FROM t2);               -- Subquery 2

Subquery 1 is a subset of subquery 2 → under OR, the larger set dominates → subquery 1 removed.

-- After
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

AND condition

-- Before
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
  AND NOT EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Both subqueries query the same table with the same base condition (a > 10); their extra conditions are merged with OR.

-- After
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

OR condition

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
   OR EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Both subqueries query the same table with the same base condition; their extra conditions are merged with OR.

-- After
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

EXISTS and NOT EXISTS — AND condition

-- Before
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2 WHERE c1 = 0)   -- Subquery 1
  AND NOT EXISTS (SELECT 1 FROM t2);               -- Subquery 2

Subquery 1 is a subset of subquery 2. A row cannot satisfy both EXISTS (subset) and NOT EXISTS (superset) at the same time → AND condition is always false.

-- After
SELECT * FROM t1 WHERE false;

ANY and ALL conflict — AND condition

Applies to: > ANY + < ALL or <= ALL; < ANY + > ALL or >= ALL.

-- Before
SELECT * FROM t1
WHERE t1.c1 > ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND c2 > 1)   -- ANY set
  AND t1.c1 < ALL (SELECT c1 FROM t2 WHERE c1 > 10);              -- ALL set

The ANY set is a subset of the ALL set. No value can be both greater than some element of the subset and less than all elements of the superset → AND condition is always false.

-- After
SELECT * FROM t1 WHERE false; //The ANY set is a subset of the ALL set.

EXISTS and NOT EXISTS — OR condition

-- Before
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2)                    -- Subquery 1
   OR NOT EXISTS (SELECT 1 FROM t2 WHERE c1 = 0);     -- Subquery 2

Subquery 2 is a subset of subquery 1. Under OR, if the superset is non-empty the condition is true; if empty, the complement of the subset is always true → OR condition is always true.

-- After
SELECT * FROM t1 WHERE true; // Subquery 2 is a subset of subquery 1.

Merge EXISTS and NOT EXISTS

-- Before
SELECT * FROM t1
WHERE     EXIST (SELECT 1 FROM t2)                -- Subquery 1
  AND NOT EXIST (SELECT 1 FROM t2 WHERE c2 = 0); -- Subquery 2

The NOT EXISTS set is a right subset of the EXISTS set. The optimizer merges both into one scan and adds a HAVING condition to exclude the rows matched by the NOT EXISTS predicate.

-- After
SELECT * FROM t1
WHERE EXIST (
  SELECT 1 FROM t2
  HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0
);

This merge is not always optimal. By default, the CBQT component decides whether to apply it. To force the merge, set force_coalesce_subquery = ON.

The following chart shows query duration for TPCH Q21 before and after enabling subquery folding. A shorter bar indicates better performance.

Merge IN and NOT IN (or = ANY and != ALL)

Applies to: IN + NOT IN (NOT IN set is the left subset); = ANY + != ALL (ALL set is the left subset).

-- Before
SELECT * FROM t1
WHERE  t1.c1  = ANY (SELECT c1 FROM t2 WHERE c1 > 10)     -- = ANY set
  AND t1.c1 != ALL  (SELECT c1 FROM t2 WHERE c1 > 100);   -- != ALL set (left subset)

The != ALL set (c1 > 100) is a subset of the = ANY set (c1 > 10). The optimizer folds them by adding the LNNVL condition to the larger subquery, excluding rows that also satisfy the smaller subquery.

-- After
SELECT * FROM t1
WHERE t1.c1 = ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND LNNVL(c1 > 100));

This merge is always optimal and is applied by default without setting force_coalesce_subquery.