AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版 自研内核能力 优化器 子连接下推

子连接下推

更新时间：
产品详情
我的收藏

PolarDB PostgreSQL版支持子连接下推的查询改写功能，能够提升含有IN/ANY子句的SQL的执行效率。

背景

在PostgreSQL中，ANY类型的子连接（对应IN和ANY子句）会被尝试上拉为半连接（semi join）。然而，如果与之连接的表是一个无法上拉的子查询，PostgreSQL不支持为其生成参数化路径，只能作为一个整体独立执行，当子查询中的数据量较大时，可能导致整个SQL执行效率显著降低。

例如，以下SQL由于子查询中存在GROUP BY语句导致无法上拉，其执行时间主要在t_big表的扫描和排序上，并且随着t_big表规模的增大，执行时间亦相应延长。

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)

如果能够将ANY类型的子连接下推至子查询中，则可以利用子查询中的索引，从而提升执行效率。

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)

前提条件

支持的PolarDB PostgreSQL版的版本如下：

  • PostgreSQL 14（内核小版本2.0.14.13.28.0及以上）

  • PostgreSQL 11（内核小版本2.0.11.15.44.0及以上）

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本号：

SHOW polardb_version;

如未满足内核小版本要求，请升级版本。

应用场景

子连接下推功能适用于IN/ANY子句引用的包含GROUP BY语句的子查询，并且该子查询涉及一个大表。将IN/ANY子句下推至子查询中，能够利用大表的索引，从而减少对大表数据的访问。

子连接下推功能存在以下限制：

  • IN/ANY子句必须引用一个有GROUP BY语句的子查询，否则当前原生PostgreSQL将直接生成参数化路径，不必使用子连接下推功能。

  • IN/ANY子句的列必须包含在GROUP BY语句的列中，否则下推后的SQL不等价。

  • 当前查询块不能存在外连接，以确保经过下推后的SQL是等价的。

  • 目前只支持单列的场景。例如：a in (select a from t)或者a = any(select a from t)。

  • 目前只支持SELECT和CREATE TABLE AS语句。

使用说明

子连接下推功能受参数控制，相关的参数名称及其作用如下所示：

参数名称

描述

polar_cbqt_pushdown_sublink

用于控制子连接下推功能的开关，取值如下：

  • OFF（默认）：关闭子连接下推功能。

  • ON：开启子连接下推功能，由基于代价的查询变换（CBQT）框架根据代价决定是否应用下推。

  • FORCE：强制开启子连接下推功能，不受CBQT框架控制。该参数值一般用于 HINT，不建议全局设置。

示例

数据准备

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.a = t_small.a连接条件无法作为参数化路径进行下推，导致t_big表需要进行全表扫描，执行效率很低。

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)

通过CBQT开启子连接下推

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

说明

原计划的代价需要大于参数polar_cbqt_cost_threshold，“子连接下推”查询改写才会应用。

-- 开启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.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)

通过HINT开启子连接下推

通过HINT的方式，在SQL级别强制开启子连接下推功能，同样将a in (select a from t_small)子句下推到子查询中，可以利用连接条件为t_big表生成参数化路径，扫描的数据大大减少，执行时间明显缩短。

-- polar_cbqt_pushdown_sublink参数默认关闭
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)

上一篇：OR子句转UNION ALL下一篇：数据库自治服务