PostgreSQL DBA(34) - (HashAggregate vs GroupAggregate)

最新推荐文章于 2024-04-23 10:32:10 发布
最新推荐文章于 2024-04-23 10:32:10 发布
阅读量671 收藏
点赞数
文章标签： 数据库

PostgreSQL数据库中,聚合函数如max/min/count等有两种实现方式:HashAggregate与GroupAggregate.本节简单介绍这两者的区别.
案例一

首先我们看一个案例:
测试表: 


drop table  if exists t_agg;
create table t_agg(bh varchar(20),c1 int,c2 int,c3 int,c4 int,c5 int,c6 int);
insert into t_agg select 'GZ01',col,col,col,col,col,col from generate_series(1,100000) as col;
insert into t_agg select 'GZ02',col,col,col,col,col,col from generate_series(1,100000) as col;
insert into t_agg select 'GZ03',col,col,col,col,col,col from generate_series(1,100000) as col;
insert into t_agg select 'GZ04',col,col,col,col,col,col from generate_series(1,100000) as col;
insert into t_agg select 'GZ05',col,col,col,col,col,col from generate_series(1,100000) as col;

执行查询: 


testdb=# -- 禁用并行
testdb=# set max_parallel_workers_per_gather=0;
SET
testdb=# explain verbose select bh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5) from t_agg group by bh;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=22427.00..22427.05 rows=5 width=45)
   Output: bh, min(c1), max(c1), min(c2), max(c2), min(c3), max(c3), min(c4), max(c4), min(c5), max(c5)
   Group Key: t_agg.bh
   ->  Seq Scan on public.t_agg  (cost=0.00..8677.00 rows=500000 width=25)
         Output: bh, c1, c2, c3, c4, c5, c6
(5 rows)

PG的优化器选择了HashAggregate.
下面禁用HashAggregate,优化器只能选择GroupAggregate.可以看到两者的总成本比较:22427.05 vs 82968.97 


testdb=# set enable_hashagg = off;
SET
testdb=# explain verbose select bh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5) from t_agg group by bh;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=67968.92..82968.97 rows=5 width=45)
   Output: bh, min(c1), max(c1), min(c2), max(c2), min(c3), max(c3), min(c4), max(c4), min(c5), max(c5)
   Group Key: t_agg.bh
   ->  Sort  (cost=67968.92..69218.92 rows=500000 width=25)
         Output: bh, c1, c2, c3, c4, c5
         Sort Key: t_agg.bh
         ->  Seq Scan on public.t_agg  (cost=0.00..8677.00 rows=500000 width=25)
               Output: bh, c1, c2, c3, c4, c5
(8 rows)

案例二
下面用一个宽表来进行测试:分组键值很少,但聚合列很多 


drop table  if exists t_agg_width;
create table t_agg_width
(bh varchar(20)
,c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int
,c11 int,c12 int,c13 int,c14 int,c15 int,c16 int,c17 int,c18 int,c19 int
,c21 int,c22 int,c23 int,c24 int,c25 int,c26 int,c27 int,c28 int,c29 int
,c31 int,c32 int,c33 int,c34 int,c35 int,c36 int,c37 int,c38 int,c39 int);
insert into t_agg_width 
select 'GZ01'
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
from generate_series(1,100000) as col;
insert into t_agg_width 
select 'GZ02'
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
from generate_series(1,100000) as col;
insert into t_agg_width 
select 'GZ03'
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
from generate_series(1,100000) as col;
insert into t_agg_width 
select 'GZ04'
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
,col,col,col,col,col,col,col,col,col 
from generate_series(1,100000) as col;
-- 禁用hashagg
set enable_hashagg = off;
-- 禁用并行
set max_parallel_workers_per_gather=0;
select bh
,min(c1),min(c2) ,min(c3) ,min(c4) ,min(c5) ,min(c6) ,min(c7) ,min(c8) ,min(c9)
,min(c11),min(c12) ,min(c13) ,min(c14) ,min(c15) ,min(c16) ,min(c17) ,min(c18) ,min(c19)
,min(c21),min(c22) ,min(c23) ,min(c24) ,min(c25) ,min(c26) ,min(c27) ,min(c28) ,min(c29)
,min(c31),min(c32) ,min(c33) ,min(c34) ,min(c35) ,min(c36) ,min(c37) ,min(c38) ,min(c39)
from t_agg_width group by bh;

在这种情况下,优化器仍会选择Hash 


