PostgreSQL17优化器改进(5)GROUP BY优化

PostgreSQL17优化器改进(5)GROUP BY优化

我们知道GROUP BY聚集有两种常见实现方式,一种是基于哈希表,我们称为哈希聚集(Hash agg);另一种则要求先对元组进行排序,我们称为分组聚集(Group Agg)。本次在PostgreSQL17版本中描述的根据索引或order by排序来优化GROUP BY列的排序,同时新增了enable_group_by_reordering参数进行控制,默认值为on,这里GROUP BY优化其实就是对分组聚集实现方式的优化。

创建测试用例表

CREATE TABLE btg AS SELECT
  i % 100 AS x,
  i % 100 AS y,
  'abc' || i % 10 AS z,
  i AS w
FROM generate_series(1,10000) AS i;
CREATE INDEX abc ON btg(x,y);
ANALYZE btg;
--为了使得执行计划走排序聚集,需要禁用该参数enable_hashagg
SET enable_hashagg=off;
SET max_parallel_workers= 0;
SET max_parallel_workers_per_gather = 0;

GROUP BY存在的问题

针对PostgreSQL17优化器对GROUP BY优化的场景,我们先来查看PostgreSQL16.3版本的执行计划,在案例中GROUP BY的列和order by列以不同的组合执行,观察是否利可以用索引扫描排序来避免Sort操作。

1、GROUP BY顺序和索引顺序比较

--GROUP BY顺序和索引顺序一致
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y;
               QUERY PLAN               
----------------------------------------
 GroupAggregate
   Group Key: x, y
   ->  Index Only Scan using abc on btg
(3 rows)
--GROUP BY顺序和索引顺序不一致
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x;
         QUERY PLAN          
-----------------------------
 GroupAggregate
   Group Key: y, x
   ->  Sort
         Sort Key: y, x
         ->  Seq Scan on btg
(5 rows)

2、GROUP BY和ORDER BY的顺序与索引顺序比较

  • 当order by顺序与索引顺序一致时
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x order by x,y;
               QUERY PLAN               
----------------------------------------
 GroupAggregate
   Group Key: x, y
   ->  Index Only Scan using abc on btg
(3 rows)
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y order by x,y;
               QUERY PLAN               
----------------------------------------
 GroupAggregate
   Group Key: x, y
   ->  Index Only Scan using abc on btg
(3 rows)

通过执行计划我们也可以很明显的看出,当order by的顺序和索引顺序一致的时候,无论 GROUP BY列的顺序是什么样的,都不影响执行计划结果。

  • 当order by顺序与索引顺序不一致时
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y order by y,x;
         QUERY PLAN          
-----------------------------
 GroupAggregate
   Group Key: y, x
   ->  Sort
         Sort Key: y, x
         ->  Seq Scan on btg
(5 rows)

testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x order by y,x;
         QUERY PLAN          
-----------------------------
 GroupAggregate
   Group Key: y, x
   ->  Sort
         Sort Key: y, x
         ->  Seq Scan on btg
(5 rows)

通过执行计划我们可以看出,当order by的顺序和索引顺序不一致的时候,无论 GROUP BY列的顺序是什么样的,在扫描表的时候无法使用到索引,因此使用的是顺序扫描的方式。

下面我们来对上面PostgreSQL16.3版本group by的问题简单的汇总一下

  • 在语句中没有order by子句时,GROUP BY顺序和索引顺序不一致时,未使用到索引
  • 当语句中有order by子句时且顺序与索引顺序不一致时,未使用到索引

其实对于上面的两种情况,问题原因是一样的,就是对于Group Agg,只是按照Group By中指定列的顺序和索引列的顺序进行比较keys,因此无法使用索引。

但是对于Group Agg,我们只是按照查询中指定的顺序比较键

解决方案

对于上述的问题,解决思路就是利用了group by并不意味着必须固定的顺序排序,而且可以以任意的顺序排序,而不影响最终的结果。下面我们来验证一下结果

testdb=# SELECT x,y, count(*) FROM btg where x<10 GROUP BY y,x ;
 x | y | count 
---+---+-------
 0 | 0 |   100
 1 | 1 |   100
 2 | 2 |   100
 3 | 3 |   100
 4 | 4 |   100
 5 | 5 |   100
 6 | 6 |   100
 7 | 7 |   100
 8 | 8 |   100
 9 | 9 |   100
(10 rows)

testdb=# SELECT x,y, count(*) FROM btg where x<10 GROUP BY x,y ;
 x | y | count 
---+---+-------
 0 | 0 |   100
 1 | 1 |   100
 2 | 2 |   100
 3 | 3 |   100
 4 | 4 |   100
 5 | 5 |   100
 6 | 6 |   100
 7 | 7 |   100
 8 | 8 |   100
 9 | 9 |   100
(10 rows)

测试的结果是和预期是一样的,group by顺序并不会影响最终数据的结果。

优化场景

GROUP BY顺序和索引顺序不一致

--PostgreSQL17版本优化后的执行计划
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x;
               QUERY PLAN               
----------------------------------------
 GroupAggregate
   Group Key: x, y
   ->  Index Only Scan using abc on btg
(3 rows)

从PostgreSQL17版本执行计划我们也可以看到,即使GROUP BY顺序和索引顺序不一致,也可以使用到创建的索引;另外在执行计划中我们也可以看到Group Key的顺序是以索引的顺序来分组的。

ORDER BY顺序与索引顺序不一致时

testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y order by y,x;
                  QUERY PLAN                  
----------------------------------------------
 Sort
   Sort Key: y, x
   ->  GroupAggregate
         Group Key: x, y
         ->  Index Only Scan using abc on btg
(5 rows)

testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x order by y,x;
                  QUERY PLAN                  
----------------------------------------------
 Sort
   Sort Key: y, x
   ->  GroupAggregate
         Group Key: x, y
         ->  Index Only Scan using abc on btg
(5 rows)

从PostgreSQL17版本执行计划我们也可以看到,优化后的执行计划中可知,即使ORDER BY顺序和索引顺序不一致,也可以使用到创建的索引;另外在执行计划中我们也可以看到Group Key的顺序同样是以索引的顺序来分组的,与sql中指定的分组顺序没有关系。

适用于增量排序

--该sql语句在PostgreSQL16.3版本的执行计划和PostgreSQL17是一致的
testdb=# explain (COSTS OFF) SELECT x,y FROM btg GROUP BY x,y,z,w;
               QUERY PLAN                
-----------------------------------------
 Group
   Group Key: x, y, z, w
   ->  Incremental Sort
         Sort Key: x, y, z, w
         Presorted Key: x, y
         ->  Index Scan using abc on btg
(6 rows)
--PostgreSQL17优化后执行计划
testdb=# explain (COSTS OFF) SELECT x,y FROM btg GROUP BY z,y,w,x;
               QUERY PLAN                
-----------------------------------------
 Group
   Group Key: x, y, z, w
   ->  Incremental Sort
         Sort Key: x, y, z, w
         Presorted Key: x, y
         ->  Index Scan using abc on btg
(6 rows)
--PostgreSQL17优化后执行计划
testdb=# explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,z,x,y;
               QUERY PLAN                
-----------------------------------------
 Group
   Group Key: x, y, w, z
   ->  Incremental Sort
         Sort Key: x, y, w, z
         Presorted Key: x, y
         ->  Index Scan using abc on btg
(6 rows)
--PostgreSQL17优化后执行计划
testdb=# explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,x,z,y;
               QUERY PLAN                
-----------------------------------------
 Group
   Group Key: x, y, w, z
   ->  Incremental Sort
         Sort Key: x, y, w, z
         Presorted Key: x, y
         ->  Index Scan using abc on btg
(6 rows)

从PostgreSQL17版本执行计划我们也可以看到,当语句中没有order by子句时,不论 GROUP BY中的分组以任何顺序排序,都可以使用到索引;另外在执行计划中我们也可以看到Presorted Key的顺序就是索引的顺序;Sort Key是以Presorted Key的顺序为准,并添加剩余的字段;Group Key的顺序就是增量排序的顺序。

子查询

--PostgreSQL16.3执行计划
testdb=# explain (COSTS OFF) SELECT x,y
testdb-# FROM (SELECT * FROM btg ORDER BY x,y,w,z) AS q1
testdb-# GROUP BY (w,x,z,y);
                        QUERY PLAN                        
----------------------------------------------------------
 Group
   Group Key: q1.w, q1.x, q1.z, q1.y
   ->  Sort
         Sort Key: q1.w, q1.x, q1.z, q1.y
         ->  Subquery Scan on q1
               ->  Incremental Sort
                     Sort Key: btg.x, btg.y, btg.w, btg.z
                     Presorted Key: btg.x, btg.y
                     ->  Index Scan using abc on btg
(9 rows)
--PostgreSQL17优化后执行计划
testdb=# explain (COSTS OFF) SELECT x,y
testdb-# FROM (SELECT * FROM btg ORDER BY x,y,w,z) AS q1
testdb-# GROUP BY (w,x,z,y);
                  QUERY PLAN                  
----------------------------------------------
 Group
   Group Key: btg.x, btg.y, btg.w, btg.z
   ->  Incremental Sort
         Sort Key: btg.x, btg.y, btg.w, btg.z
         Presorted Key: btg.x, btg.y
         ->  Index Scan using abc on btg
(6 rows)
============================================第二条sql===========================================
--PostgreSQL16.3执行计划
testdb=# explain (COSTS OFF) SELECT x,y
testdb-# FROM (SELECT * FROM btg ORDER BY x,y,w,z LIMIT 100) AS q1
testdb-# GROUP BY (w,x,z,y);
                           QUERY PLAN                           
----------------------------------------------------------------
 Group
   Group Key: q1.w, q1.x, q1.z, q1.y
   ->  Sort
         Sort Key: q1.w, q1.x, q1.z, q1.y
         ->  Subquery Scan on q1
               ->  Limit
                     ->  Incremental Sort
                           Sort Key: btg.x, btg.y, btg.w, btg.z
                           Presorted Key: btg.x, btg.y
                           ->  Index Scan using abc on btg
(10 rows)
--PostgreSQL17优化后执行计划
testdb=# explain (COSTS OFF) SELECT x,y
testdb-# FROM (SELECT * FROM btg ORDER BY x,y,w,z LIMIT 100) AS q1
testdb-# GROUP BY (w,x,z,y);
                     QUERY PLAN                     
----------------------------------------------------
 Group
   Group Key: btg.x, btg.y, btg.w, btg.z
   ->  Limit
         ->  Incremental Sort
               Sort Key: btg.x, btg.y, btg.w, btg.z
               Presorted Key: btg.x, btg.y
               ->  Index Scan using abc on btg
(7 rows)

通过以上对子查询执行计划的对比,我们可以看到执行计划的差异还是很明显的,对于子查询外面的GROUP BY 即使指定的顺序与索引不一致,也用到了索引。

总结

总的来说,当使用多列GROUP BY子句计算查询时,如果我们将GROUP BY子句的顺序与order BY排序子句或索引顺序保持一致,则可以最小化或避免排序操作。

  • 9
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

墨竹~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值