From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Andres Freund <andres@anarazel.de>, lauri.laanmets@eesti.ee,
        pgsql-bugs@lists.postgresql.org
Subject: Re: Memory leak on subquery as scalar operand
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>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Tue, 01 Nov 2022 16:48:50 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1073511.1667275802.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 01 Nov 2022 00:10:02 -0400
Message-ID: <1073512.1667275802@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1073512.1667275802%40sss.pgh.pa.us>
Precedence: bulk

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 thresh=
old.

I see JIT being invoked either way:

regression=3D# explain verbose SELECT
    id,
    (SELECT count(*) FROM leak_test x WHERE x.id=3Dl.id) as x_result,     =
            =

    (SELECT count(*) FROM leak_test y WHERE y.id=3Dl.id) as y_result
FROM leak_test l;
                                                 QUERY PLAN               =
                                  =

--------------------------------------------------------------------------=
----------------------------------
 Seq Scan on public.leak_test l  (cost=3D0.00..865943.00 rows=3D100000 wid=
th=3D20)
   Output: l.id, (SubPlan 1), (SubPlan 2)
   SubPlan 1
     ->  Aggregate  (cost=3D4.31..4.32 rows=3D1 width=3D8)
           Output: count(*)
           ->  Index Only Scan using leak_test_pkey on public.leak_test x =
 (cost=3D0.29..4.31 rows=3D1 width=3D0)
                 Output: x.id
                 Index Cond: (x.id =3D l.id)
   SubPlan 2
     ->  Aggregate  (cost=3D4.31..4.32 rows=3D1 width=3D8)
           Output: count(*)
           ->  Index Only Scan using leak_test_pkey on public.leak_test y =
 (cost=3D0.29..4.31 rows=3D1 width=3D0)
                 Output: y.id
                 Index Cond: (y.id =3D l.id)
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming =
true
(17 rows)

regression=3D# explain verbose SELECT
    id,
    (SELECT count(*) FROM leak_test x WHERE x.id=3Dl.id) as x_result FROM =
leak_test l;
                                                 QUERY PLAN               =
                                  =

--------------------------------------------------------------------------=
----------------------------------
 Seq Scan on public.leak_test l  (cost=3D0.00..433693.00 rows=3D100000 wid=
th=3D12)
   Output: l.id, (SubPlan 1)
   SubPlan 1
     ->  Aggregate  (cost=3D4.31..4.32 rows=3D1 width=3D8)
           Output: count(*)
           ->  Index Only Scan using leak_test_pkey on public.leak_test x =
 (cost=3D0.29..4.31 rows=3D1 width=3D0)
                 Output: x.id
                 Index Cond: (x.id =3D l.id)
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deformin=
g true
(11 rows)

Maybe the different "inlining" choice makes a difference?

			regards, tom lane