testdb=# explain verbose select bh
testdb-# ,min(c1),min(c2) ,min(c3) ,min(c4) ,min(c5) ,min(c6) ,min(c7) ,min(c8) ,min(c9)
testdb-# ,min(c11),min(c12) ,min(c13) ,min(c14) ,min(c15) ,min(c16) ,min(c17) ,min(c18) ,min(c19)
testdb-# ,min(c21),min(c22) ,min(c23) ,min(c24) ,min(c25) ,min(c26) ,min(c27) ,min(c28) ,min(c29)
testdb-# ,min(c31),min(c32) ,min(c33) ,min(c34) ,min(c35) ,min(c36) ,min(c37) ,min(c38) ,min(c39)
testdb-# from t_agg_width group by bh;
                                                    QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=49889.00..49889.04 rows=4 width=149)
   Output: bh, min(c1), min(c2), min(c3), min(c4), min(c5), min(c6), min(c7), min(c8), min(c9), min(c11), min(c12), min(c13),
 min(c14), min(c15), min(c16), min(c17), min(c18), min(c19), min(c21), min(c22), min(c23), min(c24), min(c25), min(c26), min(
c27), min(c28), min(c29), min(c31), min(c32), min(c33), min(c34), min(c35), min(c36), min(c37), min(c38), min(c39)
   Group Key: t_agg_width.bh
   ->  Seq Scan on public.t_agg_width  (cost=0.00..12889.00 rows=400000 width=149)
         Output: bh, c1, c2, c3, c4, c5, c6, c7, c8, c9, c11, c12, c13, c14, c15, c16, c17, c18, c19, c21, c22, c23, c24, c25
, c26, c27, c28, c29, c31, c32, c33, c34, c35, c36, c37, c38, c39
(5 rows)
testdb=# set enable_hashagg = off;
SET
testdb=# explain verbose select bh
,min(c1),min(c2) ,min(c3) ,min(c4) ,min(c5) ,min(c6) ,min(c7) ,min(c8) ,min(c9)
,min(c11),min(c12) ,min(c13) ,min(c14) ,min(c15) ,min(c16) ,min(c17) ,min(c18) ,min(c19)
,min(c21),min(c22) ,min(c23) ,min(c24) ,min(c25) ,min(c26) ,min(c27) ,min(c28) ,min(c29)
,min(c31),min(c32) ,min(c33) ,min(c34) ,min(c35) ,min(c36) ,min(c37) ,min(c38) ,min(c39)
from t_agg_width group by bh;
                                                    QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=110266.28..148266.32 rows=4 width=149)
   Output: bh, min(c1), min(c2), min(c3), min(c4), min(c5), min(c6), min(c7), min(c8), min(c9), min(c11), min(c12), min(c13),
 min(c14), min(c15), min(c16), min(c17), min(c18), min(c19), min(c21), min(c22), min(c23), min(c24), min(c25), min(c26), min(
c27), min(c28), min(c29), min(c31), min(c32), min(c33), min(c34), min(c35), min(c36), min(c37), min(c38), min(c39)
   Group Key: t_agg_width.bh
   ->  Sort  (cost=110266.28..111266.28 rows=400000 width=149)
         Output: bh, c1, c2, c3, c4, c5, c6, c7, c8, c9, c11, c12, c13, c14, c15, c16, c17, c18, c19, c21, c22, c23, c24, c25
, c26, c27, c28, c29, c31, c32, c33, c34, c35, c36, c37, c38, c39
         Sort Key: t_agg_width.bh
         ->  Seq Scan on public.t_agg_width  (cost=0.00..12889.00 rows=400000 width=149)
               Output: bh, c1, c2, c3, c4, c5, c6, c7, c8, c9, c11, c12, c13, c14, c15, c16, c17, c18, c19, c21, c22, c23, c2
4, c25, c26, c27, c28, c29, c31, c32, c33, c34, c35, c36, c37, c38, c39
(8 rows)
testdb=#

下面增大分组键值的分布,同时提高c1等列的选择率,再次测试: 


testdb=# insert into t_agg_width 
testdb-# select 'GZ'||col
testdb-# ,mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100) 
testdb-# ,mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100) 
testdb-# ,mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100) 
testdb-# ,mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100),mod(col,100) 
testdb-# from generate_series(1,1000000) as col;
INSERT 0 1000000
testdb=# set enable_hashagg = on;
SET
testdb=# explain verbose select bh
,min(c1),min(c2) ,min(c3) ,min(c4) ,min(c5) ,min(c6) ,min(c7) ,min(c8) ,min(c9)
,min(c11),min(c12) ,min(c13) ,min(c14) ,min(c15) ,min(c16) ,min(c17) ,min(c18) ,min(c19)
,min(c21),min(c22) ,min(c23) ,min(c24) ,min(c25) ,min(c26) ,min(c27) ,min(c28) ,min(c29)
,min(c31),min(c32) ,min(c33) ,min(c34) ,min(c35) ,min(c36) ,min(c37) ,min(c38) ,min(c39)
from t_agg_width group by bh;
                                                    QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=440012.46..586553.52 rows=7414 width=149)
   Output: bh, min(c1), min(c2), min(c3), min(c4), min(c5), min(c6), min(c7), min(c8), min(c9), min(c11), min(c12), min(c13),
 min(c14), min(c15), min(c16), min(c17), min(c18), min(c19), min(c21), min(c22), min(c23), min(c24), min(c25), min(c26), min(
c27), min(c28), min(c29), min(c31), min(c32), min(c33), min(c34), min(c35), min(c36), min(c37), min(c38), min(c39)
   Group Key: t_agg_width.bh
   ->  Sort  (cost=440012.46..443866.86 rows=1541757 width=149)
         Output: bh, c1, c2, c3, c4, c5, c6, c7, c8, c9, c11, c12, c13, c14, c15, c16, c17, c18, c19, c21, c22, c23, c24, c25
