| blob | dfa4b036b52fa32738b79f602019d0265b0aa7c4 |
1 --
2 -- PARTITION_AGGREGATE
3 -- Test partitionwise aggregation on partitioned tables
4 --
5 -- Note: to ensure plan stability, it's a good idea to make the partitions of
6 -- any one partitioned table in this test all have different numbers of rows.
7 --
8 -- Enable partitionwise aggregate, which by default is disabled.
9 SET enable_partitionwise_aggregate TO true;
10 -- Enable partitionwise join, which by default is disabled.
11 SET enable_partitionwise_join TO true;
12 -- Disable parallel plans.
13 SET max_parallel_workers_per_gather TO 0;
14 -- Disable incremental sort, which can influence selected plans due to fuzz factor.
15 SET enable_incremental_sort TO off;
16 --
17 -- Tests for list partitioned tables.
18 --
19 CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c);
20 CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003', '0004');
21 CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005', '0006', '0007', '0008');
22 CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009', '0010', '0011');
23 INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i;
24 ANALYZE pagg_tab;
25 -- When GROUP BY clause matches; full aggregation is performed for each partition.
26 EXPLAIN (COSTS OFF)
27 SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
28 QUERY PLAN
29 --------------------------------------------------------------
30 Sort
31 Sort Key: pagg_tab.c, (sum(pagg_tab.a)), (avg(pagg_tab.b))
32 -> Append
33 -> HashAggregate
34 Group Key: pagg_tab.c
35 Filter: (avg(pagg_tab.d) < '15'::numeric)
36 -> Seq Scan on pagg_tab_p1 pagg_tab
37 -> HashAggregate
38 Group Key: pagg_tab_1.c
39 Filter: (avg(pagg_tab_1.d) < '15'::numeric)
40 -> Seq Scan on pagg_tab_p2 pagg_tab_1
41 -> HashAggregate
42 Group Key: pagg_tab_2.c
43 Filter: (avg(pagg_tab_2.d) < '15'::numeric)
44 -> Seq Scan on pagg_tab_p3 pagg_tab_2
45 (15 rows)
47 SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
48 c | sum | avg | count | min | max
49 ------+------+---------------------+-------+-----+-----
50 0000 | 2000 | 12.0000000000000000 | 250 | 0 | 24
51 0001 | 2250 | 13.0000000000000000 | 250 | 1 | 25
52 0002 | 2500 | 14.0000000000000000 | 250 | 2 | 26
53 0006 | 2500 | 12.0000000000000000 | 250 | 2 | 24
54 0007 | 2750 | 13.0000000000000000 | 250 | 3 | 25
55 0008 | 2000 | 14.0000000000000000 | 250 | 0 | 26
56 (6 rows)
58 -- When GROUP BY clause does not match; partial aggregation is performed for each partition.
59 EXPLAIN (COSTS OFF)
60 SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3;
61 QUERY PLAN
62 --------------------------------------------------------------
63 Sort
64 Sort Key: pagg_tab.a, (sum(pagg_tab.b)), (avg(pagg_tab.b))
65 -> Finalize HashAggregate
66 Group Key: pagg_tab.a
67 Filter: (avg(pagg_tab.d) < '15'::numeric)
68 -> Append
69 -> Partial HashAggregate
70 Group Key: pagg_tab.a
71 -> Seq Scan on pagg_tab_p1 pagg_tab
72 -> Partial HashAggregate
73 Group Key: pagg_tab_1.a
74 -> Seq Scan on pagg_tab_p2 pagg_tab_1
75 -> Partial HashAggregate
76 Group Key: pagg_tab_2.a
77 -> Seq Scan on pagg_tab_p3 pagg_tab_2
78 (15 rows)
80 SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3;
81 a | sum | avg | count | min | max
82 ----+------+---------------------+-------+-----+-----
83 0 | 1500 | 10.0000000000000000 | 150 | 0 | 20
84 1 | 1650 | 11.0000000000000000 | 150 | 1 | 21
85 2 | 1800 | 12.0000000000000000 | 150 | 2 | 22
86 3 | 1950 | 13.0000000000000000 | 150 | 3 | 23
87 4 | 2100 | 14.0000000000000000 | 150 | 4 | 24
88 10 | 1500 | 10.0000000000000000 | 150 | 10 | 20
89 11 | 1650 | 11.0000000000000000 | 150 | 11 | 21
90 12 | 1800 | 12.0000000000000000 | 150 | 12 | 22
91 13 | 1950 | 13.0000000000000000 | 150 | 13 | 23
92 14 | 2100 | 14.0000000000000000 | 150 | 14 | 24
93 (10 rows)
95 -- Check with multiple columns in GROUP BY
96 EXPLAIN (COSTS OFF)
97 SELECT a, c, count(*) FROM pagg_tab GROUP BY a, c;
98 QUERY PLAN
99 ------------------------------------------------
100 Append
101 -> HashAggregate
102 Group Key: pagg_tab.a, pagg_tab.c
103 -> Seq Scan on pagg_tab_p1 pagg_tab
104 -> HashAggregate
105 Group Key: pagg_tab_1.a, pagg_tab_1.c
106 -> Seq Scan on pagg_tab_p2 pagg_tab_1
107 -> HashAggregate
108 Group Key: pagg_tab_2.a, pagg_tab_2.c
109 -> Seq Scan on pagg_tab_p3 pagg_tab_2
110 (10 rows)
112 -- Check with multiple columns in GROUP BY, order in GROUP BY is reversed
113 EXPLAIN (COSTS OFF)
114 SELECT a, c, count(*) FROM pagg_tab GROUP BY c, a;
115 QUERY PLAN
116 ------------------------------------------------
117 Append
118 -> HashAggregate
119 Group Key: pagg_tab.c, pagg_tab.a
120 -> Seq Scan on pagg_tab_p1 pagg_tab
121 -> HashAggregate
122 Group Key: pagg_tab_1.c, pagg_tab_1.a
123 -> Seq Scan on pagg_tab_p2 pagg_tab_1
124 -> HashAggregate
125 Group Key: pagg_tab_2.c, pagg_tab_2.a
126 -> Seq Scan on pagg_tab_p3 pagg_tab_2
127 (10 rows)
129 -- Check with multiple columns in GROUP BY, order in target-list is reversed
130 EXPLAIN (COSTS OFF)
131 SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c;
132 QUERY PLAN
133 ------------------------------------------------
134 Append
135 -> HashAggregate
136 Group Key: pagg_tab.a, pagg_tab.c
137 -> Seq Scan on pagg_tab_p1 pagg_tab
138 -> HashAggregate
139 Group Key: pagg_tab_1.a, pagg_tab_1.c
140 -> Seq Scan on pagg_tab_p2 pagg_tab_1
141 -> HashAggregate
142 Group Key: pagg_tab_2.a, pagg_tab_2.c
143 -> Seq Scan on pagg_tab_p3 pagg_tab_2
144 (10 rows)
146 -- Test when input relation for grouping is dummy
147 EXPLAIN (COSTS OFF)
148 SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c;
149 QUERY PLAN
150 --------------------------------
151 HashAggregate
152 Group Key: c
153 -> Result
154 One-Time Filter: false
155 (4 rows)
157 SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c;
158 c | sum
159 ---+-----
160 (0 rows)
162 EXPLAIN (COSTS OFF)
163 SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
164 QUERY PLAN
165 --------------------------------
166 GroupAggregate
167 Group Key: c
168 -> Result
169 One-Time Filter: false
170 (4 rows)
172 SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
173 c | sum
174 ---+-----
175 (0 rows)
177 -- Test GroupAggregate paths by disabling hash aggregates.
178 SET enable_hashagg TO false;
179 -- When GROUP BY clause matches full aggregation is performed for each partition.
180 EXPLAIN (COSTS OFF)
181 SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
182 QUERY PLAN
183 --------------------------------------------------------------
184 Sort
185 Sort Key: pagg_tab.c, (sum(pagg_tab.a)), (avg(pagg_tab.b))
186 -> Append
187 -> GroupAggregate
188 Group Key: pagg_tab.c
189 Filter: (avg(pagg_tab.d) < '15'::numeric)
190 -> Sort
191 Sort Key: pagg_tab.c
192 -> Seq Scan on pagg_tab_p1 pagg_tab
193 -> GroupAggregate
194 Group Key: pagg_tab_1.c
195 Filter: (avg(pagg_tab_1.d) < '15'::numeric)
196 -> Sort
197 Sort Key: pagg_tab_1.c
198 -> Seq Scan on pagg_tab_p2 pagg_tab_1
199 -> GroupAggregate
200 Group Key: pagg_tab_2.c
201 Filter: (avg(pagg_tab_2.d) < '15'::numeric)
202 -> Sort
203 Sort Key: pagg_tab_2.c
204 -> Seq Scan on pagg_tab_p3 pagg_tab_2
205 (21 rows)
207 SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
208 c | sum | avg | count
209 ------+------+---------------------+-------
210 0000 | 2000 | 12.0000000000000000 | 250
211 0001 | 2250 | 13.0000000000000000 | 250
212 0002 | 2500 | 14.0000000000000000 | 250
213 0006 | 2500 | 12.0000000000000000 | 250
214 0007 | 2750 | 13.0000000000000000 | 250
215 0008 | 2000 | 14.0000000000000000 | 250
216 (6 rows)
218 -- When GROUP BY clause does not match; partial aggregation is performed for each partition.
219 EXPLAIN (COSTS OFF)
220 SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
221 QUERY PLAN
222 ------------------------------------------------------------------
223 Sort
224 Sort Key: pagg_tab.a, (sum(pagg_tab.b)), (avg(pagg_tab.b))
225 -> Finalize GroupAggregate
226 Group Key: pagg_tab.a
227 Filter: (avg(pagg_tab.d) < '15'::numeric)
228 -> Merge Append
229 Sort Key: pagg_tab.a
230 -> Partial GroupAggregate
231 Group Key: pagg_tab.a
232 -> Sort
233 Sort Key: pagg_tab.a
234 -> Seq Scan on pagg_tab_p1 pagg_tab
235 -> Partial GroupAggregate
236 Group Key: pagg_tab_1.a
237 -> Sort
238 Sort Key: pagg_tab_1.a
239 -> Seq Scan on pagg_tab_p2 pagg_tab_1
240 -> Partial GroupAggregate
241 Group Key: pagg_tab_2.a
242 -> Sort
243 Sort Key: pagg_tab_2.a
244 -> Seq Scan on pagg_tab_p3 pagg_tab_2
245 (22 rows)
247 SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
248 a | sum | avg | count
249 ----+------+---------------------+-------
250 0 | 1500 | 10.0000000000000000 | 150
251 1 | 1650 | 11.0000000000000000 | 150
252 2 | 1800 | 12.0000000000000000 | 150
253 3 | 1950 | 13.0000000000000000 | 150
254 4 | 2100 | 14.0000000000000000 | 150
255 10 | 1500 | 10.0000000000000000 | 150
256 11 | 1650 | 11.0000000000000000 | 150
257 12 | 1800 | 12.0000000000000000 | 150
258 13 | 1950 | 13.0000000000000000 | 150
259 14 | 2100 | 14.0000000000000000 | 150
260 (10 rows)
262 -- Test partitionwise grouping without any aggregates
263 EXPLAIN (COSTS OFF)
264 SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
265 QUERY PLAN
266 ------------------------------------------------------
267 Merge Append
268 Sort Key: pagg_tab.c
269 -> Group
270 Group Key: pagg_tab.c
271 -> Sort
272 Sort Key: pagg_tab.c
273 -> Seq Scan on pagg_tab_p1 pagg_tab
274 -> Group
275 Group Key: pagg_tab_1.c
276 -> Sort
277 Sort Key: pagg_tab_1.c
278 -> Seq Scan on pagg_tab_p2 pagg_tab_1
279 -> Group
280 Group Key: pagg_tab_2.c
281 -> Sort
282 Sort Key: pagg_tab_2.c
283 -> Seq Scan on pagg_tab_p3 pagg_tab_2
284 (17 rows)
286 SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
287 c
288 ------
289 0000
290 0001
291 0002
292 0003
293 0004
294 0005
295 0006
296 0007
297 0008
298 0009
299 0010
300 0011
301 (12 rows)
303 EXPLAIN (COSTS OFF)
304 SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
305 QUERY PLAN
306 ------------------------------------------------------------
307 Group
308 Group Key: pagg_tab.a
309 -> Merge Append
310 Sort Key: pagg_tab.a
311 -> Group
312 Group Key: pagg_tab.a
313 -> Sort
314 Sort Key: pagg_tab.a
315 -> Seq Scan on pagg_tab_p1 pagg_tab
316 Filter: (a < 3)
317 -> Group
318 Group Key: pagg_tab_1.a
319 -> Sort
320 Sort Key: pagg_tab_1.a
321 -> Seq Scan on pagg_tab_p2 pagg_tab_1
322 Filter: (a < 3)
323 -> Group
324 Group Key: pagg_tab_2.a
325 -> Sort
326 Sort Key: pagg_tab_2.a
327 -> Seq Scan on pagg_tab_p3 pagg_tab_2
328 Filter: (a < 3)
329 (22 rows)
331 SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
332 a
333 ---
334 0
335 1
336 2
337 (3 rows)
339 RESET enable_hashagg;
340 -- ROLLUP, partitionwise aggregation does not apply
341 EXPLAIN (COSTS OFF)
342 SELECT c, sum(a) FROM pagg_tab GROUP BY rollup(c) ORDER BY 1, 2;
343 QUERY PLAN
344 ------------------------------------------------------
345 Sort
346 Sort Key: pagg_tab.c, (sum(pagg_tab.a))
347 -> MixedAggregate
348 Hash Key: pagg_tab.c
349 Group Key: ()
350 -> Append
351 -> Seq Scan on pagg_tab_p1 pagg_tab_1
352 -> Seq Scan on pagg_tab_p2 pagg_tab_2
353 -> Seq Scan on pagg_tab_p3 pagg_tab_3
354 (9 rows)
356 -- ORDERED SET within the aggregate.
357 -- Full aggregation; since all the rows that belong to the same group come
358 -- from the same partition, having an ORDER BY within the aggregate doesn't
359 -- make any difference.
360 EXPLAIN (COSTS OFF)
361 SELECT c, sum(b order by a) FROM pagg_tab GROUP BY c ORDER BY 1, 2;
362 QUERY PLAN
363 ---------------------------------------------------------------
364 Sort
365 Sort Key: pagg_tab.c, (sum(pagg_tab.b ORDER BY pagg_tab.a))
366 -> Append
367 -> GroupAggregate
368 Group Key: pagg_tab.c
369 -> Sort
370 Sort Key: pagg_tab.c
371 -> Seq Scan on pagg_tab_p1 pagg_tab
372 -> GroupAggregate
373 Group Key: pagg_tab_1.c
374 -> Sort
375 Sort Key: pagg_tab_1.c
376 -> Seq Scan on pagg_tab_p2 pagg_tab_1
377 -> GroupAggregate
378 Group Key: pagg_tab_2.c
379 -> Sort
380 Sort Key: pagg_tab_2.c
381 -> Seq Scan on pagg_tab_p3 pagg_tab_2
382 (18 rows)
384 -- Since GROUP BY clause does not match with PARTITION KEY; we need to do
385 -- partial aggregation. However, ORDERED SET are not partial safe and thus
386 -- partitionwise aggregation plan is not generated.
387 EXPLAIN (COSTS OFF)
388 SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2;
389 QUERY PLAN
390 ---------------------------------------------------------------
391 Sort
392 Sort Key: pagg_tab.a, (sum(pagg_tab.b ORDER BY pagg_tab.a))
393 -> GroupAggregate
394 Group Key: pagg_tab.a
395 -> Sort
396 Sort Key: pagg_tab.a
397 -> Append
398 -> Seq Scan on pagg_tab_p1 pagg_tab_1
399 -> Seq Scan on pagg_tab_p2 pagg_tab_2
400 -> Seq Scan on pagg_tab_p3 pagg_tab_3
401 (10 rows)
403 -- JOIN query
404 CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x);
405 CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10);
406 CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20);
407 CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30);
408 CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y);
409 CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10);
410 CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20);
411 CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30);
412 INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i;
413 INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i;
414 ANALYZE pagg_tab1;
415 ANALYZE pagg_tab2;
416 -- When GROUP BY clause matches; full aggregation is performed for each partition.
417 EXPLAIN (COSTS OFF)
418 SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
419 QUERY PLAN
420 -------------------------------------------------------------
421 Sort
422 Sort Key: t1.x, (sum(t1.y)), (count(*))
423 -> Append
424 -> HashAggregate
425 Group Key: t1.x
426 -> Hash Join
427 Hash Cond: (t1.x = t2.y)
428 -> Seq Scan on pagg_tab1_p1 t1
429 -> Hash
430 -> Seq Scan on pagg_tab2_p1 t2
431 -> HashAggregate
432 Group Key: t1_1.x
433 -> Hash Join
434 Hash Cond: (t1_1.x = t2_1.y)
435 -> Seq Scan on pagg_tab1_p2 t1_1
436 -> Hash
437 -> Seq Scan on pagg_tab2_p2 t2_1
438 -> HashAggregate
439 Group Key: t1_2.x
440 -> Hash Join
441 Hash Cond: (t2_2.y = t1_2.x)
442 -> Seq Scan on pagg_tab2_p3 t2_2
443 -> Hash
444 -> Seq Scan on pagg_tab1_p3 t1_2
445 (24 rows)
447 SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
448 x | sum | count
449 ----+------+-------
450 0 | 500 | 100
451 6 | 1100 | 100
452 12 | 700 | 100
453 18 | 1300 | 100
454 24 | 900 | 100
455 (5 rows)
457 -- Check with whole-row reference; partitionwise aggregation does not apply
458 EXPLAIN (COSTS OFF)
459 SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
460 QUERY PLAN
461 -------------------------------------------------------------
462 Sort
463 Sort Key: t1.x, (sum(t1.y)), (count(((t1.*)::pagg_tab1)))
464 -> HashAggregate
465 Group Key: t1.x
466 -> Hash Join
467 Hash Cond: (t1.x = t2.y)
468 -> Append
469 -> Seq Scan on pagg_tab1_p1 t1_1
470 -> Seq Scan on pagg_tab1_p2 t1_2
471 -> Seq Scan on pagg_tab1_p3 t1_3
472 -> Hash
473 -> Append
474 -> Seq Scan on pagg_tab2_p1 t2_1
475 -> Seq Scan on pagg_tab2_p2 t2_2
476 -> Seq Scan on pagg_tab2_p3 t2_3
477 (15 rows)
479 SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
480 x | sum | count
481 ----+------+-------
482 0 | 500 | 100
483 6 | 1100 | 100
484 12 | 700 | 100
485 18 | 1300 | 100
486 24 | 900 | 100
487 (5 rows)
489 -- GROUP BY having other matching key
490 EXPLAIN (COSTS OFF)
491 SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3;
492 QUERY PLAN
493 -------------------------------------------------------------
494 Sort
495 Sort Key: t2.y, (sum(t1.y)), (count(*))
496 -> Append
497 -> HashAggregate
498 Group Key: t2.y
499 -> Hash Join
500 Hash Cond: (t1.x = t2.y)
501 -> Seq Scan on pagg_tab1_p1 t1
502 -> Hash
503 -> Seq Scan on pagg_tab2_p1 t2
504 -> HashAggregate
505 Group Key: t2_1.y
506 -> Hash Join
507 Hash Cond: (t1_1.x = t2_1.y)
508 -> Seq Scan on pagg_tab1_p2 t1_1
509 -> Hash
510 -> Seq Scan on pagg_tab2_p2 t2_1
511 -> HashAggregate
512 Group Key: t2_2.y
513 -> Hash Join
514 Hash Cond: (t2_2.y = t1_2.x)
515 -> Seq Scan on pagg_tab2_p3 t2_2
516 -> Hash
517 -> Seq Scan on pagg_tab1_p3 t1_2
518 (24 rows)
520 -- When GROUP BY clause does not match; partial aggregation is performed for each partition.
521 -- Also test GroupAggregate paths by disabling hash aggregates.
522 SET enable_hashagg TO false;
523 EXPLAIN (COSTS OFF)
524 SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3;
525 QUERY PLAN
526 -------------------------------------------------------------------------
527 Sort
528 Sort Key: t1.y, (sum(t1.x)), (count(*))
529 -> Finalize GroupAggregate
530 Group Key: t1.y
531 Filter: (avg(t1.x) > '10'::numeric)
532 -> Merge Append
533 Sort Key: t1.y
534 -> Partial GroupAggregate
535 Group Key: t1.y
536 -> Sort
537 Sort Key: t1.y
538 -> Hash Join
539 Hash Cond: (t1.x = t2.y)
540 -> Seq Scan on pagg_tab1_p1 t1
541 -> Hash
542 -> Seq Scan on pagg_tab2_p1 t2
543 -> Partial GroupAggregate
544 Group Key: t1_1.y
545 -> Sort
546 Sort Key: t1_1.y
547 -> Hash Join
548 Hash Cond: (t1_1.x = t2_1.y)
549 -> Seq Scan on pagg_tab1_p2 t1_1
550 -> Hash
551 -> Seq Scan on pagg_tab2_p2 t2_1
552 -> Partial GroupAggregate
553 Group Key: t1_2.y
554 -> Sort
555 Sort Key: t1_2.y
556 -> Hash Join
557 Hash Cond: (t2_2.y = t1_2.x)
558 -> Seq Scan on pagg_tab2_p3 t2_2
559 -> Hash
560 -> Seq Scan on pagg_tab1_p3 t1_2
561 (34 rows)
563 SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3;
564 y | sum | count
565 ----+------+-------
566 2 | 600 | 50
567 4 | 1200 | 50
568 8 | 900 | 50
569 12 | 600 | 50
570 14 | 1200 | 50
571 18 | 900 | 50
572 (6 rows)
574 RESET enable_hashagg;
575 -- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for
576 -- aggregation
577 -- LEFT JOIN, should produce partial partitionwise aggregation plan as
578 -- GROUP BY is on nullable column
579 EXPLAIN (COSTS OFF)
580 SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST;
581 QUERY PLAN
582 ------------------------------------------------------------------
583 Finalize GroupAggregate
584 Group Key: b.y
585 -> Sort
586 Sort Key: b.y
587 -> Append
588 -> Partial HashAggregate
589 Group Key: b.y
590 -> Hash Left Join
591 Hash Cond: (a.x = b.y)
592 -> Seq Scan on pagg_tab1_p1 a
593 -> Hash
594 -> Seq Scan on pagg_tab2_p1 b
595 -> Partial HashAggregate
596 Group Key: b_1.y
597 -> Hash Left Join
598 Hash Cond: (a_1.x = b_1.y)
599 -> Seq Scan on pagg_tab1_p2 a_1
600 -> Hash
601 -> Seq Scan on pagg_tab2_p2 b_1
602 -> Partial HashAggregate
603 Group Key: b_2.y
604 -> Hash Right Join
605 Hash Cond: (b_2.y = a_2.x)
606 -> Seq Scan on pagg_tab2_p3 b_2
607 -> Hash
608 -> Seq Scan on pagg_tab1_p3 a_2
609 (26 rows)
611 SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST;
612 y | sum
613 ----+------
614 0 | 500
615 6 | 1100
616 12 | 700
617 18 | 1300
618 24 | 900
619 | 900
620 (6 rows)
622 -- RIGHT JOIN, should produce full partitionwise aggregation plan as
623 -- GROUP BY is on non-nullable column
624 EXPLAIN (COSTS OFF)
625 SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST;
626 QUERY PLAN
627 ------------------------------------------------------------
628 Sort
629 Sort Key: b.y
630 -> Append
631 -> HashAggregate
632 Group Key: b.y
633 -> Hash Right Join
634 Hash Cond: (a.x = b.y)
635 -> Seq Scan on pagg_tab1_p1 a
636 -> Hash
637 -> Seq Scan on pagg_tab2_p1 b
638 -> HashAggregate
639 Group Key: b_1.y
640 -> Hash Right Join
641 Hash Cond: (a_1.x = b_1.y)
642 -> Seq Scan on pagg_tab1_p2 a_1
643 -> Hash
644 -> Seq Scan on pagg_tab2_p2 b_1
645 -> HashAggregate
646 Group Key: b_2.y
647 -> Hash Left Join
648 Hash Cond: (b_2.y = a_2.x)
649 -> Seq Scan on pagg_tab2_p3 b_2
650 -> Hash
651 -> Seq Scan on pagg_tab1_p3 a_2
652 (24 rows)
654 SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST;
655 y | sum
656 ----+------
657 0 | 500
658 3 |
659 6 | 1100
660 9 |
661 12 | 700
662 15 |
663 18 | 1300
664 21 |
665 24 | 900
666 27 |
667 (10 rows)
669 -- FULL JOIN, should produce partial partitionwise aggregation plan as
670 -- GROUP BY is on nullable column
671 EXPLAIN (COSTS OFF)
672 SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST;
673 QUERY PLAN
674 ------------------------------------------------------------------
675 Finalize GroupAggregate
676 Group Key: a.x
677 -> Sort
678 Sort Key: a.x
679 -> Append
680 -> Partial HashAggregate
681 Group Key: a.x
682 -> Hash Full Join
683 Hash Cond: (a.x = b.y)
684 -> Seq Scan on pagg_tab1_p1 a
685 -> Hash
686 -> Seq Scan on pagg_tab2_p1 b
687 -> Partial HashAggregate
688 Group Key: a_1.x
689 -> Hash Full Join
690 Hash Cond: (a_1.x = b_1.y)
691 -> Seq Scan on pagg_tab1_p2 a_1
692 -> Hash
693 -> Seq Scan on pagg_tab2_p2 b_1
694 -> Partial HashAggregate
695 Group Key: a_2.x
696 -> Hash Full Join
697 Hash Cond: (b_2.y = a_2.x)
698 -> Seq Scan on pagg_tab2_p3 b_2
699 -> Hash
700 -> Seq Scan on pagg_tab1_p3 a_2
701 (26 rows)
703 SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST;
704 x | sum
705 ----+------
706 0 | 500
707 2 |
708 4 |
709 6 | 1100
710 8 |
711 10 |
712 12 | 700
713 14 |
714 16 |
715 18 | 1300
716 20 |
717 22 |
718 24 | 900
719 26 |
720 28 |
721 | 500
722 (16 rows)
724 -- LEFT JOIN, with dummy relation on right side, ideally
725 -- should produce full partitionwise aggregation plan as GROUP BY is on
726 -- non-nullable columns.
727 -- But right now we are unable to do partitionwise join in this case.
728 EXPLAIN (COSTS OFF)
729 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
730 QUERY PLAN
731 --------------------------------------------------------------------
732 Sort
733 Sort Key: pagg_tab1.x, pagg_tab2.y
734 -> HashAggregate
735 Group Key: pagg_tab1.x, pagg_tab2.y
736 -> Hash Left Join
737 Hash Cond: (pagg_tab1.x = pagg_tab2.y)
738 Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
739 -> Append
740 -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
741 Filter: (x < 20)
742 -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
743 Filter: (x < 20)
744 -> Hash
745 -> Append
746 -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
747 Filter: (y > 10)
748 -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
749 Filter: (y > 10)
750 (18 rows)
752 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
753 x | y | count
754 ----+----+-------
755 6 | | 10
756 8 | | 10
757 10 | | 10
758 12 | 12 | 100
759 14 | | 10
760 16 | | 10
761 18 | 18 | 100
762 (7 rows)
764 -- FULL JOIN, with dummy relations on both sides, ideally
765 -- should produce partial partitionwise aggregation plan as GROUP BY is on
766 -- nullable columns.
767 -- But right now we are unable to do partitionwise join in this case.
768 EXPLAIN (COSTS OFF)
769 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
770 QUERY PLAN
771 --------------------------------------------------------------------
772 Sort
773 Sort Key: pagg_tab1.x, pagg_tab2.y
774 -> HashAggregate
775 Group Key: pagg_tab1.x, pagg_tab2.y
776 -> Hash Full Join
777 Hash Cond: (pagg_tab1.x = pagg_tab2.y)
778 Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
779 -> Append
780 -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
781 Filter: (x < 20)
782 -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
783 Filter: (x < 20)
784 -> Hash
785 -> Append
786 -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
787 Filter: (y > 10)
788 -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
789 Filter: (y > 10)
790 (18 rows)
792 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
793 x | y | count
794 ----+----+-------
795 6 | | 10
796 8 | | 10
797 10 | | 10
798 12 | 12 | 100
799 14 | | 10
800 16 | | 10
801 18 | 18 | 100
802 | 15 | 10
803 (8 rows)
805 -- Empty join relation because of empty outer side, no partitionwise agg plan
806 EXPLAIN (COSTS OFF)
807 SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
808 QUERY PLAN
809 ---------------------------------------
810 GroupAggregate
811 Group Key: pagg_tab1.x, pagg_tab1.y
812 -> Sort
813 Sort Key: pagg_tab1.y
814 -> Result
815 One-Time Filter: false
816 (6 rows)
818 SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
819 x | y | count
820 ---+---+-------
821 (0 rows)
823 -- Partition by multiple columns
824 CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2));
825 CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (12, 12);
826 CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 12) TO (22, 22);
827 CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 22) TO (30, 30);
828 INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i;
829 ANALYZE pagg_tab_m;
830 -- Partial aggregation as GROUP BY clause does not match with PARTITION KEY
831 EXPLAIN (COSTS OFF)
832 SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3;
833 QUERY PLAN
834 --------------------------------------------------------------------
835 Sort
836 Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c))
837 -> Finalize HashAggregate
838 Group Key: pagg_tab_m.a
839 Filter: (avg(pagg_tab_m.c) < '22'::numeric)
840 -> Append
841 -> Partial HashAggregate
842 Group Key: pagg_tab_m.a
843 -> Seq Scan on pagg_tab_m_p1 pagg_tab_m
844 -> Partial HashAggregate
845 Group Key: pagg_tab_m_1.a
846 -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1
847 -> Partial HashAggregate
848 Group Key: pagg_tab_m_2.a
849 -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2
850 (15 rows)
852 SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3;
853 a | sum | avg | count
854 ----+------+---------------------+-------
855 0 | 1500 | 20.0000000000000000 | 100
856 1 | 1600 | 21.0000000000000000 | 100
857 10 | 1500 | 20.0000000000000000 | 100
858 11 | 1600 | 21.0000000000000000 | 100
859 20 | 1500 | 20.0000000000000000 | 100
860 21 | 1600 | 21.0000000000000000 | 100
861 (6 rows)
863 -- Full aggregation as GROUP BY clause matches with PARTITION KEY
864 EXPLAIN (COSTS OFF)
865 SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3;
866 QUERY PLAN
867 ----------------------------------------------------------------------------------
868 Sort
869 Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c))
870 -> Append
871 -> HashAggregate
872 Group Key: pagg_tab_m.a, ((pagg_tab_m.a + pagg_tab_m.b) / 2)
873 Filter: (sum(pagg_tab_m.b) < 50)
874 -> Seq Scan on pagg_tab_m_p1 pagg_tab_m
875 -> HashAggregate
876 Group Key: pagg_tab_m_1.a, ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2)
877 Filter: (sum(pagg_tab_m_1.b) < 50)
878 -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1
879 -> HashAggregate
880 Group Key: pagg_tab_m_2.a, ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2)
881 Filter: (sum(pagg_tab_m_2.b) < 50)
882 -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2
883 (15 rows)
885 SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3;
886 a | sum | avg | count
887 ----+-----+---------------------+-------
888 0 | 0 | 20.0000000000000000 | 25
889 1 | 25 | 21.0000000000000000 | 25
890 10 | 0 | 20.0000000000000000 | 25
891 11 | 25 | 21.0000000000000000 | 25
892 20 | 0 | 20.0000000000000000 | 25
893 21 | 25 | 21.0000000000000000 | 25
894 (6 rows)
896 -- Full aggregation as PARTITION KEY is part of GROUP BY clause
897 EXPLAIN (COSTS OFF)
898 SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3;
899 QUERY PLAN
900 --------------------------------------------------------------------------------------------------
901 Sort
902 Sort Key: pagg_tab_m.a, pagg_tab_m.c, (sum(pagg_tab_m.b))
903 -> Append
904 -> HashAggregate
905 Group Key: ((pagg_tab_m.a + pagg_tab_m.b) / 2), pagg_tab_m.c, pagg_tab_m.a
906 Filter: ((sum(pagg_tab_m.b) = 50) AND (avg(pagg_tab_m.c) > '25'::numeric))
907 -> Seq Scan on pagg_tab_m_p1 pagg_tab_m
908 -> HashAggregate
909 Group Key: ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2), pagg_tab_m_1.c, pagg_tab_m_1.a
910 Filter: ((sum(pagg_tab_m_1.b) = 50) AND (avg(pagg_tab_m_1.c) > '25'::numeric))
911 -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1
912 -> HashAggregate
913 Group Key: ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2), pagg_tab_m_2.c, pagg_tab_m_2.a
914 Filter: ((sum(pagg_tab_m_2.b) = 50) AND (avg(pagg_tab_m_2.c) > '25'::numeric))
915 -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2
916 (15 rows)
918 SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3;
919 a | c | sum | avg | count
920 ----+----+-----+---------------------+-------
921 0 | 30 | 50 | 30.0000000000000000 | 5
922 0 | 40 | 50 | 40.0000000000000000 | 5
923 10 | 30 | 50 | 30.0000000000000000 | 5
924 10 | 40 | 50 | 40.0000000000000000 | 5
925 20 | 30 | 50 | 30.0000000000000000 | 5
926 20 | 40 | 50 | 40.0000000000000000 | 5
927 (6 rows)
929 -- Test with multi-level partitioning scheme
930 CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a);
931 CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (12);
932 CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (12) TO (20) PARTITION BY LIST (c);
933 CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001', '0002');
934 CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0003');
935 -- This level of partitioning has different column positions than the parent
936 CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b);
937 CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int);
938 CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (7) TO (10);
939 ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (7);
940 ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30);
941 INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i;
942 ANALYZE pagg_tab_ml;
943 -- For Parallel Append
944 SET max_parallel_workers_per_gather TO 2;
945 -- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
946 -- for level 1 only. For subpartitions, GROUP BY clause does not match with
947 -- PARTITION KEY, but still we do not see a partial aggregation as array_agg()
948 -- is not partial agg safe.
949 EXPLAIN (COSTS OFF)
950 SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
951 QUERY PLAN
952 --------------------------------------------------------------------------------------
953 Sort
954 Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (array_agg(DISTINCT pagg_tab_ml.c))
955 -> Gather
956 Workers Planned: 2
957 -> Parallel Append
958 -> GroupAggregate
959 Group Key: pagg_tab_ml.a
960 Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
961 -> Sort
962 Sort Key: pagg_tab_ml.a
963 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
964 -> GroupAggregate
965 Group Key: pagg_tab_ml_5.a
966 Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
967 -> Sort
968 Sort Key: pagg_tab_ml_5.a
969 -> Append
970 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
971 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
972 -> GroupAggregate
973 Group Key: pagg_tab_ml_2.a
974 Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
975 -> Sort
976 Sort Key: pagg_tab_ml_2.a
977 -> Append
978 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
979 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
980 (27 rows)
982 SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
983 a | sum | array_agg | count
984 ----+------+-------------+-------
985 0 | 0 | {0000,0002} | 1000
986 1 | 1000 | {0001,0003} | 1000
987 2 | 2000 | {0000,0002} | 1000
988 10 | 0 | {0000,0002} | 1000
989 11 | 1000 | {0001,0003} | 1000
990 12 | 2000 | {0000,0002} | 1000
991 20 | 0 | {0000,0002} | 1000
992 21 | 1000 | {0001,0003} | 1000
993 22 | 2000 | {0000,0002} | 1000
994 (9 rows)
996 -- Without ORDER BY clause, to test Gather at top-most path
997 EXPLAIN (COSTS OFF)
998 SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3;
999 QUERY PLAN
1000 ---------------------------------------------------------------------------
1001 Gather
1002 Workers Planned: 2
1003 -> Parallel Append
1004 -> GroupAggregate
1005 Group Key: pagg_tab_ml.a
1006 Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
1007 -> Sort
1008 Sort Key: pagg_tab_ml.a
1009 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1010 -> GroupAggregate
1011 Group Key: pagg_tab_ml_5.a
1012 Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
1013 -> Sort
1014 Sort Key: pagg_tab_ml_5.a
1015 -> Append
1016 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
1017 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
1018 -> GroupAggregate
1019 Group Key: pagg_tab_ml_2.a
1020 Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
1021 -> Sort
1022 Sort Key: pagg_tab_ml_2.a
1023 -> Append
1024 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
1025 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
1026 (25 rows)
1028 -- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
1029 -- for level 1 only. For subpartitions, GROUP BY clause does not match with
1030 -- PARTITION KEY, thus we will have a partial aggregation for them.
1031 EXPLAIN (COSTS OFF)
1032 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
1033 QUERY PLAN
1034 ---------------------------------------------------------------------------------
1035 Sort
1036 Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*))
1037 -> Append
1038 -> HashAggregate
1039 Group Key: pagg_tab_ml.a
1040 Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
1041 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1042 -> Finalize GroupAggregate
1043 Group Key: pagg_tab_ml_2.a
1044 Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
1045 -> Sort
1046 Sort Key: pagg_tab_ml_2.a
1047 -> Append
1048 -> Partial HashAggregate
1049 Group Key: pagg_tab_ml_2.a
1050 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
1051 -> Partial HashAggregate
1052 Group Key: pagg_tab_ml_3.a
1053 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
1054 -> Finalize GroupAggregate
1055 Group Key: pagg_tab_ml_5.a
1056 Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
1057 -> Sort
1058 Sort Key: pagg_tab_ml_5.a
1059 -> Append
1060 -> Partial HashAggregate
1061 Group Key: pagg_tab_ml_5.a
1062 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
1063 -> Partial HashAggregate
1064 Group Key: pagg_tab_ml_6.a
1065 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
1066 (31 rows)
1068 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
1069 a | sum | count
1070 ----+------+-------
1071 0 | 0 | 1000
1072 1 | 1000 | 1000
1073 2 | 2000 | 1000
1074 10 | 0 | 1000
1075 11 | 1000 | 1000
1076 12 | 2000 | 1000
1077 20 | 0 | 1000
1078 21 | 1000 | 1000
1079 22 | 2000 | 1000
1080 (9 rows)
1082 -- Partial aggregation at all levels as GROUP BY clause does not match with
1083 -- PARTITION KEY
1084 EXPLAIN (COSTS OFF)
1085 SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3;
1086 QUERY PLAN
1087 ---------------------------------------------------------------------------
1088 Sort
1089 Sort Key: pagg_tab_ml.b, (sum(pagg_tab_ml.a)), (count(*))
1090 -> Finalize GroupAggregate
1091 Group Key: pagg_tab_ml.b
1092 -> Sort
1093 Sort Key: pagg_tab_ml.b
1094 -> Append
1095 -> Partial HashAggregate
1096 Group Key: pagg_tab_ml.b
1097 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1098 -> Partial HashAggregate
1099 Group Key: pagg_tab_ml_1.b
1100 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1
1101 -> Partial HashAggregate
1102 Group Key: pagg_tab_ml_2.b
1103 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2
1104 -> Partial HashAggregate
1105 Group Key: pagg_tab_ml_3.b
1106 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3
1107 -> Partial HashAggregate
1108 Group Key: pagg_tab_ml_4.b
1109 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4
1110 (22 rows)
1112 SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3;
1113 b | sum | count
1114 ---+-------+-------
1115 0 | 30000 | 3000
1116 1 | 33000 | 3000
1117 2 | 36000 | 3000
1118 3 | 39000 | 3000
1119 4 | 42000 | 3000
1120 (5 rows)
1122 -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY
1123 EXPLAIN (COSTS OFF)
1124 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3;
1125 QUERY PLAN
1126 ----------------------------------------------------------------------------
1127 Sort
1128 Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*))
1129 -> Append
1130 -> HashAggregate
1131 Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c
1132 Filter: (avg(pagg_tab_ml.b) > '7'::numeric)
1133 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1134 -> HashAggregate
1135 Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c
1136 Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric)
1137 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1
1138 -> HashAggregate
1139 Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c
1140 Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric)
1141 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2
1142 -> HashAggregate
1143 Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c
1144 Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric)
1145 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3
1146 -> HashAggregate
1147 Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c
1148 Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric)
1149 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4
1150 (23 rows)
1152 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3;
1153 a | sum | count
1154 ----+------+-------
1155 8 | 4000 | 500
1156 8 | 4000 | 500
1157 9 | 4500 | 500
1158 9 | 4500 | 500
1159 18 | 4000 | 500
1160 18 | 4000 | 500
1161 19 | 4500 | 500
1162 19 | 4500 | 500
1163 28 | 4000 | 500
1164 28 | 4000 | 500
1165 29 | 4500 | 500
1166 29 | 4500 | 500
1167 (12 rows)
1169 -- Parallelism within partitionwise aggregates
1170 SET min_parallel_table_scan_size TO '8kB';
1171 SET parallel_setup_cost TO 0;
1172 -- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
1173 -- for level 1 only. For subpartitions, GROUP BY clause does not match with
1174 -- PARTITION KEY, thus we will have a partial aggregation for them.
1175 EXPLAIN (COSTS OFF)
1176 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
1177 QUERY PLAN
1178 ------------------------------------------------------------------------------------------------
1179 Sort
1180 Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*))
1181 -> Append
1182 -> Finalize GroupAggregate
1183 Group Key: pagg_tab_ml.a
1184 Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
1185 -> Gather Merge
1186 Workers Planned: 2
1187 -> Sort
1188 Sort Key: pagg_tab_ml.a
1189 -> Partial HashAggregate
1190 Group Key: pagg_tab_ml.a
1191 -> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1192 -> Finalize GroupAggregate
1193 Group Key: pagg_tab_ml_2.a
1194 Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
1195 -> Gather Merge
1196 Workers Planned: 2
1197 -> Sort
1198 Sort Key: pagg_tab_ml_2.a
1199 -> Parallel Append
1200 -> Partial HashAggregate
1201 Group Key: pagg_tab_ml_2.a
1202 -> Parallel Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
1203 -> Partial HashAggregate
1204 Group Key: pagg_tab_ml_3.a
1205 -> Parallel Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
1206 -> Finalize GroupAggregate
1207 Group Key: pagg_tab_ml_5.a
1208 Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
1209 -> Gather Merge
1210 Workers Planned: 2
1211 -> Sort
1212 Sort Key: pagg_tab_ml_5.a
1213 -> Parallel Append
1214 -> Partial HashAggregate
1215 Group Key: pagg_tab_ml_5.a
1216 -> Parallel Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
1217 -> Partial HashAggregate
1218 Group Key: pagg_tab_ml_6.a
1219 -> Parallel Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
1220 (41 rows)
1222 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
1223 a | sum | count
1224 ----+------+-------
1225 0 | 0 | 1000
1226 1 | 1000 | 1000
1227 2 | 2000 | 1000
1228 10 | 0 | 1000
1229 11 | 1000 | 1000
1230 12 | 2000 | 1000
1231 20 | 0 | 1000
1232 21 | 1000 | 1000
1233 22 | 2000 | 1000
1234 (9 rows)
1236 -- Partial aggregation at all levels as GROUP BY clause does not match with
1237 -- PARTITION KEY
1238 EXPLAIN (COSTS OFF)
1239 SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3;
1240 QUERY PLAN
1241 ------------------------------------------------------------------------------------------
1242 Sort
1243 Sort Key: pagg_tab_ml.b, (sum(pagg_tab_ml.a)), (count(*))
1244 -> Finalize GroupAggregate
1245 Group Key: pagg_tab_ml.b
1246 -> Gather Merge
1247 Workers Planned: 2
1248 -> Sort
1249 Sort Key: pagg_tab_ml.b
1250 -> Parallel Append
1251 -> Partial HashAggregate
1252 Group Key: pagg_tab_ml.b
1253 -> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1254 -> Partial HashAggregate
1255 Group Key: pagg_tab_ml_3.b
1256 -> Parallel Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3
1257 -> Partial HashAggregate
1258 Group Key: pagg_tab_ml_1.b
1259 -> Parallel Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1
1260 -> Partial HashAggregate
1261 Group Key: pagg_tab_ml_4.b
1262 -> Parallel Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4
1263 -> Partial HashAggregate
1264 Group Key: pagg_tab_ml_2.b
1265 -> Parallel Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2
1266 (24 rows)
1268 SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3;
1269 b | sum | count
1270 ---+-------+-------
1271 0 | 30000 | 3000
1272 1 | 33000 | 3000
1273 2 | 36000 | 3000
1274 3 | 39000 | 3000
1275 4 | 42000 | 3000
1276 (5 rows)
1278 -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY
1279 EXPLAIN (COSTS OFF)
1280 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3;
1281 QUERY PLAN
1282 ----------------------------------------------------------------------------------
1283 Gather Merge
1284 Workers Planned: 2
1285 -> Sort
1286 Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*))
1287 -> Parallel Append
1288 -> HashAggregate
1289 Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c
1290 Filter: (avg(pagg_tab_ml.b) > '7'::numeric)
1291 -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1292 -> HashAggregate
1293 Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c
1294 Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric)
1295 -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3
1296 -> HashAggregate
1297 Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c
1298 Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric)
1299 -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1
1300 -> HashAggregate
1301 Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c
1302 Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric)
1303 -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4
1304 -> HashAggregate
1305 Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c
1306 Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric)
1307 -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2
1308 (25 rows)
1310 SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3;
1311 a | sum | count
1312 ----+------+-------
1313 8 | 4000 | 500
1314 8 | 4000 | 500
1315 9 | 4500 | 500
1316 9 | 4500 | 500
1317 18 | 4000 | 500
1318 18 | 4000 | 500
1319 19 | 4500 | 500
1320 19 | 4500 | 500
1321 28 | 4000 | 500
1322 28 | 4000 | 500
1323 29 | 4500 | 500
1324 29 | 4500 | 500
1325 (12 rows)
1327 -- Parallelism within partitionwise aggregates (single level)
1328 -- Add few parallel setup cost, so that we will see a plan which gathers
1329 -- partially created paths even for full aggregation and sticks a single Gather
1330 -- followed by finalization step.
1331 -- Without this, the cost of doing partial aggregation + Gather + finalization
1332 -- for each partition and then Append over it turns out to be same and this
1333 -- wins as we add it first. This parallel_setup_cost plays a vital role in
1334 -- costing such plans.
1335 SET parallel_setup_cost TO 10;
1336 CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x);
1337 CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (12);
1338 CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (12) TO (22);
1339 CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (22) TO (30);
1340 INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i;
1341 ANALYZE pagg_tab_para;
1342 -- When GROUP BY clause matches; full aggregation is performed for each partition.
1343 EXPLAIN (COSTS OFF)
1344 SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1345 QUERY PLAN
1346 -------------------------------------------------------------------------------------------
1347 Sort
1348 Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y))
1349 -> Finalize GroupAggregate
1350 Group Key: pagg_tab_para.x
1351 Filter: (avg(pagg_tab_para.y) < '7'::numeric)
1352 -> Gather Merge
1353 Workers Planned: 2
1354 -> Sort
1355 Sort Key: pagg_tab_para.x
1356 -> Parallel Append
1357 -> Partial HashAggregate
1358 Group Key: pagg_tab_para.x
1359 -> Parallel Seq Scan on pagg_tab_para_p1 pagg_tab_para
1360 -> Partial HashAggregate
1361 Group Key: pagg_tab_para_1.x
1362 -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_1
1363 -> Partial HashAggregate
1364 Group Key: pagg_tab_para_2.x
1365 -> Parallel Seq Scan on pagg_tab_para_p3 pagg_tab_para_2
1366 (19 rows)
1368 SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1369 x | sum | avg | count
1370 ----+------+--------------------+-------
1371 0 | 5000 | 5.0000000000000000 | 1000
1372 1 | 6000 | 6.0000000000000000 | 1000
1373 10 | 5000 | 5.0000000000000000 | 1000
1374 11 | 6000 | 6.0000000000000000 | 1000
1375 20 | 5000 | 5.0000000000000000 | 1000
1376 21 | 6000 | 6.0000000000000000 | 1000
1377 (6 rows)
1379 -- When GROUP BY clause does not match; partial aggregation is performed for each partition.
1380 EXPLAIN (COSTS OFF)
1381 SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3;
1382 QUERY PLAN
1383 -------------------------------------------------------------------------------------------
1384 Sort
1385 Sort Key: pagg_tab_para.y, (sum(pagg_tab_para.x)), (avg(pagg_tab_para.x))
1386 -> Finalize GroupAggregate
1387 Group Key: pagg_tab_para.y
1388 Filter: (avg(pagg_tab_para.x) < '12'::numeric)
1389 -> Gather Merge
1390 Workers Planned: 2
1391 -> Sort
1392 Sort Key: pagg_tab_para.y
1393 -> Parallel Append
1394 -> Partial HashAggregate
1395 Group Key: pagg_tab_para.y
1396 -> Parallel Seq Scan on pagg_tab_para_p1 pagg_tab_para
1397 -> Partial HashAggregate
1398 Group Key: pagg_tab_para_1.y
1399 -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_1
1400 -> Partial HashAggregate
1401 Group Key: pagg_tab_para_2.y
1402 -> Parallel Seq Scan on pagg_tab_para_p3 pagg_tab_para_2
1403 (19 rows)
1405 SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3;
1406 y | sum | avg | count
1407 ----+-------+---------------------+-------
1408 0 | 15000 | 10.0000000000000000 | 1500
1409 1 | 16500 | 11.0000000000000000 | 1500
1410 10 | 15000 | 10.0000000000000000 | 1500
1411 11 | 16500 | 11.0000000000000000 | 1500
1412 (4 rows)
1414 -- Test when parent can produce parallel paths but not any (or some) of its children
1415 -- (Use one more aggregate to tilt the cost estimates for the plan we want)
1416 ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0);
1417 ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0);
1418 ANALYZE pagg_tab_para;
1419 EXPLAIN (COSTS OFF)
1420 SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1421 QUERY PLAN
1422 -------------------------------------------------------------------------------------------
1423 Sort
1424 Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y))
1425 -> Finalize GroupAggregate
1426 Group Key: pagg_tab_para.x
1427 Filter: (avg(pagg_tab_para.y) < '7'::numeric)
1428 -> Gather Merge
1429 Workers Planned: 2
1430 -> Sort
1431 Sort Key: pagg_tab_para.x
1432 -> Partial HashAggregate
1433 Group Key: pagg_tab_para.x
1434 -> Parallel Append
1435 -> Seq Scan on pagg_tab_para_p1 pagg_tab_para_1
1436 -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_3
1437 -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_2
1438 (15 rows)
1440 SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1441 x | sum | avg | sum | count
1442 ----+------+--------------------+-------+-------
1443 0 | 5000 | 5.0000000000000000 | 5000 | 1000
1444 1 | 6000 | 6.0000000000000000 | 7000 | 1000
1445 10 | 5000 | 5.0000000000000000 | 15000 | 1000
1446 11 | 6000 | 6.0000000000000000 | 17000 | 1000
1447 20 | 5000 | 5.0000000000000000 | 25000 | 1000
1448 21 | 6000 | 6.0000000000000000 | 27000 | 1000
1449 (6 rows)
1451 ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0);
1452 ANALYZE pagg_tab_para;
1453 EXPLAIN (COSTS OFF)
1454 SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1455 QUERY PLAN
1456 ----------------------------------------------------------------------------------
1457 Sort
1458 Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y))
1459 -> Finalize GroupAggregate
1460 Group Key: pagg_tab_para.x
1461 Filter: (avg(pagg_tab_para.y) < '7'::numeric)
1462 -> Gather Merge
1463 Workers Planned: 2
1464 -> Sort
1465 Sort Key: pagg_tab_para.x
1466 -> Partial HashAggregate
1467 Group Key: pagg_tab_para.x
1468 -> Parallel Append
1469 -> Seq Scan on pagg_tab_para_p1 pagg_tab_para_1
1470 -> Seq Scan on pagg_tab_para_p2 pagg_tab_para_2
1471 -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_3
1472 (15 rows)
1474 SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1475 x | sum | avg | sum | count
1476 ----+------+--------------------+-------+-------
1477 0 | 5000 | 5.0000000000000000 | 5000 | 1000
1478 1 | 6000 | 6.0000000000000000 | 7000 | 1000
1479 10 | 5000 | 5.0000000000000000 | 15000 | 1000
1480 11 | 6000 | 6.0000000000000000 | 17000 | 1000
1481 20 | 5000 | 5.0000000000000000 | 25000 | 1000
1482 21 | 6000 | 6.0000000000000000 | 27000 | 1000
1483 (6 rows)
1485 -- Reset parallelism parameters to get partitionwise aggregation plan.
1486 RESET min_parallel_table_scan_size;
1487 RESET parallel_setup_cost;
1488 EXPLAIN (COSTS OFF)
1489 SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1490 QUERY PLAN
1491 -----------------------------------------------------------------------------
1492 Sort
1493 Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y))
1494 -> Append
1495 -> HashAggregate
1496 Group Key: pagg_tab_para.x
1497 Filter: (avg(pagg_tab_para.y) < '7'::numeric)
1498 -> Seq Scan on pagg_tab_para_p1 pagg_tab_para
1499 -> HashAggregate
1500 Group Key: pagg_tab_para_1.x
1501 Filter: (avg(pagg_tab_para_1.y) < '7'::numeric)
1502 -> Seq Scan on pagg_tab_para_p2 pagg_tab_para_1
1503 -> HashAggregate
1504 Group Key: pagg_tab_para_2.x
1505 Filter: (avg(pagg_tab_para_2.y) < '7'::numeric)
1506 -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_2
1507 (15 rows)
1509 SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1510 x | sum | avg | count
1511 ----+------+--------------------+-------
1512 0 | 5000 | 5.0000000000000000 | 1000
1513 1 | 6000 | 6.0000000000000000 | 1000
1514 10 | 5000 | 5.0000000000000000 | 1000
1515 11 | 6000 | 6.0000000000000000 | 1000
1516 20 | 5000 | 5.0000000000000000 | 1000
1517 21 | 6000 | 6.0000000000000000 | 1000
1518 (6 rows)