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的顺序与索引顺序比较
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列的顺序是什么样的，都不影响执行计划结果。
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排序子句或索引顺序保持一致，则可以最小化或避免排序操作。
