Date: Thu, 2 Mar 2023 23:57:21 +0100
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Memory leak from ExecutorState context?
Message-ID: <20230302235721.54af8258@karst>
In-Reply-To: <dbae24d7-0dda-18aa-5e08-8138ac1caef9@enterprisedb.com>
References: <20230228190643.1e368315@karst>
	<45d453c8-b2d3-b477-36eb-32fdf4455f3c@enterprisedb.com>
	<20230301184840.0a897a80@karst>
	<3013398b-316c-638f-2a73-3783e8e2ef02@enterprisedb.com>
	<20230302001827.66e95dc3@karst>
	<41c5766d-ed71-b70c-bbbc-d3396c462d62@enterprisedb.com>
	<20230302130838.717e888d@karst>
	<77a96d42-00cb-2448-465a-aa1e92d00cac@enterprisedb.com>
	<20230302191530.781909fe@karst>
	<dbae24d7-0dda-18aa-5e08-8138ac1caef9@enterprisedb.com>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20230302235721.54af8258%40karst>
Precedence: bulk

On Thu, 2 Mar 2023 19:53:14 +0100
Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
> On 3/2/23 19:15, Jehan-Guillaume de Rorthais wrote:
...

> > There was some thoughts about how to make a better usage of the memory.=
 As
> > memory is exploding way beyond work_mem, at least, avoid to waste it wi=
th
> > too many buffers of BufFile. So you expand either the work_mem or the
> > number of batch, depending on what move is smarter. TJis is explained a=
nd
> > tested here:
> >=20
> > https://www.postgresql.org/message-id/20190421161434.4hedytsadpbnglgk%4=
0development
> > https://www.postgresql.org/message-id/20190422030927.3huxq7gghms4kmf4%4=
0development
> >=20
> > And then, another patch to overflow each batch to a dedicated temp file=
 and
> > stay inside work_mem (v4-per-slice-overflow-file.patch):
> >=20
> > https://www.postgresql.org/message-id/20190428141901.5dsbge2ka3rxmpk6%4=
0development
> >=20
> > Then, nothing more on the discussion about this last patch. So I guess =
it
> > just went cold.
>=20
> I think a contributing factor was that the OP did not respond for a
> couple months, so the thread went cold.
>=20
> > For what it worth, these two patches seems really interesting to me. Do=
 you
> > need any help to revive it?
>=20
> I think another reason why that thread went nowhere were some that we've
> been exploring a different (and likely better) approach to fix this by
> falling back to a nested loop for the "problematic" batches.
>=20
> As proposed in this thread:
>=20
>  https://www.postgresql.org/message-id/20190421161434.4hedytsadpbnglgk%40=
development

Unless I'm wrong, you are linking to the same =C2=ABfrustrated as heck!=C2=
=BB discussion,
for your patch v2-0001-account-for-size-of-BatchFile-structure-in-hashJo.pa=
tch
(balancing between increasing batches *and* work_mem).

No sign of turning "problematic" batches to nested loop. Did I miss somethi=
ng?

Do you have a link close to your hand about such algo/patch test by any cha=
nce?

> I was hoping we'd solve this by the BNL, but if we didn't get that in 4
> years, maybe we shouldn't stall and get at least an imperfect stop-gap
> solution ...

I'll keep searching tomorrow about existing BLN discussions (is it block le=
vel
nested loops?).

Regards,