regression.diffs
application/octet-stream
Filename: regression.diffs
Type: application/octet-stream
Part: 0
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