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. 9SET enable_partitionwise_aggregate TO true; 10-- Enable partitionwise join, which by default is disabled. 11SET enable_partitionwise_join TO true; 12-- Disable parallel plans. 13SET max_parallel_workers_per_gather TO 0; 14-- Disable incremental sort, which can influence selected plans due to fuzz factor. 15SET enable_incremental_sort TO off; 16-- 17-- Tests for list partitioned tables. 18-- 19CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c); 20CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003', '0004'); 21CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005', '0006', '0007', '0008'); 22CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009', '0010', '0011'); 23INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i; 24ANALYZE pagg_tab; 25-- When GROUP BY clause matches; full aggregation is performed for each partition. 26EXPLAIN (COSTS OFF) 27SELECT 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) 46 47SELECT 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) 57 58-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 59EXPLAIN (COSTS OFF) 60SELECT 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) 79 80SELECT 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) 94 95-- Check with multiple columns in GROUP BY 96EXPLAIN (COSTS OFF) 97SELECT 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) 111 112-- Check with multiple columns in GROUP BY, order in GROUP BY is reversed 113EXPLAIN (COSTS OFF) 114SELECT 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) 128 129-- Check with multiple columns in GROUP BY, order in target-list is reversed 130EXPLAIN (COSTS OFF) 131SELECT 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) 145 146-- Test when input relation for grouping is dummy 147EXPLAIN (COSTS OFF) 148SELECT 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) 156 157SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; 158 c | sum 159---+----- 160(0 rows) 161 162EXPLAIN (COSTS OFF) 163SELECT 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) 171 172SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; 173 c | sum 174---+----- 175(0 rows) 176 177-- Test GroupAggregate paths by disabling hash aggregates. 178SET enable_hashagg TO false; 179-- When GROUP BY clause matches full aggregation is performed for each partition. 180EXPLAIN (COSTS OFF) 181SELECT 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) 206 207SELECT 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) 217 218-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 219EXPLAIN (COSTS OFF) 220SELECT 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) 246 247SELECT 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) 261 262-- Test partitionwise grouping without any aggregates 263EXPLAIN (COSTS OFF) 264SELECT 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) 285 286SELECT 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) 302 303EXPLAIN (COSTS OFF) 304SELECT 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) 330 331SELECT 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) 338 339RESET enable_hashagg; 340-- ROLLUP, partitionwise aggregation does not apply 341EXPLAIN (COSTS OFF) 342SELECT 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) 355 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. 360EXPLAIN (COSTS OFF) 361SELECT 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) 383 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. 387EXPLAIN (COSTS OFF) 388SELECT 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) 402 403-- JOIN query 404CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x); 405CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10); 406CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20); 407CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30); 408CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y); 409CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10); 410CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20); 411CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30); 412INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i; 413INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i; 414ANALYZE pagg_tab1; 415ANALYZE pagg_tab2; 416-- When GROUP BY clause matches; full aggregation is performed for each partition. 417EXPLAIN (COSTS OFF) 418SELECT 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) 446 447SELECT 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) 456 457-- Check with whole-row reference; partitionwise aggregation does not apply 458EXPLAIN (COSTS OFF) 459SELECT 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) 478 479SELECT 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) 488 489-- GROUP BY having other matching key 490EXPLAIN (COSTS OFF) 491SELECT 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) 519 520-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 521-- Also test GroupAggregate paths by disabling hash aggregates. 522SET enable_hashagg TO false; 523EXPLAIN (COSTS OFF) 524SELECT 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) 562 563SELECT 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) 573 574RESET 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 579EXPLAIN (COSTS OFF) 580SELECT 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) 610 611SELECT 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) 621 622-- RIGHT JOIN, should produce full partitionwise aggregation plan as 623-- GROUP BY is on non-nullable column 624EXPLAIN (COSTS OFF) 625SELECT 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) 653 654SELECT 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) 668 669-- FULL JOIN, should produce partial partitionwise aggregation plan as 670-- GROUP BY is on nullable column 671EXPLAIN (COSTS OFF) 672SELECT 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) 702 703SELECT 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) 723 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. 728EXPLAIN (COSTS OFF) 729SELECT 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) 751 752SELECT 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) 763 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. 768EXPLAIN (COSTS OFF) 769SELECT 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) 791 792SELECT 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) 804 805-- Empty join relation because of empty outer side, no partitionwise agg plan 806EXPLAIN (COSTS OFF) 807SELECT 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) 817 818SELECT 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) 822 823-- Partition by multiple columns 824CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2)); 825CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (12, 12); 826CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 12) TO (22, 22); 827CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 22) TO (30, 30); 828INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i; 829ANALYZE pagg_tab_m; 830-- Partial aggregation as GROUP BY clause does not match with PARTITION KEY 831EXPLAIN (COSTS OFF) 832SELECT 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) 851 852SELECT 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) 862 863-- Full aggregation as GROUP BY clause matches with PARTITION KEY 864EXPLAIN (COSTS OFF) 865SELECT 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) 884 885SELECT 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) 895 896-- Full aggregation as PARTITION KEY is part of GROUP BY clause 897EXPLAIN (COSTS OFF) 898SELECT 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) 917 918SELECT 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) 928 929-- Test with multi-level partitioning scheme 930CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); 931CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (12); 932CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (12) TO (20) PARTITION BY LIST (c); 933CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001', '0002'); 934CREATE 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 936CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b); 937CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int); 938CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (7) TO (10); 939ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (7); 940ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30); 941INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i; 942ANALYZE pagg_tab_ml; 943-- For Parallel Append 944SET 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. 949EXPLAIN (COSTS OFF) 950SELECT 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) 981 982SELECT 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) 995 996-- Without ORDER BY clause, to test Gather at top-most path 997EXPLAIN (COSTS OFF) 998SELECT 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) 1027 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. 1031EXPLAIN (COSTS OFF) 1032SELECT 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) 1067 1068SELECT 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) 1081 1082-- Partial aggregation at all levels as GROUP BY clause does not match with 1083-- PARTITION KEY 1084EXPLAIN (COSTS OFF) 1085SELECT 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) 1111 1112SELECT 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) 1121 1122-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY 1123EXPLAIN (COSTS OFF) 1124SELECT 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) 1151 1152SELECT 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) 1168 1169-- Parallelism within partitionwise aggregates 1170SET min_parallel_table_scan_size TO '8kB'; 1171SET 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. 1175EXPLAIN (COSTS OFF) 1176SELECT 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) 1221 1222SELECT 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) 1235 1236-- Partial aggregation at all levels as GROUP BY clause does not match with 1237-- PARTITION KEY 1238EXPLAIN (COSTS OFF) 1239SELECT 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) 1267 1268SELECT 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) 1277 1278-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY 1279EXPLAIN (COSTS OFF) 1280SELECT 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) 1309 1310SELECT 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) 1326 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. 1335SET parallel_setup_cost TO 10; 1336CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x); 1337CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (12); 1338CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (12) TO (22); 1339CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (22) TO (30); 1340INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i; 1341ANALYZE pagg_tab_para; 1342-- When GROUP BY clause matches; full aggregation is performed for each partition. 1343EXPLAIN (COSTS OFF) 1344SELECT 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) 1367 1368SELECT 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) 1378 1379-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 1380EXPLAIN (COSTS OFF) 1381SELECT 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) 1404 1405SELECT 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) 1413 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) 1416ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0); 1417ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0); 1418ANALYZE pagg_tab_para; 1419EXPLAIN (COSTS OFF) 1420SELECT 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) 1439 1440SELECT 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) 1450 1451ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0); 1452ANALYZE pagg_tab_para; 1453EXPLAIN (COSTS OFF) 1454SELECT 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) 1473 1474SELECT 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) 1484 1485-- Reset parallelism parameters to get partitionwise aggregation plan. 1486RESET min_parallel_table_scan_size; 1487RESET parallel_setup_cost; 1488EXPLAIN (COSTS OFF) 1489SELECT 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) 1508 1509SELECT 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) 1519 1520