, c26, c27, c28, c29, c31, c32, c33, c34, c35, c36, c37, c38, c39
         Sort Key: t_agg_width.bh
         ->  Seq Scan on public.t_agg_width  (cost=0.00..49681.57 rows=1541757 width=149)
               Output: bh, c1, c2, c3, c4, c5, c6, c7, c8, c9, c11, c12, c13, c14, c15, c16, c17, c18, c19, c21, c22, c23, c2
4, c25, c26, c27, c28, c29, c31, c32, c33, c34, c35, c36, c37, c38, c39
(8 rows)
testdb=#

这一次选择的是GroupAggregate.

HashAggregate
HashAggregate,数据库会根据group by字段后面的值算出hash值,并在内存中维护对应的Hash表,比如select有n个聚合函数,那么在内存中就会维护n个Hash表.这种方式使用的内存比GroupAggregate要大,内存的使用与group by COLUMN中的COLUMN的唯一键值以及聚合列的多少成正比.

GroupAggregate
GroupAggregate,数据库先将表中的数据按group by的字段进行排序,然后对排好序的数据进行一次扫描,计算得到聚合的结果.这种方式需要先执行一次排序,计算复杂度上面要比HashAggregate要高,但这种方法的好处是与group by COLUMN中的COLUMN的唯一键值多寡/聚合列多寡无关,分组键值很多而且聚合列很多且列数据选择很高的情况下,会优于HashAggregate.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-2642384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6906/viewspace-2642384/

