On Tue, Jul 7, 2020 at 5:55 AM David Rowley <dgrowle...@gmail.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
