Cost-based query transformation (CBQT)

Some query transformations — such as sublink pushdown and OR-to-UNION ALL conversion — improve performance for some queries but worsen it for others. Cost-based query transformation (CBQT) solves this by evaluating whether a transformation actually reduces execution cost before applying it. For complex queries, CBQT can reduce execution time dramatically — in some cases from over a second to under a millisecond.

How it works

PostgreSQL applies several query transformations unconditionally, because they always produce a better or equivalent plan: subquery pull-up, outer join removal, expression preprocessing, useless join removal, and predicate pushdown.

CBQT handles the cost-sensitive transformations that may or may not improve a plan. For each SQL statement, CBQT:

Collects all applicable cost-based transformations across each query block.
Builds a state space of possible transformation combinations.
Searches the state space using the configured strategy.
Applies the combination with the lowest estimated execution cost.

For example, given an input SQL statement with two query blocks and two possible transformations (A and B), the state space includes: no transformation, transformation A in block 1, transformation B in block 1, and transformation A in block 2. Using the default linear strategy, CBQT evaluates each state and selects the one that produces the best plan.

Prerequisites

Before you begin, ensure that your cluster meets the version requirement:

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 the revision version in the console. To upgrade, see Upgrade the version.

Configure CBQT

For clusters running revision version 2.0.14.15.29.0 or later, configure these parameters directly in the PolarDB console. See Configure cluster parameters. For earlier versions, connect to the cluster and set parameters with SET.

CBQT control parameters

Parameter	Default	Valid values	Description
`polar_enable_cbqt`	`off`	`off`, `on`	Enables or disables CBQT.
`polar_cbqt_cost_threshold`	`50000`	`[0, +∞)`	The minimum execution cost of the original plan required to activate CBQT. If the original plan cost is below this threshold, CBQT is skipped and the original plan is used.
`polar_cbqt_strategy`	`linear`	`linear`, `twophase`	The search strategy for the CBQT state space. See the table below for behavior details.
`polar_cbqt_iteration_limit`	`10`	`[1, +∞)`	The number of iterations CBQT runs. More iterations increase the chance of finding the optimal plan but take longer.

Search strategy behavior

Strategy	Behavior
`linear`	Evaluates each transformation state one at a time, comparing costs before and after each state is applied. Selects the plan with the lowest cost at each step.
`twophase`	Compares two extremes: all transformations applied versus none applied. Selects the better of the two. May miss the optimal plan when only a subset of transformations is beneficial.

Supported query rewrite features

Parameter	Description
`polar_cbqt_convert_or_to_union_all_mode`	Converts OR conditions to UNION ALL to enable index usage on each branch. For more information, see OR to UNION ALL.
`polar_cbqt_pushdown_sublink`	Pushes a sublink down into a subquery so the optimizer can generate a parameterized index path, reducing the number of rows scanned. For more information, see Sublink pushdown.

CBQT applies these transformations only when they reduce the estimated execution cost. If a transformation would not improve the plan, CBQT skips it and uses the original plan.

Examples

The following examples use sublink pushdown to demonstrate how CBQT and its parameters affect query plans. All examples use the same test tables.

Set up test tables

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;

t_big has 1,000,000 rows. t_small has 2 rows.

Example 1: CBQT off — sublink pushdown has no effect

With CBQT disabled, polar_cbqt_pushdown_sublink is ignored. The optimizer performs a sequential scan over all 1,000,000 rows in t_big.

SET polar_enable_cbqt TO off;
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
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.052..1274.435 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.033..1151.005 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.022..433.821 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.015..0.016 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.904 ms
 Execution Time: 1274.539 ms
(11 rows)

Example 2: CBQT on — sublink pushdown takes effect

With CBQT enabled, the a IN (SELECT a FROM t_small) sublink is pushed down into the subquery. The optimizer generates a parameterized index path on t_big(a), scanning only 2 rows instead of 1,000,000.

SET polar_enable_cbqt TO on;
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.060..0.063 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.052..0.053 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.046 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.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.006..0.007 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.010 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.644 ms
 Execution Time: 0.150 ms
(15 rows)

Execution time drops from 1,274 ms to 0.150 ms — roughly an 8,000x improvement.

Example 3: Cost threshold prevents CBQT from activating

CBQT only activates when the original plan's cost exceeds polar_cbqt_cost_threshold. In this example, the original plan cost is 59,511.17. Setting the threshold to 500,000 means CBQT never runs, and the original plan is used.

SET polar_enable_cbqt TO on;
SET polar_cbqt_cost_threshold TO 500000;
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
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.059..1253.452 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.041..1127.255 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.029..414.488 rows=1000000 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.005..0.006 rows=2 loops=1)
 Planning Time: 0.280 ms
 Execution Time: 1253.558 ms
(11 rows)

Example 4: Search strategy comparison

This example uses a query with two sublinks. Pushing down only the second sublink (against t_small) is optimal. Pushing down both (against t_big and t_small) is not, because t_big is large.

`linear` strategy — selects the optimal transformation

The linear strategy evaluates each transformation state independently. It correctly identifies that pushing down only the sublink against t_small gives the best plan.

SET polar_enable_cbqt TO on;
SET polar_cbqt_strategy TO linear;
SET polar_cbqt_pushdown_sublink TO on;

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

QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..105692.60 rows=500002 width=12)
   ->  Merge Semi Join  (cost=0.85..98174.56 rows=500000 width=12)
         Merge Cond: (t_big_1.a = t_big.a)
         ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12)
               Group Key: t_big_1.a
               ->  Index Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..31910.13 rows=1000000 width=8)
         ->  Index Only Scan using t_big_a_idx on t_big  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)

`twophase` strategy — pushes down both sublinks

The twophase strategy compares all transformations applied versus none applied. It pushes down both sublinks, including the one against t_big, which increases total cost.

SET polar_enable_cbqt TO on;
SET polar_cbqt_strategy TO twophase;
SET polar_cbqt_pushdown_sublink TO on;

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

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..113192.60 rows=1000002 width=12)
   ->  GroupAggregate  (cost=0.85..88174.56 rows=1000000 width=12)
         Group Key: t_big.a
         ->  Merge Semi Join  (cost=0.85..73174.56 rows=1000000 width=8)
               Merge Cond: (t_big.a = t_big_1.a)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8)
               ->  Index Only Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)

In this scenario, linear produces a lower-cost plan (105,692) than twophase (113,192).

Example 5: Iteration limit

Limiting iterations can prevent CBQT from reaching the optimal state. In this example, setting polar_cbqt_iteration_limit to 1 with the twophase strategy means CBQT runs only one iteration and does not explore the state where only the second sublink is pushed down.

SET polar_enable_cbqt TO on;
SET polar_cbqt_strategy TO twophase;
SET polar_cbqt_iteration_limit TO 1;
SET polar_cbqt_pushdown_sublink TO on;

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

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..113192.60 rows=1000002 width=12)
   ->  GroupAggregate  (cost=0.85..88174.56 rows=1000000 width=12)
         Group Key: t_big.a
         ->  Merge Semi Join  (cost=0.85..73174.56 rows=1000000 width=8)
               Merge Cond: (t_big.a = t_big_1.a)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8)
               ->  Index Only Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)

The result matches the twophase plan from Example 4, not the more optimal linear plan. Increase polar_cbqt_iteration_limit to give CBQT more iterations to explore the state space.

What's next

OR to UNION ALL — how CBQT rewrites OR conditions into separate index-friendly queries
Sublink pushdown — how CBQT pushes sublinks into subqueries to enable parameterized index paths

References

Convert an OR condition into a UNION ALL