Use Correlated subquery pull-up to improve query performance - PolarDB

When an IN or ANY subquery references a column from the outer query, the PostgreSQL optimizer treats it as correlated and plans it independently—running it once for every row the outer query returns. On a 100,000-row table, this means 100,000 subquery executions, which can significantly increase execution time.

polar_enable_pullup_with_lateral lets the optimizer pull up these correlated ANY_SUBLINK subqueries and merge them with the outer query into a single semi-join plan. Enable it to recover a semi-join execution plan for queries that match the pattern:

SELECT ... FROM t1 WHERE t1.col IN (SELECT col FROM t2 WHERE t2.x = t1.x ...);

Supported versions

This feature requires PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.11.0 or later.

To check your revision version, run:

SHOW polar_version;

How it works

The PostgreSQL optimizer represents each subquery as a SUBLINK. Three types are supported:

Type	Implements
`EXISTS_SUBLINK`	`EXISTS (SELECT ...)`
`ALL_SUBLINK`	`ALL (SELECT ...)`
`ANY_SUBLINK`	`ANY (SELECT ...)` or `IN (SELECT ...)`

The optimizer can normally convert ANY_SUBLINK, IN, EXISTS, and NOT EXISTS subqueries into efficient semi-join or anti-join plans. However, when an ANY_SUBLINK subquery references a column from the outer query, the optimizer marks it as correlated and skips the pull-up. The subquery is then planned and run independently—once per outer row.

polar_enable_pullup_with_lateral extends the optimizer's pull-up logic to handle these correlated ANY_SUBLINK subqueries. When enabled, the optimizer can generate a semi-join plan even when the subquery references outer query columns, eliminating the per-row subquery overhead.

Enable correlated subquery pull-up

Set polar_enable_pullup_with_lateral to control pull-up behavior for correlated ANY_SUBLINK subqueries.

Value	Behavior
`ON` (default)	Pull up correlated `IN`/`ANY` subqueries that reference outer query columns
`OFF`	Leave correlated `ANY_SUBLINK` subqueries unoptimized

To change the setting for the current session:

SET polar_enable_pullup_with_lateral TO ON;

Example

Prepare the data

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, 1 FROM generate_series(1, 100000) i;
CREATE TABLE t2 AS SELECT * FROM t1;

Run with pull-up disabled

SET polar_enable_pullup_with_lateral TO OFF;

EXPLAIN (COSTS OFF, ANALYZE)
SELECT * FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);

Output:

                                    QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t1 (actual time=67.631..1641827.119 rows=100000 loops=1)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result (actual time=0.005..13.124 rows=50000 loops=100000)
           One-Time Filter: (t1.b = 1)
           ->  Seq Scan on t2 (actual time=0.005..7.718 rows=50000 loops=100000)
                 Filter: (b = 1)
 Planning Time: 0.145 ms
 Execution Time: 1641847.702 ms
(9 rows)

SubPlan 1 is nested inside the sequential scan of t1. The subquery runs once per row—100,000 times—resulting in an execution time of over 27 minutes.

Run with pull-up enabled

SET polar_enable_pullup_with_lateral TO ON;

EXPLAIN (COSTS OFF, ANALYZE)
SELECT * FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);

Output:

                                  QUERY PLAN
----------------------------------------------------------------------------
 Hash Semi Join (actual time=64.783..173.482 rows=100000 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1 (actual time=0.016..25.440 rows=100000 loops=1)
         Filter: (b = 1)
   ->  Hash (actual time=64.550..64.551 rows=100000 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2976kB
         ->  Seq Scan on t2 (actual time=0.010..30.330 rows=100000 loops=1)
               Filter: (b = 1)
 Planning Time: 0.195 ms
 Execution Time: 178.050 ms
(10 rows)

Hash Semi Join appears at the top level. Both tables are scanned once, the subquery filter is applied to t2 before the join, and execution time drops from over 27 minutes to 178 milliseconds.

Verify that pull-up is active

Check the execution plan for a top-level join node such as Hash Semi Join. If the plan still shows SubPlan nested inside a sequential scan of the outer table, pull-up did not take effect. Verify the parameter value and confirm your cluster meets the version requirement.