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: Stephen Frost <sfrost@snowman.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Jeff Davis <pgsql@j-davis.com>, 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-11T16:49:43Z
Lists: pgsql-hackers, pgsql-docs 
On Sat, Jul 11, 2020 at 7:22 AM Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Have you got a better proposal that is reasonably implementable for v13?
> > (I do not accept the argument that "do nothing" is a better proposal.)

> So, no, I don't agree that 'do nothing' (except ripping out the one GUC
> that was already added) is a worse proposal than adding another work_mem
> like thing that's only for some nodes types.

The question was "Have you got a better proposal that is reasonably
implementable for v13?".

This is anecdotal, but just today somebody on Twitter reported
*increasing* work_mem to stop getting OOMs from group aggregate +
sort:

https://twitter.com/theDressler/status/1281942941133615104

It was possible to fix the problem in this instance, since evidently
there wasn't anything else that really did try to consume ~5 GB of
work_mem memory. Evidently the memory isn't available in any general
sense, so there are no OOMs now. Nevertheless, we can expect OOMs on
this server just as soon as there is a real need to do a ~5GB sort,
regardless of anything else.

I don't think that this kind of perverse effect is uncommon. Hash
aggregate can naturally be far faster than group agg + sort, Hash agg
can naturally use a lot less memory in many cases, and we have every
reason to think that grouping estimates are regularly totally wrong.
You're significantly underestimating the risk.

--
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.