Sublink pushdown

When a SQL statement uses an IN or ANY clause that references a subquery with a GROUP BY clause, PostgreSQL cannot create a parameterized path for the subquery. Instead, the outer query performs a full table scan — even if only a few rows match. As the table grows, execution time grows with it. PolarDB for PostgreSQL (Compatible with Oracle) rewrites the query to push the IN or ANY clause inside the subquery, enabling index use on the large table and significantly reducing execution time.

Background

In PostgreSQL, ANY-type sublinks (IN and ANY clauses) are typically pulled up as semi joins. When the referenced subquery cannot be pulled up — for example, because it contains a GROUP BY clause — PostgreSQL cannot create a parameterized path. The subquery runs independently, and the outer query performs a full table scan regardless of how many rows the subquery returns.

The following query has a subquery with a GROUP BY clause. Because the subquery cannot be pulled up, t_big is fully scanned: 1,000,000 rows scanned for a result of 2 rows.

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=0.55..59523.15 rows=10000 width=12) (actual time=0.064..1237.621 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.99 rows=1000000 width=12) (actual time=0.033..1113.615 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.99 rows=1000000 width=8) (actual time=0.024..420.575 rows=1000000 loops=1)
   ->  Index Only Scan using t_small_a_idx on t_small  (cost=0.13..12.16 rows=2 width=4) (actual time=0.028..0.030 rows=2 loops=1)
         Heap Fetches: 2
 Planning Time: 0.256 ms
 Execution Time: 1237.700 ms
(9 rows)

Sublink pushdown moves the IN clause inside the subquery. PostgreSQL can then generate a parameterized path for t_big and use its index, reducing both the rows scanned and the execution time:

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big WHERE a IN (SELECT a FROM t_small) GROUP BY a)v;

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.061..0.064 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.054..0.056 rows=2 loops=1)
         Sort Key: t_big.a
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8) (actual time=0.031..0.045 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.017 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.013..0.014 rows=2 loops=1)
                           Sort Key: t_small.a
                           Sort Method: quicksort  Memory: 25kB
                           ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.527 ms
 Execution Time: 0.143 ms
(15 rows)

Prerequisites

Before you begin, ensure that your cluster runs one of the following database engine versions:

PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.13.28.0 or later
PolarDB for PostgreSQL (Compatible with Oracle) 1.0, revision version 2.0.11.15.44.0 or later

To check your revision version, run SHOW polardb_version; or view it in the console. To upgrade, see Upgrade the version.

Use cases

Sublink pushdown is most effective when all of the following are true:

The IN or ANY clause references a subquery with a GROUP BY clause
The subquery involves a large table

In these cases, pushing the IN or ANY clause into the subquery lets PostgreSQL use the index instead of performing a full table scan, significantly reducing the data accessed.

Limitations

Limitation	Detail
GROUP BY required	The IN or ANY clause must reference a subquery with a GROUP BY clause. If the subquery has no GROUP BY clause, open source PostgreSQL can already create a parameterized path natively and the feature is not applied.
Column alignment	The columns in the IN or ANY clause must be part of the GROUP BY columns. Otherwise, the rewritten query may return different results than the original.
No outer joins	The current query block must not contain outer joins. Otherwise, the rewritten query may return different results than the original.
Single column only	Only one column can be referenced in the IN or ANY clause, such as `a IN (SELECT a FROM t)` or `a = ANY(SELECT a FROM t)`.
Supported statement types	Only `SELECT` and `CREATE TABLE AS` statements are supported. Other statement types are not rewritten.

Configure sublink pushdown

Use the polar_cbqt_pushdown_sublink parameter to control the feature:

Parameter	Default	Valid values	Description
`polar_cbqt_pushdown_sublink`	`OFF`	`OFF`, `ON`, `FORCE`	`OFF` disables sublink pushdown. `ON` enables it through cost-based query transformation (CBQT) — pushdown applies only when the original query plan cost exceeds `polar_cbqt_cost_threshold`. `FORCE` bypasses the cost check and always applies pushdown; use this value in a query hint rather than as a global setting.

Note

When using ON, sublink pushdown applies only when the original query plan cost exceeds polar_cbqt_cost_threshold. If your query is not being rewritten, check whether this threshold is met.

Examples

Data preparation

CREATE TABLE t_small(a int);
CREATE TABLE t_big(a int, b int, c int);

CREATE INDEX ON t_big(a);

INSERT INTO t_big SELECT i, i, i FROM generate_series(1, 1000000)i;
INSERT INTO t_small VALUES(1), (1000000);

ANALYZE t_small, t_big;

Original query

Without sublink pushdown, the join condition t_big.a = t_small.a cannot be pushed down as a parameterized path. PostgreSQL performs a full table scan on t_big, scanning all 1,000,000 rows:

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59510.27 rows=10000 width=12) (actual time=0.049..1239.128 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46909.23 rows=1000000 width=12) (actual time=0.034..1113.324 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31909.23 rows=1000000 width=8) (actual time=0.025..412.650 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.012..0.013 rows=2 loops=1)
         Sort Key: t_small.a
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)
 Planning Time: 0.219 ms
 Execution Time: 1239.208 ms
(11 rows)

Enable sublink pushdown via CBQT

Enable cost-based query transformation (CBQT) alongside sublink pushdown. The clause a IN (SELECT a FROM t_small) is pushed into the subquery, generating a parameterized path for t_big. The plan changes from Merge Semi Join to Nested Loop + Index Scan, confirming pushdown is active:

-- Enable CBQT
SET polar_enable_cbqt TO on;

-- Enable sublink pushdown
SET polar_cbqt_pushdown_sublink TO on;

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.056..0.059 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.051..0.052 rows=2 loops=1)
         Sort Key: t_big.a
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8) (actual time=0.032..0.045 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.018 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.014..0.015 rows=2 loops=1)
                           Sort Key: t_small.a
                           Sort Method: quicksort  Memory: 25kB
                           ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.007..0.008 rows=2 loops=1)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.518 ms
 Execution Time: 0.141 ms
(15 rows)

Execution time drops from 1,239 ms to 0.141 ms — a reduction of over 8,000x.

Enable sublink pushdown via a hint

To apply pushdown to a single query without changing session-level settings, use the FORCE value in a query hint. This bypasses the CBQT cost check and always applies pushdown. The plan shows the same Nested Loop + Index Scan structure, confirming pushdown is active:

-- Reset to default
SET polar_cbqt_pushdown_sublink TO off;

EXPLAIN ANALYZE /*+ Set(polar_cbqt_pushdown_sublink force) */ SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.073..0.076 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.067..0.069 rows=2 loops=1)
         Sort Key: t_big.a
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8) (actual time=0.026..0.040 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.011..0.015 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.010..0.011 rows=2 loops=1)
                           Sort Key: t_small.a
                           Sort Method: quicksort  Memory: 25kB
                           ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.788 ms
 Execution Time: 0.156 ms
(15 rows)

What's next

CBQT — Learn about cost-based query transformation, which controls when ON mode applies sublink pushdown