Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit01474f5

Browse files
committed
Force parallelism in partition_aggregate
Commitdb0d67d tweaked sort costing, which however resulted in acouple plan changes in our regression tests. Most of the new plans werefine, but partition_aggregate were meant to test parallel plans and thenew plans were serial.Fix that by lowering parallel_setup_cost to 0, which is enough to switchto the parallel plan again.Report and patch by David Rowley.Author: David RowleyReviewed-by: Tomas VondraDiscussion:https://postgr.es/m/CAApHDvpVFgWzXdtUQkjyOPhNrNvumRi_=ftgS79KeAZ92tnHKQ@mail.gmail.com
1 parent4209126 commit01474f5

File tree

2 files changed

+63
-54
lines changed

2 files changed

+63
-54
lines changed

‎src/test/regress/expected/partition_aggregate.out‎

Lines changed: 60 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -942,40 +942,43 @@ INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM gen
942942
ANALYZE pagg_tab_ml;
943943
-- For Parallel Append
944944
SET max_parallel_workers_per_gather TO 2;
945+
SET parallel_setup_cost = 0;
945946
-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
946947
-- for level 1 only. For subpartitions, GROUP BY clause does not match with
947948
-- PARTITION KEY, but still we do not see a partial aggregation as array_agg()
948949
-- is not partial agg safe.
949950
EXPLAIN (COSTS OFF)
950951
SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
951-
QUERY PLAN
952-
--------------------------------------------------------------------------------------
953-
Sort
954-
Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (array_agg(DISTINCT pagg_tab_ml.c))
955-
-> Append
956-
-> GroupAggregate
957-
Group Key: pagg_tab_ml.a
958-
Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
959-
-> Sort
960-
Sort Key: pagg_tab_ml.a
961-
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
962-
-> GroupAggregate
963-
Group Key: pagg_tab_ml_2.a
964-
Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
965-
-> Sort
966-
Sort Key: pagg_tab_ml_2.a
967-
-> Append
968-
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
969-
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
970-
-> GroupAggregate
971-
Group Key: pagg_tab_ml_5.a
972-
Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
973-
-> Sort
974-
Sort Key: pagg_tab_ml_5.a
975-
-> Append
976-
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
977-
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
978-
(25 rows)
952+
QUERY PLAN
953+
--------------------------------------------------------------------------------------------
954+
Gather Merge
955+
Workers Planned: 2
956+
-> Sort
957+
Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (array_agg(DISTINCT pagg_tab_ml.c))
958+
-> Parallel Append
959+
-> GroupAggregate
960+
Group Key: pagg_tab_ml.a
961+
Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
962+
-> Sort
963+
Sort Key: pagg_tab_ml.a
964+
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
965+
-> GroupAggregate
966+
Group Key: pagg_tab_ml_5.a
967+
Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
968+
-> Sort
969+
Sort Key: pagg_tab_ml_5.a
970+
-> Append
971+
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
972+
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
973+
-> GroupAggregate
974+
Group Key: pagg_tab_ml_2.a
975+
Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
976+
-> Sort
977+
Sort Key: pagg_tab_ml_2.a
978+
-> Append
979+
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
980+
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
981+
(27 rows)
979982

980983
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;
981984
a | sum | array_agg | count
@@ -994,33 +997,36 @@ SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HA
994997
-- Without ORDER BY clause, to test Gather at top-most path
995998
EXPLAIN (COSTS OFF)
996999
SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3;
997-
QUERY PLAN
998-
---------------------------------------------------------------------
999-
Append
1000-
-> GroupAggregate
1001-
Group Key: pagg_tab_ml.a
1002-
Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
1003-
-> Sort
1004-
Sort Key: pagg_tab_ml.a
1005-
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1006-
-> GroupAggregate
1007-
Group Key: pagg_tab_ml_2.a
1008-
Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
1009-
-> Sort
1010-
Sort Key: pagg_tab_ml_2.a
1011-
-> Append
1012-
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
1013-
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
1014-
-> GroupAggregate
1015-
Group Key: pagg_tab_ml_5.a
1016-
Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
1017-
-> Sort
1018-
Sort Key: pagg_tab_ml_5.a
1019-
-> Append
1020-
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
1021-
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
1022-
(23 rows)
1000+
QUERY PLAN
1001+
---------------------------------------------------------------------------
1002+
Gather
1003+
Workers Planned: 2
1004+
-> Parallel Append
1005+
-> GroupAggregate
1006+
Group Key: pagg_tab_ml.a
1007+
Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
1008+
-> Sort
1009+
Sort Key: pagg_tab_ml.a
1010+
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
1011+
-> GroupAggregate
1012+
Group Key: pagg_tab_ml_5.a
1013+
Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
1014+
-> Sort
1015+
Sort Key: pagg_tab_ml_5.a
1016+
-> Append
1017+
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
1018+
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
1019+
-> GroupAggregate
1020+
Group Key: pagg_tab_ml_2.a
1021+
Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
1022+
-> Sort
1023+
Sort Key: pagg_tab_ml_2.a
1024+
-> Append
1025+
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
1026+
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
1027+
(25 rows)
10231028

1029+
RESET parallel_setup_cost;
10241030
-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
10251031
-- for level 1 only. For subpartitions, GROUP BY clause does not match with
10261032
-- PARTITION KEY, thus we will have a partial aggregation for them.

‎src/test/regress/sql/partition_aggregate.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -222,6 +222,7 @@ ANALYZE pagg_tab_ml;
222222

223223
-- For Parallel Append
224224
SET max_parallel_workers_per_gather TO2;
225+
SET parallel_setup_cost=0;
225226

226227
-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
227228
-- for level 1 only. For subpartitions, GROUP BY clause does not match with
@@ -235,6 +236,8 @@ SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HA
235236
EXPLAIN (COSTS OFF)
236237
SELECT a,sum(b), array_agg(distinct c),count(*)FROM pagg_tab_mlGROUP BY aHAVINGavg(b)<3;
237238

239+
RESET parallel_setup_cost;
240+
238241
-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY
239242
-- for level 1 only. For subpartitions, GROUP BY clause does not match with
240243
-- PARTITION KEY, thus we will have a partial aggregation for them.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp