Greenplum聚合函数的两种实现：HashAggregate与GroupAggregate
|
在Postgresql/Greenplum数据库中，聚合函数有两种实现方式：HashAggregate与GroupAggregate。 我们现在通过一个最简单的sql来分析这两种聚合的区别以及其应用场景。 select count(1) from pg_class group by oid; 一、两种实现算法的比较：
对于hash聚合来说，数据库会根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表。如果select后面有两个聚合函数，那么在内存中就会维护两个对应的数据。同样的，有n个聚合函数就会维护n个同样的数组。对于hash算法来说，数组的长度肯定是大于group by的字段的distinct值的个数的，且跟这个值应该呈线性关系，group by后面的值越唯一，使用的内存也就越大。 执行计划如下： aligputf8=# explain select count(1) from pg_class group by oid;
QUERY PLAN
----------------------------------------------------------------------
HashAggregate (cost=1721.40..2020.28 rows=23910 width=4)
Group By: oid
-> Seq Scan on pg_class (cost=0.00..1004.10 rows=143460 width=4)
Settings: enable_seqscan=on
(4 rows)
对于普通聚合函数，使用group聚合，其原理是先将表中的数据按照group by的字段排序，这样子同一个group by的值就在一起，这样就只需要对排好序的数据进行一次全扫描，就可以得到聚合的结果了。 执行计划如下： aligputf8=# set enable_hashagg = off;
SET
aligputf8=# explain select count(1) from pg_class group by oid;
QUERY PLAN
----------------------------------------------------------------------------
GroupAggregate (cost=13291.66..14666.48 rows=23910 width=4)
Group By: oid
-> Sort (cost=13291.66..13650.31 rows=143460 width=4)
Sort Key: oid
-> Seq Scan on pg_class (cost=0.00..1004.10 rows=143460 width=4)
Settings: enable_hashagg=off; enable_seqscan=on
(6 rows)
从上面的两个执行计划的cost来说，GroupAggregate 由于需要排序，效率很差，消耗是HashAggregate的7倍。所以在GP里面，对于这种聚合函数的使用，采用的都是HashAggregate。 二、两种实现的内存消耗： 先建立一张测试表，并且往里面insert数据，通过每个字段的数据唯一性不一致，还有聚合函数的个数来观察HashAggregate与GroupAggregate在内存的消耗情况以及实际的计算时间的比较。 1.表结构如下： create table test_group( id integer,col1 numeric,col2 numeric,col3 numeric,col4 numeric,col5 numeric,col6 numeric,col7 numeric,col8 numeric,col9 numeric,col11 varchar(100),col12 varchar(100),col13 varchar(100),col14 varchar(100) )distributed by(id); 2.插入数据，通过random函数，实现每个字段数据的唯一性不一样 aligputf8=# insert into test_group aligputf8-# select generate_series(1,100000),aligputf8-# (random()*200)::int,aligputf8-# (random()*800)::int,aligputf8-# (random()*1600)::int,aligputf8-# (random()*3200)::int,aligputf8-# (random()*6400)::int,aligputf8-# (random()*12800)::int,aligputf8-# (random()*40000)::int,aligputf8-# (random()*100000)::int,aligputf8-# (random()*1000000)::int,aligputf8-# 'hello',aligputf8-# 'welcome',aligputf8-# 'haha',aligputf8-# 'chen'; INSERT 0 100000 表大小为： aligputf8=# select pg_size_pretty(pg_relation_size('test_group'));
pg_size_pretty
----------------
12 MB
(1 row)
以下是底层单个节点来计算的，避免了广播的时间跟内存消耗 HashAggregatealigputf8=# explain analyze select sum(col1),sum(col2),sum(col3),sum(col4),sum(col5),sum(col6),sum(col7),sum(col8),sum(col9) from test_group group by col5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
HashAggregate (cost=4186.96..5432.88 rows=38336 width=62)
Group By: col5
Rows out: 6401 rows with 289 ms to first row,295 ms to end,start offset by 0.143 ms.
Executor memory: 2818K bytes.
-> Seq Scan on test_group (cost=0.00..1480.56 rows=108256 width=62)
Rows out: 100000 rows with 0.023 ms to first row,48 ms to end,start offset by 0.218 ms.
Slice statistics:
(slice0) Executor memory: 2996K bytes.
Settings: enable_seqscan=off
Total runtime: 296.283 ms
(10 rows)
GroupAggregatealigputf8=# explain analyze select sum(col1),sum(col9) from test_group group by col5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
GroupAggregate (cost=10532.97..14755.93 rows=38336 width=62)
Group By: col5
Rows out: 6401 rows with 306 ms to first row,585 ms to end,start offset by 0.092 ms.
Executor memory: 8K bytes.
-> Sort (cost=10532.97..10803.61 rows=108256 width=62)
Sort Key: col5
Rows out: 100000 rows with 306 ms to first row,342 ms to end,start offset by 0.093 ms.
Executor memory: 19449K bytes.
Work_mem used: 19449K bytes.
-> Seq Scan on test_group (cost=0.00..1480.56 rows=108256 width=62)
Rows out: 100000 rows with 0.021 ms to first row,46 ms to end,start offset by 0.116 ms.
Slice statistics:
(slice0) Executor memory: 19623K bytes. Work_mem: 19449K bytes max.
Settings: enable_hashagg=off; enable_seqscan=off
Total runtime: 586.114 ms
(15 rows)
通过这种方法，可以看出，消耗的内存跟实际执行时间的比例： SQL: explain analyze select sum(col1),sum(col9) from test_group group by id; SQL: explain analyze select sum(col1),sum(col9), HashAggregate在少数聚合函数是表现优异，但是很多聚合函数，性能跟消耗的内存差异很明显。尤其是受group by字段的唯一性很明显，字段count（district）值越大，hash聚合消耗的内存越多，性能下降剧烈。 所以在sql中有大量聚合函数，group by 的字段由相对比较唯一的时候，应该用GroupAggregate，而不能用HashAggregate。 三、在GP4.1出现的SQL报错：在GP4.1中，之前出现过 有大量聚合函数，并且group by 的字段由相对比较唯一的SQL报错如下： ERROR:Unexpectedinternalerror:SegmentprocessreceivedsignalSIGSEGV 这个sql其实应该就是占用内存太多，进程被操作系统发出信号干掉导致的报错。 查看执行计划，发现是HashAggregate搞得鬼。一般来说，数据库会根据统计信息来选择HashAggregate或者是GroupAggregate，但是有可能统计信息不够详细或者sql太复杂而选错执行计划。 一般遇到这种问题，有两张办法： 1.拆分成多个sql来跑，减少HashAggregate使用的内存. 2.在跑sql之前，先执行enable_hashagg = off;将hash聚合参数关掉。强制不走HashAggregate，建议用这种。 下次如果再遇到这种sql报错，建议采用这种方法改一下脚本试一下。 注：当work_mem不够内存使用时： aligputf8=# explain analyze select sum(col1),max(col1),avg(col1),avg(col9) from test_group group by id;
QUERY PLAN
----------------------------------------------------------------------------------------------------
HashAggregate (cost=15225.85..29783.06 rows=108256 width=66)
Group By: id
Rows out: 100000 rows with 722 ms to first row,1367 ms to end,start offset by 0.125 ms.
Executor memory: 32536K bytes.
Work_mem used: 32001K bytes.
Work_mem wanted: 106876K bytes to lessen workfile I/O.
100000 groups total in 32 batches; 1 overflows; 100000 spill groups.
Hash chain length 1.8 avg,20 max,using 74100 of 135168 buckets.
-> Seq Scan on test_group (cost=0.00..1480.56 rows=108256 width=66)
Rows out: 100000 rows with 0.016 ms to first row,51 ms to end,start offset by 0.142 ms.
Slice statistics:
(slice0) * Executor memory: 32697K bytes. Work_mem: 32001K bytes max,106876K bytes wanted.
Settings: enable_groupagg=off; enable_hashagg=on; enable_seqscan=off; work_mem=32000kB
Total runtime: 1391.138 ms
(14 rows)
（编辑：安卓应用网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |