AI 助理
备案控制台登录注册
官方文档
输入文档关键字查找
首页云原生数据库 PolarDB云原生数据库PolarDB PostgreSQL版（兼容Oracle）自研内核能力优化器基于代价的查询变换（CBQT）

基于代价的查询变换（CBQT）

更新时间：2025-04-11 01:43:06
产品详情
我的收藏

PolarDB PostgreSQL版（兼容Oracle）实现了基于代价的查询变换（CBQT，Cost Based Query Transformation）框架，能够基于代价选择是否应用某种查询变换，大幅提升某些复杂查询的执行效率。

背景

查询变换是指依据等价规则，将一个查询语句重写为语义上等价的另一种形式。在社区PostgreSQL中，常见的查询变换包括子查询上拉、外连接消除、表达式预处理、消除无用连接、谓词下推等，这些变换均基于等价规则进行。经过这些查询变换后，生成的执行计划必然更优，因此PostgreSQL一定会尝试应用这些变换。

但另一些查询变换，如子连接下推、OR转UNION ALL等，应用后的计划不一定更优。对此，PolarDB PostgreSQL版（兼容Oracle）实现基于代价的查询变换（CBQT）框架，能够基于执行代价选择是否做这些变换。

对于复杂查询，CBQT会收集该查询在各查询块中能够进行的基于代价的查询变换，这些变换将汇总成一个状态空间。CBQT将依据指定的策略对状态空间进行搜索，以选择代价最低的状态。

如下图所示，对于输入的SQL语句，CBQT在Query Block 1和Query Block 2中收集到基于代价的查询变换A和B，这两个变换组成的状态空间有：

  • None：均不作变换。

  • [1，A]：Query Block 1做变换A。

  • [1，B]：Query Block 1做变换B。

  • [2，A]：Query Block 2做变换A。

CBQT会依次尝试应用状态空间的各种变换，在默认的搜索策略linear中，状态[1，B]能使计划更优，因此在生成Plan4时，也会保持[1，B]的应用。

image

前提条件

支持的PolarDB PostgreSQL版（兼容Oracle）的版本如下：

  • Oracle语法兼容 2.0（内核小版本2.0.14.13.28.0及以上）

  • Oracle语法兼容 1.0（内核小版本2.0.11.15.44.0及以上）

说明

您可在控制台查看内核小版本号，也可以通过SHOW polardb_version;语句查看。如未满足内核小版本要求，请升级内核小版本。

使用说明

PolarDB PostgreSQL版（兼容Oracle）提供以下参数用于控制CBQT行为：

说明

在内核小版本2.0.14.15.29.0及以上集群，支持直接在控制台修改相关参数，您也可以连接数据库集群手动修改。

参数名

描述

参数名

描述

polar_enable_cbqt

是否开启CBQT功能，取值如下：

  • off（默认）：关闭CBQT功能。

  • on：开启CBQT功能。

polar_cbqt_cost_threshold

设置开启CBQT代价的阈值，取值范围：[0, +∞)，默认值为50000。只有原始计划的代价超过阈值才会启用CBQT。

polar_cbqt_strategy

设置CBQT状态空间的搜索策略，取值如下：

  • linear（默认）：线性搜索。在该策略下，会依次对比每个状态应用前后的代价，选择更优的计划。

  • twophase：两遍搜索。在该策略下，只会对比所有状态都应用或者都不应用的代价，选择更优的计划。

polar_cbqt_iteration_limit

设置CBQT迭代次数。取值范围：[1, +∞)，默认值为10。

迭代次数越多选择出最优计划的可能性越大，但优化时间会更长。反之，选择出最优计划的可能性越小，优化时间更短。

目前已经支持基于代价的查询改写功能如下：

功能开关

描述

功能开关

描述

polar_cbqt_convert_or_to_union_all_mode

OR子句转UNION ALL：尝试将合适的OR子句转换成UNION ALL的形式，提升查询的效率。

polar_cbqt_pushdown_sublink

子连接下推：尝试将子连接下推到子查询中，利用子查询中的索引生成参数化路径，提升查询的效率。

示例

此处以子连接下推功能为例，介绍CBQT功能及各个参数的使用。

  1. 准备测试表并插入数据。

    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;

  2. 关闭CBQT功能，并开启子连接下推功能。此时子连接下推功能并不会开启，t_big表仍然需要全表扫描，执行效率很低。

    -- 关闭CBQT功能
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)

  3. 开启CBQT功能，并开启子连接下推功能，子连接下推功能正确开启。a in (select a from t_small)子句被下推到子查询中，可以利用连接条件为t_big表生成参数化路径，扫描的数据大大减少，执行时间明显缩短。

    -- 开启CBQT功能
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)

  4. 当SQL语句不满足CBQT的代价阈值时，不会启用CBQT功能，仍然会选择原始的计划。例如，原始计划的代价为59511.17，设置polar_cbqt_cost_threshold参数为500000时：

    -- 开启CBQT功能
SET polar_enable_cbqt to on;

--- 设置CBQT代价阈值
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)

  5. 设置CBQT状态空间的搜索策略。以下示例中有两个能够下推的子连接，但只有第二个子连接下推才是更优的。

    • 设置polar_cbqt_strategy为linear，即线性搜索策略，CBQT选择了最优的计划。

      -- 开启CBQT功能
SET polar_enable_cbqt to on;

--- 设置CBQT状态空间的搜索策略
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)

    • 设置polar_cbqt_strategy为twophase，即两遍搜索策略，两个子连接都选择了下推。

      -- 开启CBQT功能
SET polar_enable_cbqt to on;

--- 设置CBQT状态空间的搜索策略
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)

  6. 限制CBQT迭代次数。设置polar_cbqt_iteration_limit参数为1，限制CBQT的迭代次数为1。对于上述场景，即使已知第二个子连接下推会使计划更优，但由于迭代次数的限制，并不会进行尝试。

    -- 开启CBQT功能
SET polar_enable_cbqt to on;

--- 设置CBQT状态空间的搜索策略
SET polar_cbqt_strategy to twophase;

--- 设置CBQT迭代次数
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)
上一篇：计划固定（STATEMENT OUTLINE）下一篇：OR子句转UNION ALL
  • 本页导读 （1）
  • 背景
  • 前提条件
  • 使用说明
  • 示例
AI助理

点击开启售前

在线咨询服务

你好，我是AI助理

可以解答问题、推荐解决方案等