From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: lauri.laanmets@eesti.ee
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Memory leak on subquery as scalar operand
Date: Tue, 01 Nov 2022 00:10:02 -0400
Message-ID: <1073512.1667275802@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvpL=3-61Lsfd9P5vuf48sJgKYP1qUULDEoKcAsEJpZA7g@mail.gmail.com>
References: <CAPH-tTxLf44s3CvUUtQpkDr1D8Hxqc2NGDzGXS1ODsfiJ6WSqA@mail.gmail.com>
<CAApHDvrSbQkPUacnaonQzw1efFJM6HE5we-i9VVogBfEKGCCiA@mail.gmail.com>
<1046179.1667263449@sss.pgh.pa.us>
<CAApHDvpL=3-61Lsfd9P5vuf48sJgKYP1qUULDEoKcAsEJpZA7g@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 1 Nov 2022 at 13:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's pretty odd though that it requires two
>> sub-selects to cause the problem.
> Perhaps that's just what it takes to bump the costs above the JIT threshold.
I see JIT being invoked either way:
regression=# explain verbose SELECT
id,
(SELECT count(*) FROM leak_test x WHERE x.id=l.id) as x_result,
(SELECT count(*) FROM leak_test y WHERE y.id=l.id) as y_result
FROM leak_test l;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on public.leak_test l (cost=0.00..865943.00 rows=100000 width=20)
Output: l.id, (SubPlan 1), (SubPlan 2)
SubPlan 1
-> Aggregate (cost=4.31..4.32 rows=1 width=8)
Output: count(*)
-> Index Only Scan using leak_test_pkey on public.leak_test x (cost=0.29..4.31 rows=1 width=0)
Output: x.id
Index Cond: (x.id = l.id)
SubPlan 2
-> Aggregate (cost=4.31..4.32 rows=1 width=8)
Output: count(*)
-> Index Only Scan using leak_test_pkey on public.leak_test y (cost=0.29..4.31 rows=1 width=0)
Output: y.id
Index Cond: (y.id = l.id)
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
(17 rows)
regression=# explain verbose SELECT
id,
(SELECT count(*) FROM leak_test x WHERE x.id=l.id) as x_result FROM leak_test l;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on public.leak_test l (cost=0.00..433693.00 rows=100000 width=12)
Output: l.id, (SubPlan 1)
SubPlan 1
-> Aggregate (cost=4.31..4.32 rows=1 width=8)
Output: count(*)
-> Index Only Scan using leak_test_pkey on public.leak_test x (cost=0.29..4.31 rows=1 width=0)
Output: x.id
Index Cond: (x.id = l.id)
JIT:
Functions: 7
Options: Inlining false, Optimization false, Expressions true, Deforming true
(11 rows)
Maybe the different "inlining" choice makes a difference?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, andres@anarazel.de, lauri.laanmets@eesti.ee, pgsql-bugs@lists.postgresql.org
Subject: Re: Memory leak on subquery as scalar operand
In-Reply-To: <1073512.1667275802@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox