Horton

Re: Default setting for enable_hashagg_disk (hash_mem)

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Bruce Momjian <bruce@momjian.us>, Justin Pryzby <pryzby@telsasoft.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, David Rowley <dgrowleyml@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-07-07T04:57:08Z
Lists: pgsql-hackers, pgsql-docs 
On Sun, 2020-07-05 at 16:47 -0700, Peter Geoghegan wrote:
> Where does that leave the hash_mem idea (or some other similar
> proposal)?

hash_mem is acceptable to me if the consensus is moving toward that,
but I'm not excited about it.

It would be one thing if hash_mem was a nice clean solution. But it
doesn't seem like a clean solution to me; and it's likely that it will
get in the way of the next person who tries to improve the work_mem
situation.

> I think that we should offer something like hash_mem that can work as
> a multiple of work_mem, for the reason that Justin mentioned
> recently.
> This can be justified as something that more or less maintains some
> kind of continuity with the old design.

Didn't Justin argue against using a multiplier?
https://postgr.es/m/20200703024649.GJ4107@telsasoft.com

> I think that it should affect hash join too, though I suppose that
> that part might be controversial -- that is certainly more than an
> escape hatch for this particular problem. Any thoughts on that?

If it's called hash_mem, then I guess it needs to affect HJ. If not, it
should have a different name.

> There are several reasons to get rid of work_mem entirely in the
> medium to long term. Some relatively obvious, others less so.

Agreed.

It seems like the only argument against the escape hatch GUCs is that
they are cruft and we will end up stuck with them. But if we are
dispensing with work_mem in a few releases, surely we'd need to
dispense with hash_mem or the proposed escape-hatch GUCs anyway.

> An example in the latter category is "hash teams" [1]: a design that
> teaches multiple hash operations (e.g. a hash join and a hash
> aggregate that hash on the same columns) to cooperate in processing
> their inputs.

Cool! It would certainly be nice to share the partitioning work between
a HashAgg and a HJ.

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.