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 <[email protected]> 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
