Re: OOM in hash join

On Fri, Apr 14, 2023 at 10:59 PM Konstantin Knizhnik <knizhnik(at)garret(dot)ru> wrote:
> Too small value of work_mem cause memory overflow in parallel hash join
> because of too much number batches.

Yeah. Not only in parallel hash join, but in any hash join
(admittedly parallel hash join has higher per-batch overheads; that is
perhaps something we could improve). That's why we tried to invent an
alternative strategy where you loop over batches N times, instead of
making more batches, at some point:

https://www.postgresql.org/message-id/flat/CA+hUKGKWWmf=WELLG=aUGbcugRaSQbtm0tKYiBut-B2rVKX63g(at)mail(dot)gmail(dot)com

That thread starts out talking about 'extreme skew' etc but the more
general problem is that, at some point, even with perfectly evenly
distributed keys, adding more batches requires more memory than you
can save by doing so. Sure, it's a problem that we don't account for
that memory properly, as complained about here:

https://www.postgresql.org/message-id/flat/20190504003414(dot)bulcbnge3rhwhcsh(at)development

If you did have perfect prediction of every byte you will need, maybe
you could say, oh, well, we just don't have enough memory for a hash
join, so let's do a sort/merge instead. But you can't, because (1)
some types aren't merge-joinable, and (2) in reality sometimes you've
already started the hash join due to imperfect stats so it's too late
to change strategies.