On Fri, Apr 14, 2023 at 10:59 PM Konstantin Knizhnik <knizh...@garret.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-b2rvkx...@mail.gmail.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.bulcbnge3rhwhcsh@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.