Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb563594

Browse files
committed
Support hashed aggregation with grouping sets.
This extends the Aggregate node with two new features: HashAggregatecan now run multiple hashtables concurrently, and a new strategyMixedAggregate populates hashtables while doing sorted grouping.The planner will now attempt to save as many sorts as possible whenplanning grouping sets queries, while not exceeding work_mem for theestimated combined sizes of all hashtables used. No SQL-level changesare required. There should be no user-visible impact other than thenew EXPLAIN output and possible changes to result ordering when ORDERBY was not used (which affected a few regression tests). Theenable_hashagg option is respected.Author: Andrew GierthReviewers: Mark Dilger, Andres FreundDiscussion:https://postgr.es/m/87vatszyhj.fsf@news-spur.riddles.org.uk
1 parentf0a6046 commitb563594

File tree

22 files changed

+2544
-594
lines changed

22 files changed

+2544
-594
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 33 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -3276,16 +3276,19 @@ select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.
32763276
-- Grouping sets
32773277
explain (verbose, costs off)
32783278
select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last;
3279-
QUERY PLAN
3280-
---------------------------------------------------------------------------------------------------
3281-
GroupAggregate
3282-
Output: c2, sum(c1)
3283-
Group Key: ft1.c2
3284-
Group Key: ()
3285-
-> Foreign Scan on public.ft1
3286-
Output: c2, c1
3287-
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3)) ORDER BY c2 ASC NULLS LAST
3288-
(7 rows)
3279+
QUERY PLAN
3280+
------------------------------------------------------------------------------
3281+
Sort
3282+
Output: c2, (sum(c1))
3283+
Sort Key: ft1.c2
3284+
-> MixedAggregate
3285+
Output: c2, sum(c1)
3286+
Hash Key: ft1.c2
3287+
Group Key: ()
3288+
-> Foreign Scan on public.ft1
3289+
Output: c2, c1
3290+
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3))
3291+
(10 rows)
32893292

32903293
select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last;
32913294
c2 | sum
@@ -3298,16 +3301,19 @@ select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls la
32983301

32993302
explain (verbose, costs off)
33003303
select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last;
3301-
QUERY PLAN
3302-
---------------------------------------------------------------------------------------------------
3303-
GroupAggregate
3304-
Output: c2, sum(c1)
3305-
Group Key: ft1.c2
3306-
Group Key: ()
3307-
-> Foreign Scan on public.ft1
3308-
Output: c2, c1
3309-
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3)) ORDER BY c2 ASC NULLS LAST
3310-
(7 rows)
3304+
QUERY PLAN
3305+
------------------------------------------------------------------------------
3306+
Sort
3307+
Output: c2, (sum(c1))
3308+
Sort Key: ft1.c2
3309+
-> MixedAggregate
3310+
Output: c2, sum(c1)
3311+
Hash Key: ft1.c2
3312+
Group Key: ()
3313+
-> Foreign Scan on public.ft1
3314+
Output: c2, c1
3315+
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3))
3316+
(10 rows)
33113317

33123318
select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last;
33133319
c2 | sum
@@ -3320,20 +3326,19 @@ select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last
33203326

33213327
explain (verbose, costs off)
33223328
select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last;
3323-
QUERY PLAN
3324-
-------------------------------------------------------------------------------------------------------------
3329+
QUERY PLAN
3330+
----------------------------------------------------------------------------------
33253331
Sort
33263332
Output: c2, c6, (sum(c1))
33273333
Sort Key: ft1.c2, ft1.c6
3328-
->GroupAggregate
3334+
->HashAggregate
33293335
Output: c2, c6, sum(c1)
3330-
Group Key: ft1.c2
3331-
Sort Key: ft1.c6
3332-
Group Key: ft1.c6
3336+
Hash Key: ft1.c2
3337+
Hash Key: ft1.c6
33333338
-> Foreign Scan on public.ft1
33343339
Output: c2, c6, c1
3335-
Remote SQL: SELECT "C 1", c2, c6 FROM "S 1"."T 1" WHERE ((c2 < 3)) ORDER BY c2 ASC NULLS LAST
3336-
(11 rows)
3340+
Remote SQL: SELECT "C 1", c2, c6 FROM "S 1"."T 1" WHERE ((c2 < 3))
3341+
(10 rows)
33373342

33383343
select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last;
33393344
c2 | c6 | sum

‎src/backend/commands/explain.c

Lines changed: 21 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1015,6 +1015,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
10151015
pname="HashAggregate";
10161016
strategy="Hashed";
10171017
break;
1018+
caseAGG_MIXED:
1019+
pname="MixedAggregate";
1020+
strategy="Mixed";
1021+
break;
10181022
default:
10191023
pname="Aggregate ???";
10201024
strategy="???";
@@ -1978,6 +1982,19 @@ show_grouping_set_keys(PlanState *planstate,
19781982
ListCell*lc;
19791983
List*gsets=aggnode->groupingSets;
19801984
AttrNumber*keycols=aggnode->grpColIdx;
1985+
constchar*keyname;
1986+
constchar*keysetname;
1987+
1988+
if (aggnode->aggstrategy==AGG_HASHED||aggnode->aggstrategy==AGG_MIXED)
1989+
{
1990+
keyname="Hash Key";
1991+
keysetname="Hash Keys";
1992+
}
1993+
else
1994+
{
1995+
keyname="Group Key";
1996+
keysetname="Group Keys";
1997+
}
19811998

19821999
ExplainOpenGroup("Grouping Set",NULL, true,es);
19832000

@@ -1992,7 +2009,7 @@ show_grouping_set_keys(PlanState *planstate,
19922009
es->indent++;
19932010
}
19942011

1995-
ExplainOpenGroup("Group Keys","Group Keys", false,es);
2012+
ExplainOpenGroup(keysetname,keysetname, false,es);
19962013

19972014
foreach(lc,gsets)
19982015
{
@@ -2016,12 +2033,12 @@ show_grouping_set_keys(PlanState *planstate,
20162033
}
20172034

20182035
if (!result&&es->format==EXPLAIN_FORMAT_TEXT)
2019-
ExplainPropertyText("Group Key","()",es);
2036+
ExplainPropertyText(keyname,"()",es);
20202037
else
2021-
ExplainPropertyListNested("Group Key",result,es);
2038+
ExplainPropertyListNested(keyname,result,es);
20222039
}
20232040

2024-
ExplainCloseGroup("Group Keys","Group Keys", false,es);
2041+
ExplainCloseGroup(keysetname,keysetname, false,es);
20252042

20262043
if (sortnode&&es->format==EXPLAIN_FORMAT_TEXT)
20272044
es->indent--;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp