Subject: Re: OOM in hash join

From: Jehan-Guillaume de Rorthais <[email-masked]>
Date: Fri, 14 Apr 2023 13:43:21 +0200

On Fri, 14 Apr 2023 13:21:05 +0200
Matthias van de Meent <[email-masked]> wrote:

> On Fri, 14 Apr 2023 at 12:59, Konstantin Knizhnik <[email-masked]> wrote:
> >
> > Hi hackers,
> >
> > Too small value of work_mem cause memory overflow in parallel hash join
> > because of too much number batches.
> > There is the plan:  
> 
> [...]
> 
> > There is still some gap between size reported by memory context sump and
> > actual size of backend.
> > But is seems to be obvious, that trying to fit in work_mem
> > sharedtuplestore creates so much batches, that  them consume much more
> > memory than work_mem.

Indeed. The memory consumed by batches is not accounted and the consumption
reported in explain analyze is wrong.

Would you be able to test the latest patchset posted [1] ? This does not fix
the work_mem overflow, but it helps to keep the number of batches
balanced and acceptable. Any feedback, comment or review would be useful.

[1] https://www.postgresql.org/message-id/flat/20230408020119.32a0841b%40karst#616c1f41fcc10e8f89d41e8e5693618c

Regards,

---

From: Thomas Munro <[email-masked]>
Date: Sat, 15 Apr 2023 00:46:09 +1200

> [1] https://www.postgresql.org/message-id/flat/20230408020119.32a0841b%40karst#616c1f41fcc10e8f89d41e8e5693618c

Hi Jehan-Guillaume.  I hadn't paid attention to that thread before
probably due to timing and the subject and erm ETOOMANYTHREADS.
Thanks for all the work you've done to study this area and also review
and summarise the previous writing/patches/ideas.