public inbox for [email protected]
help / color / mirror / Atom feedAdd enable_groupagg GUC parameter to control GroupAggregate usage
18+ messages / 5 participants
[nested] [flat]
* Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-06 07:59 Tatsuro Yamada <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Tatsuro Yamada @ 2025-06-06 07:59 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-06 10:03 Tatsuro Yamada <[email protected]>
parent: Tatsuro Yamada <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Tatsuro Yamada @ 2025-06-06 10:03 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-09 10:50 Ashutosh Bapat <[email protected]>
parent: Tatsuro Yamada <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Ashutosh Bapat @ 2025-06-09 10:50 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: [email protected]
On Fri, Jun 6, 2025 at 1:29 PM Tatsuro Yamada <[email protected]> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-11 03:07 Tatsuro Yamada <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2025-06-11 03:07 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: [email protected]
Hi Ashutosh,
Thanks for your reply!
>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.
Yes, exactly! Thank you for the clarification.
>I am surprised that we didn't see this being a problem for so long.
I was also wondering why this GUC parameter hadn't been introduced
earlier.
>We seem to disable mixed strategy when enable_hashagg is false. Do we want
to do the same when enable_groupagg = false as well?
As I mentioned in my previous email, setting enable_groupagg = false
resulted in better execution time compared to the mixed strategy (which,
as I understand, includes both HashAgg and GroupAgg). So, I believe
this new GUC parameter would be helpful for users in certain situations.
Here’s how the current and proposed behaviors compare:
Current behavior:
enable_hashagg = ON → Mixed
enable_hashagg = OFF → GroupAgg
After applying the patch:
enable_hashagg = ON, enable_groupagg = ON → Mixed
enable_hashagg = OFF, enable_groupagg = ON → GroupAgg
enable_hashagg = ON, enable_groupagg = OFF → HashAgg (new)
enable_hashagg = OFF, enable_groupagg = OFF → GroupAgg
In addition, if the both parameters = OFF, GroupAgg will be selected in
the patch. The reason is that I found a comment that HashAgg might use
too much memory, so I decided to prioritize using GroupAgg, which is
more secure.
In the last case, I chose to default to GroupAgg since I found a
comment suggesting HashAgg might consume excessive memory, so GroupAgg
seemed the safer fallback.
Some hackers may want to compare the actual execution plans and times
under different GUC settings, so I've attached my test results in
"test_result.txt".
>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.
Thanks for your advice. I'll create a regression test and send a new patch
to -hackers in my next email.
Regards,
Tatsuro Yamada
# 1. Setting: default (enable_hashagg = ON, enable_groupagg = ON)
# Mix strategy was used
# Execution time was 41 sec
====
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=159135.83..7790760.21 rows=100 width=66) (actual time=12449.124..41253.731 rows=100.00 loops=1)
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> GroupAggregate (cost=159135.83..1689495507.58 rows=22136 width=66) (actual time=12449.121..41253.648 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> Nested Loop (cost=159135.83..1689494898.84 rows=22136 width=34) (actual time=12317.664..41252.393 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197188565
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=10122.049..10269.869 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4889 read=11029, temp read=1482 written=11035
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=9985.641..10044.083 rows=11982.00 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 28488kB
Buffers: shared hit=4889 read=11029, temp read=1482 written=11035
Worker 0: Sort Method: external merge Disk: 29392kB
Worker 1: Sort Method: external merge Disk: 30112kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.377..852.678 rows=640266.67 loops=3)
Buffers: shared hit=4799 read=11029
-> Materialize (cost=33945.36..1051363622.90 rows=22136 width=4) (actual time=0.052..0.337 rows=5752.97 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=111584 read=19138
-> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=1785.640..2183.175 rows=5753.00 loops=1)
Buffers: shared hit=111584 read=19138
-> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=1785.567..2119.781 rows=6409.00 loops=1)
Buffers: shared hit=92357 read=19138
-> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=819.215..842.604 rows=28544.00 loops=1)
Group Key: ss.ss_customer_sk
Batches: 1 Memory Usage: 2329kB
Buffers: shared hit=2187 read=11744
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=23.253..768.211 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2187 read=11744
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=22.719..780.073 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2187 read=11744
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=5.144..424.772 rows=960134.67 loops=3)
Buffers: shared hit=1002 read=11744
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=17.501..17.502 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..17.253 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.044..0.044 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=90170 read=7394
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=18.769..103.477 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2496 read=1873
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=27.114..97.488 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=2496 read=1873
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.005..26.446 rows=239794.67 loops=3)
Buffers: shared hit=1311 read=1873
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=27.079..27.080 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.033..26.791 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=11.472..831.348 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2043 read=5521
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=18.591..824.234 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2043 read=5521
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=0.334..455.274 rows=480516.00 loops=3)
Buffers: shared hit=858 read=5521
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=18.205..18.206 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.048..17.917 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Planning:
Buffers: shared hit=316
Planning Time: 5.683 ms
Execution Time: 41263.299 ms
(98 rows)
====
# 2. Setting: enable_hashagg = OFF, enable_groupagg = ON
# GroupAgg was used
# Execution time was 44 sec
====
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=10197.188..44152.766 rows=100.00 loops=1)
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=10197.187..44152.676 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=10129.162..44151.929 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197184348
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=8741.033..8900.320 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5469 read=10449, temp read=1482 written=11040
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=8499.285..8571.085 rows=11989.67 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 30368kB
Buffers: shared hit=5469 read=10449, temp read=1482 written=11040
Worker 0: Sort Method: external merge Disk: 29104kB
Worker 1: Sort Method: external merge Disk: 28560kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=2.220..766.199 rows=640266.67 loops=3)
Buffers: shared hit=5379 read=10449
-> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.032..0.363 rows=5752.84 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=113289 read=17433
-> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=1084.423..1391.426 rows=5753.00 loops=1)
Buffers: shared hit=113289 read=17433
-> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=1084.375..1346.642 rows=6409.00 loops=1)
Buffers: shared hit=94062 read=17433
-> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=545.023..574.883 rows=28544.00 loops=1)
Buffers: shared hit=2031 read=11900
-> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=545.019..556.733 rows=33609.00 loops=1)
Sort Key: ss.ss_customer_sk
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=2031 read=11900
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=33.440..529.429 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2031 read=11900
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=37.334..511.304 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2031 read=11900
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=4.599..258.454 rows=960134.67 loops=3)
Buffers: shared hit=846 read=11900
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=32.247..32.248 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..31.303 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.026..0.026 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92031 read=5533
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=28.354..100.442 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=15.023..74.578 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.007..19.459 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=14.987..14.987 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.016..14.672 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=69.799..443.128 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2031 read=5533
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=66.236..401.988 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2031 read=5533
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=10.580..208.806 rows=480516.00 loops=3)
Buffers: shared hit=846 read=5533
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=55.629..55.630 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..53.491 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.006..0.006 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Settings: enable_hashagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 2.101 ms
Execution Time: 44168.266 ms
(101 rows)
====
# 3. Setting: enable_hashagg = ON, enable_groupagg = OFF
# HashAgg was used
# Execution time was 1 sec
====
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1051399723.16..1051399723.41 rows=100 width=66) (actual time=1096.948..1097.642 rows=100.00 loops=1)
Buffers: shared hit=133811 read=19929
-> Sort (cost=1051399723.16..1051399778.50 rows=22136 width=66) (actual time=1096.946..1097.633 rows=100.00 loops=1)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: top-N heapsort Memory: 49kB
Buffers: shared hit=133811 read=19929
-> HashAggregate (cost=1051398655.78..1051398877.14 rows=22136 width=66) (actual time=1087.378..1090.664 rows=5740.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Batches: 1 Memory Usage: 1049kB
Buffers: shared hit=133805 read=19929
-> Nested Loop (cost=33945.79..1051398268.40 rows=22136 width=34) (actual time=702.467..1076.172 rows=5753.00 loops=1)
Buffers: shared hit=133805 read=19929
-> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=702.451..958.299 rows=5753.00 loops=1)
Buffers: shared hit=114045 read=16677
-> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=702.387..921.978 rows=6409.00 loops=1)
Buffers: shared hit=94818 read=16677
-> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=354.993..373.328 rows=28544.00 loops=1)
Group Key: ss.ss_customer_sk
Batches: 1 Memory Usage: 2329kB
Buffers: shared hit=2223 read=11708
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=17.988..336.471 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2223 read=11708
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=16.964..337.747 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2223 read=11708
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=0.394..157.640 rows=960134.67 loops=3)
Buffers: shared hit=1038 read=11708
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=16.492..16.493 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..15.934 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.018..0.018 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92595 read=4969
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=72.010..153.327 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=44.282..111.007 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.006..22.145 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=44.238..44.239 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.015..42.452 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=34.268..183.058 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2595 read=4969
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=30.132..174.422 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2595 read=4969
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=3.432..69.160 rows=480516.00 loops=3)
Buffers: shared hit=1410 read=4969
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=26.671..26.672 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..26.377 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.005..0.005 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
-> Index Scan using customer_demographics_pkey on customer_demographics cd (cost=0.43..1.57 rows=1 width=38) (actual time=0.019..0.019 rows=1.00 loops=5753)
Index Cond: (cd_demo_sk = c.c_current_cdemo_sk)
Index Searches: 5753
Buffers: shared hit=19760 read=3252
Settings: enable_groupagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 21.057 ms
Execution Time: 1105.672 ms
(91 rows)
====
# 4. Setting: enable_hashagg = OFF, enable_groupagg = OFF
# GroupAgg was used
# Execution time was 38 sec
====
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=7611.145..38354.564 rows=100.00 loops=1)
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=7611.128..38354.435 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=7510.502..38353.114 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197184348
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=6363.769..6499.733 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5833 read=10085, temp read=1482 written=11039
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=6163.457..6232.614 rows=11966.67 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 28992kB
Buffers: shared hit=5833 read=10085, temp read=1482 written=11039
Worker 0: Sort Method: external merge Disk: 28752kB
Worker 1: Sort Method: external merge Disk: 30280kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.737..321.713 rows=640266.67 loops=3)
Buffers: shared hit=5743 read=10085
-> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.025..0.325 rows=5752.84 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=113950 read=16772
-> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=865.332..1133.116 rows=5753.00 loops=1)
Buffers: shared hit=113950 read=16772
-> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=863.365..1073.375 rows=6409.00 loops=1)
Buffers: shared hit=94723 read=16772
-> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=393.964..415.541 rows=28544.00 loops=1)
Buffers: shared hit=2128 read=11803
-> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=393.957..402.146 rows=33609.00 loops=1)
Sort Key: ss.ss_customer_sk
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=2128 read=11803
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=80.713..349.762 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2128 read=11803
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=44.679..308.141 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2128 read=11803
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=9.033..125.081 rows=960134.67 loops=3)
Buffers: shared hit=943 read=11803
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=35.605..35.606 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.036..35.172 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.022..0.022 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92595 read=4969
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=39.476..124.540 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=20.456..94.877 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.020..28.916 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=20.396..20.397 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..20.094 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=56.442..326.649 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2595 read=4969
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=64.537..301.087 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2595 read=4969
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=11.234..130.365 rows=480516.00 loops=3)
Buffers: shared hit=1410 read=4969
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=53.242..53.243 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.025..52.328 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Settings: enable_hashagg = 'off', enable_groupagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 10.736 ms
Execution Time: 38358.196 ms
(101 rows)
====
Attachments:
[text/plain] test_result.txt (40.5K, ../../CAOKkKFsLbbpVMCG2_bV8u7M2sXLTs0y_yfMpywKekGi+qKtWKw@mail.gmail.com/3-test_result.txt)
download | inline:
# 1. Setting: default (enable_hashagg = ON, enable_groupagg = ON)
# Mix strategy was used
# Execution time was 41 sec
====
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=159135.83..7790760.21 rows=100 width=66) (actual time=12449.124..41253.731 rows=100.00 loops=1)
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> GroupAggregate (cost=159135.83..1689495507.58 rows=22136 width=66) (actual time=12449.121..41253.648 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> Nested Loop (cost=159135.83..1689494898.84 rows=22136 width=34) (actual time=12317.664..41252.393 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197188565
Buffers: shared hit=116473 read=30167, temp read=1482 written=11035
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=10122.049..10269.869 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4889 read=11029, temp read=1482 written=11035
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=9985.641..10044.083 rows=11982.00 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 28488kB
Buffers: shared hit=4889 read=11029, temp read=1482 written=11035
Worker 0: Sort Method: external merge Disk: 29392kB
Worker 1: Sort Method: external merge Disk: 30112kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.377..852.678 rows=640266.67 loops=3)
Buffers: shared hit=4799 read=11029
-> Materialize (cost=33945.36..1051363622.90 rows=22136 width=4) (actual time=0.052..0.337 rows=5752.97 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=111584 read=19138
-> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=1785.640..2183.175 rows=5753.00 loops=1)
Buffers: shared hit=111584 read=19138
-> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=1785.567..2119.781 rows=6409.00 loops=1)
Buffers: shared hit=92357 read=19138
-> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=819.215..842.604 rows=28544.00 loops=1)
Group Key: ss.ss_customer_sk
Batches: 1 Memory Usage: 2329kB
Buffers: shared hit=2187 read=11744
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=23.253..768.211 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2187 read=11744
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=22.719..780.073 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2187 read=11744
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=5.144..424.772 rows=960134.67 loops=3)
Buffers: shared hit=1002 read=11744
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=17.501..17.502 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..17.253 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.044..0.044 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=90170 read=7394
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=18.769..103.477 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2496 read=1873
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=27.114..97.488 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=2496 read=1873
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.005..26.446 rows=239794.67 loops=3)
Buffers: shared hit=1311 read=1873
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=27.079..27.080 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.033..26.791 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=11.472..831.348 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2043 read=5521
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=18.591..824.234 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2043 read=5521
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=0.334..455.274 rows=480516.00 loops=3)
Buffers: shared hit=858 read=5521
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=18.205..18.206 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.048..17.917 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Planning:
Buffers: shared hit=316
Planning Time: 5.683 ms
Execution Time: 41263.299 ms
(98 rows)
====
# 2. Setting: enable_hashagg = OFF, enable_groupagg = ON
# GroupAgg was used
# Execution time was 44 sec
====
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=10197.188..44152.766 rows=100.00 loops=1)
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=10197.187..44152.676 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=10129.162..44151.929 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197184348
Buffers: shared hit=118758 read=27882, temp read=1482 written=11040
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=8741.033..8900.320 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5469 read=10449, temp read=1482 written=11040
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=8499.285..8571.085 rows=11989.67 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 30368kB
Buffers: shared hit=5469 read=10449, temp read=1482 written=11040
Worker 0: Sort Method: external merge Disk: 29104kB
Worker 1: Sort Method: external merge Disk: 28560kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=2.220..766.199 rows=640266.67 loops=3)
Buffers: shared hit=5379 read=10449
-> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.032..0.363 rows=5752.84 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=113289 read=17433
-> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=1084.423..1391.426 rows=5753.00 loops=1)
Buffers: shared hit=113289 read=17433
-> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=1084.375..1346.642 rows=6409.00 loops=1)
Buffers: shared hit=94062 read=17433
-> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=545.023..574.883 rows=28544.00 loops=1)
Buffers: shared hit=2031 read=11900
-> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=545.019..556.733 rows=33609.00 loops=1)
Sort Key: ss.ss_customer_sk
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=2031 read=11900
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=33.440..529.429 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2031 read=11900
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=37.334..511.304 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2031 read=11900
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=4.599..258.454 rows=960134.67 loops=3)
Buffers: shared hit=846 read=11900
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=32.247..32.248 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..31.303 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.026..0.026 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92031 read=5533
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=28.354..100.442 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=15.023..74.578 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.007..19.459 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=14.987..14.987 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.016..14.672 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=69.799..443.128 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2031 read=5533
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=66.236..401.988 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2031 read=5533
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=10.580..208.806 rows=480516.00 loops=3)
Buffers: shared hit=846 read=5533
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=55.629..55.630 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..53.491 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.006..0.006 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Settings: enable_hashagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 2.101 ms
Execution Time: 44168.266 ms
(101 rows)
====
# 3. Setting: enable_hashagg = ON, enable_groupagg = OFF
# HashAgg was used
# Execution time was 1 sec
====
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1051399723.16..1051399723.41 rows=100 width=66) (actual time=1096.948..1097.642 rows=100.00 loops=1)
Buffers: shared hit=133811 read=19929
-> Sort (cost=1051399723.16..1051399778.50 rows=22136 width=66) (actual time=1096.946..1097.633 rows=100.00 loops=1)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: top-N heapsort Memory: 49kB
Buffers: shared hit=133811 read=19929
-> HashAggregate (cost=1051398655.78..1051398877.14 rows=22136 width=66) (actual time=1087.378..1090.664 rows=5740.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Batches: 1 Memory Usage: 1049kB
Buffers: shared hit=133805 read=19929
-> Nested Loop (cost=33945.79..1051398268.40 rows=22136 width=34) (actual time=702.467..1076.172 rows=5753.00 loops=1)
Buffers: shared hit=133805 read=19929
-> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=702.451..958.299 rows=5753.00 loops=1)
Buffers: shared hit=114045 read=16677
-> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=702.387..921.978 rows=6409.00 loops=1)
Buffers: shared hit=94818 read=16677
-> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=354.993..373.328 rows=28544.00 loops=1)
Group Key: ss.ss_customer_sk
Batches: 1 Memory Usage: 2329kB
Buffers: shared hit=2223 read=11708
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=17.988..336.471 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2223 read=11708
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=16.964..337.747 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2223 read=11708
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=0.394..157.640 rows=960134.67 loops=3)
Buffers: shared hit=1038 read=11708
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=16.492..16.493 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..15.934 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.018..0.018 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92595 read=4969
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=72.010..153.327 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=44.282..111.007 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.006..22.145 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=44.238..44.239 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.015..42.452 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=34.268..183.058 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2595 read=4969
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=30.132..174.422 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2595 read=4969
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=3.432..69.160 rows=480516.00 loops=3)
Buffers: shared hit=1410 read=4969
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=26.671..26.672 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..26.377 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.005..0.005 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
-> Index Scan using customer_demographics_pkey on customer_demographics cd (cost=0.43..1.57 rows=1 width=38) (actual time=0.019..0.019 rows=1.00 loops=5753)
Index Cond: (cd_demo_sk = c.c_current_cdemo_sk)
Index Searches: 5753
Buffers: shared hit=19760 read=3252
Settings: enable_groupagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 21.057 ms
Execution Time: 1105.672 ms
(91 rows)
====
# 4. Setting: enable_hashagg = OFF, enable_groupagg = OFF
# GroupAgg was used
# Execution time was 38 sec
====
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=7611.145..38354.564 rows=100.00 loops=1)
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=7611.128..38354.435 rows=100.00 loops=1)
Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=7510.502..38353.114 rows=101.00 loops=1)
Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk)
Rows Removed by Join Filter: 197184348
Buffers: shared hit=119783 read=26857, temp read=1482 written=11039
-> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=6363.769..6499.733 rows=34276.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5833 read=10085, temp read=1482 written=11039
-> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=6163.457..6232.614 rows=11966.67 loops=3)
Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count
Sort Method: external merge Disk: 28992kB
Buffers: shared hit=5833 read=10085, temp read=1482 written=11039
Worker 0: Sort Method: external merge Disk: 28752kB
Worker 1: Sort Method: external merge Disk: 30280kB
-> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.737..321.713 rows=640266.67 loops=3)
Buffers: shared hit=5743 read=10085
-> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.025..0.325 rows=5752.84 loops=34276)
Storage: Memory Maximum Storage: 289kB
Buffers: shared hit=113950 read=16772
-> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=865.332..1133.116 rows=5753.00 loops=1)
Buffers: shared hit=113950 read=16772
-> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=863.365..1073.375 rows=6409.00 loops=1)
Buffers: shared hit=94723 read=16772
-> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=393.964..415.541 rows=28544.00 loops=1)
Buffers: shared hit=2128 read=11803
-> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=393.957..402.146 rows=33609.00 loops=1)
Sort Key: ss.ss_customer_sk
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=2128 read=11803
-> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=80.713..349.762 rows=33609.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2128 read=11803
-> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=44.679..308.141 rows=11203.00 loops=3)
Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk)
Buffers: shared hit=2128 read=11803
-> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=9.033..125.081 rows=960134.67 loops=3)
Buffers: shared hit=943 read=11803
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=35.605..35.606 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.036..35.172 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.022..0.022 rows=0.22 loops=28544)
Index Cond: (c_customer_sk = ss.ss_customer_sk)
Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1)))
Rows Removed by Filter: 1
Index Searches: 28544
Buffers: shared hit=92595 read=4969
SubPlan 2
-> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=39.476..124.540 rows=8156.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4369
-> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=20.456..94.877 rows=2718.67 loops=3)
Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk)
Buffers: shared hit=4369
-> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.020..28.916 rows=239794.67 loops=3)
Buffers: shared hit=3184
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=20.396..20.397 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..20.094 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
SubPlan 4
-> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=56.442..326.649 rows=16874.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2595 read=4969
-> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=64.537..301.087 rows=5624.67 loops=3)
Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk)
Buffers: shared hit=2595 read=4969
-> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=11.234..130.365 rows=480516.00 loops=3)
Buffers: shared hit=1410 read=4969
-> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=53.242..53.243 rows=848.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 38kB
Buffers: shared hit=1185
-> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.025..52.328 rows=848.00 loops=3)
Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002))
Rows Removed by Filter: 72201
Buffers: shared hit=1185
-> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409)
Index Cond: (ca_address_sk = c.c_current_addr_sk)
Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[]))
Rows Removed by Filter: 0
Index Searches: 6409
Buffers: shared hit=19227
Settings: enable_hashagg = 'off', enable_groupagg = 'off'
Planning:
Buffers: shared hit=316
Planning Time: 10.736 ms
Execution Time: 38358.196 ms
(101 rows)
====
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-11 08:36 Tatsuro Yamada <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2025-06-11 08:36 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: [email protected]
Hi Ashtosh and hackers,
> >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.
>
> Thanks for your advice. I'll create a regression test and send a new patch
> to -hackers in my next email.
>
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.
Please see the attached file.
Thanks,
Tatsuro Yamada
Attachments:
[application/octet-stream] 0001-Add-new-GUC-parameter-enable_groupagg-WIP-r2.patch (11.7K, ../../CAOKkKFvgjAwtUFKh3baZ7BcQ5u+wS_DO+2n7dh0un+19v_VOzQ@mail.gmail.com/3-0001-Add-new-GUC-parameter-enable_groupagg-WIP-r2.patch)
download | inline diff:
From 2d9798207cdbb1bbe7c1858e36d69bdb17103ecf 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/aggregates.out | 120 ++++++++++++++++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/aggregates.sql | 81 ++++++++++++
8 files changed, 232 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/aggregates.out b/src/test/regress/expected/aggregates.out
index 1f1ce2380af..0911cb33c0a 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -3581,3 +3581,123 @@ drop table agg_hash_1;
drop table agg_hash_2;
drop table agg_hash_3;
drop table agg_hash_4;
+-- create table to check enable_groupagg
+CREATE TABLE test_groupagg(
+ id serial primary key,
+ c1 text,
+ c2 text,
+ c3 numeric);
+INSERT INTO test_groupagg (c1, c2, c3) VALUES
+('a', 'GGG', 100),
+('a', 'GGG', 150),
+('a', 'rrr', 200),
+('b', 'ooo', 300),
+('b', 'ooo', 250),
+('b', 'uuu', 100),
+('c', 'ppp', 500),
+('c', 'ppp', 600),
+('c', 'aaa', 550);
+ANALYZE;
+-- default: GroupAgg and HashAgg are mixed and selected
+SET max_parallel_workers to 0;
+SET max_parallel_workers_per_gather to 0;
+SET enable_hashagg to default;
+SET enable_groupagg to default;
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ GroupAggregate
+ Group Key: sub.c1
+ -> Sort
+ Sort Key: sub.c1
+ -> Subquery Scan on sub
+ -> HashAggregate
+ Group Key: test_groupagg.c1, test_groupagg.c2
+ -> Seq Scan on test_groupagg
+ Settings: max_parallel_workers = '0', max_parallel_workers_per_gather = '0'
+(9 rows)
+
+-- Only GroupAgg is selected
+set enable_hashagg to off;
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
+ GroupAggregate
+ Group Key: test_groupagg.c1
+ -> GroupAggregate
+ Group Key: test_groupagg.c1, test_groupagg.c2
+ -> Sort
+ Sort Key: test_groupagg.c1, test_groupagg.c2
+ -> Seq Scan on test_groupagg
+ Settings: max_parallel_workers = '0', max_parallel_workers_per_gather = '0', enable_hashagg = 'off'
+(8 rows)
+
+-- Only HashAgg is selected
+SET enable_hashagg to on;
+SET enable_groupagg to off;
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: test_groupagg.c1
+ -> HashAggregate
+ Group Key: test_groupagg.c1
+ -> HashAggregate
+ Group Key: test_groupagg.c1, test_groupagg.c2
+ -> Seq Scan on test_groupagg
+ Settings: max_parallel_workers = '0', max_parallel_workers_per_gather = '0', enable_groupagg = 'off'
+(8 rows)
+
+-- Only GroupAgg is selected as a fallback
+SET enable_hashagg to off;
+SET enable_groupagg to off;
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ GroupAggregate
+ Group Key: test_groupagg.c1
+ -> GroupAggregate
+ Group Key: test_groupagg.c1, test_groupagg.c2
+ -> Sort
+ Sort Key: test_groupagg.c1, test_groupagg.c2
+ -> Seq Scan on test_groupagg
+ Settings: max_parallel_workers = '0', max_parallel_workers_per_gather = '0', enable_hashagg = 'off', enable_groupagg = 'off'
+(8 rows)
+
+RESET enable_hashagg;
+RESET enable_groupagg;
+RESET max_parallel_workers;
+RESET max_parallel_workers_per_gather;
+DROP TABLE test_groupagg;
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.
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 277b4b198cc..92493ead1f9 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1644,3 +1644,84 @@ drop table agg_hash_1;
drop table agg_hash_2;
drop table agg_hash_3;
drop table agg_hash_4;
+
+-- create table to check enable_groupagg
+CREATE TABLE test_groupagg(
+ id serial primary key,
+ c1 text,
+ c2 text,
+ c3 numeric);
+INSERT INTO test_groupagg (c1, c2, c3) VALUES
+('a', 'GGG', 100),
+('a', 'GGG', 150),
+('a', 'rrr', 200),
+('b', 'ooo', 300),
+('b', 'ooo', 250),
+('b', 'uuu', 100),
+('c', 'ppp', 500),
+('c', 'ppp', 600),
+('c', 'aaa', 550);
+ANALYZE;
+
+-- default: GroupAgg and HashAgg are mixed and selected
+SET max_parallel_workers to 0;
+SET max_parallel_workers_per_gather to 0;
+SET enable_hashagg to default;
+SET enable_groupagg to default;
+
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+
+-- Only GroupAgg is selected
+set enable_hashagg to off;
+
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+
+-- Only HashAgg is selected
+SET enable_hashagg to on;
+SET enable_groupagg to off;
+
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+
+-- Only GroupAgg is selected as a fallback
+SET enable_hashagg to off;
+SET enable_groupagg to off;
+
+EXPLAIN(costs off, settings)
+SELECT c1, AVG(total)
+FROM (
+ SELECT c1, c2, SUM(c3) AS total
+ FROM test_groupagg
+ GROUP BY c1, c2
+) AS sub
+GROUP BY c1
+ORDER BY c1;
+
+RESET enable_hashagg;
+RESET enable_groupagg;
+RESET max_parallel_workers;
+RESET max_parallel_workers_per_gather;
+DROP TABLE test_groupagg;
--
2.43.5
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-16 02:49 David Rowley <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: David Rowley @ 2025-06-16 02:49 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; [email protected]
On Wed, 11 Jun 2025 at 20:37, Tatsuro Yamada <[email protected]> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2025-06-20 10:34 Tatsuro Yamada <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2025-06-20 10:34 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; [email protected]
--0000000000008179180637fe69a5
Content-Type: text/plain; charset="UTF-8"
Hi David,
>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.
Thank you for the explanation.
I wasn't aware that enable_sort could be used to switch from GroupAgg
to HashAgg.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-06-24 23:05 Richard Guo <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Richard Guo @ 2026-06-24 23:05 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected]
On Fri, Jun 20, 2025 at 7:35 PM Tatsuro Yamada <[email protected]> wrote:
> 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?
I think this is a nice idea. It is true that we can sometimes use
enable_sort to push the planner off a sorted grouping plan, but it is
a much bigger hammer, as it discourages every Sort in the query, not
just the one under the grouping, so it can move other parts of the
plan around and doesn't really isolate the grouping choice. And it
doesn't always work anyway. If the grouping input happens to be
cheaply sorted there's no Sort to penalize, so the sorted plan
survives. I ran into exactly that in union.sql, where enable_sort=off
isn't really producing the hashed INTERSECT it looks like it is
testing. Look at this plan from union.out:
-- check hashed implementation
set enable_hashagg = true;
set enable_sort = false;
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
QUERY PLAN
----------------------------------------------------------
SetOp Intersect
-> Function Scan on generate_series
-> Function Scan on generate_series generate_series_1
(3 rows)
Also, without such a GUC, there are some paths that we have no way to
generate. Look at this comment in union.sql:
-- We've no way to check hashed UNION as the empty pathkeys in the Append are
-- fine to make use of Unique, which is cheaper than HashAggregate and we've
-- no means to disable Unique.
Regarding the patch, currently the new GUC only covers GroupAggregate.
I think we should make it to cover all sort-based equivalents of
enable_hashagg, such as the sorted mode of SetOp (as David suggested),
sort-based Unique step used for DISTINCT and semijoin
unique-ification, and maybe also Group nodes.
I rebased your last patch and made some changes. Attached is what I
ended up with.
- Richard
Attachments:
[application/octet-stream] v3-0001-Add-an-enable_groupagg-GUC-parameter.patch (31.8K, ../../CAMbWs4-rqGC78jHduXO_feXCFzK=gzrcSHRTkATTKChG3YGq0A@mail.gmail.com/2-v3-0001-Add-an-enable_groupagg-GUC-parameter.patch)
download | inline diff:
From 96b2d5526ebf3e5d910446b088098702836869cd Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Wed, 24 Jun 2026 18:09:06 +0900
Subject: [PATCH v3] Add an enable_groupagg GUC parameter
We've long had enable_hashagg to discourage hashed aggregation, but
there was no equivalent for grouping that works by reading presorted
input. That's handy when investigating planner cost misestimates, and
as an escape hatch when a sorted grouping plan is chosen over a much
cheaper hashed one.
enable_groupagg (on by default) covers the GroupAggregate and Group
nodes, the sort-based Unique step used for DISTINCT and semijoin
unique-ification, and the sorted mode of SetOp. It isn't a hard
switch; it only bumps disabled_nodes, so plans with no other choice
are still produced.
Several regression and module tests had been setting enable_sort off,
and one enable_indexscan off, only to force a hashed plan. Use
enable_groupagg there instead, where that was the real intent. In
union.sql this also lets us test the hashed UNION path, which we had
no way to reach before.
---
contrib/hstore/expected/hstore.out | 4 +-
contrib/hstore/sql/hstore.sql | 4 +-
contrib/ltree/expected/ltree.out | 4 +-
contrib/ltree/sql/ltree.sql | 4 +-
doc/src/sgml/config.sgml | 14 +++++++
src/backend/optimizer/path/costsize.c | 23 +++++++++---
src/backend/optimizer/util/pathnode.c | 20 ++++++++++
src/backend/utils/misc/guc_parameters.dat | 7 ++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/optimizer/cost.h | 1 +
.../injection_points/expected/hashagg.out | 2 +-
.../modules/injection_points/sql/hashagg.sql | 2 +-
src/test/regress/expected/aggregates.out | 10 ++---
src/test/regress/expected/groupingsets.out | 7 +++-
src/test/regress/expected/jsonb.out | 6 +--
src/test/regress/expected/multirangetypes.out | 4 +-
src/test/regress/expected/rangetypes.out | 4 +-
src/test/regress/expected/select_distinct.out | 4 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/union.out | 37 ++++++++++++-------
src/test/regress/sql/aggregates.sql | 10 ++---
src/test/regress/sql/groupingsets.sql | 7 +++-
src/test/regress/sql/jsonb.sql | 6 +--
src/test/regress/sql/multirangetypes.sql | 4 +-
src/test/regress/sql/rangetypes.sql | 4 +-
src/test/regress/sql/select_distinct.sql | 4 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/union.sql | 16 ++++----
29 files changed, 147 insertions(+), 69 deletions(-)
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index acea8806ba4..9713372b7a4 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1509,7 +1509,7 @@ select count(*) from (select h from (select * from testhstore union all select *
(1 row)
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
count
-------
@@ -1522,7 +1522,7 @@ select distinct * from (values (hstore '' || ''),('')) v(h);
(1 row)
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
create index hidx on testhstore using btree (h);
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 8ae95e8a510..ac929e07509 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -345,10 +345,10 @@ select count(distinct h) from testhstore;
set enable_hashagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
select distinct * from (values (hstore '' || ''),('')) v(h);
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
diff --git a/contrib/ltree/expected/ltree.out b/contrib/ltree/expected/ltree.out
index f1d0eb37b81..0ab623cc204 100644
--- a/contrib/ltree/expected/ltree.out
+++ b/contrib/ltree/expected/ltree.out
@@ -7891,7 +7891,7 @@ reset enable_seqscan;
reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GROUP BY t
@@ -7915,7 +7915,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
-- test gist index
create index tstidx on ltreetest using gist (t gist_ltree_ops(siglen=0));
diff --git a/contrib/ltree/sql/ltree.sql b/contrib/ltree/sql/ltree.sql
index 833091dc6bb..5c324160afd 100644
--- a/contrib/ltree/sql/ltree.sql
+++ b/contrib/ltree/sql/ltree.sql
@@ -372,7 +372,7 @@ reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
@@ -384,7 +384,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
) t2;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fa566c9e553..b395221cad1 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5861,6 +5861,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 sort-based grouping
+ and aggregation plan types. The default is <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
<term><varname>enable_hashagg</varname> (<type>boolean</type>)
<indexterm>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1c575e56ff6..9d1e7e9b9a6 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -151,6 +151,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;
@@ -2843,8 +2844,6 @@ 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_MIXED && !enable_hashagg)
- ++disabled_nodes;
/* calcs phrased this way to match HASHED case, see note above */
total_cost += aggcosts->transCost.startup;
total_cost += aggcosts->transCost.per_tuple * input_tuples;
@@ -2853,13 +2852,24 @@ cost_agg(Path *path, PlannerInfo *root,
total_cost += aggcosts->finalCost.per_tuple * numGroups;
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ /*
+ * AGG_MIXED performs both hashed and sorted grouping, so it is
+ * disabled if either switch is off; AGG_SORTED is disabled only by
+ * enable_groupagg.
+ */
+ if (aggstrategy == AGG_MIXED)
+ {
+ if (!enable_hashagg || !enable_groupagg)
+ ++disabled_nodes;
+ }
+ else if (!enable_groupagg) /* AGG_SORTED */
+ ++disabled_nodes;
}
else
{
/* must be AGG_HASHED */
startup_cost = input_total_cost;
- if (!enable_hashagg)
- ++disabled_nodes;
startup_cost += aggcosts->transCost.startup;
startup_cost += aggcosts->transCost.per_tuple * input_tuples;
/* cost of computing hash value */
@@ -2871,6 +2881,9 @@ cost_agg(Path *path, PlannerInfo *root,
/* cost of retrieving from hash table */
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ if (!enable_hashagg)
+ ++disabled_nodes;
}
/*
@@ -3340,7 +3353,7 @@ cost_group(Path *path, PlannerInfo *root,
}
path->rows = output_tuples;
- path->disabled_nodes = input_disabled_nodes;
+ path->disabled_nodes = input_disabled_nodes + (enable_groupagg ? 0 : 1);
path->startup_cost = startup_cost;
path->total_cost = total_cost;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..3875e3dd571 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3036,6 +3036,16 @@ create_unique_path(PlannerInfo *root,
cpu_operator_cost * subpath->rows * numCols;
pathnode->path.rows = numGroups;
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this isn't
+ * a grouping Agg node, it is the sort-based way of removing duplicates
+ * and so is the natural counterpart to the AGG_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting that
+ * switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
+
return pathnode;
}
@@ -3522,6 +3532,16 @@ create_setop_path(PlannerInfo *root,
* qual-checking or projection.
*/
pathnode->path.total_cost += cpu_operator_cost * outputRows;
+
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this
+ * isn't a grouping Agg node, it is the sort-based implementation and
+ * so is the natural counterpart to the SETOP_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting
+ * that switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
}
else
{
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7b1eb6e61bc..3f9dc3fc5a6 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -905,6 +905,13 @@
boot_val => 'true',
},
+{ name => 'enable_groupagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
+ short_desc => 'Enables the planner\'s use of sort-based grouping and aggregation plans.',
+ flags => 'GUC_EXPLAIN',
+ variable => 'enable_groupagg',
+ boot_val => 'true',
+},
+
{ name => 'enable_hashagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
short_desc => 'Enables the planner\'s use of hashed aggregation plans.',
flags => 'GUC_EXPLAIN',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ac38cddaaf9..2490f276019 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -426,6 +426,7 @@
#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
+#enable_groupagg = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index f2fd5d31507..bda3f1690c0 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/modules/injection_points/expected/hashagg.out b/src/test/modules/injection_points/expected/hashagg.out
index cc4247af97d..2f75e9be8b7 100644
--- a/src/test/modules/injection_points/expected/hashagg.out
+++ b/src/test/modules/injection_points/expected/hashagg.out
@@ -36,7 +36,7 @@ CREATE TABLE hashagg_ij(x INTEGER);
INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
NOTICE: notice triggered for injection point hash-aggregate-spill-1000
diff --git a/src/test/modules/injection_points/sql/hashagg.sql b/src/test/modules/injection_points/sql/hashagg.sql
index 51d814623fc..5d580f8e425 100644
--- a/src/test/modules/injection_points/sql/hashagg.sql
+++ b/src/test/modules/injection_points/sql/hashagg.sql
@@ -17,7 +17,7 @@ INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 89e051ee824..de6f94a9e19 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1459,7 +1459,7 @@ drop cascades to table minmaxtest2
drop cascades to table minmaxtest3
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -3804,7 +3804,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
--
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
group by unique1
@@ -3863,7 +3863,7 @@ order by sum(twothousand);
(48 rows)
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
-- aggregation. Force spilling in both cases by setting work_mem low.
@@ -3912,7 +3912,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
explain (costs off)
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
@@ -3944,7 +3944,7 @@ select (g/2)::numeric as c1, sum(7::int4) as c2, count(*) as c3
create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
(select * from agg_hash_1 except select * from agg_group_1)
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index b08083ec54c..60376db3ea3 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2010,6 +2010,9 @@ alter table bug_16784 set (autovacuum_enabled = 'false');
update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
+-- Use enable_sort, not enable_groupagg, to force hashing here: cube()
+-- includes the empty grouping set, which can't be hashed, so even the
+-- most-hashed plan is AGG_MIXED. enable_groupagg would disable that too.
set enable_sort = false;
select * from
(values (1),(2)) v(a),
@@ -2232,7 +2235,9 @@ from gs_data_1 group by cube (g1000, g100,g10);
create table gs_group_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
--- Produce results with hash aggregation.
+-- Produce results with hash aggregation. As above, use enable_sort rather
+-- than enable_groupagg: cube()'s empty grouping set forces an AGG_MIXED
+-- plan, which enable_groupagg would disable, leaving a sorted plan instead.
set enable_hashagg = true;
set enable_sort = false;
explain (costs off)
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..b1d2ce5f03a 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3473,7 +3473,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
(1 row)
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
count
-------
@@ -3486,9 +3486,9 @@ SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
{}
(1 row)
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
-- btree
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index 6006aede31d..d47ce4b6d6a 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -3442,14 +3442,14 @@ NOTICE: drop cascades to type two_ints_range
--
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
varbitmultirange
------------------
{(01,10)}
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index e062a4e5c2c..ba630e6f215 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1819,14 +1819,14 @@ NOTICE: drop cascades to type two_ints_range
-- Check behavior when subtype lacks a hash function
--
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
varbitrange
-------------
(01,10)
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out
index 379ba0bc9fa..741f7238742 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -187,7 +187,7 @@ SELECT DISTINCT hundred, two FROM tenk1;
RESET enable_seqscan;
SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
EXPLAIN (costs off)
SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
@@ -203,7 +203,7 @@ SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
-- Compare results
(SELECT * FROM distinct_hash_1 EXCEPT SELECT * FROM distinct_group_1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index a3778c23c34..a75c4ef3d3f 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1454,7 +1454,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
onek o cross join lateral (
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 132b56a5864..1e327c2afa4 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -161,6 +161,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_eager_aggregate | on
enable_gathermerge | on
enable_group_by_reordering | on
+ enable_groupagg | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
@@ -180,7 +181,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 3a49b354058..62065953a84 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -386,14 +386,14 @@ select count(*) from
(1 row)
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------------------------------------
HashSetOp Except
- -> Seq Scan on tenk1
- -> Seq Scan on tenk1 tenk1_1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
Filter: (unique2 <> 10)
(4 rows)
@@ -403,7 +403,7 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
10
(1 row)
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
select * from int8_tbl intersect select q2, q1 from int8_tbl order by 1, 2;
@@ -981,19 +981,30 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+set enable_groupagg = false;
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
+ QUERY PLAN
+----------------------------------------------------------------
+ HashAggregate
+ -> Append
+ -> Function Scan on generate_series
+ -> Function Scan on generate_series generate_series_1
+(4 rows)
+
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
QUERY PLAN
----------------------------------------------------------
- SetOp Intersect
+ HashSetOp Intersect
-> Function Scan on generate_series
-> Function Scan on generate_series generate_series_1
(3 rows)
+select from generate_series(1,5) union select from generate_series(1,3);
+--
+(1 row)
+
select from generate_series(1,5) union all select from generate_series(1,3);
--
(8 rows)
@@ -1016,7 +1027,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
QUERY PLAN
@@ -1075,7 +1086,7 @@ select from cte union select from cte;
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
-- an undecorated constant will work in all cases, but historically this
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 916383db927..b66dad5660e 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -493,7 +493,7 @@ drop table minmaxtest cascade;
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -1672,7 +1672,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
@@ -1681,7 +1681,7 @@ having sum(fivethous) > 4975
order by sum(twothousand);
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
@@ -1736,7 +1736,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
@@ -1769,7 +1769,7 @@ create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index a594449b697..eb74e04d3b2 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -583,6 +583,9 @@ update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
+-- Use enable_sort, not enable_groupagg, to force hashing here: cube()
+-- includes the empty grouping set, which can't be hashed, so even the
+-- most-hashed plan is AGG_MIXED. enable_groupagg would disable that too.
set enable_sort = false;
select * from
@@ -621,7 +624,9 @@ create table gs_group_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
--- Produce results with hash aggregation.
+-- Produce results with hash aggregation. As above, use enable_sort rather
+-- than enable_groupagg: cube()'s empty grouping set forces an AGG_MIXED
+-- plan, which enable_groupagg would disable, leaving a sorted plan instead.
set enable_hashagg = true;
set enable_sort = false;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index d28ed1c1e85..d4e8d7d8c9e 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -927,13 +927,13 @@ SELECT count(distinct j) FROM testjsonb;
SET enable_hashagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index ddff722b28c..cf0fff6fddd 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -862,11 +862,11 @@ drop type two_ints cascade;
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index 5c4b0337b7a..dbfe0d049da 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -587,11 +587,11 @@ drop type two_ints cascade;
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql
index 50ac7dde396..2ed1616b098 100644
--- a/src/test/regress/sql/select_distinct.sql
+++ b/src/test/regress/sql/select_distinct.sql
@@ -81,7 +81,7 @@ SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
@@ -96,7 +96,7 @@ SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 1a02c3f86c0..5c8e24eb6db 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -730,7 +730,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 078c858429f..f814ade45a7 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -135,13 +135,13 @@ select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
@@ -321,14 +321,14 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
+select from generate_series(1,5) union select from generate_series(1,3);
select from generate_series(1,5) union all select from generate_series(1,3);
select from generate_series(1,5) intersect select from generate_series(1,3);
select from generate_series(1,5) intersect all select from generate_series(1,3);
@@ -337,7 +337,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
@@ -363,7 +363,7 @@ with cte as not materialized (select s from generate_series(1,5) s)
select from cte union select from cte;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-06-25 03:18 Richard Guo <[email protected]>
parent: Richard Guo <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Richard Guo @ 2026-06-25 03:18 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected]
On Thu, Jun 25, 2026 at 8:05 AM Richard Guo <[email protected]> wrote:
> I rebased your last patch and made some changes. Attached is what I
> ended up with.
I looked into the AGG_MIXED case some more, and I don't think the v3
patch handles it correctly. I'd assumed an AGG_MIXED plan always does
some sorted grouping, but that's not true. For the common cube/rollup
queries the grouping sets always include the empty set, which can't be
hashed, so even the maximally-hashed plan comes out as AGG_MIXED.
Disabling it under enable_groupagg therefore penalizes the very plan
we want.
To recap how grouping sets are costed: create_groupingsets_path loops
over the rollups and calls cost_agg once per rollup. The first call
gets the path's overall strategy (AGG_MIXED for a mixed plan); the
rest come through as AGG_HASHED or AGG_SORTED, one per rollup, with
their disabled_nodes summed into the path. So an AGG_MIXED plan's
sorted grouping shows up in the per-rollup AGG_SORTED calls, not in
the AGG_MIXED call itself.
And the empty grouping set is really computed like AGG_PLAIN, with no
hash table and no sort, so it shouldn't bump disabled_nodes at all.
It reaches cost_agg with numGroupCols == 0, which makes it easy to
tell apart. So in the attached patch AGG_MIXED is disabled only by
enable_hashagg, and AGG_SORTED is disabled by enable_groupagg only
when numGroupCols > 0. That also lets the tests in groupingsets.sql
use enable_groupagg, which they couldn't before.
- Richard
Attachments:
[application/octet-stream] v4-0001-Add-an-enable_groupagg-GUC-parameter.patch (33.0K, ../../CAMbWs4-YtrPXJ+pmYYC+6y+q=Dwj0f845MHAz4gm41skmzWFtg@mail.gmail.com/2-v4-0001-Add-an-enable_groupagg-GUC-parameter.patch)
download | inline diff:
From e2d7a0d8c361fe6f7fe93706dc9501e8c5d8c6df Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Wed, 24 Jun 2026 18:09:06 +0900
Subject: [PATCH v4] Add an enable_groupagg GUC parameter
We've long had enable_hashagg to discourage hashed aggregation, but
there was no equivalent for grouping that works by reading presorted
input. That's handy when investigating planner cost misestimates, and
as an escape hatch when a sorted grouping plan is chosen over a much
cheaper hashed one.
enable_groupagg (on by default) covers the GroupAggregate and Group
nodes, the sort-based Unique step used for DISTINCT and semijoin
unique-ification, and the sorted mode of SetOp. It isn't a hard
switch; it only bumps disabled_nodes, so plans with no other choice
are still produced.
Several regression and module tests had been setting enable_sort off,
and one enable_indexscan off, only to force a hashed plan. Use
enable_groupagg there instead, where that was the real intent. In
union.sql this also lets us test the hashed UNION path, which we had
no way to reach before.
---
contrib/hstore/expected/hstore.out | 4 +-
contrib/hstore/sql/hstore.sql | 4 +-
contrib/ltree/expected/ltree.out | 4 +-
contrib/ltree/sql/ltree.sql | 4 +-
doc/src/sgml/config.sgml | 14 +++++++
src/backend/optimizer/path/costsize.c | 33 ++++++++++++++---
src/backend/optimizer/util/pathnode.c | 20 ++++++++++
src/backend/utils/misc/guc_parameters.dat | 7 ++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/optimizer/cost.h | 1 +
.../injection_points/expected/hashagg.out | 2 +-
.../modules/injection_points/sql/hashagg.sql | 2 +-
src/test/regress/expected/aggregates.out | 10 ++---
src/test/regress/expected/groupingsets.out | 8 ++--
src/test/regress/expected/jsonb.out | 6 +--
src/test/regress/expected/multirangetypes.out | 4 +-
src/test/regress/expected/rangetypes.out | 4 +-
src/test/regress/expected/select_distinct.out | 4 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/union.out | 37 ++++++++++++-------
src/test/regress/sql/aggregates.sql | 10 ++---
src/test/regress/sql/groupingsets.sql | 8 ++--
src/test/regress/sql/jsonb.sql | 6 +--
src/test/regress/sql/multirangetypes.sql | 4 +-
src/test/regress/sql/rangetypes.sql | 4 +-
src/test/regress/sql/select_distinct.sql | 4 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/union.sql | 16 ++++----
29 files changed, 153 insertions(+), 75 deletions(-)
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index acea8806ba4..9713372b7a4 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1509,7 +1509,7 @@ select count(*) from (select h from (select * from testhstore union all select *
(1 row)
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
count
-------
@@ -1522,7 +1522,7 @@ select distinct * from (values (hstore '' || ''),('')) v(h);
(1 row)
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
create index hidx on testhstore using btree (h);
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 8ae95e8a510..ac929e07509 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -345,10 +345,10 @@ select count(distinct h) from testhstore;
set enable_hashagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
select distinct * from (values (hstore '' || ''),('')) v(h);
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
diff --git a/contrib/ltree/expected/ltree.out b/contrib/ltree/expected/ltree.out
index f1d0eb37b81..0ab623cc204 100644
--- a/contrib/ltree/expected/ltree.out
+++ b/contrib/ltree/expected/ltree.out
@@ -7891,7 +7891,7 @@ reset enable_seqscan;
reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GROUP BY t
@@ -7915,7 +7915,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
-- test gist index
create index tstidx on ltreetest using gist (t gist_ltree_ops(siglen=0));
diff --git a/contrib/ltree/sql/ltree.sql b/contrib/ltree/sql/ltree.sql
index 833091dc6bb..5c324160afd 100644
--- a/contrib/ltree/sql/ltree.sql
+++ b/contrib/ltree/sql/ltree.sql
@@ -372,7 +372,7 @@ reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
@@ -384,7 +384,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
) t2;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fa566c9e553..b395221cad1 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5861,6 +5861,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 sort-based grouping
+ and aggregation plan types. The default is <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
<term><varname>enable_hashagg</varname> (<type>boolean</type>)
<indexterm>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1c575e56ff6..ac523ecf9a8 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -151,6 +151,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;
@@ -2837,14 +2838,14 @@ cost_agg(Path *path, PlannerInfo *root,
/* we aren't grouping */
total_cost = startup_cost + cpu_tuple_cost;
output_tuples = 1;
+
+ /* AGG_PLAIN neither hashes nor sorts, so neither switch disables it */
}
else if (aggstrategy == AGG_SORTED || aggstrategy == AGG_MIXED)
{
/* Here we are able to deliver output on-the-fly */
startup_cost = input_startup_cost;
total_cost = input_total_cost;
- if (aggstrategy == AGG_MIXED && !enable_hashagg)
- ++disabled_nodes;
/* calcs phrased this way to match HASHED case, see note above */
total_cost += aggcosts->transCost.startup;
total_cost += aggcosts->transCost.per_tuple * input_tuples;
@@ -2853,13 +2854,31 @@ cost_agg(Path *path, PlannerInfo *root,
total_cost += aggcosts->finalCost.per_tuple * numGroups;
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ /*
+ * AGG_MIXED hashes at least one grouping set, so it is disabled when
+ * enable_hashagg is off. Any sorted grouping it also performs is
+ * costed separately, since create_groupingsets_path() calls
+ * cost_agg() once per rollup and the non-hashed rollups come through
+ * as AGG_SORTED.
+ *
+ * AGG_SORTED is disabled when enable_groupagg is off, but only when
+ * there are grouping columns. The empty grouping set arrives with
+ * numGroupCols == 0 and is computed like AGG_PLAIN, with no hashing
+ * or sorting, so it isn't disabled.
+ */
+ if (aggstrategy == AGG_MIXED)
+ {
+ if (!enable_hashagg)
+ ++disabled_nodes;
+ }
+ else if (numGroupCols > 0 && !enable_groupagg) /* AGG_SORTED */
+ ++disabled_nodes;
}
else
{
/* must be AGG_HASHED */
startup_cost = input_total_cost;
- if (!enable_hashagg)
- ++disabled_nodes;
startup_cost += aggcosts->transCost.startup;
startup_cost += aggcosts->transCost.per_tuple * input_tuples;
/* cost of computing hash value */
@@ -2871,6 +2890,10 @@ cost_agg(Path *path, PlannerInfo *root,
/* cost of retrieving from hash table */
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ /* AGG_HASHED is disabled when enable_hashagg is off */
+ if (!enable_hashagg)
+ ++disabled_nodes;
}
/*
@@ -3340,7 +3363,7 @@ cost_group(Path *path, PlannerInfo *root,
}
path->rows = output_tuples;
- path->disabled_nodes = input_disabled_nodes;
+ path->disabled_nodes = input_disabled_nodes + (enable_groupagg ? 0 : 1);
path->startup_cost = startup_cost;
path->total_cost = total_cost;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..3875e3dd571 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3036,6 +3036,16 @@ create_unique_path(PlannerInfo *root,
cpu_operator_cost * subpath->rows * numCols;
pathnode->path.rows = numGroups;
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this isn't
+ * a grouping Agg node, it is the sort-based way of removing duplicates
+ * and so is the natural counterpart to the AGG_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting that
+ * switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
+
return pathnode;
}
@@ -3522,6 +3532,16 @@ create_setop_path(PlannerInfo *root,
* qual-checking or projection.
*/
pathnode->path.total_cost += cpu_operator_cost * outputRows;
+
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this
+ * isn't a grouping Agg node, it is the sort-based implementation and
+ * so is the natural counterpart to the SETOP_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting
+ * that switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
}
else
{
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7b1eb6e61bc..3f9dc3fc5a6 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -905,6 +905,13 @@
boot_val => 'true',
},
+{ name => 'enable_groupagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
+ short_desc => 'Enables the planner\'s use of sort-based grouping and aggregation plans.',
+ flags => 'GUC_EXPLAIN',
+ variable => 'enable_groupagg',
+ boot_val => 'true',
+},
+
{ name => 'enable_hashagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
short_desc => 'Enables the planner\'s use of hashed aggregation plans.',
flags => 'GUC_EXPLAIN',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ac38cddaaf9..2490f276019 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -426,6 +426,7 @@
#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
+#enable_groupagg = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index f2fd5d31507..bda3f1690c0 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/modules/injection_points/expected/hashagg.out b/src/test/modules/injection_points/expected/hashagg.out
index cc4247af97d..2f75e9be8b7 100644
--- a/src/test/modules/injection_points/expected/hashagg.out
+++ b/src/test/modules/injection_points/expected/hashagg.out
@@ -36,7 +36,7 @@ CREATE TABLE hashagg_ij(x INTEGER);
INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
NOTICE: notice triggered for injection point hash-aggregate-spill-1000
diff --git a/src/test/modules/injection_points/sql/hashagg.sql b/src/test/modules/injection_points/sql/hashagg.sql
index 51d814623fc..5d580f8e425 100644
--- a/src/test/modules/injection_points/sql/hashagg.sql
+++ b/src/test/modules/injection_points/sql/hashagg.sql
@@ -17,7 +17,7 @@ INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 89e051ee824..de6f94a9e19 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1459,7 +1459,7 @@ drop cascades to table minmaxtest2
drop cascades to table minmaxtest3
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -3804,7 +3804,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
--
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
group by unique1
@@ -3863,7 +3863,7 @@ order by sum(twothousand);
(48 rows)
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
-- aggregation. Force spilling in both cases by setting work_mem low.
@@ -3912,7 +3912,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
explain (costs off)
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
@@ -3944,7 +3944,7 @@ select (g/2)::numeric as c1, sum(7::int4) as c2, count(*) as c3
create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
(select * from agg_hash_1 except select * from agg_group_1)
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index b08083ec54c..c3f00771f6e 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2010,7 +2010,7 @@ alter table bug_16784 set (autovacuum_enabled = 'false');
update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
-set enable_sort = false;
+set enable_groupagg = false;
select * from
(values (1),(2)) v(a),
lateral (select a, i, j, count(*) from
@@ -2205,7 +2205,7 @@ alter table gs_data_1 set (autovacuum_enabled = 'false');
update pg_class set reltuples = 10 where relname='gs_data_1';
set work_mem='64kB';
-- Produce results with sorting.
-set enable_sort = true;
+set enable_groupagg = true;
set enable_hashagg = false;
set jit_above_cost = 0;
explain (costs off)
@@ -2234,7 +2234,7 @@ select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-- Produce results with hash aggregation.
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
explain (costs off)
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
@@ -2255,7 +2255,7 @@ from gs_data_1 group by cube (g1000, g100,g10);
create table gs_hash_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
set hash_mem_multiplier to default;
-- Compare results
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..b1d2ce5f03a 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3473,7 +3473,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
(1 row)
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
count
-------
@@ -3486,9 +3486,9 @@ SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
{}
(1 row)
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
-- btree
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index 6006aede31d..d47ce4b6d6a 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -3442,14 +3442,14 @@ NOTICE: drop cascades to type two_ints_range
--
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
varbitmultirange
------------------
{(01,10)}
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index e062a4e5c2c..ba630e6f215 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1819,14 +1819,14 @@ NOTICE: drop cascades to type two_ints_range
-- Check behavior when subtype lacks a hash function
--
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
varbitrange
-------------
(01,10)
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out
index 379ba0bc9fa..741f7238742 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -187,7 +187,7 @@ SELECT DISTINCT hundred, two FROM tenk1;
RESET enable_seqscan;
SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
EXPLAIN (costs off)
SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
@@ -203,7 +203,7 @@ SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
-- Compare results
(SELECT * FROM distinct_hash_1 EXCEPT SELECT * FROM distinct_group_1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index a3778c23c34..a75c4ef3d3f 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1454,7 +1454,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
onek o cross join lateral (
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 132b56a5864..1e327c2afa4 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -161,6 +161,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_eager_aggregate | on
enable_gathermerge | on
enable_group_by_reordering | on
+ enable_groupagg | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
@@ -180,7 +181,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 3a49b354058..62065953a84 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -386,14 +386,14 @@ select count(*) from
(1 row)
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------------------------------------
HashSetOp Except
- -> Seq Scan on tenk1
- -> Seq Scan on tenk1 tenk1_1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
Filter: (unique2 <> 10)
(4 rows)
@@ -403,7 +403,7 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
10
(1 row)
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
select * from int8_tbl intersect select q2, q1 from int8_tbl order by 1, 2;
@@ -981,19 +981,30 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+set enable_groupagg = false;
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
+ QUERY PLAN
+----------------------------------------------------------------
+ HashAggregate
+ -> Append
+ -> Function Scan on generate_series
+ -> Function Scan on generate_series generate_series_1
+(4 rows)
+
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
QUERY PLAN
----------------------------------------------------------
- SetOp Intersect
+ HashSetOp Intersect
-> Function Scan on generate_series
-> Function Scan on generate_series generate_series_1
(3 rows)
+select from generate_series(1,5) union select from generate_series(1,3);
+--
+(1 row)
+
select from generate_series(1,5) union all select from generate_series(1,3);
--
(8 rows)
@@ -1016,7 +1027,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
QUERY PLAN
@@ -1075,7 +1086,7 @@ select from cte union select from cte;
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
-- an undecorated constant will work in all cases, but historically this
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 916383db927..b66dad5660e 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -493,7 +493,7 @@ drop table minmaxtest cascade;
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -1672,7 +1672,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
@@ -1681,7 +1681,7 @@ having sum(fivethous) > 4975
order by sum(twothousand);
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
@@ -1736,7 +1736,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
@@ -1769,7 +1769,7 @@ create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index a594449b697..c5d4ed2eb57 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -583,7 +583,7 @@ update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
-set enable_sort = false;
+set enable_groupagg = false;
select * from
(values (1),(2)) v(a),
@@ -609,7 +609,7 @@ set work_mem='64kB';
-- Produce results with sorting.
-set enable_sort = true;
+set enable_groupagg = true;
set enable_hashagg = false;
set jit_above_cost = 0;
@@ -624,7 +624,7 @@ from gs_data_1 group by cube (g1000, g100,g10);
-- Produce results with hash aggregation.
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
explain (costs off)
select g100, g10, sum(g::numeric), count(*), max(g::text)
@@ -634,7 +634,7 @@ create table gs_hash_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
set hash_mem_multiplier to default;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index d28ed1c1e85..d4e8d7d8c9e 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -927,13 +927,13 @@ SELECT count(distinct j) FROM testjsonb;
SET enable_hashagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index ddff722b28c..cf0fff6fddd 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -862,11 +862,11 @@ drop type two_ints cascade;
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index 5c4b0337b7a..dbfe0d049da 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -587,11 +587,11 @@ drop type two_ints cascade;
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql
index 50ac7dde396..2ed1616b098 100644
--- a/src/test/regress/sql/select_distinct.sql
+++ b/src/test/regress/sql/select_distinct.sql
@@ -81,7 +81,7 @@ SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
@@ -96,7 +96,7 @@ SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 1a02c3f86c0..5c8e24eb6db 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -730,7 +730,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 078c858429f..f814ade45a7 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -135,13 +135,13 @@ select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
@@ -321,14 +321,14 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
+select from generate_series(1,5) union select from generate_series(1,3);
select from generate_series(1,5) union all select from generate_series(1,3);
select from generate_series(1,5) intersect select from generate_series(1,3);
select from generate_series(1,5) intersect all select from generate_series(1,3);
@@ -337,7 +337,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
@@ -363,7 +363,7 @@ with cte as not materialized (select s from generate_series(1,5) s)
select from cte union select from cte;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-06-25 09:11 Tatsuro Yamada <[email protected]>
parent: Richard Guo <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2026-06-25 09:11 UTC (permalink / raw)
To: Richard Guo <[email protected]>; Tatsuro Yamada <[email protected]>; +Cc: David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
Hi Richard!
> > What do others involved in planner and plan-tuning think?
>
> I think this is a nice idea. It is true that we can sometimes use
> enable_sort to push the planner off a sorted grouping plan, but it is
> a much bigger hammer, as it discourages every Sort in the query, not
> just the one under the grouping, so it can move other parts of the
> plan around and doesn't really isolate the grouping choice. And it
> doesn't always work anyway. If the grouping input happens to be
> cheaply sorted there's no Sort to penalize, so the sorted plan
> survives. I ran into exactly that in union.sql, where enable_sort=off
> isn't really producing the hashed INTERSECT it looks like it is
> testing. Look at this plan from union.out:
>
> -- check hashed implementation
> set enable_hashagg = true;
> set enable_sort = false;
>
> explain (costs off)
> select from generate_series(1,5) intersect select from generate_series(1,3);
> QUERY PLAN
> ----------------------------------------------------------
> SetOp Intersect
> -> Function Scan on generate_series
> -> Function Scan on generate_series generate_series_1
> (3 rows)
>
> Also, without such a GUC, there are some paths that we have no way to
> generate. Look at this comment in union.sql:
>
> -- We've no way to check hashed UNION as the empty pathkeys in the
> Append are
> -- fine to make use of Unique, which is cheaper than HashAggregate and
> we've
> -- no means to disable Unique.
Thank you for your comments!
I'm glad that you appreciate the value of the patch.
Regarding the comments,
I hadn't realized that there were other plan nodes besides GroupAgg that
would also benefit from this GUC. I believe this feature would be useful to
many users. I also think it could be valuable for testing and tuning tools
such as pg_plan_advice, developed by Robert. For that reason, I'd like to
continue improving the patch with the goal of getting it committed for PG20.
> Regarding the patch, currently the new GUC only covers GroupAggregate.
> I think we should make it to cover all sort-based equivalents of
> enable_hashagg, such as the sorted mode of SetOp (as David suggested),
> sort-based Unique step used for DISTINCT and semijoin
> unique-ification, and maybe also Group nodes.
It sounds like there are several plan nodes that could be covered by this GUC
(such as SetOp, sort-based Unique for DISTINCT, semijoin uniqueification,
and Group nodes). Do you think we should cover all of them before the patch
would be considered complete enough for commit?
I plan to work through them one by one, but if others are interested in
contributing, I would certainly welcome the collaboration. That would help
move the patch forward more quickly and allow us to gather feedback on
different parts of the design in parallel.
As a practical matter, only part of my time can be devoted to community
development work. If I end up handling everything myself, progress may be
slower, and I'd like to avoid the patch losing momentum.
A related challenge is that extending the patch to cover more plan nodes will
likely increase the scope of the regression test changes. Perhaps it would
make sense to split the work into smaller patches, allowing each set of
regression test changes to be reviewed independently.
I'd be interested to hear your thoughts on how best to proceed, and whether
dividing the work into smaller pieces would make sense.
Regards,
Tatsuro Yamada
> - Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-06-26 06:26 Richard Guo <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Richard Guo @ 2026-06-26 06:26 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
On Thu, Jun 25, 2026 at 6:12 PM Tatsuro Yamada <[email protected]> wrote:
> It sounds like there are several plan nodes that could be covered by this GUC
> (such as SetOp, sort-based Unique for DISTINCT, semijoin uniqueification,
> and Group nodes). Do you think we should cover all of them before the patch
> would be considered complete enough for commit?
Yeah, I think so. The v4 patch actually already did that. You can
find these changes in create_setop_path(), create_unique_path(), and
cost_group().
> I'd be interested to hear your thoughts on how best to proceed, and whether
> dividing the work into smaller pieces would make sense.
It seems that the scope of the regression test changes isn't too
large, so I think keeping them in one patch is fine.
- Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-06-29 10:02 Tatsuro Yamada <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2026-06-29 10:02 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
Hi Richard,
> Yeah, I think so. The v4 patch actually already did that. You can
> find these changes in create_setop_path(), create_unique_path(), and
> cost_group().
Thank you for the clarification. I've reviewed the v4 patch and confirmed
that it already covers SetOp, Unique, Group, etc.
I ran make installcheck in my environment and all tests passed without
errors.
Since the enable_groupagg parameter also affects SetOp, Unique, and
other nodes, I've created documentation patches to clarify this. I'm attaching
them to this email. They apply on top of your v4 patch.
- 0001 patch: This improves the documentation for the enable_groupagg
parameter so that users can more easily understand which operations it
affects. If you agree with the changes, I'd like to post an updated patch
that integrates this into v4.
- 0002 patch: This provides a similar improvement for the enable_hashagg
parameter documentation. Since it concerns enable_hashagg, I'm happy
to move this to a separate thread if you prefer.
Best Regards,
Tatsuro Yamada
> -----Original Message-----
> From: Richard Guo <[email protected]>
> Sent: Friday, June 26, 2026 3:26 PM
> To: Tatsuro Yamada（山田達朗） <[email protected]>
> Cc: Tatsuro Yamada <[email protected]>; David Rowley
> <[email protected]>; Ashutosh Bapat
> <[email protected]>; [email protected]
> Subject: Re: Add enable_groupagg GUC parameter to control
> GroupAggregate usage
>
> On Thu, Jun 25, 2026 at 6:12 PM Tatsuro Yamada <[email protected]>
> wrote:
> > It sounds like there are several plan nodes that could be covered by this
> GUC
> > (such as SetOp, sort-based Unique for DISTINCT, semijoin uniqueification,
> > and Group nodes). Do you think we should cover all of them before the
> patch
> > would be considered complete enough for commit?
>
> Yeah, I think so. The v4 patch actually already did that. You can
> find these changes in create_setop_path(), create_unique_path(), and
> cost_group().
>
> > I'd be interested to hear your thoughts on how best to proceed, and
> whether
> > dividing the work into smaller pieces would make sense.
>
> It seems that the scope of the regression test changes isn't too
> large, so I think keeping them in one patch is fine.
>
> - Richard
Attachments:
[application/octet-stream] 0001-doc-Clarify-enable_groupagg-also-covers-Unique-and-S.patch (1.2K, ../../TY4P301MB12997F863B7B51A520E02ABC9EE82@TY4P301MB1299.JPNP301.PROD.OUTLOOK.COM/2-0001-doc-Clarify-enable_groupagg-also-covers-Unique-and-S.patch)
download | inline diff:
From a643d66ba152d241879a63b88dc5a5c787d6aab2 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <[email protected]>
Date: Mon, 29 Jun 2026 14:02:48 +0900
Subject: [PATCH 1/2] doc: Clarify enable_groupagg also covers Unique and SetOp
plans
The parameter description mentioned only sort-based grouping and
aggregation plan types, but it also discourages sort-based Unique
and SetOp plans. Mention them explicitly so users know the full
scope of the switch.
---
doc/src/sgml/config.sgml | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a80df44786e..435730b5ac2 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5870,7 +5870,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<listitem>
<para>
Enables or disables the query planner's use of sort-based grouping
- and aggregation plan types. The default is <literal>on</literal>.
+ and aggregation plan types. This also covers sort-based <literal>Unique</literal> and
+ <literal>SetOp</literal> plans. The default is <literal>on</literal>.
</para>
</listitem>
</varlistentry>
--
2.47.3
[application/octet-stream] 0002-doc-Clarify-enable_hashagg-also-covers-hash-based-Se.patch (1.1K, ../../TY4P301MB12997F863B7B51A520E02ABC9EE82@TY4P301MB1299.JPNP301.PROD.OUTLOOK.COM/3-0002-doc-Clarify-enable_hashagg-also-covers-hash-based-Se.patch)
download | inline diff:
From 94c72dfebc5bdc663e5243b3a8f322c22994d3b4 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <[email protected]>
Date: Mon, 29 Jun 2026 14:16:11 +0900
Subject: [PATCH 2/2] doc: Clarify enable_hashagg also covers hash-based SetOp
plans
The parameter description mentioned only hashed aggregation plan
types without noting that it also covers hash-based SetOp plans.
Add this for consistency with the enable_groupagg description.
---
doc/src/sgml/config.sgml | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 435730b5ac2..64a827cf563 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5885,7 +5885,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<listitem>
<para>
Enables or disables the query planner's use of hashed
- aggregation plan types. The default is <literal>on</literal>.
+ aggregation plan types. This also covers hash-based <literal>SetOp</literal> plans.
+ The default is <literal>on</literal>.
</para>
</listitem>
</varlistentry>
--
2.47.3
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-07 06:47 Richard Guo <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Richard Guo @ 2026-07-07 06:47 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
On Mon, Jun 29, 2026 at 7:02 PM Tatsuro Yamada <[email protected]> wrote:
> Since the enable_groupagg parameter also affects SetOp, Unique, and
> other nodes, I've created documentation patches to clarify this. I'm attaching
> them to this email. They apply on top of your v4 patch.
I've thought it over, and I'm inclined to leave the docs as they are.
My main concern is consistency with the surrounding parameters. All
of the other enable_* GUCs are documented conceptually rather than by
the specific node types they affect, and none of them enumerate the
nodes you'd see in EXPLAIN. Unique and SetOp are really EXPLAIN
labels rather than user-facing concepts, and I'd argue the existing
wording already covers them, since sort-based Unique and sorted SetOp
are both forms of sort-based grouping.
Similarly, enable_hashagg has covered hash-based SetOp for a long time
without us documenting it, which is the same convention at work. I'd
rather not add that now just to match the new parameter.
Attached is a rebased patch to make cfbot work again.
- Richard
Attachments:
[application/octet-stream] v5-0001-Add-an-enable_groupagg-GUC-parameter.patch (33.3K, ../../CAMbWs49RCumHErWYOLm4jDcvLSGqhSm1Asnwsv9Tdn2Yjn0r2A@mail.gmail.com/2-v5-0001-Add-an-enable_groupagg-GUC-parameter.patch)
download | inline diff:
From 4812649b0e7d984378b462eedf22f8ddf3af72a0 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Wed, 24 Jun 2026 18:09:06 +0900
Subject: [PATCH v5] Add an enable_groupagg GUC parameter
We've long had enable_hashagg to discourage hashed aggregation, but
there was no equivalent for grouping that works by reading presorted
input. That's handy when investigating planner cost misestimates, and
as an escape hatch when a sorted grouping plan is chosen over a much
cheaper hashed one.
enable_groupagg (on by default) covers the GroupAggregate and Group
nodes, the sort-based Unique step used for DISTINCT and semijoin
unique-ification, and the sorted mode of SetOp. It isn't a hard
switch; it only bumps disabled_nodes, so plans with no other choice
are still produced.
Several regression and module tests had been setting enable_sort off,
and one enable_indexscan off, only to force a hashed plan. Use
enable_groupagg there instead, where that was the real intent. In
union.sql this also lets us test the hashed UNION path, which we had
no way to reach before.
Author: Tatsuro Yamada <[email protected]>
Co-authored-by: Richard Guo <[email protected]>
Reviewed-by: Ashutosh Bapat <[email protected]>
Reviewed-by: David Rowley <[email protected]>
Discussion: https://postgr.es/m/CAOKkKFvYHSEsFazkrf9bRH14p-H27XMaqbZfRYjS6EHBruvZMQ@mail.gmail.com
---
contrib/hstore/expected/hstore.out | 4 +-
contrib/hstore/sql/hstore.sql | 4 +-
contrib/ltree/expected/ltree.out | 4 +-
contrib/ltree/sql/ltree.sql | 4 +-
doc/src/sgml/config.sgml | 14 +++++++
src/backend/optimizer/path/costsize.c | 33 ++++++++++++++---
src/backend/optimizer/util/pathnode.c | 20 ++++++++++
src/backend/utils/misc/guc_parameters.dat | 7 ++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/optimizer/cost.h | 1 +
.../injection_points/expected/hashagg.out | 2 +-
.../modules/injection_points/sql/hashagg.sql | 2 +-
src/test/regress/expected/aggregates.out | 10 ++---
src/test/regress/expected/groupingsets.out | 8 ++--
src/test/regress/expected/jsonb.out | 6 +--
src/test/regress/expected/multirangetypes.out | 4 +-
src/test/regress/expected/rangetypes.out | 4 +-
src/test/regress/expected/select_distinct.out | 4 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/union.out | 37 ++++++++++++-------
src/test/regress/sql/aggregates.sql | 10 ++---
src/test/regress/sql/groupingsets.sql | 8 ++--
src/test/regress/sql/jsonb.sql | 6 +--
src/test/regress/sql/multirangetypes.sql | 4 +-
src/test/regress/sql/rangetypes.sql | 4 +-
src/test/regress/sql/select_distinct.sql | 4 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/union.sql | 16 ++++----
29 files changed, 153 insertions(+), 75 deletions(-)
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index acea8806ba4..9713372b7a4 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1509,7 +1509,7 @@ select count(*) from (select h from (select * from testhstore union all select *
(1 row)
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
count
-------
@@ -1522,7 +1522,7 @@ select distinct * from (values (hstore '' || ''),('')) v(h);
(1 row)
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
create index hidx on testhstore using btree (h);
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 8ae95e8a510..ac929e07509 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -345,10 +345,10 @@ select count(distinct h) from testhstore;
set enable_hashagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
select distinct * from (values (hstore '' || ''),('')) v(h);
-set enable_sort = true;
+set enable_groupagg = true;
-- btree
drop index hidx;
diff --git a/contrib/ltree/expected/ltree.out b/contrib/ltree/expected/ltree.out
index f1d0eb37b81..0ab623cc204 100644
--- a/contrib/ltree/expected/ltree.out
+++ b/contrib/ltree/expected/ltree.out
@@ -7891,7 +7891,7 @@ reset enable_seqscan;
reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GROUP BY t
@@ -7915,7 +7915,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
-- test gist index
create index tstidx on ltreetest using gist (t gist_ltree_ops(siglen=0));
diff --git a/contrib/ltree/sql/ltree.sql b/contrib/ltree/sql/ltree.sql
index 833091dc6bb..5c324160afd 100644
--- a/contrib/ltree/sql/ltree.sql
+++ b/contrib/ltree/sql/ltree.sql
@@ -372,7 +372,7 @@ reset enable_bitmapscan;
-- test hash aggregate
set enable_hashagg=on;
-set enable_sort=off;
+set enable_groupagg=off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (
@@ -384,7 +384,7 @@ SELECT t FROM (SELECT * FROM ltreetest UNION ALL SELECT * FROM ltreetest) t1 GRO
) t2;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
drop index tstidx;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 9172a4c5c95..c67130c620e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5863,6 +5863,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 sort-based grouping
+ and aggregation plan types. The default is <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
<term><varname>enable_hashagg</varname> (<type>boolean</type>)
<indexterm>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1c575e56ff6..ac523ecf9a8 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -151,6 +151,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;
@@ -2837,14 +2838,14 @@ cost_agg(Path *path, PlannerInfo *root,
/* we aren't grouping */
total_cost = startup_cost + cpu_tuple_cost;
output_tuples = 1;
+
+ /* AGG_PLAIN neither hashes nor sorts, so neither switch disables it */
}
else if (aggstrategy == AGG_SORTED || aggstrategy == AGG_MIXED)
{
/* Here we are able to deliver output on-the-fly */
startup_cost = input_startup_cost;
total_cost = input_total_cost;
- if (aggstrategy == AGG_MIXED && !enable_hashagg)
- ++disabled_nodes;
/* calcs phrased this way to match HASHED case, see note above */
total_cost += aggcosts->transCost.startup;
total_cost += aggcosts->transCost.per_tuple * input_tuples;
@@ -2853,13 +2854,31 @@ cost_agg(Path *path, PlannerInfo *root,
total_cost += aggcosts->finalCost.per_tuple * numGroups;
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ /*
+ * AGG_MIXED hashes at least one grouping set, so it is disabled when
+ * enable_hashagg is off. Any sorted grouping it also performs is
+ * costed separately, since create_groupingsets_path() calls
+ * cost_agg() once per rollup and the non-hashed rollups come through
+ * as AGG_SORTED.
+ *
+ * AGG_SORTED is disabled when enable_groupagg is off, but only when
+ * there are grouping columns. The empty grouping set arrives with
+ * numGroupCols == 0 and is computed like AGG_PLAIN, with no hashing
+ * or sorting, so it isn't disabled.
+ */
+ if (aggstrategy == AGG_MIXED)
+ {
+ if (!enable_hashagg)
+ ++disabled_nodes;
+ }
+ else if (numGroupCols > 0 && !enable_groupagg) /* AGG_SORTED */
+ ++disabled_nodes;
}
else
{
/* must be AGG_HASHED */
startup_cost = input_total_cost;
- if (!enable_hashagg)
- ++disabled_nodes;
startup_cost += aggcosts->transCost.startup;
startup_cost += aggcosts->transCost.per_tuple * input_tuples;
/* cost of computing hash value */
@@ -2871,6 +2890,10 @@ cost_agg(Path *path, PlannerInfo *root,
/* cost of retrieving from hash table */
total_cost += cpu_tuple_cost * numGroups;
output_tuples = numGroups;
+
+ /* AGG_HASHED is disabled when enable_hashagg is off */
+ if (!enable_hashagg)
+ ++disabled_nodes;
}
/*
@@ -3340,7 +3363,7 @@ cost_group(Path *path, PlannerInfo *root,
}
path->rows = output_tuples;
- path->disabled_nodes = input_disabled_nodes;
+ path->disabled_nodes = input_disabled_nodes + (enable_groupagg ? 0 : 1);
path->startup_cost = startup_cost;
path->total_cost = total_cost;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..3875e3dd571 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3036,6 +3036,16 @@ create_unique_path(PlannerInfo *root,
cpu_operator_cost * subpath->rows * numCols;
pathnode->path.rows = numGroups;
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this isn't
+ * a grouping Agg node, it is the sort-based way of removing duplicates
+ * and so is the natural counterpart to the AGG_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting that
+ * switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
+
return pathnode;
}
@@ -3522,6 +3532,16 @@ create_setop_path(PlannerInfo *root,
* qual-checking or projection.
*/
pathnode->path.total_cost += cpu_operator_cost * outputRows;
+
+ /*
+ * Mark the path as disabled if enable_groupagg is off. While this
+ * isn't a grouping Agg node, it is the sort-based implementation and
+ * so is the natural counterpart to the SETOP_HASHED path that
+ * enable_hashagg controls; it seems close enough to justify letting
+ * that switch control it.
+ */
+ if (!enable_groupagg)
+ pathnode->path.disabled_nodes++;
}
else
{
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index c9118e71988..dd799a5e70f 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -905,6 +905,13 @@
boot_val => 'true',
},
+{ name => 'enable_groupagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
+ short_desc => 'Enables the planner\'s use of sort-based grouping and aggregation plans.',
+ flags => 'GUC_EXPLAIN',
+ variable => 'enable_groupagg',
+ boot_val => 'true',
+},
+
{ name => 'enable_hashagg', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
short_desc => 'Enables the planner\'s use of hashed aggregation plans.',
flags => 'GUC_EXPLAIN',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index d7942f50a70..47e1221f3c3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -426,6 +426,7 @@
#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
+#enable_groupagg = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index f2fd5d31507..bda3f1690c0 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/modules/injection_points/expected/hashagg.out b/src/test/modules/injection_points/expected/hashagg.out
index cc4247af97d..2f75e9be8b7 100644
--- a/src/test/modules/injection_points/expected/hashagg.out
+++ b/src/test/modules/injection_points/expected/hashagg.out
@@ -36,7 +36,7 @@ CREATE TABLE hashagg_ij(x INTEGER);
INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
NOTICE: notice triggered for injection point hash-aggregate-spill-1000
diff --git a/src/test/modules/injection_points/sql/hashagg.sql b/src/test/modules/injection_points/sql/hashagg.sql
index 51d814623fc..5d580f8e425 100644
--- a/src/test/modules/injection_points/sql/hashagg.sql
+++ b/src/test/modules/injection_points/sql/hashagg.sql
@@ -17,7 +17,7 @@ INSERT INTO hashagg_ij SELECT g FROM generate_series(1,5100) g;
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET work_mem='4MB';
SELECT COUNT(*) FROM (SELECT DISTINCT x FROM hashagg_ij) s;
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 728a3ecd03f..f21f4e225da 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1459,7 +1459,7 @@ drop cascades to table minmaxtest2
drop cascades to table minmaxtest3
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -3866,7 +3866,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
--
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
group by unique1
@@ -3925,7 +3925,7 @@ order by sum(twothousand);
(48 rows)
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
-- aggregation. Force spilling in both cases by setting work_mem low.
@@ -3974,7 +3974,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
explain (costs off)
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
@@ -4006,7 +4006,7 @@ select (g/2)::numeric as c1, sum(7::int4) as c2, count(*) as c3
create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
(select * from agg_hash_1 except select * from agg_group_1)
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index b08083ec54c..c3f00771f6e 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2010,7 +2010,7 @@ alter table bug_16784 set (autovacuum_enabled = 'false');
update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
-set enable_sort = false;
+set enable_groupagg = false;
select * from
(values (1),(2)) v(a),
lateral (select a, i, j, count(*) from
@@ -2205,7 +2205,7 @@ alter table gs_data_1 set (autovacuum_enabled = 'false');
update pg_class set reltuples = 10 where relname='gs_data_1';
set work_mem='64kB';
-- Produce results with sorting.
-set enable_sort = true;
+set enable_groupagg = true;
set enable_hashagg = false;
set jit_above_cost = 0;
explain (costs off)
@@ -2234,7 +2234,7 @@ select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-- Produce results with hash aggregation.
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
explain (costs off)
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
@@ -2255,7 +2255,7 @@ from gs_data_1 group by cube (g1000, g100,g10);
create table gs_hash_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
set hash_mem_multiplier to default;
-- Compare results
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..b1d2ce5f03a 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3473,7 +3473,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
(1 row)
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
count
-------
@@ -3486,9 +3486,9 @@ SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
{}
(1 row)
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
-- btree
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index 6006aede31d..d47ce4b6d6a 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -3442,14 +3442,14 @@ NOTICE: drop cascades to type two_ints_range
--
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
varbitmultirange
------------------
{(01,10)}
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 98ba738fb1d..083e948bc41 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1819,14 +1819,14 @@ NOTICE: drop cascades to type two_ints_range
-- Check behavior when subtype lacks a hash function
--
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
varbitrange
-------------
(01,10)
(1 row)
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out
index 379ba0bc9fa..741f7238742 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -187,7 +187,7 @@ SELECT DISTINCT hundred, two FROM tenk1;
RESET enable_seqscan;
SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
EXPLAIN (costs off)
SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
@@ -203,7 +203,7 @@ SELECT DISTINCT g%1000 FROM generate_series(0,9999) g;
SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
-- Compare results
(SELECT * FROM distinct_hash_1 EXCEPT SELECT * FROM distinct_group_1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 20140f171af..e7ff7191082 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1454,7 +1454,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
onek o cross join lateral (
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 132b56a5864..1e327c2afa4 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -161,6 +161,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_eager_aggregate | on
enable_gathermerge | on
enable_group_by_reordering | on
+ enable_groupagg | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
@@ -180,7 +181,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index ca9089e9a7d..84abcd6b14f 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -386,14 +386,14 @@ select count(*) from
(1 row)
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------------------------------------
HashSetOp Except
- -> Seq Scan on tenk1
- -> Seq Scan on tenk1 tenk1_1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
Filter: (unique2 <> 10)
(4 rows)
@@ -403,7 +403,7 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
10
(1 row)
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
select * from int8_tbl intersect select q2, q1 from int8_tbl order by 1, 2;
@@ -981,19 +981,30 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+set enable_groupagg = false;
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
+ QUERY PLAN
+----------------------------------------------------------------
+ HashAggregate
+ -> Append
+ -> Function Scan on generate_series
+ -> Function Scan on generate_series generate_series_1
+(4 rows)
+
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
QUERY PLAN
----------------------------------------------------------
- SetOp Intersect
+ HashSetOp Intersect
-> Function Scan on generate_series
-> Function Scan on generate_series generate_series_1
(3 rows)
+select from generate_series(1,5) union select from generate_series(1,3);
+--
+(1 row)
+
select from generate_series(1,5) union all select from generate_series(1,3);
--
(8 rows)
@@ -1016,7 +1027,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
QUERY PLAN
@@ -1075,7 +1086,7 @@ select from cte union select from cte;
(1 row)
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
-- an undecorated constant will work in all cases, but historically this
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 342605d5497..5cb9a9dc1be 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -493,7 +493,7 @@ drop table minmaxtest cascade;
-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465)
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -1702,7 +1702,7 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
-- Hash Aggregation Spill tests
--
-set enable_sort=false;
+set enable_groupagg=false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
@@ -1711,7 +1711,7 @@ having sum(fivethous) > 4975
order by sum(twothousand);
set work_mem to default;
-set enable_sort to default;
+set enable_groupagg to default;
--
-- Compare results between plans using sorting and plans using hash
@@ -1766,7 +1766,7 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
-- Produce results with hash aggregation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
set jit_above_cost = 0;
@@ -1799,7 +1799,7 @@ create table agg_hash_4 as
select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
-- Compare group aggregation results to hash aggregation results
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index a594449b697..c5d4ed2eb57 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -583,7 +583,7 @@ update pg_class set reltuples = 10 where relname='bug_16784';
insert into bug_16784 select g/10, g from generate_series(1,40) g;
set work_mem='64kB';
-set enable_sort = false;
+set enable_groupagg = false;
select * from
(values (1),(2)) v(a),
@@ -609,7 +609,7 @@ set work_mem='64kB';
-- Produce results with sorting.
-set enable_sort = true;
+set enable_groupagg = true;
set enable_hashagg = false;
set jit_above_cost = 0;
@@ -624,7 +624,7 @@ from gs_data_1 group by cube (g1000, g100,g10);
-- Produce results with hash aggregation.
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
explain (costs off)
select g100, g10, sum(g::numeric), count(*), max(g::text)
@@ -634,7 +634,7 @@ create table gs_hash_1 as
select g100, g10, sum(g::numeric), count(*), max(g::text)
from gs_data_1 group by cube (g1000, g100,g10);
-set enable_sort = true;
+set enable_groupagg = true;
set work_mem to default;
set hash_mem_multiplier to default;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index d28ed1c1e85..d4e8d7d8c9e 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -927,13 +927,13 @@ SELECT count(distinct j) FROM testjsonb;
SET enable_hashagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SET enable_hashagg = on;
-SET enable_sort = off;
+SET enable_groupagg = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
-SET enable_sort = on;
+SET enable_groupagg = on;
RESET enable_hashagg;
-RESET enable_sort;
+RESET enable_groupagg;
DROP INDEX jidx;
DROP INDEX jidx_array;
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index ddff722b28c..cf0fff6fddd 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -862,11 +862,11 @@ drop type two_ints cascade;
-- Check behavior when subtype lacks a hash function
--
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index 5c4b0337b7a..dbfe0d049da 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -587,11 +587,11 @@ drop type two_ints cascade;
create type varbitrange as range (subtype = varbit);
-set enable_sort = off; -- try to make it pick a hash setop implementation
+set enable_groupagg = off; -- try to make it pick a hash setop implementation
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
-reset enable_sort;
+reset enable_groupagg;
--
-- OUT/INOUT/TABLE functions
diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql
index 50ac7dde396..2ed1616b098 100644
--- a/src/test/regress/sql/select_distinct.sql
+++ b/src/test/regress/sql/select_distinct.sql
@@ -81,7 +81,7 @@ SET enable_hashagg=TRUE;
-- Produce results with hash aggregation.
-SET enable_sort=FALSE;
+SET enable_groupagg=FALSE;
SET jit_above_cost=0;
@@ -96,7 +96,7 @@ SET jit_above_cost TO DEFAULT;
CREATE TABLE distinct_hash_2 AS
SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g;
-SET enable_sort=TRUE;
+SET enable_groupagg=TRUE;
SET work_mem TO DEFAULT;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 3defbc29177..76bce5cdba5 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -730,7 +730,7 @@ where o.ten = 0;
-- Test rescan of a hashed SetOp node
--
begin;
-set local enable_sort = off;
+set local enable_groupagg = off;
explain (costs off)
select count(*) from
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 780b704b53b..c8de276c2b5 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -135,13 +135,13 @@ select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
-- this query will prefer a sorted setop unless we force it.
-set enable_indexscan to off;
+set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
-reset enable_indexscan;
+reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
@@ -321,14 +321,14 @@ select except select;
-- check hashed implementation
set enable_hashagg = true;
-set enable_sort = false;
+set enable_groupagg = false;
--- We've no way to check hashed UNION as the empty pathkeys in the Append are
--- fine to make use of Unique, which is cheaper than HashAggregate and we've
--- no means to disable Unique.
+explain (costs off)
+select from generate_series(1,5) union select from generate_series(1,3);
explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
+select from generate_series(1,5) union select from generate_series(1,3);
select from generate_series(1,5) union all select from generate_series(1,3);
select from generate_series(1,5) intersect select from generate_series(1,3);
select from generate_series(1,5) intersect all select from generate_series(1,3);
@@ -337,7 +337,7 @@ select from generate_series(1,5) except all select from generate_series(1,3);
-- check sorted implementation
set enable_hashagg = false;
-set enable_sort = true;
+set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
@@ -363,7 +363,7 @@ with cte as not materialized (select s from generate_series(1,5) s)
select from cte union select from cte;
reset enable_hashagg;
-reset enable_sort;
+reset enable_groupagg;
--
-- Check handling of a case with unknown constants. We don't guarantee
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-07 06:53 Richard Guo <[email protected]>
parent: Richard Guo <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Richard Guo @ 2026-07-07 06:53 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
On Tue, Jul 7, 2026 at 3:47 PM Richard Guo <[email protected]> wrote:
> Attached is a rebased patch to make cfbot work again.
I intend to push this patch soon. That way newly added test cases can
start using enable_groupagg directly. Otherwise we'll have to keep
rebasing it to convert any new tests that use enable_sort to force
hashed grouping. Any thoughts?
- Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-08 11:09 Tatsuro Yamada <[email protected]>
parent: Richard Guo <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Tatsuro Yamada @ 2026-07-08 11:09 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
Hi Richard,
>I've thought it over, and I'm inclined to leave the docs as they are.
>
>My main concern is consistency with the surrounding parameters. All
>of the other enable_* GUCs are documented conceptually rather than by
>the specific node types they affect, and none of them enumerate the
>nodes you'd see in EXPLAIN. Unique and SetOp are really EXPLAIN
>labels rather than user-facing concepts, and I'd argue the existing
>wording already covers them, since sort-based Unique and sorted SetOp
>are both forms of sort-based grouping.
>
>Similarly, enable_hashagg has covered hash-based SetOp for a long time
>without us documenting it, which is the same convention at work. I'd
>rather not add that now just to match the new parameter.
Thanks for your comments and the rebased patch.
I understand and agree with the decision not to modify the documentation.
My initial motivation for proposing the documentation update was to make it
clearer which plan nodes are affected by these GUC parameters. While reviewing
your previous patch, I realized that enable_hashagg and enable_groupagg affect
not only HashAgg and GroupAgg nodes but also other plan nodes.
However, documenting that level of detail only for these parameters would be
inconsistent with the descriptions of the other enable_* GUCs. So I agree that
we should keep the current documentation as it is.
>Attached is a rebased patch to make cfbot work again.
I applied the attached v5 patch to commit 57f93af36, and confirmed that all
regression tests completed successfully.
Regards,
Tatsuro Yamada
> -----Original Message-----
> From: Richard Guo <[email protected]>
> Sent: Tuesday, July 7, 2026 3:47 PM
> To: Tatsuro Yamada（山田達朗） <[email protected]>
> Cc: Tatsuro Yamada <[email protected]>; David Rowley
> <[email protected]>; Ashutosh Bapat
> <[email protected]>; [email protected]
> Subject: Re: Add enable_groupagg GUC parameter to control GroupAggregate
> usage
>
> On Mon, Jun 29, 2026 at 7:02 PM Tatsuro Yamada
> <[email protected]> wrote:
> > Since the enable_groupagg parameter also affects SetOp, Unique, and
> > other nodes, I've created documentation patches to clarify this. I'm
> attaching
> > them to this email. They apply on top of your v4 patch.
>
> I've thought it over, and I'm inclined to leave the docs as they are.
>
> My main concern is consistency with the surrounding parameters. All
> of the other enable_* GUCs are documented conceptually rather than by
> the specific node types they affect, and none of them enumerate the
> nodes you'd see in EXPLAIN. Unique and SetOp are really EXPLAIN
> labels rather than user-facing concepts, and I'd argue the existing
> wording already covers them, since sort-based Unique and sorted SetOp
> are both forms of sort-based grouping.
>
> Similarly, enable_hashagg has covered hash-based SetOp for a long time
> without us documenting it, which is the same convention at work. I'd
> rather not add that now just to match the new parameter.
>
> Attached is a rebased patch to make cfbot work again.
>
> - Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-08 11:54 Tatsuro Yamada <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tatsuro Yamada @ 2026-07-08 11:54 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
Hi Richard,
>> Attached is a rebased patch to make cfbot work again.
>
>I intend to push this patch soon. That way newly added test cases can
>start using enable_groupagg directly. Otherwise we'll have to keep
>rebasing it to convert any new tests that use enable_sort to force
>hashed grouping. Any thoughts?
I'm in favor of committing this.
Before you do, I'd like to clarify one point regarding the commit message.
Would it make sense to list both of us as authors?
The reason I ask is that you made substantial changes to the patch,
including handling additional plan nodes such as SetOp and adding regression
tests. Given those contributions, I wondered whether it would be more
appropriate for both of us to be listed as authors. (I've seen past commits that
included two Author: lines.)
I'm happy to leave that decision to you.
By the way, I re-ran the test query from the initial email in this thread.
The execution times were:
Default: 25,791.642 ms
SET enable_groupagg TO off;: 1,006.839 ms
This again confirmed an approximately 25x speedup.
Of course, the actual benefit depends on the specific query, but I believe
this parameter will allow users to improve query performance in more situations.
Regards,
Tatsuro Yamada
> -----Original Message-----
> From: Richard Guo <[email protected]>
> Sent: Tuesday, July 7, 2026 3:53 PM
> To: Tatsuro Yamada（山田達朗） <[email protected]>
> Cc: Tatsuro Yamada <[email protected]>; David Rowley
> <[email protected]>; Ashutosh Bapat
> <[email protected]>; [email protected]
> Subject: Re: Add enable_groupagg GUC parameter to control GroupAggregate
> usage
>
> On Tue, Jul 7, 2026 at 3:47 PM Richard Guo <[email protected]>
> wrote:
> > Attached is a rebased patch to make cfbot work again.
>
> I intend to push this patch soon. That way newly added test cases can
> start using enable_groupagg directly. Otherwise we'll have to keep
> rebasing it to convert any new tests that use enable_sort to force
> hashed grouping. Any thoughts?
>
> - Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-09 01:01 Richard Guo <[email protected]>
parent: Tatsuro Yamada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Richard Guo @ 2026-07-09 01:01 UTC (permalink / raw)
To: Tatsuro Yamada <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
On Wed, Jul 8, 2026 at 8:54 PM Tatsuro Yamada <[email protected]> wrote:
> I'm in favor of committing this.
> Before you do, I'd like to clarify one point regarding the commit message.
> Would it make sense to list both of us as authors?
>
> The reason I ask is that you made substantial changes to the patch,
> including handling additional plan nodes such as SetOp and adding regression
> tests. Given those contributions, I wondered whether it would be more
> appropriate for both of us to be listed as authors. (I've seen past commits that
> included two Author: lines.)
> I'm happy to leave that decision to you.
Pushed.
I still listed myself as Co-authored-by rather than Author, because I
want to give full credit to you, as this is your idea and your patch.
- Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Add enable_groupagg GUC parameter to control GroupAggregate usage
@ 2026-07-09 10:55 Tatsuro Yamada <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Tatsuro Yamada @ 2026-07-09 10:55 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tatsuro Yamada <[email protected]>; David Rowley <[email protected]>; Ashutosh Bapat <[email protected]>; [email protected] <[email protected]>
Hi Richard,
> Pushed.
>
> I still listed myself as Co-authored-by rather than Author, because I
> want to give full credit to you, as this is your idea and your patch.
Thank you for the commit!
I also appreciate your consideration in giving me full credit for the patch.
Regards,
Tatsuro Yamada
> -----Original Message-----
> From: Richard Guo <[email protected]>
> Sent: Thursday, July 9, 2026 10:02 AM
> To: Tatsuro Yamada（山田達朗） <[email protected]>
> Cc: Tatsuro Yamada <[email protected]>; David Rowley
> <[email protected]>; Ashutosh Bapat
> <[email protected]>; [email protected]
> Subject: Re: Add enable_groupagg GUC parameter to control GroupAggregate
> usage
>
> On Wed, Jul 8, 2026 at 8:54 PM Tatsuro Yamada <[email protected]>
> wrote:
> > I'm in favor of committing this.
> > Before you do, I'd like to clarify one point regarding the commit message.
> > Would it make sense to list both of us as authors?
> >
> > The reason I ask is that you made substantial changes to the patch,
> > including handling additional plan nodes such as SetOp and adding
> regression
> > tests. Given those contributions, I wondered whether it would be more
> > appropriate for both of us to be listed as authors. (I've seen past commits
> that
> > included two Author: lines.)
> > I'm happy to leave that decision to you.
>
> Pushed.
>
> I still listed myself as Co-authored-by rather than Author, because I
> want to give full credit to you, as this is your idea and your patch.
>
> - Richard
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-07-09 10:55 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-06 07:59 Add enable_groupagg GUC parameter to control GroupAggregate usage Tatsuro Yamada <[email protected]>
2025-06-06 10:03 ` Tatsuro Yamada <[email protected]>
2025-06-09 10:50 ` Ashutosh Bapat <[email protected]>
2025-06-11 03:07 ` Tatsuro Yamada <[email protected]>
2025-06-11 08:36 ` Tatsuro Yamada <[email protected]>
2025-06-16 02:49 ` David Rowley <[email protected]>
2025-06-20 10:34 ` Tatsuro Yamada <[email protected]>
2026-06-24 23:05 ` Richard Guo <[email protected]>
2026-06-25 03:18 ` Richard Guo <[email protected]>
2026-06-25 09:11 ` Tatsuro Yamada <[email protected]>
2026-06-26 06:26 ` Richard Guo <[email protected]>
2026-06-29 10:02 ` Tatsuro Yamada <[email protected]>
2026-07-07 06:47 ` Richard Guo <[email protected]>
2026-07-07 06:53 ` Richard Guo <[email protected]>
2026-07-08 11:54 ` Tatsuro Yamada <[email protected]>
2026-07-09 01:01 ` Richard Guo <[email protected]>
2026-07-09 10:55 ` Tatsuro Yamada <[email protected]>
2026-07-08 11:09 ` Tatsuro Yamada <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox