diff -U3 /home/jcoleman/Source/postgres/src/test/regress/expected/partition_join.out /home/jcoleman/Source/postgres/src/test/regress/results/partition_join.out --- /home/jcoleman/Source/postgres/src/test/regress/expected/partition_join.out 2019-06-11 20:41:11.637297274 -0400 +++ /home/jcoleman/Source/postgres/src/test/regress/results/partition_join.out 2019-07-07 09:00:20.781660472 -0400 @@ -65,6 +65,7 @@ -- left outer join, with whole-row reference; partitionwise join does not apply EXPLAIN (COSTS OFF) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path QUERY PLAN -------------------------------------------------- Sort @@ -86,6 +87,7 @@ (16 rows) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path t1 | t2 --------------+-------------- (0,0,0000) | (0,0,0000) @@ -208,6 +210,7 @@ -- Currently we can't do partitioned join if nullable-side partitions are pruned EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path QUERY PLAN ----------------------------------------------------------- Sort @@ -228,6 +231,7 @@ (15 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path a | c | b | c -----+------+-----+------ 0 | 0000 | | @@ -566,6 +570,8 @@ EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path QUERY PLAN -------------------------------------------------------------- Sort @@ -604,6 +610,8 @@ (33 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path a | c | b | c | ?column? | c -----+------+-----+------+----------+--- 0 | 0000 | 0 | 0000 | 0 | 0 @@ -622,6 +630,7 @@ EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path QUERY PLAN ------------------------------------------------------------------- Sort @@ -657,6 +666,7 @@ (30 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path a | c | b | c | ?column? | c -----+------+-----+------+----------+--- 0 | 0000 | 0 | 0000 | 0 | 0 @@ -908,6 +918,7 @@ EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path QUERY PLAN ---------------------------------------------------------------------------- Sort @@ -964,6 +975,7 @@ (51 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; +WARNING: adding incremental sort + gather merge path a | c | b | c | ?column? | c -----+------+-----+------+----------+--- 0 | 0000 | 0 | 0000 | 0 | 0 @@ -984,6 +996,7 @@ -- This should generate a partitionwise join, but currently fails to EXPLAIN (COSTS OFF) SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path QUERY PLAN ----------------------------------------------------------- Sort @@ -1007,6 +1020,7 @@ (18 rows) SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; +WARNING: adding incremental sort + gather merge path a | b -----+----- 0 | @@ -1149,6 +1163,9 @@ -- test partition matching with N-way join EXPLAIN (COSTS OFF) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path QUERY PLAN -------------------------------------------------------------------------------- GroupAggregate @@ -1186,6 +1203,9 @@ (32 rows) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path avg | avg | avg | c | c | c ----------------------+----------------------+-----------------------+------+------+------- 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000 @@ -1293,6 +1313,9 @@ -- test partition matching with N-way join EXPLAIN (COSTS OFF) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path QUERY PLAN -------------------------------------------------------------------------------- GroupAggregate @@ -1330,6 +1353,9 @@ (32 rows) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path avg | avg | avg | c | c | c ----------------------+----------------------+----------------------+------+------+------- 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000 diff -U3 /home/jcoleman/Source/postgres/src/test/regress/expected/partition_aggregate.out /home/jcoleman/Source/postgres/src/test/regress/results/partition_aggregate.out --- /home/jcoleman/Source/postgres/src/test/regress/expected/partition_aggregate.out 2019-07-03 22:59:05.044423362 -0400 +++ /home/jcoleman/Source/postgres/src/test/regress/results/partition_aggregate.out 2019-07-07 09:00:20.985660449 -0400 @@ -1027,142 +1027,48 @@ -- PARTITION KEY, thus we will have a partial aggregation for them. EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) - -> Append - -> HashAggregate - Group Key: pagg_tab_ml_p1.a - Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) - -> Seq Scan on pagg_tab_ml_p1 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a - Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_p2_s1.a - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s1.a - -> Seq Scan on pagg_tab_ml_p2_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s2.a - -> Seq Scan on pagg_tab_ml_p2_s2 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a - Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_p3_s1.a - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s1.a - -> Seq Scan on pagg_tab_ml_p3_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s2.a - -> Seq Scan on pagg_tab_ml_p3_s2 -(31 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 0 | 0 | 1000 - 1 | 1000 | 1000 - 2 | 2000 | 1000 - 10 | 0 | 1000 - 11 | 1000 | 1000 - 12 | 2000 | 1000 - 20 | 0 | 1000 - 21 | 1000 | 1000 - 22 | 2000 | 1000 -(9 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Partial aggregation at all levels as GROUP BY clause does not match with -- PARTITION KEY EXPLAIN (COSTS OFF) SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml_p1.b, (sum(pagg_tab_ml_p1.a)), (count(*)) - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p1.b - -> Sort - Sort Key: pagg_tab_ml_p1.b - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p1.b - -> Seq Scan on pagg_tab_ml_p1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s1.b - -> Seq Scan on pagg_tab_ml_p2_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s2.b - -> Seq Scan on pagg_tab_ml_p2_s2 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s1.b - -> Seq Scan on pagg_tab_ml_p3_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s2.b - -> Seq Scan on pagg_tab_ml_p3_s2 -(22 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; - b | sum | count ----+-------+------- - 0 | 30000 | 3000 - 1 | 33000 | 3000 - 2 | 36000 | 3000 - 3 | 39000 | 3000 - 4 | 42000 | 3000 -(5 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) - -> Append - -> HashAggregate - Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c - Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 - -> HashAggregate - Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c - Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 - -> HashAggregate - Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c - Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 - -> HashAggregate - Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c - Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 - -> HashAggregate - Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c - Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 -(23 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 8 | 4000 | 500 - 8 | 4000 | 500 - 9 | 4500 | 500 - 9 | 4500 | 500 - 18 | 4000 | 500 - 18 | 4000 | 500 - 19 | 4500 | 500 - 19 | 4500 | 500 - 28 | 4000 | 500 - 28 | 4000 | 500 - 29 | 4500 | 500 - 29 | 4500 | 500 -(12 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Parallelism within partitionwise aggregates SET min_parallel_table_scan_size TO '8kB'; SET parallel_setup_cost TO 0; @@ -1171,156 +1077,48 @@ -- PARTITION KEY, thus we will have a partial aggregation for them. EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) - -> Append - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p1.a - Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_p1.a - -> Partial HashAggregate - Group Key: pagg_tab_ml_p1.a - -> Parallel Seq Scan on pagg_tab_ml_p1 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a - Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_p2_s1.a - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s1.a - -> Parallel Seq Scan on pagg_tab_ml_p2_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s2.a - -> Parallel Seq Scan on pagg_tab_ml_p2_s2 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a - Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_p3_s1.a - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s1.a - -> Parallel Seq Scan on pagg_tab_ml_p3_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s2.a - -> Parallel Seq Scan on pagg_tab_ml_p3_s2 -(41 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 0 | 0 | 1000 - 1 | 1000 | 1000 - 2 | 2000 | 1000 - 10 | 0 | 1000 - 11 | 1000 | 1000 - 12 | 2000 | 1000 - 20 | 0 | 1000 - 21 | 1000 | 1000 - 22 | 2000 | 1000 -(9 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Partial aggregation at all levels as GROUP BY clause does not match with -- PARTITION KEY EXPLAIN (COSTS OFF) SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml_p1.b, (sum(pagg_tab_ml_p1.a)), (count(*)) - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p1.b - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_p1.b - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_p1.b - -> Parallel Seq Scan on pagg_tab_ml_p1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s1.b - -> Parallel Seq Scan on pagg_tab_ml_p2_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p2_s2.b - -> Parallel Seq Scan on pagg_tab_ml_p2_s2 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s1.b - -> Parallel Seq Scan on pagg_tab_ml_p3_s1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_p3_s2.b - -> Parallel Seq Scan on pagg_tab_ml_p3_s2 -(24 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; - b | sum | count ----+-------+------- - 0 | 30000 | 3000 - 1 | 33000 | 3000 - 2 | 36000 | 3000 - 3 | 39000 | 3000 - 4 | 42000 | 3000 -(5 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------------------- - Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) - -> Parallel Append - -> HashAggregate - Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c - Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 - -> HashAggregate - Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c - Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 - -> HashAggregate - Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c - Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 - -> HashAggregate - Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c - Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 - -> HashAggregate - Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c - Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 -(25 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 8 | 4000 | 500 - 8 | 4000 | 500 - 9 | 4500 | 500 - 9 | 4500 | 500 - 18 | 4000 | 500 - 18 | 4000 | 500 - 19 | 4500 | 500 - 19 | 4500 | 500 - 28 | 4000 | 500 - 28 | 4000 | 500 - 29 | 4500 | 500 - 29 | 4500 | 500 -(12 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Parallelism within partitionwise aggregates (single level) -- Add few parallel setup cost, so that we will see a plan which gathers -- partially created paths even for full aggregation and sticks a single Gather @@ -1339,177 +1137,54 @@ -- When GROUP BY clause matches; full aggregation is performed for each partition. EXPLAIN (COSTS OFF) SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x - Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para_p1.x - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_para_p1.x - -> Parallel Seq Scan on pagg_tab_para_p1 - -> Partial HashAggregate - Group Key: pagg_tab_para_p2.x - -> Parallel Seq Scan on pagg_tab_para_p2 - -> Partial HashAggregate - Group Key: pagg_tab_para_p3.x - -> Parallel Seq Scan on pagg_tab_para_p3 -(19 rows) - -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- When GROUP BY clause does not match; partial aggregation is performed for each partition. EXPLAIN (COSTS OFF) SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para_p1.y, (sum(pagg_tab_para_p1.x)), (avg(pagg_tab_para_p1.x)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.y - Filter: (avg(pagg_tab_para_p1.x) < '12'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para_p1.y - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_para_p1.y - -> Parallel Seq Scan on pagg_tab_para_p1 - -> Partial HashAggregate - Group Key: pagg_tab_para_p2.y - -> Parallel Seq Scan on pagg_tab_para_p2 - -> Partial HashAggregate - Group Key: pagg_tab_para_p3.y - -> Parallel Seq Scan on pagg_tab_para_p3 -(19 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; - y | sum | avg | count -----+-------+---------------------+------- - 0 | 15000 | 10.0000000000000000 | 1500 - 1 | 16500 | 11.0000000000000000 | 1500 - 10 | 15000 | 10.0000000000000000 | 1500 - 11 | 16500 | 11.0000000000000000 | 1500 -(4 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Test when parent can produce parallel paths but not any (or some) of its children ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0); ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0); ANALYZE pagg_tab_para; EXPLAIN (COSTS OFF) SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x - Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para_p1.x - -> Partial HashAggregate - Group Key: pagg_tab_para_p1.x - -> Parallel Append - -> Seq Scan on pagg_tab_para_p1 - -> Seq Scan on pagg_tab_para_p3 - -> Parallel Seq Scan on pagg_tab_para_p2 -(15 rows) - -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; +WARNING: adding incremental sort + gather merge path +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0); ANALYZE pagg_tab_para; EXPLAIN (COSTS OFF) SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x - Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para_p1.x - -> Partial HashAggregate - Group Key: pagg_tab_para_p1.x - -> Parallel Append - -> Seq Scan on pagg_tab_para_p1 - -> Seq Scan on pagg_tab_para_p2 - -> Seq Scan on pagg_tab_para_p3 -(15 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort -- Reset parallelism parameters to get partitionwise aggregation plan. RESET min_parallel_table_scan_size; RESET parallel_setup_cost; EXPLAIN (COSTS OFF) SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) - -> Append - -> HashAggregate - Group Key: pagg_tab_para_p1.x - Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p1 - -> HashAggregate - Group Key: pagg_tab_para_p2.x - Filter: (avg(pagg_tab_para_p2.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p2 - -> HashAggregate - Group Key: pagg_tab_para_p3.x - Filter: (avg(pagg_tab_para_p3.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p3 -(15 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - +WARNING: adding incremental sort + gather merge path +ERROR: could not find pathkey item to sort