Partition pruning is not happening (even in PG18)

Hi super-experts,
I am trying to solve a mystery for a customer where they had some very
large heap tables (500GB+ each) on their PG 13 database which was bringing
them loads of performance problems.
Apart from the regular server tuning efforts, I suggested them to upgrade
it to at least Postgresql 15 and partition these large tables. They've
accepted it and they have applied my suggestions.
However, partition pruning is not happening. Instead, PG is doing
sequential scans against all partitions. I thought it was PG 15 behaviour,
so I tested the same on PG 17 and compiled PG18rc1, getting the same
results. I read on the release notes of PG18 that partition pruning had
many fixes but it seems this one is not in place.
The only way I found to make pruning work is to force index_scan using
pg_hint_plan, but I wanted to influence the planner to decide it by itself
rather than relying on hints. What's the reason for this misbehaving and
what could I do to overcome it?

*Relevant parameters/info:*
DB cluster hosted on Azure Flex Server
random_page_cost=1.1;
plan_cache_mode=force_custom_plan; *# the so called magic-bullet for
pruning*
default_statistics_target=1000;* # better stats to the planner*
geqo_effort=7; *# I wanted the planner to "think" better and do pruning*
enable_partition_pruning=on;

*Test case*
*-- Tables def*
postgres=> \d+ accounts
Partitioned table
"public.accounts"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
------------------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
aid | integer | | not null |
nextval('accounts_aid_seq'::regclass) | plain | |
|
bid | integer | | |
| plain | | |
abalance | integer | | |
| plain | | |
filler | text | | |
| extended | | |
transaction_date | date | | not null |
| plain | | |
Partition key: RANGE (transaction_date)
Indexes:
"accounts_pkey" PRIMARY KEY, btree (transaction_date, aid)
Partitions: accounts_p1 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'),
accounts_p10 FOR VALUES FROM ('2025-10-01') TO ('2025-11-01'),
accounts_p11 FOR VALUES FROM ('2025-11-01') TO ('2025-12-01'),
accounts_p12 FOR VALUES FROM ('2025-12-01') TO ('2026-01-01'),
accounts_p2 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'),
accounts_p3 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'),
accounts_p4 FOR VALUES FROM ('2025-04-01') TO ('2025-05-01'),
accounts_p5 FOR VALUES FROM ('2025-05-01') TO ('2025-06-01'),
accounts_p6 FOR VALUES FROM ('2025-06-01') TO ('2025-07-01'),
accounts_p7 FOR VALUES FROM ('2025-07-01') TO ('2025-08-01'),
accounts_p8 FOR VALUES FROM ('2025-08-01') TO ('2025-09-01'),
accounts_p9 FOR VALUES FROM ('2025-09-01') TO ('2025-10-01')

postgres=> \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+------+-----------+----------+---------+---------+-------------+--------------+-------------
dt_col | date | | | | plain | |
|
Access method: heap

