Обсуждение: Add enable_groupagg GUC parameter to control GroupAggregate usage
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.
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
Tatsuro Yamada
Вложения
Hi,
# Query Execution Results (Average of 3 measurements)
- With parallel query: 39546 seconds
- With parallel query and enable_groupagg turned off: 1115 seconds
Oops, I made a mistake. The correct execution time is:
- With parallel query: 39546 ms
- With parallel query and enable_groupagg turned off: 1115 ms
Regards,
Tatsuro Yamada
On Fri, Jun 6, 2025 at 1:29 PM Tatsuro Yamada <yamatattsu@gmail.com> wrote:
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.
I first thought enable_hashagg should be sufficient to choose one strategy over the other. But that is not true, enable_hashagg = true allows both the strategies, enable_hashagg = false disables just hash strategy. There's no way to disable group agg alone. So I think it makes sense to have this GUC.
I am surprised that we didn't see this being a problem for so long.
We seem to disable mixed strategy when enable_hashagg is false. Do we want to do the same when enable_groupagg = false as well?
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.
Some of those instances are for plan stability, all of which need not be replicated. But some of them explicitly test sort based grouping. For rest of them hash based plan seems to be the best one, so explicit enable_groupagg = false is not needed. We will need some test to test the switch though.
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.
+1.
Best Wishes,
Ashutosh Bapat
On Wed, 11 Jun 2025 at 20:37, Tatsuro Yamada <yamatattsu@gmail.com> wrote: > I created a regression test to check the enable_groupagg parameter in > the new patch. > To ensure plan stability, I disabled parallel query by setting the max_parallel_* > parameters to 0. > > Any feedback is welcome. Typically, in the regression tests we've used enable_sort to force a HashAgg. There are certainly times when that's not good enough and you might also need to disabe enable_indexscan too, so I understand the desire to add this GUC. It's probably going to be worth going over the regression tests to find where we use enable_sort to disable GroupAgg and replace those with your new GUC. Otherwise, people looking at those tests in the future will be a bit confused as to why the test didn't just SET enable_groupagg TO false; These will likely be good enough to serve as your test, rather than creating a new table to test this feature. I think you should also look at create_setop_path(), as I imagine that the same arguments for using enable_hashagg in that function apply equally to enable_groupagg. + if (aggstrategy == AGG_SORTED && !enable_groupagg && enable_hashagg) + ++disabled_nodes; This code looks a bit strange. You're only going to disable it if hash agg is enabled? If they're both disabled, let add_path() decide. Anyone who complains that they didn't get the aggregate type they wanted with both enable_hashagg and enable_groupagg set to off hasn't got a leg to stand on. David
Hi David,
>HashAgg. There are certainly times when that's not good enough and you
>might also need to disabe enable_indexscan too, so I understand the
desire to add this GUC.
Thank you for the explanation.
I wasn't aware that enable_sort could be used to switch from GroupAgg
to HashAgg.
From a user's perspective, I think many would expect that if
enable_hashagg exists, then enable_groupagg would as well. Adding
such a GUC parameter seems intuitive and reasonable.So, I believe
there's value in introducing the parameter I proposed.
On the other hand, if this technique (using enable_sort) is already
widely known and commonly used, and I'm simply unfamiliar with it,
then perhaps adding this GUC might not be worth the effort.
If several people support the idea of adding this GUC parameter,
I'm thinking of creating and submitting a patch that incorporates your
comment below. I believe both David and Ashutosh support the proposal.
Can I go ahead as planned?
What do others involved in planner and plan-tuning think?
>It's probably going to be worth going over the regression tests to
>find where we use enable_sort to disable GroupAgg and replace those
>with your new GUC. Otherwise, people looking at those tests in the
>future will be a bit confused as to why the test didn't just SET
>enable_groupagg TO false; These will likely be good enough to serve
>as your test, rather than creating a new table to test this feature.
I agree.
Replacing existing enable_sort usages in regression tests with the new
GUC parameter would help future developers understand the intent more
clearly.
Also, I realized we can sufficiently test the feature without creating
a new table.
>I think you should also look at create_setop_path(), as I imagine that
>the same arguments for using enable_hashagg in that function apply
>equally to enable_groupagg.
I looked into create_setop_path(), and I see that the aggregation
method is currently controlled as follows:
=====
/*
* Mark the path as disabled if enable_hashagg is off. While this
* isn't exactly a HashAgg node, it seems close enough to justify
* letting that switch control it.
*/
if (!enable_hashagg)
pathnode->path.disabled_nodes++;
=====
I plan to add enable_groupagg handling in a similar way.
>+ if (aggstrategy == AGG_SORTED && !enable_groupagg && enable_hashagg)
>+ ++disabled_nodes;
>
>This code looks a bit strange. You're only going to disable it if hash
>agg is enabled? If they're both disabled, let add_path() decide.
>Anyone who complains that they didn't get the aggregate type they
>wanted with both enable_hashagg and enable_groupagg set to off hasn't
>got a leg to stand on.
My intention was to make groupagg the fallback when both are disabled.
However, I understand that if the both are disabled, it's acceptable to
let the planner decide which strategy to use. I'll revise that part accordingly.
Thanks,
Tatsuro Yamada
On the other hand, if this technique (using enable_sort) is already
widely known and commonly used, and I'm simply unfamiliar with it,
then perhaps adding this GUC might not be worth the effort.
If several people support the idea of adding this GUC parameter,
I'm thinking of creating and submitting a patch that incorporates your
comment below. I believe both David and Ashutosh support the proposal.
Can I go ahead as planned?
What do others involved in planner and plan-tuning think?
>It's probably going to be worth going over the regression tests to
>find where we use enable_sort to disable GroupAgg and replace those
>with your new GUC. Otherwise, people looking at those tests in the
>future will be a bit confused as to why the test didn't just SET
>enable_groupagg TO false; These will likely be good enough to serve
>as your test, rather than creating a new table to test this feature.
I agree.
Replacing existing enable_sort usages in regression tests with the new
GUC parameter would help future developers understand the intent more
clearly.
Also, I realized we can sufficiently test the feature without creating
a new table.
>I think you should also look at create_setop_path(), as I imagine that
>the same arguments for using enable_hashagg in that function apply
>equally to enable_groupagg.
I looked into create_setop_path(), and I see that the aggregation
method is currently controlled as follows:
=====
/*
* Mark the path as disabled if enable_hashagg is off. While this
* isn't exactly a HashAgg node, it seems close enough to justify
* letting that switch control it.
*/
if (!enable_hashagg)
pathnode->path.disabled_nodes++;
=====
I plan to add enable_groupagg handling in a similar way.
>+ if (aggstrategy == AGG_SORTED && !enable_groupagg && enable_hashagg)
>+ ++disabled_nodes;
>
>This code looks a bit strange. You're only going to disable it if hash
>agg is enabled? If they're both disabled, let add_path() decide.
>Anyone who complains that they didn't get the aggregate type they
>wanted with both enable_hashagg and enable_groupagg set to off hasn't
>got a leg to stand on.
My intention was to make groupagg the fallback when both are disabled.
However, I understand that if the both are disabled, it's acceptable to
let the planner decide which strategy to use. I'll revise that part accordingly.
Thanks,
Tatsuro Yamada