diff options
| -rw-r--r-- | doc/src/sgml/config.sgml | 23 | ||||
| -rw-r--r-- | src/backend/optimizer/path/costsize.c | 1 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/planner.c | 3 | ||||
| -rw-r--r-- | src/backend/utils/misc/guc_tables.c | 15 | ||||
| -rw-r--r-- | src/backend/utils/misc/postgresql.conf.sample | 1 | ||||
| -rw-r--r-- | src/include/optimizer/cost.h | 1 | ||||
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/sysviews.out | 3 | ||||
| -rw-r--r-- | src/test/regress/sql/aggregates.sql | 6 |
9 files changed, 62 insertions, 2 deletions
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8e4145979dc..9eedab652df 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5311,6 +5311,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" </listitem> </varlistentry> + <varlistentry id="guc-enable-presorted-aggregate" xreflabel="enable_presorted_aggregate"> + <term><varname>enable_presorted_aggregate</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>enable_presorted_aggregate</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Controls if the query planner will produce a plan which will provide + rows which are presorted in the order required for the query's + <literal>ORDER BY</literal> / <literal>DISTINCT</literal> aggregate + functions. When disabled, the query planner will produce a plan which + will always require the executor to perform a sort before performing + aggregation of each aggregate function containing an + <literal>ORDER BY</literal> or <literal>DISTINCT</literal> clause. + When enabled, the planner will try to produce a more efficient plan + which provides input to the aggregate functions which is presorted in + the order they require for aggregation. The default value is + <literal>on</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan"> <term><varname>enable_seqscan</varname> (<type>boolean</type>) <indexterm> diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 0f0bcfb7e50..89d3c4352ce 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -151,6 +151,7 @@ bool enable_partitionwise_aggregate = false; bool enable_parallel_append = true; bool enable_parallel_hash = true; bool enable_partition_pruning = true; +bool enable_presorted_aggregate = true; bool enable_async_append = true; typedef struct diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index dfda251d956..e21e72eb870 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -3191,7 +3191,8 @@ make_pathkeys_for_groupagg(PlannerInfo *root, List *groupClause, List *tlist, * sets. All handling specific to ordered aggregates must be done by the * executor in that case. */ - if (root->numOrderedAggs == 0 || root->parse->groupingSets != NIL) + if (root->numOrderedAggs == 0 || root->parse->groupingSets != NIL || + !enable_presorted_aggregate) return grouppathkeys; /* diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 1bf14eec661..436afe1d215 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -972,6 +972,21 @@ struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, { + {"enable_presorted_aggregate", PGC_USERSET, QUERY_TUNING_METHOD, + gettext_noop("Enables the planner's ability to produce plans which " + "provide presorted input for ORDER BY / DISTINCT aggregate " + "functions."), + gettext_noop("Allows the query planner to build plans which provide " + "presorted input for aggregate functions with an ORDER BY / " + "DISTINCT clause. When disabled, implicit sorts are always " + "performed during execution."), + GUC_EXPLAIN + }, + &enable_presorted_aggregate, + true, + NULL, NULL, NULL + }, + { {"enable_async_append", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of async append plans."), NULL, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 043864597f8..5afdeb04de8 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -384,6 +384,7 @@ #enable_partition_pruning = on #enable_partitionwise_join = off #enable_partitionwise_aggregate = off +#enable_presorted_aggregate = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 204e94b6d10..b6cc2c9cd60 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -68,6 +68,7 @@ extern PGDLLIMPORT bool enable_partitionwise_aggregate; extern PGDLLIMPORT bool enable_parallel_append; extern PGDLLIMPORT bool enable_parallel_hash; extern PGDLLIMPORT bool enable_partition_pruning; +extern PGDLLIMPORT bool enable_presorted_aggregate; extern PGDLLIMPORT bool enable_async_append; extern PGDLLIMPORT int constraint_exclusion; diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index fc2bd40be2d..309c2dc8654 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1471,6 +1471,17 @@ group by ten; -> Seq Scan on tenk1 (5 rows) +-- Ensure no ordering is requested when enable_presorted_aggregate is off +set enable_presorted_aggregate to off; +explain (costs off) +select sum(two order by two) from tenk1; + QUERY PLAN +------------------------- + Aggregate + -> Seq Scan on tenk1 +(2 rows) + +reset enable_presorted_aggregate; -- -- Test combinations of DISTINCT and/or ORDER BY -- diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index 579b861d84f..001c6e7eb9d 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -128,10 +128,11 @@ select name, setting from pg_settings where name like 'enable%'; enable_partition_pruning | on enable_partitionwise_aggregate | off enable_partitionwise_join | off + enable_presorted_aggregate | on enable_seqscan | on enable_sort | on enable_tidscan | on -(20 rows) +(21 rows) -- Test that the pg_timezone_names and pg_timezone_abbrevs views are -- more-or-less working. We can't test their contents in any great detail diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index a4c00ff7a9d..15f6be8e15a 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -546,6 +546,12 @@ select from tenk1 group by ten; +-- Ensure no ordering is requested when enable_presorted_aggregate is off +set enable_presorted_aggregate to off; +explain (costs off) +select sum(two order by two) from tenk1; +reset enable_presorted_aggregate; + -- -- Test combinations of DISTINCT and/or ORDER BY -- |