Hi Baji, Etsuro,
Reproduced both repro scripts on two PG18.4 builds (same REL_18_4 tag, one
unpatched, one with just the costsize.c hunk). Output attached.
repro_plain.sql: plan shapes match your report exactly (Hash Join -> Nested
Loop+Memoize).
repro_e2e.sql: plan_cache_mode=auto, no config changes --
unpatched: generic_plans=0, custom_plans=10, ~65 ms/call
patched: generic_plans=5, custom_plans=5, ~1.8 ms/call
Confirms the undercosted custom plan was defeating the plan cache's own
generic-vs-custom check.
Thanks,
Nazneen
On Thu, Oct 22, 2026 at 6:32 AM Baji Shaik <[email protected]> wrote:
> On Thu, Oct 22, 2026 at 6:47 AM Etsuro Fujita <[email protected]>
> wrote:
>
>> I agree that this is for master-only as well. Could you add it to the
>> upcoming commitfest. I'd like to review it next month.
>
>
> Thank you for looking into this, Etsuro.
> Added to the commitfest: https://commitfest.postgresql.org/patch/7502/
>
> Thanks,
> Baji Shaik.
>
psql:repro_e2e.sql:19: NOTICE: function pc_f(pg_catalog.int4[],timestamptz)
does not exist, skipping
psql:repro_e2e.sql:20: NOTICE: table "pc_ev" does not exist, skipping
psql:repro_e2e.sql:20: NOTICE: table "pc_lk" does not exist, skipping
PREPARE
=== 10 executions with plan_cache_mode = auto ===
generic_plans | custom_plans
---------------+--------------
5 | 5
(1 row)
=== plan used under auto ===
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50100.95..50100.96 rows=1 width=8) (actual time=2.011..2.013
rows=1.00 loops=1)
-> Nested Loop (cost=4996.31..50100.50 rows=180 width=9) (actual
time=0.036..1.976 rows=500.00 loops=1)
-> Nested Loop (cost=4996.02..49964.29 rows=180 width=4) (actual
time=0.030..0.914 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..0.10 rows=10 width=4)
(actual time=0.009..0.016 rows=100.00 loops=1)
-> Limit (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.008..0.008 rows=5.00 loops=100)
-> Sort (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.007..0.008 rows=5.00 loops=100)
Sort Key: e.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e
(cost=43.47..4995.64 rows=18 width=12) (actual time=0.003..0.005 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= $2) AND (ts >= ($2 - '90
days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on pc_ev_gid_idx
(cost=0.00..43.47 rows=3606 width=0) (actual time=0.002..0.002 rows=5.00
loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
-> Memoize (cost=0.30..6.98 rows=1 width=13) (actual
time=0.002..0.002 rows=1.00 loops=500)
Cache Key: e.lk_id
Cache Mode: logical
Hits: 0 Misses: 500 Evictions: 0 Overflows: 0 Memory Usage:
57kB
-> Index Scan using pc_lk_pkey on pc_lk k (cost=0.29..6.97
rows=1 width=13) (actual time=0.001..0.001 rows=1.00 loops=500)
Index Cond: (id = e.lk_id)
Index Searches: 500
(22 rows)
=== generic plan ===
SET
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50100.95..50100.96 rows=1 width=8) (actual time=1.988..1.989
rows=1.00 loops=1)
-> Nested Loop (cost=4996.31..50100.50 rows=180 width=9) (actual
time=0.033..1.952 rows=500.00 loops=1)
-> Nested Loop (cost=4996.02..49964.29 rows=180 width=4) (actual
time=0.028..0.917 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..0.10 rows=10 width=4)
(actual time=0.009..0.016 rows=100.00 loops=1)
-> Limit (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.008..0.008 rows=5.00 loops=100)
-> Sort (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.007..0.008 rows=5.00 loops=100)
Sort Key: e.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e
(cost=43.47..4995.64 rows=18 width=12) (actual time=0.003..0.005 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= $2) AND (ts >= ($2 - '90
days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on pc_ev_gid_idx
(cost=0.00..43.47 rows=3606 width=0) (actual time=0.002..0.002 rows=5.00
loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
-> Memoize (cost=0.30..6.98 rows=1 width=13) (actual
time=0.002..0.002 rows=1.00 loops=500)
Cache Key: e.lk_id
Cache Mode: logical
Hits: 0 Misses: 500 Evictions: 0 Overflows: 0 Memory Usage:
57kB
-> Index Scan using pc_lk_pkey on pc_lk k (cost=0.29..6.97
rows=1 width=13) (actual time=0.001..0.001 rows=1.00 loops=500)
Index Cond: (id = e.lk_id)
Index Searches: 500
(22 rows)
RESET
CREATE FUNCTION
=== SQL function, auto: 10 calls, then timed 3 ===
Timing is on.
pc_f
------
500
(1 row)
Time: 1.895 ms
pc_f
------
500
(1 row)
Time: 1.827 ms
pc_f
------
500
(1 row)
Time: 1.827 ms
Timing is off.
=== SQL function, force_generic_plan: timed 3 ===
ALTER FUNCTION
Timing is on.
pc_f
------
500
(1 row)
Time: 2.126 ms
pc_f
------
500
(1 row)
Time: 1.837 ms
pc_f
------
500
(1 row)
Time: 1.822 ms
Timing is off.
psql:repro_plain.sql:4: NOTICE: table "pc_ev" does not exist, skipping
psql:repro_plain.sql:4: NOTICE: table "pc_lk" does not exist, skipping
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=53215.44..53215.45 rows=1 width=8) (actual time=2.187..2.189
rows=1.00 loops=1)
InitPlan 1
-> Function Scan on generate_series g_1 (cost=0.00..1.25 rows=100
width=4) (actual time=0.012..0.021 rows=100.00 loops=1)
-> Nested Loop (cost=5247.50..53201.74 rows=4978 width=9) (actual
time=0.104..2.150 rows=500.00 loops=1)
-> Nested Loop (cost=5247.20..52584.61 rows=5000 width=4) (actual
time=0.095..1.017 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..0.10 rows=10 width=4)
(actual time=0.037..0.045 rows=100.00 loops=1)
-> Limit (cost=5247.20..5248.45 rows=500 width=12) (actual
time=0.008..0.009 rows=5.00 loops=100)
-> Sort (cost=5247.20..5256.60 rows=3758 width=12)
(actual time=0.008..0.008 rows=5.00 loops=100)
Sort Key: e.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e
(cost=45.55..5059.94 rows=3758 width=12) (actual time=0.004..0.006 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= '2026-03-31
00:00:00+00'::timestamp with time zone) AND (ts >= ('2026-03-31
00:00:00+00'::timestamp with time zone - '90 days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on pc_ev_gid_idx
(cost=0.00..44.61 rows=3758 width=0) (actual time=0.002..0.002 rows=5.00
loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
-> Memoize (cost=0.30..0.99 rows=1 width=13) (actual
time=0.002..0.002 rows=1.00 loops=500)
Cache Key: e.lk_id
Cache Mode: logical
Hits: 0 Misses: 500 Evictions: 0 Overflows: 0 Memory Usage:
57kB
-> Index Scan using pc_lk_pkey on pc_lk k (cost=0.29..0.98
rows=1 width=13) (actual time=0.002..0.002 rows=1.00 loops=500)
Index Cond: (id = e.lk_id)
Index Searches: 500
Planning Time: 0.386 ms
Execution Time: 2.224 ms
(26 rows)
psql:repro_plain.sql:4: NOTICE: table "pc_ev" does not exist, skipping
psql:repro_plain.sql:4: NOTICE: table "pc_lk" does not exist, skipping
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7447.03..7447.04 rows=1 width=8) (actual
time=132.407..132.410 rows=1.00 loops=1)
InitPlan 1
-> Function Scan on generate_series g_1 (cost=0.00..1.25 rows=100
width=4) (actual time=0.012..0.021 rows=100.00 loops=1)
-> Nested Loop (cost=5283.18..7433.28 rows=5000 width=9) (actual
time=0.348..132.359 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..0.10 rows=10 width=4)
(actual time=0.038..0.052 rows=100.00 loops=1)
-> Hash Join (cost=5283.17..5493.17 rows=500 width=9) (actual
time=0.164..1.321 rows=5.00 loops=100)
Hash Cond: (k.id = e.lk_id)
-> Seq Scan on pc_lk k (cost=0.00..155.00 rows=10000 width=13)
(actual time=0.001..0.577 rows=10000.00 loops=100)
-> Hash (cost=5276.92..5276.92 rows=500 width=4) (actual
time=0.012..0.012 rows=5.00 loops=100)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on e (cost=5270.67..5276.92 rows=500
width=4) (actual time=0.009..0.011 rows=5.00 loops=100)
-> Limit (cost=5270.67..5271.92 rows=500 width=12)
(actual time=0.009..0.010 rows=5.00 loops=100)
-> Sort (cost=5270.67..5280.20 rows=3812
width=12) (actual time=0.009..0.009 rows=5.00 loops=100)
Sort Key: e_1.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e_1
(cost=45.97..5080.73 rows=3812 width=12) (actual time=0.005..0.007 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= '2026-03-31
00:00:00+00'::timestamp with time zone) AND (ts >= ('2026-03-31
00:00:00+00'::timestamp with time zone - '90 days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on
pc_ev_gid_idx (cost=0.00..45.01 rows=3812 width=0) (actual time=0.003..0.003
rows=5.00 loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
Planning Time: 0.432 ms
Execution Time: 132.450 ms
(24 rows)
psql:repro_e2e.sql:19: NOTICE: function pc_f(pg_catalog.int4[],timestamptz)
does not exist, skipping
psql:repro_e2e.sql:20: NOTICE: table "pc_ev" does not exist, skipping
psql:repro_e2e.sql:20: NOTICE: table "pc_lk" does not exist, skipping
PREPARE
=== 10 executions with plan_cache_mode = auto ===
generic_plans | custom_plans
---------------+--------------
0 | 10
(1 row)
=== plan used under auto ===
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=26814.72..26814.73 rows=1 width=8) (actual
time=132.005..132.008 rows=1.00 loops=1)
-> Nested Loop (cost=5188.72..26689.72 rows=50000 width=9) (actual
time=0.258..131.959 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..1.00 rows=100 width=4)
(actual time=0.012..0.040 rows=100.00 loops=1)
-> Hash Join (cost=5188.72..5398.72 rows=500 width=9) (actual
time=0.164..1.317 rows=5.00 loops=100)
Hash Cond: (k.id = e.lk_id)
-> Seq Scan on pc_lk k (cost=0.00..155.00 rows=10000 width=13)
(actual time=0.001..0.571 rows=10000.00 loops=100)
-> Hash (cost=5182.47..5182.47 rows=500 width=4) (actual
time=0.011..0.011 rows=5.00 loops=100)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on e (cost=5176.22..5182.47 rows=500
width=4) (actual time=0.009..0.010 rows=5.00 loops=100)
-> Limit (cost=5176.22..5177.47 rows=500 width=12)
(actual time=0.009..0.010 rows=5.00 loops=100)
-> Sort (cost=5176.22..5185.23 rows=3606
width=12) (actual time=0.009..0.009 rows=5.00 loops=100)
Sort Key: e_1.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e_1
(cost=44.37..4996.54 rows=3606 width=12) (actual time=0.004..0.007 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= '2026-03-31
00:00:00+00'::timestamp with time zone) AND (ts >= ('2026-03-31
00:00:00+00'::timestamp with time zone - '90 days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on
pc_ev_gid_idx (cost=0.00..43.47 rows=3606 width=0) (actual time=0.002..0.002
rows=5.00 loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
(20 rows)
=== generic plan ===
SET
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50100.95..50100.96 rows=1 width=8) (actual time=2.184..2.186
rows=1.00 loops=1)
-> Nested Loop (cost=4996.31..50100.50 rows=180 width=9) (actual
time=0.043..2.148 rows=500.00 loops=1)
-> Nested Loop (cost=4996.02..49964.29 rows=180 width=4) (actual
time=0.033..0.952 rows=500.00 loops=1)
-> Function Scan on unnest g (cost=0.00..0.10 rows=10 width=4)
(actual time=0.011..0.019 rows=100.00 loops=1)
-> Limit (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.008..0.009 rows=5.00 loops=100)
-> Sort (cost=4996.01..4996.06 rows=18 width=12) (actual
time=0.008..0.008 rows=5.00 loops=100)
Sort Key: e.ts DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on pc_ev e
(cost=43.47..4995.64 rows=18 width=12) (actual time=0.003..0.006 rows=5.00
loops=100)
Recheck Cond: (gid = g.id)
Filter: ((ts <= $2) AND (ts >= ($2 - '90
days'::interval)))
Heap Blocks: exact=372
-> Bitmap Index Scan on pc_ev_gid_idx
(cost=0.00..43.47 rows=3606 width=0) (actual time=0.002..0.002 rows=5.00
loops=100)
Index Cond: (gid = g.id)
Index Searches: 100
-> Memoize (cost=0.30..6.98 rows=1 width=13) (actual
time=0.002..0.002 rows=1.00 loops=500)
Cache Key: e.lk_id
Cache Mode: logical
Hits: 0 Misses: 500 Evictions: 0 Overflows: 0 Memory Usage:
57kB
-> Index Scan using pc_lk_pkey on pc_lk k (cost=0.29..6.97
rows=1 width=13) (actual time=0.002..0.002 rows=1.00 loops=500)
Index Cond: (id = e.lk_id)
Index Searches: 500
(22 rows)
RESET
CREATE FUNCTION
=== SQL function, auto: 10 calls, then timed 3 ===
Timing is on.
pc_f
------
500
(1 row)
Time: 65.080 ms
pc_f
------
500
(1 row)
Time: 65.069 ms
pc_f
------
500
(1 row)
Time: 65.067 ms
Timing is off.
=== SQL function, force_generic_plan: timed 3 ===
ALTER FUNCTION
Timing is on.
pc_f
------
500
(1 row)
Time: 2.402 ms
pc_f
------
500
(1 row)
Time: 1.862 ms
pc_f
------
500
(1 row)
Time: 1.802 ms
Timing is off.
