Re: Add enable_groupagg GUC parameter to control GroupAggregate usage

On Fri, Jun 20, 2025 at 7:35 PM Tatsuro Yamada <yamatattsu(at)gmail(dot)com> 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