Horton

Re: Default setting for enable_hashagg_disk

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Stephen Frost <sfrost@snowman.net>, Alvaro Herrera <alvherre@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, Bruce Momjian <bruce@momjian.us>, Robert Haas <robertmhaas@gmail.com>, David Rowley <dgrowleyml@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Melanie Plageman <melanieplageman@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2020-07-10T21:24:21Z
Lists: pgsql-hackers, pgsql-docs 
On Fri, Jul 10, 2020 at 11:34 AM Jeff Davis <pgsql@j-davis.com> wrote:
> On Fri, 2020-07-10 at 13:46 -0400, Tom Lane wrote:
> > I looked over Peter's patch in [1], and it seems generally pretty
> > sane to me, though I concur with the idea that it'd be better to
> > define the GUC as a multiplier for work_mem.  (For one thing, we
> > could
> > then easily limit it to be at least 1.0, ensuring sanity; also, if
> > work_mem does eventually become more dynamic than it is now, we might
> > still be able to salvage this knob as something useful.  Or if not,
> > we just rip it out.)  So my vote is for moving in that direction.
>
> In that case, I will hold off on my "escape-hatch" GUC.

It now seems likely that the hash_mem/hash_mem_multiplier proposal has
the support it needs to get into Postgres 13. Assuming that the
proposal doesn't lose momentum, then it's about time to return to the
original question you posed at the start of the thread:

What should we do with the hashagg_avoid_disk_plan GUC (formerly known
as the enable_hashagg_disk GUC), if anything?

I myself think that there is a case to be made for removing it
entirely. But if we keep it then we should also not change the
default. In other words, by default the planner should *not* try to
avoid hash aggs that spill. AFAICT there is no particular reason to be
concerned about that now, since nobody has expressed any concerns
about any of the possibly-relevant cost models. That said, I don't
feel strongly about this hashagg_avoid_disk_plan question. It seems
*much* less important.

--
Peter Geoghegan

Commits

  1. Add hash_mem_multiplier GUC.

  2. HashAgg: use better cardinality estimate for recursive spilling.

  3. Remove hashagg_avoid_disk_plan GUC.

  4. Doc fixup for hashagg_avoid_disk_plan GUC.

  5. Rework HashAgg GUCs.

  6. Disk-based Hash Aggregation.

  7. Implement partition-wise grouping/aggregation.

  8. Defer creation of partially-grouped relation until it's needed.