Re: Partition pruning is not happening (even in PG18)

Hi,

Partition pruning is happening: pruned nodes are marked as “never executed”.
It is just that pruning is performed not by the planner but by the executor in this case.

—

Michał

> On 25 Sep 2025, at 21:49, Lauro Ojeda <lauro(dot)ojeda(at)gmail(dot)com> wrote:
>
> 
> 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