关联子查询上拉_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了关联子查询上拉功能的背景及使用方法等内容。

前提条件

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

Oracle 2.0（内核小版本2.0.14.11.0及以上）

说明

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

SHOW polar_version;

背景信息

PostgreSQL优化器中使用SubLink来表示表达式中出现的子查询以及相关运算符的组合。SubLink的类型如下：

EXISTS_SUBLINK：用于实现EXISTS (SELECT ...)子查询。
ALL_SUBLINK：用于实现ALL (SELECT ...)子查询。
ANY_SUBLINK：用于实现ANY (SELECT ...)子查询以及IN (SELECT ...)子查询。

优化器通常会对带有关联查询的ANY/IN/EXISTS/NOT EXISTS子查询尝试上拉，使其能够与父查询被共同优化为带有半连接（Semi Join）或反连接（Anti Join）的执行计划，从而提升查询性能。其中，对于ANY_SUBLINK，如果子查询引用了上一级父查询中的变量，将不会进行子查询上拉，从而错失了与父查询进行共同优化的机会，子查询只能够作为一个独立的个体被优化，导致SQL执行时间大大增加。

PolarDB PostgreSQL版（兼容Oracle）可以通过参数控制ANY_SUBLINK关联子查询上拉。对于带有关联查询的IN/ANY子查询，即使引用了上一级父查询中的变量，也能够进行子查询上拉，从而增大优化器的搜索空间，生成更好的执行计划。

使用方法

polar_enable_pullup_with_lateral参数用于是否开启ANY_SUBLINK关联子查询上拉功能，取值如下：

ON（默认）：开启ANY_SUBLINK关联子查询上拉。
OFF：关闭ANY_SUBLINK关联子查询上拉。

示例

准备数据。

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, 1 FROM generate_series(1, 100000) i;
CREATE TABLE t2 AS SELECT * FROM t1;

关闭关联子查询上拉功能后，执行计划和时间。

=> SET polar_enable_pullup_with_lateral TO OFF;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t1 (actual time=67.631..1641827.119 rows=100000 loops=1)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result (actual time=0.005..13.124 rows=50000 loops=100000)
           One-Time Filter: (t1.b = 1)
           ->  Seq Scan on t2 (actual time=0.005..7.718 rows=50000 loops=100000)
                 Filter: (b = 1)
 Planning Time: 0.145 ms
 Execution Time: 1641847.702 ms
(9 rows)

开启关联子查询上拉功能后，执行计划和时间。

=> SET polar_enable_pullup_with_lateral TO ON;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Semi Join (actual time=64.783..173.482 rows=100000 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1 (actual time=0.016..25.440 rows=100000 loops=1)
         Filter: (b = 1)
   ->  Hash (actual time=64.550..64.551 rows=100000 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2976kB
         ->  Seq Scan on t2 (actual time=0.010..30.330 rows=100000 loops=1)
               Filter: (b = 1)
 Planning Time: 0.195 ms
 Execution Time: 178.050 ms
(10 rows)

通过以上示例，可以看到，子查询上拉后，子查询与父查询被共同优化为一个半连接，位于子查询中的过滤条件可以极大地过滤父查询的结果，因此执行时间得到了非常明显的缩短。如果子查询没有上拉，是无法过滤父查询中要返回的行的。