Re: Default setting for enable_hashagg_disk (hash_mem)

On Tue, Jul 7, 2020 at 5:55 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> FWIW, I'm not a fan of the hash_mem idea. It was my impression that we
> aimed to provide an escape hatch for people we have become accustomed
> to <= PG12 behaviour and hash_mem sounds like it's not that.

The exact scope of the problem is unclear. If it was clear, then we'd
be a lot closer to a resolution than we seem to be. Determining the
scope of the problem is the hardest part of the problem.

All that is ~100% clear now is that some users will experience what
they'll call a regression. Those users will be unhappy, even if and
when they come to understand that technically they're just "living
within their means" for the first time, and were theoretically not
entitled to the performance from earlier versions all along. That's
bad, and we should try our best to avoid or mitigate it.

Sometimes the more ambitious plan (in this case hash_mem) actually has
a greater chance of succeeding, despite solving more problems than the
immediate problem. I don't think that it's reasonable to hold that
against my proposal. It may be that hash_mem is a bad idea based on
the costs and benefits, or the new risks, in which case it should be
rejected. But if it's the best proposal on the table by a clear
margin, then it shouldn't be disqualified for not satisfying the
original framing of the problem.

> Surely a
> GUC by that name would control what Hash Join does too? Otherwise, it
> would be called hashagg_mem. I'd say changing the behaviour of Hash
> join is not well aligned to the goal of allowing users to get
> something closer to what PG12 did.

My tentative hash_mem proposal assumed that hash join would be
affected alongside hash agg, in the obvious way. Yes, that's clearly
beyond the scope of the open item.

The history of some other database systems is instructive. At least a
couple of these systems had something like a work_mem/sort_mem GUC, as
well as a separate hash_mem-like GUC that only affects hashing. It's
sloppy, but nevertheless better than completely ignoring the
fundamental ways in which hashing really is special. This is a way of
papering-over one of the main deficiencies of the general idea of a
work_mem style per-node allocation. Yes, that's pretty ugly.

I think that work_mem would be a lot easier to tune if you assume that
hash-based nodes don't exist (i.e. only merge joins and nestloop joins
are available, plus group aggregate for aggregation). You don't need
to do this as a thought experiment. That really was how things were up
until about the mid-1980s, when increasing memory capacities made hash
join and hash agg in database systems feasible for the first time.
Hashing came after most of the serious work on cost-based optimizers
had already been done. This argues for treating hash-based nodes as
special now, if only to extend work_mem beyond its natural life as a
pragmatic short-term measure. Separately, it argues for a *total
rethink* of how memory is used in the executor in the long term -- it
shouldn't be per-node in a few important cases (I'm thinking of the
"hash teams" design I mentioned on this thread recently, which seems
like a fundamentally better way of doing it).

> We're certainly not
> going to get that for PG13, so I do think what we need here is just a
> simple escape hatch. I mentioned my thoughts in [2], so won't go over
> it again here. Once we've improved the situation in some future
> version of postgres, perhaps along the lines of what Tomas mentioned,
> then we can get rid of the escape hatch.

If it really has to be a simple escape hatch in Postgres 13, then I
could live with a hard disabling of spilling at execution time. That
seems like the most important thing that is addressed by your
proposal. I'm concerned that way too many users will have to use the
escape hatch, and that that misses the opportunity to provide a
smoother experience.

> Here are my reasons for not liking the hash_mem idea:

I'm sure that your objections are valid to varying degrees. But they
could almost be thought of as problems with work_mem itself. I am
trying to come up with a practical way of ameliorating the downsides
of work_mem. I don't for a second imagine that this won't create new
problems. I think that it's the least worst thing right now. I have my
misgivings.

--
Peter Geoghegan