Re: Partition pruning is not happening (even in PG18) - Mailing list pgsql-performance



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.ojeda@gmail.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