Horton

Re: Default setting for enable_hashagg_disk

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: Jeff Davis <pgsql@j-davis.com>, Peter Geoghegan <pg@bowt.ie>, Alvaro Herrera <alvherre@2ndquadrant.com>, David Rowley <dgrowleyml@gmail.com>, Andres Freund <andres@anarazel.de>, Bruce Momjian <bruce@momjian.us>, Robert Haas <robertmhaas@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-19T14:43:49Z
Lists: pgsql-hackers, pgsql-docs 
Stephen Frost <sfrost@snowman.net> writes:
> In other words, if we'd stop trying to shoehorn something in, which
> we're doing because we're in beta, we'd very likely be talking about all
> of this in a very different way and probably be contemplating something
> like a query_mem that provides for an overall memory limit and which
> favors memory for hashing over memory for sorting, etc.

Even if we were at the start of the dev cycle rather than its end,
I'm not sure I agree.  Yes, replacing work_mem with some more-holistic
approach would be great.  But that's a research project, one that
we can't be sure will yield fruit on any particular schedule.  (Seeing
that we've understood this to be a problem for *decades*, I would tend
to bet on a longer not shorter time frame for a solution.)

I think that if we are worried about hashagg-spill behavior in the near
term, we have to have some fix that's not conditional on solving that
very large problem.  The only other practical alternative is "do
nothing for v13", and I agree with the camp that doesn't like that.

			regards, tom lane

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.