*-- Executing test*
*-- Regular execution*
postgres=> explain analyze
select aid, abalance
from accounts
where transaction_date in (select dt_col from t2);

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1001.23..2073502.05 rows=2767123 width=8) (actual
time=3.574..211619.991 rows=1664742 loops=1)
Workers Planned: 2
Workers Launched: 1
-> Hash Semi Join (cost=1.23..1795789.75 rows=1152968 width=8) (actual
time=77575.241..203629.828 rows=832371 loops=2)
Hash Cond: (accounts.transaction_date = t2.dt_col)
-> Parallel Append (cost=0.00..1672493.00 rows=42083334
width=12) (actual time=0.645..197115.678 rows=50500000 loops=2)
-> Parallel Seq Scan on accounts_p10 accounts_10
(cost=0.00..124553.77 rows=3585078 width=12) (actual time=0.560..30193.541
rows=8604186 loops=1)
-> Parallel Seq Scan on accounts_p5 accounts_5
(cost=0.00..124550.68 rows=3584968 width=12) (actual
time=56.415..54334.025 rows=8603923 loops=1)
-> Parallel Seq Scan on accounts_p8 accounts_8
(cost=0.00..124535.20 rows=3584520 width=12) (actual time=1.738..31555.264
rows=8602847 loops=1)
-> Parallel Seq Scan on accounts_p7 accounts_7
(cost=0.00..124531.08 rows=3584408 width=12) (actual time=2.357..29998.452
rows=8602579 loops=1)
-> Parallel Seq Scan on accounts_p3 accounts_3
(cost=0.00..124481.85 rows=3582985 width=12) (actual time=2.525..24765.109
rows=8599165 loops=1)
-> Parallel Seq Scan on accounts_p1 accounts_1
(cost=0.00..122514.91 rows=3526391 width=12) (actual time=2.675..13909.461
rows=4231669 loops=2)
-> Parallel Seq Scan on accounts_p12 accounts_12
(cost=0.00..122486.55 rows=3525555 width=12) (actual
time=32.199..14350.771 rows=8461332 loops=1)
-> Parallel Seq Scan on accounts_p6 accounts_6
(cost=0.00..120551.91 rows=3469891 width=12) (actual time=1.284..27367.522
rows=8327739 loops=1)
-> Parallel Seq Scan on accounts_p9 accounts_9
(cost=0.00..120515.24 rows=3468824 width=12) (actual time=2.594..29812.536
rows=8325177 loops=1)
-> Parallel Seq Scan on accounts_p4 accounts_4
(cost=0.00..120505.38 rows=3468538 width=12) (actual
time=42.894..30691.460 rows=8324491 loops=1)
-> Parallel Seq Scan on accounts_p11 accounts_11
(cost=0.00..120459.09 rows=3467209 width=12) (actual time=1.068..52911.011
rows=8321302 loops=1)
-> Parallel Seq Scan on accounts_p2 accounts_2
(cost=0.00..112390.67 rows=3234967 width=12) (actual time=0.728..28041.580
rows=7763921 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=4) (actual
time=0.261..0.265 rows=6 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t2 (cost=0.00..1.10 rows=10 width=4)
(actual time=0.253..0.256 rows=6 loops=2)
Planning Time: 1.490 ms
Execution Time: 211741.385 ms
(23 rows)

*-- Forcing pruning using hints*
postgres=> explain analyze
*/*+ IndexScan(accounts accounts_pkey) */*
select aid, abalance
from accounts
where transaction_date in (select dt_col from t2);

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.56..53359847.63 rows=2767123 width=8) (actual
time=9.253..103956.379 rows=1664742 loops=1)
-> HashAggregate (cost=1.12..1.23 rows=10 width=4) (actual
time=0.370..59.714 rows=6 loops=1)
Group Key: t2.dt_col
Batches: 1 Memory Usage: 24kB
-> Seq Scan on t2 (cost=0.00..1.10 rows=10 width=4) (actual
time=0.359..0.362 rows=6 loops=1)
-> Append (cost=0.43..5302777.61 rows=3320703 width=12) (actual
time=3.113..17280.161 rows=277457 loops=6)
-> Index Scan using accounts_p1_pkey on accounts_p1 accounts_1
(cost=0.43..434635.82 rows=273011 width=12) (actual time=5.202..17125.276
rows=276971 loops=1)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p2_pkey on accounts_p2 accounts_2
(cost=0.43..441097.44 rows=277283 width=12) (actual time=0.718..14671.096
rows=276634 loops=1)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p3_pkey on accounts_p3 accounts_3
(cost=0.43..441666.94 rows=277392 width=12) (actual time=1.368..15826.853
rows=278390 loops=1)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p4_pkey on accounts_p4 accounts_4
(cost=0.43..441689.70 rows=277483 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p5_pkey on accounts_p5 accounts_5
(cost=0.43..441883.26 rows=277546 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p6_pkey on accounts_p6 accounts_6
(cost=0.43..441857.26 rows=277591 width=12) (actual time=3.678..18625.085
rows=277582 loops=3)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p7_pkey on accounts_p7 accounts_7
(cost=0.43..441837.57 rows=277503 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p8_pkey on accounts_p8 accounts_8
(cost=0.43..441843.95 rows=277511 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p9_pkey on accounts_p9 accounts_9
(cost=0.43..441711.03 rows=277506 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p10_pkey on accounts_p10 accounts_10
(cost=0.43..441918.96 rows=277554 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p11_pkey on accounts_p11 accounts_11
(cost=0.43..441501.86 rows=277377 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
-> Index Scan using accounts_p12_pkey on accounts_p12 accounts_12
(cost=0.43..434530.31 rows=272946 width=12) (never executed)
Index Cond: (transaction_date = t2.dt_col)
Planning Time: 1.147 ms
Execution Time: 104040.968 ms
(32 rows)

Any help/explanation would be appreciated.
Also, how could I contribute to get this partition pruning to work?

Kindest regards,
--
*Lauro Ojeda*