Horton

Re: Default setting for enable_hashagg_disk (hash_mem)

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: 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>, David Rowley <dgrowleyml@gmail.com>, Jeff Davis <pgsql@j-davis.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-07-07T17:12:16Z
Lists: pgsql-hackers, pgsql-docs 
Hi,

On 2020-07-03 10:08:08 -0400, Bruce Momjian wrote:
> Well, the bottom line is that we are designing features during beta.
> People are supposed to be testing PG 13 behavior during beta, including
> optimizer behavior.

I think it makes no too much sense to plan invent something like
hash_mem for v13, it's clearly too much work. That's a seperate
discussion from having something like it for v14.


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

I posted a repro, and no you can't fix it by increasing work_mem without
increasing memory usage in the whole query / all queries.


> If we add a new behavior to PG 13, we then have the pre-PG 13 behavior,
> the pre-patch behavior, and the post-patch behavior.  How are people
> supposed to test all of that?

I don't really buy this as a problem. It's not like the pre-13 behaviour
would be all new. It's how PG has behaved approximately forever.


My conclusion about this topic is that I think we'll be doing our users
a disservice by not providing an escape hatch, but that I also don't
have the energy / time to fight for it further. This is a long thread
already, and I sense little movement towards a conclusion.

Greetings,

Andres Freund

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.