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