Correlated subquery pull-up

Supported versions

Supported PolarDB for PostgreSQL versions:

• PostgreSQL 18 (no kernel minor version requirement)

• PostgreSQL 17 (no kernel minor version requirement)

• PostgreSQL 14 (kernel minor version 2.0.14.8.11.0 or later)

Background

In the PostgreSQL optimizer, SubLink is used to represent a subquery and its associated operators within an expression. The types of SubLink are as follows:

• EXISTS_SUBLINK: implements the EXISTS (SELECT ...) subquery.

• ALL_SUBLINK: implements the ALL (SELECT ...) subquery.

• ANY_SUBLINK: implements the ANY (SELECT ...) subquery and the IN (SELECT ...) subquery.

The optimizer typically attempts to pull up correlated ANY/IN/EXISTS/NOT EXISTS subqueries so they can be jointly optimized with the parent query into execution plans with semi-joins or anti-joins, thereby improving query performance. However, for ANY_SUBLINK, if the subquery references a variable from the parent query, the subquery is not pulled up, which misses the opportunity for joint optimization with the parent query. The subquery can only be optimized as an independent unit, significantly increasing SQL execution time.

PolarDB for PostgreSQL provides a parameter to control ANY_SUBLINK correlated subquery pull-up. For correlated IN/ANY subqueries, even if they reference variables from the parent query, the subquery can still be pulled up, thereby expanding the optimizer search space and generating better execution plans.

Usage

The polar_enable_pullup_with_lateral parameter controls whether to enable ANY_SUBLINK correlated subquery pull-up. Valid values:

• ON (default): enables ANY_SUBLINK correlated subquery pull-up.

• OFF: disables ANY_SUBLINK correlated subquery pull-up.

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;

Execution plan and time with correlated subquery 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);
                                   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)

Execution plan and time with correlated subquery 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);
                                 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)

As shown in the preceding examples, after the subquery is pulled up, the subquery and the parent query are jointly optimized into a semi-join. The filter condition from the subquery can significantly filter the results of the parent query, resulting in a dramatic reduction in execution time. Without subquery pull-up, the rows to be returned from the parent query cannot be filtered.