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

Never mind my message. I misread it and missed the plan with no hints.

Michał

> On 25 Sep 2025, at 22:10, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
> 
> 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