diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/strings.out ./results/strings.out --- /home/john/pgdev/postgresql/src/test/regress/expected/strings.out 2018-09-13 08:58:51.264376078 +0200 +++ ./results/strings.out 2019-02-23 08:47:03.579869203 +0100 @@ -1193,7 +1193,7 @@ select 0 = pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks; blocks -------- - f + t (1 row) TRUNCATE TABLE toasttest; diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/join.out ./results/join.out --- /home/john/pgdev/postgresql/src/test/regress/expected/join.out 2019-02-23 05:36:00.386251656 +0100 +++ ./results/join.out 2019-02-23 08:47:32.828085898 +0100 @@ -6512,18 +6512,17 @@ set local enable_parallel_hash = on; explain (costs off) select count(*) from simple r join extremely_skewed s using (id); - QUERY PLAN ------------------------------------------------------------------------ - Finalize Aggregate + QUERY PLAN +----------------------------------------------------------------- + Aggregate -> Gather Workers Planned: 1 - -> Partial Aggregate - -> Parallel Hash Join - Hash Cond: (r.id = s.id) - -> Parallel Seq Scan on simple r - -> Parallel Hash - -> Parallel Seq Scan on extremely_skewed s -(9 rows) + -> Parallel Hash Join + Hash Cond: (r.id = s.id) + -> Parallel Seq Scan on simple r + -> Parallel Hash + -> Parallel Seq Scan on extremely_skewed s +(8 rows) select count(*) from simple r join extremely_skewed s using (id); count diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/rowsecurity.out ./results/rowsecurity.out --- /home/john/pgdev/postgresql/src/test/regress/expected/rowsecurity.out 2019-02-23 05:35:46.818049575 +0100 +++ ./results/rowsecurity.out 2019-02-23 08:47:36.128110359 +0100 @@ -275,17 +275,17 @@ (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------- Hash Join - Hash Cond: (category.cid = document.cid) + Hash Cond: (document.cid = category.cid) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) - -> Seq Scan on category + -> Seq Scan on document + Filter: ((dlevel <= $0) AND f_leak(dtitle)) -> Hash - -> Seq Scan on document - Filter: ((dlevel <= $0) AND f_leak(dtitle)) + -> Seq Scan on category (9 rows) -- viewpoint from regress_rls_dave @@ -339,18 +339,17 @@ (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); - QUERY PLAN ----------------------------------------------------------------------------------------------------------- - Hash Join - Hash Cond: (category.cid = document.cid) + QUERY PLAN +---------------------------------------------------------------------------------------------------- + Nested Loop InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) - -> Seq Scan on category - -> Hash - -> Seq Scan on document - Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle)) -(9 rows) + -> Seq Scan on document + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle)) + -> Index Scan using category_pkey on category + Index Cond: (cid = document.cid) +(8 rows) -- 44 would technically fail for both p2r and p1r, but we should get an error -- back from p1r for this because it sorts first diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/tablesample.out ./results/tablesample.out --- /home/john/pgdev/postgresql/src/test/regress/expected/tablesample.out 2018-08-31 11:51:27.663052452 +0200 +++ ./results/tablesample.out 2019-02-23 08:47:33.644091947 +0100 @@ -5,13 +5,11 @@ SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 -(6 rows) + 9 +(4 rows) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0); id @@ -21,29 +19,27 @@ SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 -(6 rows) + 9 +(4 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0); id ---- + 3 4 5 - 6 7 8 -(5 rows) + 9 +(6 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0); id ---- - 7 -(1 row) +(0 rows) -- 100% should give repeatable count results (ie, all rows) in any case SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100); @@ -93,85 +89,79 @@ FETCH FIRST FROM tablesample_cur; id ---- - 3 + 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 4 + 7 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 5 + 8 (1 row) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 -(6 rows) + 9 +(4 rows) FETCH NEXT FROM tablesample_cur; id ---- - 6 + 9 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 7 -(1 row) +(0 rows) FETCH NEXT FROM tablesample_cur; id ---- - 8 -(1 row) +(0 rows) FETCH FIRST FROM tablesample_cur; id ---- - 3 + 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 4 + 7 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 5 + 8 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 6 + 9 (1 row) FETCH NEXT FROM tablesample_cur; id ---- - 7 -(1 row) +(0 rows) FETCH NEXT FROM tablesample_cur; id ---- - 8 -(1 row) +(0 rows) CLOSE tablesample_cur; END; diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/select_views.out ./results/select_views.out --- /home/john/pgdev/postgresql/src/test/regress/expected/select_views.out 2019-01-16 20:30:14.221863624 +0100 +++ ./results/select_views.out 2019-02-23 08:47:43.464164742 +0100 @@ -1492,11 +1492,11 @@ ------------------------------------------------------------------------------------ Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) - -> Nested Loop - Join Filter: (l.cid = r.cid) + -> Hash Join + Hash Cond: (r.cid = l.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) - -> Materialize + -> Hash -> Hash Join Hash Cond: (r_1.cid = l.cid) -> Seq Scan on credit_card r_1 diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/indirect_toast.out ./results/indirect_toast.out --- /home/john/pgdev/postgresql/src/test/regress/expected/indirect_toast.out 2018-08-31 11:51:27.635053701 +0200 +++ ./results/indirect_toast.out 2019-02-23 08:47:44.208170258 +0100 @@ -48,17 +48,17 @@ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 - ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 + ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- check we didn't screw with main/toast tuple visibility @@ -66,10 +66,10 @@ SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- now create a trigger that forces all Datums to be indirect ones @@ -89,49 +89,49 @@ UPDATE indtoasttest SET cnt = cnt +1 RETURNING substring(indtoasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- modification without modifying assigned value UPDATE indtoasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(indtoasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- modification modifying, but effectively not changing UPDATE indtoasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(indtoasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) UPDATE indtoasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(indtoasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 + (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 + (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) @@ -140,10 +140,10 @@ SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 + (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/partition_join.out ./results/partition_join.out --- /home/john/pgdev/postgresql/src/test/regress/expected/partition_join.out 2019-02-13 08:22:54.706084265 +0100 +++ ./results/partition_join.out 2019-02-23 08:47:51.160221805 +0100 @@ -1963,17 +1963,17 @@ QUERY PLAN ---------------------------------------------------------- Hash Join - Hash Cond: (t2.c = (t1.c)::text) + Hash Cond: (t3.c = (t1.c)::text) -> Append - -> Seq Scan on prt2_n_p1 t2 - -> Seq Scan on prt2_n_p2 t2_1 + -> Seq Scan on plt1_p1 t3 + -> Seq Scan on plt1_p2 t3_1 + -> Seq Scan on plt1_p3 t3_2 -> Hash -> Hash Join - Hash Cond: (t3.c = (t1.c)::text) + Hash Cond: (t2.c = (t1.c)::text) -> Append - -> Seq Scan on plt1_p1 t3 - -> Seq Scan on plt1_p2 t3_1 - -> Seq Scan on plt1_p3 t3_2 + -> Seq Scan on prt2_n_p1 t2 + -> Seq Scan on prt2_n_p2 t2_1 -> Hash -> Append -> Seq Scan on prt1_n_p1 t1 diff -U3 /home/john/pgdev/postgresql/src/test/regress/expected/partition_aggregate.out ./results/partition_aggregate.out --- /home/john/pgdev/postgresql/src/test/regress/expected/partition_aggregate.out 2018-09-01 04:40:51.641715296 +0200 +++ ./results/partition_aggregate.out 2019-02-23 08:47:51.696225780 +0100 @@ -963,36 +963,34 @@ -- is not partial agg safe. EXPLAIN (COSTS OFF) SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------- Sort - Sort Key: pagg_tab_ml_p2_s1.a, (sum(pagg_tab_ml_p2_s1.b)), (array_agg(DISTINCT pagg_tab_ml_p2_s1.c)) - -> Gather - Workers Planned: 2 - -> Parallel Append - -> 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 - -> Seq Scan on pagg_tab_ml_p2_s1 - -> Seq Scan on pagg_tab_ml_p2_s2 - -> 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 - -> Seq Scan on pagg_tab_ml_p3_s1 - -> Seq Scan on pagg_tab_ml_p3_s2 - -> GroupAggregate - Group Key: pagg_tab_ml_p1.a - Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_p1.a - -> Seq Scan on pagg_tab_ml_p1 -(27 rows) + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (array_agg(DISTINCT pagg_tab_ml_p1.c)) + -> Append + -> GroupAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p1.a + -> Seq Scan on pagg_tab_ml_p1 + -> 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 + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Seq Scan on pagg_tab_ml_p2_s2 + -> 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 + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Seq Scan on pagg_tab_ml_p3_s2 +(25 rows) SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; a | sum | array_agg | count @@ -1011,34 +1009,32 @@ -- Without ORDER BY clause, to test Gather at top-most path EXPLAIN (COSTS OFF) SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3; - QUERY PLAN ------------------------------------------------------------------ - Gather - Workers Planned: 2 - -> Parallel Append - -> 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 - -> Seq Scan on pagg_tab_ml_p2_s1 - -> Seq Scan on pagg_tab_ml_p2_s2 - -> 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 - -> Seq Scan on pagg_tab_ml_p3_s1 - -> Seq Scan on pagg_tab_ml_p3_s2 - -> GroupAggregate - Group Key: pagg_tab_ml_p1.a - Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_p1.a - -> Seq Scan on pagg_tab_ml_p1 -(25 rows) + QUERY PLAN +----------------------------------------------------------- + Append + -> GroupAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p1.a + -> Seq Scan on pagg_tab_ml_p1 + -> 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 + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Seq Scan on pagg_tab_ml_p2_s2 + -> 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 + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Seq Scan on pagg_tab_ml_p3_s2 +(23 rows) -- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY -- for level 1 only. For subpartitions, GROUP BY clause does not match with @@ -1398,24 +1394,22 @@ -------------------------------------------------------------------------------------- Sort Sort Key: pagg_tab_para_p1.y, (sum(pagg_tab_para_p1.x)), (avg(pagg_tab_para_p1.x)) - -> Finalize GroupAggregate + -> Finalize HashAggregate Group Key: pagg_tab_para_p1.y Filter: (avg(pagg_tab_para_p1.x) < '12'::numeric) - -> Gather Merge + -> Gather 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) + -> 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 +(17 rows) 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