public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tatsuro Yamada <[email protected]>
To: [email protected]
Subject: Add enable_groupagg GUC parameter to control GroupAggregate usage
Date: Fri, 6 Jun 2025 16:59:09 +0900
Message-ID: <CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com> (raw)
Hi hackers,
When I measured the execution time of a certain query with parallel query
enabled and disabled, I found that the execution time was slower when
parallel query was enabled.
To improve the performance of the parallel query, I considered adjusting
the execution plan and attempted to switch from GroupAggregate to
HashAggregate. However, I noticed that there was no GUC parameter to
disable GroupAggregate.
Therefore, I propose adding a new GUC parameter: enable_groupagg.
Below are the results of a performance test where I disabled
GroupAggregate using enable_groupagg. In this case, the planner chose
HashAggregate instead, which improved performance by about 35 times.
# Query Execution Results (Average of 3 measurements)
- With parallel query: 39546 seconds
- With parallel query and enable_groupagg turned off: 1115 seconds
# Query and Data Used (attached to this email)
- Query: test_query.sql
- Data: create_table.sql
# The steps to run the test are as follows.
For example, on psql:
1. Create tables:
\i create_table.sql
2. Execute a query:
\i test_query.sql
3. Execute a query using the new GUC parameter:
set enable_groupagg to off;
\i test_query.sql
As a benefit to users, while there has previously been a GUC parameter
to control HashAggregate, there was no corresponding way to control
GroupAggregate. This patch addresses that, giving users more flexibility
in tuning execution plans.
I've attached a WIP patch that adds this GUC parameter. I would
appreciate any feedback, especially regarding how many test cases I
should create.
To create new test cases for enable_groupagg, I looked into existing
test cases that use enable_hashagg and found that it is used in many
places (62 places). Should I add a test case for enable_groupagg in
the same place as enable_hashagg? I think that adding a new feature
requires a minimum number of test cases, so I would appreciate your
advice.
Additionally, based on the execution plan, I suspect the slowdown in the
parallel query might be caused by misestimates related to Sort or
Gather Merge.
While resolving those misestimates would ideally improve the root issue,
I'd like to keep the focus of this thread on adding the GUC parameter.
Then, I plan to report or address the estimation problem in a separate
thread.
Thanks,
Tatsuro Yamada
Attachments:
[application/octet-stream] test_query.sql (1.4K, ../CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com/3-test_query.sql)
download
[application/octet-stream] 0001-Add-new-GUC-parameter-enable_groupagg-WIP-r1.patch (5.4K, ../CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com/4-0001-Add-new-GUC-parameter-enable_groupagg-WIP-r1.patch)
download | inline diff:
From 461c64770e612661d974acc0fd39815108781580 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <[email protected]>
Date: Thu, 5 Jun 2025 18:50:34 +0900
Subject: [PATCH] Add new GUC parameter: enable_groupagg
Previously, there was no GUC parameter to control the use of
GroupAggregate, so we couldn't influence the planner's choice
in certain queries.
This patch adds a new parameter, "enable_groupagg", which
allows users to enable or disable GroupAggregate explicitly.
By disabling GroupAggregate, the planner may choose
HashAggregate instead, potentially resulting in a more
efficient execution plan for some queries.
---
doc/src/sgml/config.sgml | 14 ++++++++++++++
src/backend/optimizer/path/costsize.c | 3 +++
src/backend/utils/misc/guc_tables.c | 10 ++++++++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/optimizer/cost.h | 1 +
src/test/regress/expected/sysviews.out | 3 ++-
6 files changed, 31 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 021153b2a5f..edd0f3a13b8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5515,6 +5515,20 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-groupagg" xreflabel="enable_groupagg">
+ <term><varname>enable_groupagg</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>enable_groupagg</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Enables or disables the query planner's use of grouped
+ aggregation plan types. The default is <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
<term><varname>enable_hashjoin</varname> (<type>boolean</type>)
<indexterm>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 3d44815ed5a..80c68008d85 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -150,6 +150,7 @@ bool enable_tidscan = true;
bool enable_sort = true;
bool enable_incremental_sort = true;
bool enable_hashagg = true;
+bool enable_groupagg = true;
bool enable_nestloop = true;
bool enable_material = true;
bool enable_memoize = true;
@@ -2737,6 +2738,8 @@ cost_agg(Path *path, PlannerInfo *root,
/* Here we are able to deliver output on-the-fly */
startup_cost = input_startup_cost;
total_cost = input_total_cost;
+ if (aggstrategy == AGG_SORTED && !enable_groupagg && enable_hashagg)
+ ++disabled_nodes;
if (aggstrategy == AGG_MIXED && !enable_hashagg)
++disabled_nodes;
/* calcs phrased this way to match HASHED case, see note above */
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index f04bfedb2fd..a17b7fb1ba1 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -869,6 +869,16 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_groupagg", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the planner's use of grouped aggregation plans."),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &enable_groupagg,
+ true,
+ NULL, NULL, NULL
+ },
{
{"enable_material", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of materialization."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 341f88adc87..0514c327767 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -408,6 +408,7 @@
#enable_bitmapscan = on
#enable_gathermerge = on
#enable_hashagg = on
+#enable_groupagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
#enable_indexscan = on
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index d397fe27dc1..099d41fd7bd 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -57,6 +57,7 @@ extern PGDLLIMPORT bool enable_tidscan;
extern PGDLLIMPORT bool enable_sort;
extern PGDLLIMPORT bool enable_incremental_sort;
extern PGDLLIMPORT bool enable_hashagg;
+extern PGDLLIMPORT bool enable_groupagg;
extern PGDLLIMPORT bool enable_nestloop;
extern PGDLLIMPORT bool enable_material;
extern PGDLLIMPORT bool enable_memoize;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 83228cfca29..f10371d6e26 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -153,6 +153,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_distinct_reordering | on
enable_gathermerge | on
enable_group_by_reordering | on
+ enable_groupagg | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
@@ -172,7 +173,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(24 rows)
+(25 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
--
2.43.5
[application/octet-stream] create_table.sql (2.4K, ../CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com/5-create_table.sql)
download
view thread (18+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
In-Reply-To: <CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox