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

embedded/2024/9/23 6:26:00/

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 SELECTi % 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               
----------------------------------------GroupAggregateGroup 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          
-----------------------------GroupAggregateGroup Key: y, x->  SortSort 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               
----------------------------------------GroupAggregateGroup 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               
----------------------------------------GroupAggregateGroup 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          
-----------------------------GroupAggregateGroup Key: y, x->  SortSort 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          
-----------------------------GroupAggregateGroup Key: y, x->  SortSort 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 |   1001 | 1 |   1002 | 2 |   1003 | 3 |   1004 | 4 |   1005 | 5 |   1006 | 6 |   1007 | 7 |   1008 | 8 |   1009 | 9 |   100
(10 rows)testdb=# SELECT x,y, count(*) FROM btg where x<10 GROUP BY x,y ;x | y | count 
---+---+-------0 | 0 |   1001 | 1 |   1002 | 2 |   1003 | 3 |   1004 | 4 |   1005 | 5 |   1006 | 6 |   1007 | 7 |   1008 | 8 |   1009 | 9 |   100
(10 rows)

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

优化场景

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

--PostgreSQL17版本优化后的执行计划
testdb=# EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x;QUERY PLAN               
----------------------------------------GroupAggregateGroup 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                  
----------------------------------------------SortSort Key: y, x->  GroupAggregateGroup 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                  
----------------------------------------------SortSort Key: y, x->  GroupAggregateGroup 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                
-----------------------------------------GroupGroup Key: x, y, z, w->  Incremental SortSort Key: x, y, z, wPresorted 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                
-----------------------------------------GroupGroup Key: x, y, z, w->  Incremental SortSort Key: x, y, z, wPresorted 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                
-----------------------------------------GroupGroup Key: x, y, w, z->  Incremental SortSort Key: x, y, w, zPresorted 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                
-----------------------------------------GroupGroup Key: x, y, w, z->  Incremental SortSort Key: x, y, w, zPresorted 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                        
----------------------------------------------------------GroupGroup Key: q1.w, q1.x, q1.z, q1.y->  SortSort Key: q1.w, q1.x, q1.z, q1.y->  Subquery Scan on q1->  Incremental SortSort Key: btg.x, btg.y, btg.w, btg.zPresorted 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                  
----------------------------------------------GroupGroup Key: btg.x, btg.y, btg.w, btg.z->  Incremental SortSort Key: btg.x, btg.y, btg.w, btg.zPresorted 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                           
----------------------------------------------------------------GroupGroup Key: q1.w, q1.x, q1.z, q1.y->  SortSort Key: q1.w, q1.x, q1.z, q1.y->  Subquery Scan on q1->  Limit->  Incremental SortSort Key: btg.x, btg.y, btg.w, btg.zPresorted 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                     
----------------------------------------------------GroupGroup Key: btg.x, btg.y, btg.w, btg.z->  Limit->  Incremental SortSort Key: btg.x, btg.y, btg.w, btg.zPresorted Key: btg.x, btg.y->  Index Scan using abc on btg
(7 rows)

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

总结

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


http://www.ppmy.cn/embedded/52767.html

相关文章

oracle-定时器(job)

oracle-定时器(job)

--1分钟运行一次定时任务。sysdate为了定时任务即可生效。 DECLARE JOB NUMBER; BEGIN DBMS_JOB.SUBMIT(JOB,P_HJZ_HJZ_PJ_DDYTKAPB_INIT_JOB;,SYSDATE,sysdate1/24/60); COMMIT; END; / select * from user_jobs; --删除 begin DBMS_JOB.broken (462, false); DBM…
阅读更多...
安全技术和防火墙

安全技术和防火墙

安全技术和防火墙 安全技术 入侵检测系统&#xff1a;特点是不阻断网络访问&#xff0c;主要提供报警和事后监督&#xff0c;不主动介入&#xff0c;默默看着你&#xff08;监控&#xff09; 入侵防御系统&#xff1a;透明模式工作&#xff0c;数据包&#xff0c;网络监控&am…
阅读更多...
【博士每天一篇文献-算法】Fearnet Brain-inspired model for incremental learning

【博士每天一篇文献-算法】Fearnet Brain-inspired model for incremental learning

阅读时间&#xff1a;2023-12-16 1 介绍 年份&#xff1a;2017 作者&#xff1a;Ronald Kemker&#xff0c;美国太空部队&#xff1b;Christopher Kanan&#xff0c;罗切斯特大学 期刊&#xff1a; arXiv preprint 引用量&#xff1a;520 Kemker R, Kanan C. Fearnet: Brain-…
阅读更多...
hadoop离线与实时的电影推荐系统-计算机毕业设计源码10338

hadoop离线与实时的电影推荐系统-计算机毕业设计源码10338

摘 要 随着互联网与移动互联网迅速普及&#xff0c;网络上的电影娱乐信息数量相当庞大&#xff0c;人们对获取感兴趣的电影娱乐信息的需求越来越大,个性化的离线与实时的电影推荐系统 成为一个热门。然而电影信息的表示相当复杂&#xff0c;己有的相似度计算方法与推荐算法都各…
阅读更多...
虚拟化技术（一）

虚拟化技术（一）

目录 一、虚拟化技术简介二、服务器虚拟化&#xff08;一&#xff09;服务器虚拟化的层次&#xff08;二&#xff09;服务器虚拟化的底层实现&#xff08;三&#xff09;虚拟机迁移&#xff08;四&#xff09;隔离技术&#xff08;五&#xff09;案例分析 一、虚拟化技术简介 虚…
阅读更多...
MaxKB-无需代码，30分钟创建基于大语言模型的本地知识库问答系统

MaxKB-无需代码，30分钟创建基于大语言模型的本地知识库问答系统

简介 MaxKB 是一个基于大语言模型 (LLM) 的智能知识库问答系统。它能够帮助企业高效地管理知识&#xff0c;并提供智能问答功能。想象一下&#xff0c;你有一个虚拟助手&#xff0c;可以回答各种关于公司内部知识的问题&#xff0c;无论是政策、流程&#xff0c;还是技术文档&a…
阅读更多...
Claude 3.5重磅发布，力压 ChatGPT 速度翻倍免费可用

Claude 3.5重磅发布，力压 ChatGPT 速度翻倍免费可用

ai科技圈又掀桌子了&#xff01;这次的主角是Cloude最新发布的3.5 Sonnet 版本&#xff0c;登录Cloude官网和APP就可以直接免费体验&#xff01; 对于还不了解Cloud的小伙伴先来科普一下&#xff0c;从Cloud 3.0版本起&#xff0c;他就分为Opus、Sonnet和Haiku三个系列。Opus参…
阅读更多...
RAMS 如何利用高精度地形数据

RAMS 如何利用高精度地形数据

1. 获取高精度地形数据 通常使用的高精度地形数据源包括： SRTM (Shuttle Radar Topography Mission)：提供全球覆盖的1弧秒（约30米）到3弧秒（约90米）的分辨率。ASTER (Advanced Spaceborne Thermal Emission and Reflection Radiometer)：提供全球覆盖的30米分辨率的DEM。…
阅读更多...
最新文章

Copyright @ 2022~2023