Horton

Re: Default setting for enable_hashagg_disk

Bruce Momjian <bruce@momjian.us>

View thread View on postgr.es
From: Bruce Momjian <bruce@momjian.us>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, 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-06-25T18:25:12Z
Lists: pgsql-hackers, pgsql-docs 
On Thu, Jun 25, 2020 at 11:02:30AM -0700, Jeff Davis wrote:
> If we have the GUCs there, then at least if someone comes to the
> mailing list with a problem, we can offer them a temporary solution,
> and have time to try to avoid the problem in a future release (tweaking
> estimates, cost model, defaults, etc.).
> 
> One idea is to have undocumented GUCs. That way we don't have to
> support them forever, and we are more likely to hear problem reports.

Uh, our track record of adding GUCs just in case is not good, and
removing them is even harder.  Undocumented sounds interesting but then
how do we even document when we remove it?  I don't think we want to go
there.  Oracle has done that, and I don't think the user experience is
good.

Maybe we should just continue though beta, add an incompatibility item
to the PG 13 release notes, and see what feedback we get.  We know
increasing work_mem gets us the exceed work_mem behavior, but that
affects other nodes too, and I can't think of a way to avoid if spill is
predicted except to disable hash agg for that query.

I am still trying to get my head around why the spill is going to be so
much work to adjust for hash agg than our other spillable nodes.  What
are people doing for those cases already?  Do we have an real-world
queries that are a problem in PG 13 for this?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

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.