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: Amit Kapila <amit.kapila16@gmail.com>, Bruce Momjian <bruce@momjian.us>
Cc: Peter Geoghegan <pg@bowt.ie>, 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-04T20:53:58Z
Lists: pgsql-hackers, pgsql-docs 
On Sat, 2020-07-04 at 14:49 +0530, Amit Kapila wrote:
> > We don't even have a user report yet of a
> > regression compared to PG 12, or one that can't be fixed by
> > increasing
> > work_mem.
> > 
> 
> Yeah, this is exactly the same point I have raised above.  I feel we
> should wait before designing any solution to match pre-13 behavior
> for
> hashaggs to see what percentage of users face problems related to
> this
> and how much is a problem for them to increase work_mem to avoid
> regression.

I agree that it's good to wait for actual problems. But the challenge
is that we can't backport an added GUC. Are there other, backportable
changes we could potentially make if a lot of users have a problem with
v13 after release? Or will any users who experience a problem need to
wait for v14?

I'm OK not having a GUC, but we need consensus around what our response
will be if a user experiences a regression. If our only answer is
"tweak X, Y, and Z; and if that doesn't work, wait for v14" then I'd
like almost everyone to be on board with that. If we have some
backportable potential solutions, that gives us a little more
confidence that we can still get that user onto v13 (even if they have
to wait for a point release).

Without some backportable potential solutions, I'm inclined to ship
with either one or two escape-hatch GUCs, with warnings that they
should be used as a last resort. Hopefully users will complain on the
lists (so we understand the problem) before setting them.

It's not very principled, and we may be stuck with some cruft, but it
mitigates the risk a lot. There's a good chance we can remove them
later, especially if it's part of a larger overhall of
work_mem/hash_mem (which might happen fairly soon, given the interest
in this thread), or if we change something about HashAgg that makes the
GUCs harder to maintain.

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.