Cost-based query transformation (CBQT)
PolarDB for PostgreSQL implements a cost-based query transformation (CBQT) framework that evaluates whether a query transformation actually reduces execution cost before applying it, significantly improving execution efficiency for complex queries.
Background
Query transformation rewrites a query into a semantically equivalent form based on equivalence rules. In community PostgreSQL, common transformations include subquery pull-up, outer join elimination, expression preprocessing, useless join removal, and predicate pushdown. These transformations are all based on equivalence rules and always produce a better or equivalent plan, so PostgreSQL always applies them.
However, other transformations such as sublink pushdown and OR-to-UNION ALL may or may not produce a better plan. PolarDB for PostgreSQL implements a cost-based query transformation (CBQT) framework that evaluates the execution cost to decide whether to apply these transformations.
For complex queries, CBQT collects all applicable cost-based transformations across each query block and assembles them into a state space. CBQT then searches the state space using the configured strategy to select the state with the lowest cost.
As shown in the following diagram, for an input SQL statement with two query blocks, CBQT collects cost-based transformations A and B from Query Block 1 and Query Block 2. The state space consists of:
None: no transformation applied.
[1, A]: apply transformation A to Query Block 1.
[1, B]: apply transformation B to Query Block 1.
[2, A]: apply transformation A to Query Block 2.
CBQT iterates through each state in the state space. Under the default linear strategy, state [1, B] produces a better plan, so it is maintained when Plan 4 is generated.
Prerequisites
Supported PolarDB for PostgreSQL versions:
PostgreSQL 14 (revision version 2.0.14.13.28.0 or later)
PostgreSQL 11 (revision version 2.0.11.15.44.0 or later)
You can view the revision version in the console or run SHOW polardb_version; to check it. If the version does not meet the requirement, upgrade the revision version.
Configuration
PolarDB for PostgreSQL provides the following parameters to control CBQT behavior:
For clusters running revision version 2.0.14.15.29.0 or later, you can modify these parameters in the console. You can also connect to the cluster and modify them manually.
Parameter | Description |
polar_enable_cbqt | Enables or disables CBQT. Valid values:
|
polar_cbqt_cost_threshold | The minimum execution cost of the original plan required to activate CBQT. Valid values: |
polar_cbqt_strategy | The search strategy for the CBQT state space. Valid values:
|
polar_cbqt_iteration_limit | The number of iterations CBQT runs. Valid values: More iterations increase the chance of finding the optimal plan but take longer. Fewer iterations reduce optimization time but may miss the optimal plan. |
The following cost-based query rewrite features are supported:
Feature | Description |
polar_cbqt_convert_or_to_union_all_mode | OR clause to UNION ALL: converts qualifying OR clauses into UNION ALL to improve query efficiency. |
polar_cbqt_pushdown_sublink | Sublink pushdown: pushes a sublink down into a subquery to generate a parameterized index path, improving query efficiency. |
Examples
The following examples use sublink pushdown to demonstrate CBQT and its parameters.
Create test tables and insert data.
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;Disable CBQT and enable sublink pushdown. Sublink pushdown does not take effect because CBQT is off. A full table scan is performed on
t_big, resulting in low execution efficiency.-- Disable CBQT SET polar_enable_cbqt to off; -- 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);Output:
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)Enable CBQT and enable sublink pushdown. Sublink pushdown takes effect. The
a in (select a from t_small)clause is pushed down into the subquery, generating a parameterized path fort_bigusing the join condition. The amount of scanned data is significantly reduced and execution time is greatly improved.-- 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);Output:
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)When the plan cost does not exceed the CBQT cost threshold, CBQT is not activated and the original plan is used. For example, the cost of the original plan is 59511.17. Setting
polar_cbqt_cost_thresholdto 500000:-- Enable CBQT SET polar_enable_cbqt to on; -- Set the CBQT cost threshold SET polar_cbqt_cost_threshold to 500000; -- 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);Output:
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)Configure the CBQT search strategy. The following example has two sublinks that can be pushed down, but only pushing down the second sublink produces the optimal plan.
Set polar_cbqt_strategy to linear (linear search). CBQT selects the optimal plan.
-- Enable CBQT SET polar_enable_cbqt to on; -- Set the CBQT search strategy SET polar_cbqt_strategy to linear; -- Enable sublink pushdown 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);Output:
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)Set polar_cbqt_strategy to twophase (two-phase search). Both sublinks are pushed down.
-- Enable CBQT SET polar_enable_cbqt to on; -- Set the CBQT search strategy SET polar_cbqt_strategy to twophase; -- Enable sublink pushdown 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);Output:
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)
Limit CBQT iterations. Set
polar_cbqt_iteration_limitto 1. For the preceding scenario, even though the second sublink pushdown produces a better plan, CBQT does not try it due to the iteration limit.-- Enable CBQT SET polar_enable_cbqt to on; -- Set the CBQT search strategy SET polar_cbqt_strategy to twophase; -- Set the CBQT iteration limit SET polar_cbqt_iteration_limit to 1; -- Enable sublink pushdown 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);Output:
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)