Horton

Re: Default setting for enable_hashagg_disk

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Geoghegan <pg@bowt.ie>, Alvaro Herrera <alvherre@2ndquadrant.com>, David Rowley <dgrowleyml@gmail.com>, Stephen Frost <sfrost@snowman.net>, 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-18T22:04:52Z
Lists: pgsql-hackers, pgsql-docs 
On Sat, 2020-07-18 at 14:30 -0400, Tom Lane wrote:
> You'e being optimistic about it being possible to remove a GUC once
> we ship it.  That seems to be a hard sell most of the time.

If nothing else, a repeat of this thread in a year or two to discuss
removing a GUC doesn't seem appealing.

> I think the entire discussion
> is way out ahead of any field evidence that we need such a knob.
> In the absence of evidence, our default position ought to be to
> keep it simple, not to accumulate backwards-compatibility kluges.

Fair enough. I think that was where Stephen and Amit were coming from,
as well.

What is your opinion about pessimizing the HashAgg disk costs (not
affecting HashAgg plans expected to stay in memory)? Tomas Vondra
presented some evidence that Sort had some better IO patterns in some
cases that weren't easily reflected in a principled way in the cost
model.

That would lessen the number of changed plans, but we could easily
remove the pessimization without controversy later if it turned out to
be unnecessary, or if we further optimize HashAgg IO.

Regards,
	Jeff Davis

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.