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