PostgreSQL17优化器改进(4)允许UNION(没有ALL)使用MergeAppend

PostgreSQL17优化器改进(4)允许UNION(没有ALL)使用MergeAppend

UNION存在的问题

到PostgreSQL16.3版本为止，UNION执行计划通常不是最优的，优化器有两种处理方法：

1. 优化器只考虑使用Append节点并通过使用Hash Aggregate，Append -> Hash Aggregate
2. 对整个Append结果排序并通过unique操作符运行使结果唯一，Append -> Sort -> Unique

目前这两种方法总是需要从union子查询中读取所有行。

解决方案

在PostgreSQL17版本中，官网通过调整union优化器，使它可以请求每个子查询并以目标列表顺序产生结果，以便将这些结果通过Merge Appended合并在一起，并使用Unique节点使其唯一。因为union子节点可以使用类似于b树索引 and/or Merge Joins为top-level UNION提供预先排序的输入，所以性能有了显著提高。如果top-level UNION包含一个LIMIT节点，该节点将输出行限制为结合行的一个小子集，因为可以使用廉价的启动计划，那么这一点特别好。

执行计划对比

创建测试表

set min_parallel_table_scan_size = '1kB';
set min_parallel_index_scan_size = '1kB';
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set max_parallel_workers_per_gather = 2;
create table t (a int, b int, c int);
insert into t select mod(i,10),mod(i,10),i from generate_series(1,10000) s(i);
create index on t (a);
analyze t;

PostgreSQL16.3的执行计划

优化器只考虑使用Append节点并通过使用Hash Aggregate

由于数据库的参数enable_hashagg默认值是on，含义是允许或禁用查询规划器使用哈希聚集计划类型，因此正常情况下生成的是下面的执行计划。

testdb=# explain (costs off) select count(*) from( select a from t union select c from t ) ss;
             QUERY PLAN              
-------------------------------------
 Aggregate
   ->  HashAggregate
         Group Key: t.a
         ->  Append
               ->  Seq Scan on t
               ->  Seq Scan on t t_1
(6 rows)

对整个Append结果排序并通过unique操作符运行使结果唯一

如果要生成该执行计划，需要修该enable_hashagg为off

postgres=# set enable_hashagg to off;
SET
postgres=# explain (costs off) select count(*) from( select a from t union select c from t ) ss;
                QUERY PLAN                 
-------------------------------------------
 Aggregate
   ->  Unique
         ->  Sort
               Sort Key: t.a
               ->  Append
                     ->  Seq Scan on t
                     ->  Seq Scan on t t_1

PostgreSQL17的执行计划

优化器只考虑使用Append节点并通过使用Hash Aggregate

由于数据库的参数enable_hashagg默认值是on，含义是允许或禁用查询规划器使用哈希聚集计划类型，因此正常情况下生成的是下面的执行计划

testdb=# explain (costs off) select count(*) from( select a from t union select c from t ) ss;
                     QUERY PLAN                     
----------------------------------------------------
 Aggregate
   ->  HashAggregate
         Group Key: t.a
         ->  Gather
               Workers Planned: 2
               ->  Parallel Append
                     ->  Parallel Seq Scan on t
                     ->  Parallel Seq Scan on t t_1
(8 rows)

当前执行计划的执行路径，和PostgreSQL16.3执行计划路径，相差不大，优化器还是使用Append节点并通过使用Hash Aggregate，Append -> Hash Aggregate

对整个Append结果排序并通过unique操作符运行使结果唯一

如果要生成该执行计划，需要修该enable_hashagg为off

testdb=# set enable_hashagg to off;
SET
testdb=# explain (costs off) select count(*) from( select a from t union select c from t ) ss;
                        QUERY PLAN                        
----------------------------------------------------------
 Aggregate
   ->  Unique
         ->  Merge Append
               Sort Key: t.a
               ->  Index Only Scan using t_a_idx on t
               ->  Gather Merge
                     Workers Planned: 2
                     ->  Sort
                           Sort Key: t_1.c
                           ->  Parallel Seq Scan on t t_1
(10 rows)

总结

根据官网发布新优化器的描述和对PostgreSQL16.3和PostgreSQL17版本的对比测试，针对官网描述允许UNION(没有ALL)使用MergeAppend，应该具体是对PostgreSQL16.3版本中对于UNION执行计划的其中一个分支，即对整个Append结果排序并通过unique操作符运行使结果唯一（Append -> Sort -> Unique）执行路径进行了优化，优化后的路径应该为Sort->Merge Append-> Unique，需要得到该结果的前提是需要设置enable_hashagg为off。UNION执行计划的另外一个分支，实际上也有优化，但是本文不做说明，后续文章就继续分析。

– / END / –

如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！