From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18030: Large memory consumption caused by in-clause
 subqueries
In-reply-to: 
 <CAApHDvp=bATE0eYApWiGvHjnKgVLcC4NHQr=uZXGWS=Y8j1FRQ@mail.gmail.com>
References: <18030-2a563b7ccc7d6152@postgresql.org>
 <CAApHDvp=bATE0eYApWiGvHjnKgVLcC4NHQr=uZXGWS=Y8j1FRQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 20 Jul 2023 11:13:05 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <458886.1689811878.1@sss.pgh.pa.us>
Date: Wed, 19 Jul 2023 20:11:18 -0400
Message-ID: <458887.1689811878@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/458887.1689811878%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> When I looked at this at first, I just thought it was a problem
> because of the huge values you're passing to lpad, but on looking
> again, I don't quite see why we need to keep the result of the lpad
> around for the entire execution of the subquery. I wonder if we're
> missing a ResetExprContext(econtext) inside ExecScanSubPlan().

> The following example is a bit easier to follow:

> explain analyze select * from (values(1),(2)) v(v) where lpad(v::text,
> 1024*1024) in (select 'test' from generate_series(1,1000) where v.v >
> 0);

> Currently, this will consume around 1GB of memory due to executing the
> lpad once for each row of the subquery. But isn't it ok to just reset
> the tuple context after fetching each row from the subquery? That
> would mean we'd only allocate around 1MB at a time instead of 1GB.

I doubt that that's okay, because if we are passing any pass-by-ref
params to the subquery (cf. loop loading ecxt_param_exec_vals[] at
nodeSubplan.c:257ff), this change would zap them.  But perhaps it is
worth setting up a second, shorter-lived context to evaluate the
per-row comparison expression in?

I also wonder if we could avoid evaluating the lpad() for each row
in the first place.  This'd require breaking down the comparison
expression more finely than we do now.  But certainly the average
user would be surprised that we evaluate lpad() per-subquery-row.

			regards, tom lane