Time to increase hash_mem_multiplier default?

The current hash_mem_multiplier default is 1.0, which is a fairly
conservative default: it preserves the historic behavior, which is
that hash-based executor nodes receive the same work_mem budget as
sort-based nodes. I propose that the default be increased to 2.0 for
Postgres 15.

Arguments in favor of artificially favoring hash-based nodes like this
were made when hash_mem_mutiplier went in. The short version goes like
this:

The relationship between memory availability and overall
performance/throughput has very significant differences when we
compare sort-based nodes with hash-based nodes. It's hard to make
reliable generalizations about how the performance/throughput of
hash-based nodes will be affected as memory is subtracted, even if we
optimistically assume that requirements are fairly fixed. Data
cardinality tends to make the picture complicated, just for starters.
But overall, as a general rule, more memory tends to make everything
go faster.

On the other hand, sort-based nodes (e.g., GroupAggregate) have very
predictable performance characteristics, and the possible upside of
allowing a sort node to use more memory is quite bounded. There is a
relatively large drop-off when we go from not being able to fit
everything in memory to needing to do an external sort. But even that
drop-off isn't very big -- not in absolute terms. More importantly,
there is hardly any impact as we continue to subtract memory (or add
more data). We'll still be able to do a single pass external sort with
only a small fraction of the memory needed to sort everything in
memory, which (perhaps surprisingly) is mostly all that matters.

The choice of 2.0 is still pretty conservative. I'm not concerned
about making hash nodes go faster (or used more frequently) -- at
least not primarily. I'm more worried about avoiding occasional OOMs
from sort nodes that use much more memory than could ever really make
sense. It's easy to demonstrate that making more memory available to
an external sort makes just about no difference, until you give it all
the memory it can make use of. This effect is reliable (data
cardinality won't matter, for example). And so the improvement that is
possible from giving a sort more memory is far smaller than (say) the
improvement in performance we typically see when the optimizer
switches from a hash aggregate to a group aggregate.

--
Peter Geoghegan