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

Commit3226f47

Browse files
committed
Add enable_presorted_aggregate GUC
1349d27 added query planner support to allow more efficient execution ofaggregate functions which have an ORDER BY or a DISTINCT clause. Prior tothat commit, the planner would only request that the lower planner producea plan with the order required for the GROUP BY clause and it would beleft up to nodeAgg.c to perform the final sort of records within eachgroup so that the aggregate transition functions were called in thecorrect order. Now that the planner requests the lower planner produce aplan with the GROUP BY and the ORDER BY / DISTINCT aggregates in mind,there is the possibility that the planner chooses a plan which could beless efficient than what would have been produced before1349d27.While developing1349d27, I had in mind that Incremental Sort would helpus in cases where an index exists only on the GROUP BY column(s).Incremental Sort would just replace the implicit tuplesorts which arebeing performed in nodeAgg.c. However, because the planner has theflexibility to instead choose a plan which just performs a full sort onboth the GROUP BY and ORDER BY / DISTINCT aggregate columns, there ispotential for the planner to make a bad choice. The costing forIncremental Sort is not perfect as it assumes an even distribution of rowsto sort within each sort group.Here we add an escape hatch in the form of the enable_presorted_aggregateGUC. This will allow users to get the pre-PG16 behavior in cases wherethey have no other means to convince the query planner to produce a planwhich only sorts on the GROUP BY column(s).Discussion:https://postgr.es/m/CAApHDvr1Sm+g9hbv4REOVuvQKeDWXcKUAhmbK5K+dfun0s9CvA@mail.gmail.com
1 parentd21ded7 commit3226f47

File tree

9 files changed

+62
-2
lines changed

9 files changed

+62
-2
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5311,6 +5311,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
53115311
</listitem>
53125312
</varlistentry>
53135313

5314+
<varlistentry id="guc-enable-presorted-aggregate" xreflabel="enable_presorted_aggregate">
5315+
<term><varname>enable_presorted_aggregate</varname> (<type>boolean</type>)
5316+
<indexterm>
5317+
<primary><varname>enable_presorted_aggregate</varname> configuration parameter</primary>
5318+
</indexterm>
5319+
</term>
5320+
<listitem>
5321+
<para>
5322+
Controls if the query planner will produce a plan which will provide
5323+
rows which are presorted in the order required for the query's
5324+
<literal>ORDER BY</literal> / <literal>DISTINCT</literal> aggregate
5325+
functions. When disabled, the query planner will produce a plan which
5326+
will always require the executor to perform a sort before performing
5327+
aggregation of each aggregate function containing an
5328+
<literal>ORDER BY</literal> or <literal>DISTINCT</literal> clause.
5329+
When enabled, the planner will try to produce a more efficient plan
5330+
which provides input to the aggregate functions which is presorted in
5331+
the order they require for aggregation. The default value is
5332+
<literal>on</literal>.
5333+
</para>
5334+
</listitem>
5335+
</varlistentry>
5336+
53145337
<varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
53155338
<term><varname>enable_seqscan</varname> (<type>boolean</type>)
53165339
<indexterm>

‎src/backend/optimizer/path/costsize.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -151,6 +151,7 @@ boolenable_partitionwise_aggregate = false;
151151
boolenable_parallel_append= true;
152152
boolenable_parallel_hash= true;
153153
boolenable_partition_pruning= true;
154+
boolenable_presorted_aggregate= true;
154155
boolenable_async_append= true;
155156

156157
typedefstruct

‎src/backend/optimizer/plan/planner.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3191,7 +3191,8 @@ make_pathkeys_for_groupagg(PlannerInfo *root, List *groupClause, List *tlist,
31913191
* sets. All handling specific to ordered aggregates must be done by the
31923192
* executor in that case.
31933193
*/
3194-
if (root->numOrderedAggs==0||root->parse->groupingSets!=NIL)
3194+
if (root->numOrderedAggs==0||root->parse->groupingSets!=NIL||
3195+
!enable_presorted_aggregate)
31953196
returngrouppathkeys;
31963197

31973198
/*

‎src/backend/utils/misc/guc_tables.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -971,6 +971,21 @@ struct config_bool ConfigureNamesBool[] =
971971
true,
972972
NULL,NULL,NULL
973973
},
974+
{
975+
{"enable_presorted_aggregate",PGC_USERSET,QUERY_TUNING_METHOD,
976+
gettext_noop("Enables the planner's ability to produce plans which "
977+
"provide presorted input for ORDER BY / DISTINCT aggregate "
978+
"functions."),
979+
gettext_noop("Allows the query planner to build plans which provide "
980+
"presorted input for aggregate functions with an ORDER BY / "
981+
"DISTINCT clause. When disabled, implicit sorts are always "
982+
"performed during execution."),
983+
GUC_EXPLAIN
984+
},
985+
&enable_presorted_aggregate,
986+
true,
987+
NULL,NULL,NULL
988+
},
974989
{
975990
{"enable_async_append",PGC_USERSET,QUERY_TUNING_METHOD,
976991
gettext_noop("Enables the planner's use of async append plans."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -384,6 +384,7 @@
384384
#enable_partition_pruning = on
385385
#enable_partitionwise_join = off
386386
#enable_partitionwise_aggregate = off
387+
#enable_presorted_aggregate = on
387388
#enable_seqscan = on
388389
#enable_sort = on
389390
#enable_tidscan = on

‎src/include/optimizer/cost.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@ extern PGDLLIMPORT bool enable_partitionwise_aggregate;
6868
externPGDLLIMPORTboolenable_parallel_append;
6969
externPGDLLIMPORTboolenable_parallel_hash;
7070
externPGDLLIMPORTboolenable_partition_pruning;
71+
externPGDLLIMPORTboolenable_presorted_aggregate;
7172
externPGDLLIMPORTboolenable_async_append;
7273
externPGDLLIMPORTintconstraint_exclusion;
7374

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1471,6 +1471,17 @@ group by ten;
14711471
-> Seq Scan on tenk1
14721472
(5 rows)
14731473

1474+
-- Ensure no ordering is requested when enable_presorted_aggregate is off
1475+
set enable_presorted_aggregate to off;
1476+
explain (costs off)
1477+
select sum(two order by two) from tenk1;
1478+
QUERY PLAN
1479+
-------------------------
1480+
Aggregate
1481+
-> Seq Scan on tenk1
1482+
(2 rows)
1483+
1484+
reset enable_presorted_aggregate;
14741485
--
14751486
-- Test combinations of DISTINCT and/or ORDER BY
14761487
--

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -128,10 +128,11 @@ select name, setting from pg_settings where name like 'enable%';
128128
enable_partition_pruning | on
129129
enable_partitionwise_aggregate | off
130130
enable_partitionwise_join | off
131+
enable_presorted_aggregate | on
131132
enable_seqscan | on
132133
enable_sort | on
133134
enable_tidscan | on
134-
(20 rows)
135+
(21 rows)
135136

136137
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
137138
-- more-or-less working. We can't test their contents in any great detail

‎src/test/regress/sql/aggregates.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -546,6 +546,12 @@ select
546546
from tenk1
547547
group by ten;
548548

549+
-- Ensure no ordering is requested when enable_presorted_aggregate is off
550+
set enable_presorted_aggregate to off;
551+
explain (costs off)
552+
selectsum(twoorder by two)from tenk1;
553+
reset enable_presorted_aggregate;
554+
549555
--
550556
-- Test combinations of DISTINCT and/or ORDER BY
551557
--

0 commit comments

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