Horton

Re: Default setting for enable_hashagg_disk (hash_mem)

Bruce Momjian <bruce@momjian.us>

View thread View on postgr.es
From: Bruce Momjian <bruce@momjian.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, David Rowley <dgrowleyml@gmail.com>, Jeff Davis <pgsql@j-davis.com>, Melanie Plageman <melanieplageman@gmail.com>, pgsql-hackers@postgresql.org
Date: 2020-07-03T03:00:01Z
Lists: pgsql-hackers, pgsql-docs 
On Thu, Jul  2, 2020 at 10:58:34PM -0400, Bruce Momjian wrote:
> On Thu, Jul  2, 2020 at 09:46:49PM -0500, Justin Pryzby wrote:
> > On Thu, Jul 02, 2020 at 07:05:48PM -0700, Peter Geoghegan wrote:
> > > anything else). I still think that the new GUC should work as a
> > > multiplier of work_mem, or something else along those lines, though
> > > for now it's just an independent work_mem used for hashing. I bring it
> > > up again because I'm concerned about users that upgrade to Postgres 13
> > > incautiously, and find that hashing uses *less* memory than before.
> > > Many users probably get away with setting work_mem quite high across
> > > the board. At the very least, hash_mem should be ignored when it's set
> > > to below work_mem (which isn't what the patch does).
> > 
> > I feel it should same as work_mem, as it's written, and not a multiplier.
> > 
> > And actually I don't think a lower value should be ignored: "mechanism not
> > policy".  Do we refuse atypical values of maintenance_work_mem < work_mem ?
> > 
> > I assumed that hash_mem would default to -1, which would mean "fall back to
> > work_mem".  We'd then advise users to increase it if they have an issue in v13
> > with performance of hashes spilled to disk.  (And maybe in other cases, too.)
> 
> Uh, with this patch, don't we really have sort_mem and hash_mem, but
> hash_mem default to sort_mem, or something like that.  If hash_mem is a
> multiplier, it would make more sense to keep the work_mem name.

Also, I feel this is all out of scope for PG 13, frankly.  I think our
only option is to revert the hash spill entirely, and return to PG 13
behavior, if people are too worried about hash performance regression.

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