cuichao1900
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pg-xlogdump的使用
12-25
pg_xlogdump的使用
Greenplum聚合函数的两种实现：HashAggregate与GroupAggregate
scutshuxue的专栏
09-19 7711
在Postgresql/Greenplum数据库中，聚合函数有两种实现方式：HashAggregate与GroupAggregate。      我们现在通过一个最简单的sql来分析这两种聚合的区别以及其应用场景。       select count(1) from pg_
postgresql-13.2-1-windows-x64.rar
02-23
postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar
深入理解SPARK SQL 中HashAggregateExec和ObjectHashAggregateExec以及UnsafeRow
monkeyboy_tech的博客
03-26 1997
感悟和理解 翻译这篇文章是为了更好的理解ObjectHashAggregateExec和UnsafeRow，关于UnsafeRow的文章，可以参考Spark源码修改系列 - UnsafeRow内存布局和代码优化。 其实明显，UnsafeRow是模拟了offheap的分配方式而在堆上进行的操作，它并没有脱离了jvm，而是使用了Java unsafe API（Java提供的底层非安全操作接口），允许用户像操作C++指针一样来申请和访问内存地址，JVM也不会对这部分非安全数据进行额外的类型检查和垃圾回收。 事实上
TiDB 源码阅读系列文章（二十二）Hash Aggregation
weixin_42241611的博客
04-12 220
为了充分利用 TiDB server 所在机器的 CPU 和内存资源，加快 TiDB 层的聚合计算，TiDB 层的聚合函数计算可以这样进行：Partial2Mode --> FinalMode。由于分布式计算的需要，TiDB 对于聚合函数的计算阶段进行划分，相应定义了 5 种计算模式：CompleteMode，FinalMode，Partial1Mode，Partial2Mode，DedupMode。类型的数组，数组中的每个元素表示该下标处的聚合函数在对应 Group 中的预聚合结果。
PostgreSQL Explain 复杂执行计划怎么看 --- 逐个分解PG执行计划的那些操作
sql server的专栏
05-04 1797
开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共700人左右 1 + 2）。每个数据中都希望自己的SQL 的执行计划是最好的，最快的，最妙的，但是在你使用EXPLAIN 的命令的情况下，你看的...
PostgreSQL 如何修改执行计划 GroupAggregate 为 HashAggregate
lyu1026的博客
05-20 1086
1、前言 PostgreSQL 聚合算法有两种，HashAggregate andGroupAggregate 。我们知道GroupAggregate 需要对记录进行排序，而HashAggregate 则无需进行排序，通常HashAggregate 要快很多。 但是，我们经常会看到优化器使用GroupAggregate，即使 enable_sort=off 也不能阻止 排序操作。那么有什...
Spark SQL深入分析之图解HashAggregateExec & ObjectHashAggregateExec执行流程
Gavin_ke的博客
11-22 634
这篇文章继续探讨聚合策略，主要介绍Spark SQL提供的两个基于hash的聚合操作符，即HashAggregateExec和ObjectHashAggregateExec。
HashAggregate VS sort+GroupAgg
weixin_33725722的博客
10-23 905
什么是聚合 聚合就是把元素按照一定的规则分为不同的组，然后对各组元素进行计算。 如图：下面是通过颜色分组，然后计算sum。 postgresql中聚合算法 GroupAggregate: get the data sorted and apply aggregation function to groups one by one。 HashAggreg...
HashAggregate, GroupAggregate, hash join
晨风
10-22 2554
1. hash_join算法原理 http://wenku.baidu.com/link?url=OItLNA-dBWluVwyUNFCl6AWbTkrLfOZZcBNOsmgvVg46QGX9gauDVl0Nt1Bqbo-aSZ9iYo-cXHxAHJeLGRwEmFCa8tFPLoutgvn8j66rxoe ::倾向于用小表做hash表也即为build表（生成一份数据放在内存中），另一个表
PostgreSQL（postgresql-14.2-2-windows-x64.exe）
05-05
PostgreSQL（postgresql-14.2-2-windows-x64.exe），适用于Windows系统：PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象...
PostgreSQL（postgresql-13.5-1-osx.dmg）
11-20
PostgreSQL（postgresql-13.5-1-osx.dmg）适用于Mac OS X PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库...
postgresql-10.16-2-windows-x64.zip
04-20
postgresql-10.16-2-windows-x64
PostgreSQL DBA认证 PGCE-E-092-中级SQL题目
08-26
PostgreSQL DBA认证 PGCE-E-092-中级SQL题目，可以测试你对SQL的掌握程序，特别好的文档，分享给大家学习了
【kettle001】访问国产达梦数据库并处理数据至execl文件
kngines
04-22 229
一直以来想写下基于kettle的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。熟悉、梳理、总结下达梦（DM）关系型数据库相关知识体系。
mysql和Nosql到底有什么区别，分别应用与什么场景？
m0_60388871的博客
04-20 519
MySQL 和 NoSQL 是两种不同类型的数据库技术，它们各有其特点和适用场景。了解它们之间的区别和应用场景可以帮助选择合适的技术来支持特定的应用需求。
mysql-connector-java和spring-boot-starter-jdbc和mybatis-spring-boot-start
最新发布
xzy的博客
04-23 408
JDBC是使用java语言操作mysql数据库的规范，java语言必须按照这个规范写才可以操作mysql数据库。
表和索引分片
Sinoregal的博客
04-18 878
是将一个表中的数据分布到不同的dbspace中。SinoDB数据库支持智能化横向的表和索引分区，并将其称之为表和索引分片。分片允许您创建一个表，该表在SQL语句中被视为单个表，但由多个tbspaces组成。正常的分片要求每个 dbspace 中有一个片段。这有效地将较大的表划分为若干较小的 tablespaces，因为一个 tablespace 不能跨 dbspace。新增保留关键字分区。则允许来自一个分片表的多个片段共存于同一个 dbspace。
Sql server备份还原sql语句
hzm博客
04-16 523
Sql server备份还原sql语句
postgresql-9.0-801.jdbc4.jar 下载
01-22
postgresql-9.0-801.jdbc4.jar是一个用于连接Java和PostgreSQL数据库的JDBC驱动程序文件。如果你需要下载这个文件，可以通过以下步骤进行： 首先，打开你的网页浏览器，然后输入“postgresql-9.0-801.jdbc4.jar下载”进行搜索。 在搜索结果中，你会找到官方的PostgreSQL网站或者一些第三方网站提供的下载链接。建议优先选择官方网站或者一些知名的软件下载网站进行下载，以确保文件的安全性和完整性。 点击下载链接，根据网站的指引完成下载过程。有些网站可能会要求你创建一个账户或者填写一些个人信息，根据情况进行操作。 完成下载后，找到下载的文件在你的电脑上的存储位置。你可以将它保存在一个你容易找到的地方，比如桌面或者创建一个专门存放数据库相关文件的文件夹。 在你将postgresql-9.0-801.jdbc4.jar文件保存在合适的位置后，你可以根据你的项目需要，将这个文件加载到你的Java项目中，然后就可以使用它来连接和操作PostgreSQL数据库了。 需要注意的是，下载文件时要注意文件来源的可信度，以免下载到病毒或者恶意软件。另外，建议及时将下载的文件进行安全扫描检测，确保文件的安全性。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值