关联子查询上拉

前提条件

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

PostgreSQL 14（内核小版本14.8.11.0及以上）。

select 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版可以通过参数控制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)

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