本篇内容介绍了“PostgreSQL中HashAggregate与GroupAggregate的区别是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

案例一

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

droptableifexistst_agg;createtablet_agg(bhvarchar(20),c1int,c2int,c3int,c4int,c5int,c6int);insertintot_aggselect'GZ01',col,col,col,col,col,colfromgenerate_series(1,100000)ascol;insertintot_aggselect'GZ02',col,col,col,col,col,colfromgenerate_series(1,100000)ascol;insertintot_aggselect'GZ03',col,col,col,col,col,colfromgenerate_series(1,100000)ascol;insertintot_aggselect'GZ04',col,col,col,col,col,colfromgenerate_series(1,100000)ascol;insertintot_aggselect'GZ05',col,col,col,col,col,colfromgenerate_series(1,100000)ascol;

执行查询:

testdb=#--禁用并行testdb=#setmax_parallel_workers_per_gather=0;SETtestdb=#explainverboseselectbh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5)fromt_agggroupbybh;QUERYPLAN--------------------------------------------------------------------------------------------------------HashAggregate(cost=22427.00..22427.05rows=5width=45)Output:bh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5)GroupKey:t_agg.bh->SeqScanonpublic.t_agg(cost=0.00..8677.00rows=500000width=25)Output:bh,c1,c2,c3,c4,c5,c6(5rows)

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

testdb=#setenable_hashagg=off;SETtestdb=#explainverboseselectbh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5)fromt_agggroupbybh;QUERYPLAN--------------------------------------------------------------------------------------------------------GroupAggregate(cost=67968.92..82968.97rows=5width=45)Output:bh,min(c1),max(c1),min(c2),max(c2),min(c3),max(c3),min(c4),max(c4),min(c5),max(c5)GroupKey:t_agg.bh->Sort(cost=67968.92..69218.92rows=500000width=25)Output:bh,c1,c2,c3,c4,c5SortKey:t_agg.bh->SeqScanonpublic.t_agg(cost=0.00..8677.00rows=500000width=25)Output:bh,c1,c2,c3,c4,c5(8rows)

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

droptableifexistst_agg_width;createtablet_agg_width(bhvarchar(20),c1int,c2int,c3int,c4int,c5int,c6int,c7int,c8int,c9int,c11int,c12int,c13int,c14int,c15int,c16int,c17int,c18int,c19int,c21int,c22int,c23int,c24int,c25int,c26int,c27int,c28int,c29int,c31int,c32int,c33int,c34int,c35int,c36int,c37int,c38int,c39int);insertintot_agg_widthselect'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,colfromgenerate_series(1,100000)ascol;insertintot_agg_widthselect'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,colfromgenerate_series(1,100000)ascol;insertintot_agg_widthselect'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,colfromgenerate_series(1,100000)ascol;insertintot_agg_widthselect'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,colfromgenerate_series(1,100000)ascol;--禁用hashaggsetenable_hashagg=off;--禁用并行setmax_parallel_workers_per_gather=0;selectbh,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)fromt_agg_widthgroupbybh;

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

testdb=#explainverboseselectbhtestdb-#,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-#fromt_agg_widthgroupbybh;QUERYPLAN----------------------------------------------------------------------------------------------------------HashAggregate(cost=49889.00..49889.04rows=4width=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)GroupKey:t_agg_width.bh->SeqScanonpublic.t_agg_width(cost=0.00..12889.00rows=400000width=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(5rows)testdb=#setenable_hashagg=off;SETtestdb=#explainverboseselectbh,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)fromt_agg_widthgroupbybh;QUERYPLAN----------------------------------------------------------------------------------------------------------GroupAggregate(cost=110266.28..148266.32rows=4width=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)GroupKey:t_agg_width.bh->Sort(cost=110266.28..111266.28rows=400000width=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,c39SortKey:t_agg_width.bh->SeqScanonpublic.t_agg_width(cost=0.00..12889.00rows=400000width=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(8rows)testdb=#

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

testdb=#insertintot_agg_widthtestdb-#select'GZ'||coltestdb-#,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-#fromgenerate_series(1,1000000)ascol;INSERT01000000testdb=#setenable_hashagg=on;SETtestdb=#explainverboseselectbh,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)fromt_agg_widthgroupbybh;QUERYPLAN----------------------------------------------------------------------------------------------------------GroupAggregate(cost=440012.46..586553.52rows=7414width=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)GroupKey:t_agg_width.bh->Sort(cost=440012.46..443866.86rows=1541757width=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,c39SortKey:t_agg_width.bh->SeqScanonpublic.t_agg_width(cost=0.00..49681.57rows=1541757width=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(8rows)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.

“PostgreSQL中HashAggregate与GroupAggregate的区别是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！