public inbox for [email protected]
help / color / mirror / Atom feedPartition pruning is not happening (even in PG18)
7+ messages / 4 participants
[nested] [flat]
* Partition pruning is not happening (even in PG18)
@ 2025-09-25 19:34 Lauro Ojeda <[email protected]>
2025-09-25 20:10 ` Re: Partition pruning is not happening (even in PG18) Michał Kłeczek <[email protected]>
2025-09-25 20:17 ` Re: Partition pruning is not happening (even in PG18) David Rowley <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Lauro Ojeda @ 2025-09-25 19:34 UTC (permalink / raw)
To: pgsql-performance
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*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
@ 2025-09-25 20:10 ` Michał Kłeczek <[email protected]>
2025-09-25 20:13 ` Re: Partition pruning is not happening (even in PG18) Michał Kłeczek <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Michał Kłeczek @ 2025-09-25 20:10 UTC (permalink / raw)
To: Lauro Ojeda <[email protected]>; +Cc: 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 <[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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
2025-09-25 20:10 ` Re: Partition pruning is not happening (even in PG18) Michał Kłeczek <[email protected]>
@ 2025-09-25 20:13 ` Michał Kłeczek <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Michał Kłeczek @ 2025-09-25 20:13 UTC (permalink / raw)
To: Lauro Ojeda <[email protected]>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
@ 2025-09-25 20:17 ` David Rowley <[email protected]>
2025-09-29 13:49 ` Re: Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: David Rowley @ 2025-09-25 20:17 UTC (permalink / raw)
To: Lauro Ojeda <[email protected]>; +Cc: pgsql-performance
On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda <[email protected]> wrote:
> 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?
> Partition key: RANGE (transaction_date)
> postgres=> explain analyze
> select aid, abalance
> from accounts
> where transaction_date in (select dt_col from t2);
The only partition pruning that exists in PostgreSQL that can prune
for that query is for parameterised Nested Loop joins. For Hash Join,
it's been talked about, but this requires running the partition
pruning code for every values that goes into the Hash Table and only
scanning the unioned set of those partitions during the hash probe
phase. The trouble with that is that it's very hard to know in advance
if it'll be worth the extra effort. Putting a tuple into a hash table
is quite cheap. Running the pruning code for a range partitioned table
is likely to be a few times more costly than the hash insert
(depending on how many partitions there are), so if the end result is
that nothing was pruned, then that's quite a bit of extra effort for
no gain.
What we maybe could do better is reduce the cost of the Append scan
when there's a run-time pruning object attached. This is a little
tricky as we currently only build that object when creating the final
plan. To include that in the costs we'd need to move that to the Path
generation phase so that we didn't accidentally reject Paths which
could be cheaper than we first think.
> Also, how could I contribute to get this partition pruning to work?
The pgsql-hackers mailing list is where all the discussions about that
happen. There is plenty of past discussions on these topics. One such
(fairly) recent discussion is in [1]. There are plenty more, including
some ideas from Robert Haas about how we might cost run-time partition
pruning. That was likely around 2017-2018 range, so you might need to
dig deep to find that.
David
[1] https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
2025-09-25 20:17 ` Re: Partition pruning is not happening (even in PG18) David Rowley <[email protected]>
@ 2025-09-29 13:49 ` Lauro Ojeda <[email protected]>
2025-09-29 20:05 ` Re: Partition pruning is not happening (even in PG18) Chetan <[email protected]>
2025-09-29 20:08 ` Re: Partition pruning is not happening (even in PG18) David Rowley <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Lauro Ojeda @ 2025-09-29 13:49 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: pgsql-performance
Hi David,
Thank you for your nice reply.
I have the impression there is something heavily penalizing the usage of
partition pruning. While trying to go a bit further, I realized that
partition pruning is not occurring because the planner gives the cost of
seq-scanning all partitions to just over 2M units, but the query takes over
3 minutes. When forcing the usage of PK index via hinting (which down the
line forces the partition pruning to happen), the cost sky-rocket to 53M
units (however scanning 4 partitions out of 12). Even though with much
higher cost, the query launches less workers and completes in a third (or
less) of the time of all partition scans.
My observation is that the cost of seq-scanning each partition is estimated
in ~124k units, but index-scanning each partition is 440k (say, 3x higher).
Therefore, scanning 4 partitions, the total cost should be ~1.7M units,
which is below the 2M units from the cost of all partition scanning, thus
the planner should prefer the indexed path instead. However when analyzing
the hinted query (doing index scan and thus partition pruning) we can note
that the cost estimated to each partition scan was added to the final cost,
even though 8 out of 12 partitions were not scanned (never executed).
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=2.299..111346.649 rows=1664742 loops=1)
-> HashAggregate (cost=1.12..1.23 rows=10 width=4) (actual
time=1.060..40.562 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=1.048..1.050 rows=6 loops=1)
-> Append (cost=0.43..5302777.61 rows=3320703 width=12) (actual
time=1.554..18514.181 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=2.214..17627.113
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=2.185..16280.020
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=0.751..25992.220
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=1.376..17001.777 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: 0.879 ms
Execution Time: 111432.755 ms
(32 rows)
By looking into it, I have the impression there is a bug in the costing sum
in that situation, where the cost of the "never executed" partitions should
be deducted from the final cost estimation, which would make pruning to be
the preferred option in this case.
Are my assumptions correct?
Is there anything I could do to influence the planner to dismiss the cost
of "never executed" scans?
Kind regards,
Lauro Ojeda
On Thu, 25 Sept 2025 at 22:18, David Rowley <[email protected]> wrote:
> On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda <[email protected]> wrote:
> > 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?
>
> > Partition key: RANGE (transaction_date)
>
> > postgres=> explain analyze
> > select aid, abalance
> > from accounts
> > where transaction_date in (select dt_col from t2);
>
> The only partition pruning that exists in PostgreSQL that can prune
> for that query is for parameterised Nested Loop joins. For Hash Join,
> it's been talked about, but this requires running the partition
> pruning code for every values that goes into the Hash Table and only
> scanning the unioned set of those partitions during the hash probe
> phase. The trouble with that is that it's very hard to know in advance
> if it'll be worth the extra effort. Putting a tuple into a hash table
> is quite cheap. Running the pruning code for a range partitioned table
> is likely to be a few times more costly than the hash insert
> (depending on how many partitions there are), so if the end result is
> that nothing was pruned, then that's quite a bit of extra effort for
> no gain.
>
> What we maybe could do better is reduce the cost of the Append scan
> when there's a run-time pruning object attached. This is a little
> tricky as we currently only build that object when creating the final
> plan. To include that in the costs we'd need to move that to the Path
> generation phase so that we didn't accidentally reject Paths which
> could be cheaper than we first think.
>
> > Also, how could I contribute to get this partition pruning to work?
>
> The pgsql-hackers mailing list is where all the discussions about that
> happen. There is plenty of past discussions on these topics. One such
> (fairly) recent discussion is in [1]. There are plenty more, including
> some ideas from Robert Haas about how we might cost run-time partition
> pruning. That was likely around 2017-2018 range, so you might need to
> dig deep to find that.
>
> David
>
> [1]
> https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA...
>
--
*Lauro Ojeda*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
2025-09-25 20:17 ` Re: Partition pruning is not happening (even in PG18) David Rowley <[email protected]>
2025-09-29 13:49 ` Re: Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
@ 2025-09-29 20:05 ` Chetan <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Chetan @ 2025-09-29 20:05 UTC (permalink / raw)
To: Lauro Ojeda <[email protected]>; +Cc: David Rowley <[email protected]>; pgsql-performance
On Mon, 29 Sept 2025 at 17:55, Lauro Ojeda <[email protected]> wrote:
> Hi David,
> Thank you for your nice reply.
>
> I have the impression there is something heavily penalizing the usage of
> partition pruning. While trying to go a bit further, I realized that
> partition pruning is not occurring because the planner gives the cost of
> seq-scanning all partitions to just over 2M units, but the query takes over
> 3 minutes. When forcing the usage of PK index via hinting (which down the
> line forces the partition pruning to happen), the cost sky-rocket to 53M
> units (however scanning 4 partitions out of 12). Even though with much
> higher cost, the query launches less workers and completes in a third (or
> less) of the time of all partition scans.
>
> My observation is that the cost of seq-scanning each partition is
> estimated in ~124k units, but index-scanning each partition is 440k (say,
> 3x higher). Therefore, scanning 4 partitions, the total cost should be
> ~1.7M units, which is below the 2M units from the cost of all partition
> scanning, thus the planner should prefer the indexed path instead. However
> when analyzing the hinted query (doing index scan and thus partition
> pruning) we can note that the cost estimated to each partition scan was
> added to the final cost, even though 8 out of 12 partitions were not
> scanned (never executed).
>
> 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=2.299..111346.649 rows=1664742 loops=1)
> -> HashAggregate (cost=1.12..1.23 rows=10 width=4) (actual
> time=1.060..40.562 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=1.048..1.050 rows=6 loops=1)
> -> Append (cost=0.43..5302777.61 rows=3320703 width=12) (actual
> time=1.554..18514.181 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=2.214..17627.113 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=2.185..16280.020 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=0.751..25992.220 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=1.376..17001.777 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: 0.879 ms
> Execution Time: 111432.755 ms
> (32 rows)
>
> By looking into it, I have the impression there is a bug in the costing
> sum in that situation, where the cost of the "never executed" partitions
> should be deducted from the final cost estimation, which would make pruning
> to be the preferred option in this case.
>
>
The plan generation stage is where the costing sum is figured out whereas
the "never executed" marking/detection is in the execution stage.
So this doesnt look to be a costing bug for the above scenario where the
planner was unable to prune partitions early.
> Are my assumptions correct?
> Is there anything I could do to influence the planner to dismiss the cost
> of "never executed" scans?
>
> Kind regards,
> Lauro Ojeda
>
>
> On Thu, 25 Sept 2025 at 22:18, David Rowley <[email protected]> wrote:
>
>> On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda <[email protected]> wrote:
>> > 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?
>>
>> > Partition key: RANGE (transaction_date)
>>
>> > postgres=> explain analyze
>> > select aid, abalance
>> > from accounts
>> > where transaction_date in (select dt_col from t2);
>>
>> The only partition pruning that exists in PostgreSQL that can prune
>> for that query is for parameterised Nested Loop joins. For Hash Join,
>> it's been talked about, but this requires running the partition
>> pruning code for every values that goes into the Hash Table and only
>> scanning the unioned set of those partitions during the hash probe
>> phase. The trouble with that is that it's very hard to know in advance
>> if it'll be worth the extra effort. Putting a tuple into a hash table
>> is quite cheap. Running the pruning code for a range partitioned table
>> is likely to be a few times more costly than the hash insert
>> (depending on how many partitions there are), so if the end result is
>> that nothing was pruned, then that's quite a bit of extra effort for
>> no gain.
>>
>> What we maybe could do better is reduce the cost of the Append scan
>> when there's a run-time pruning object attached. This is a little
>> tricky as we currently only build that object when creating the final
>> plan. To include that in the costs we'd need to move that to the Path
>> generation phase so that we didn't accidentally reject Paths which
>> could be cheaper than we first think.
>>
>> > Also, how could I contribute to get this partition pruning to work?
>>
>> The pgsql-hackers mailing list is where all the discussions about that
>> happen. There is plenty of past discussions on these topics. One such
>> (fairly) recent discussion is in [1]. There are plenty more, including
>> some ideas from Robert Haas about how we might cost run-time partition
>> pruning. That was likely around 2017-2018 range, so you might need to
>> dig deep to find that.
>>
>> David
>>
>> [1]
>> https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA...
>>
>
>
> --
> *Lauro Ojeda*
>
--
--
Regards,
Chetan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partition pruning is not happening (even in PG18)
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
2025-09-25 20:17 ` Re: Partition pruning is not happening (even in PG18) David Rowley <[email protected]>
2025-09-29 13:49 ` Re: Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
@ 2025-09-29 20:08 ` David Rowley <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David Rowley @ 2025-09-29 20:08 UTC (permalink / raw)
To: Lauro Ojeda <[email protected]>; +Cc: pgsql-performance
On Tue, 30 Sept 2025 at 02:49, Lauro Ojeda <[email protected]> wrote:
> By looking into it, I have the impression there is a bug in the costing sum in that situation, where the cost of the "never executed" partitions should be deducted from the final cost estimation, which would make pruning to be the preferred option in this case.
>
> Are my assumptions correct?
The "never executed" part is not determined at planning time. It's
only something the executor gets to figure out, and by that time, it's
no good adjusting the plan's costs since the planner has already
decided on what it thinks the best plan is and it's too late to change
that.
> Is there anything I could do to influence the planner to dismiss the cost of "never executed" scans?
Not in the general sense, but for nodes that are "never executed" due
to run-time partition pruning. It would be possible to make some
assumptions during planning. The planner would need to do some extra
work during planning to figure out if an AppendPath or MergeAppend
path has run-time prunable parameters. If those parameters plus any
non-parameterized quals result in pruning being possible at run-time,
then *maybe* there's something we can do better. For equi joins, you
could assume that only 1 of the Append children will be scanned. The
planner will have no idea which one, but it could do something like
total_append_cost / number_of_append_children. For other join types,
it's much less clear how that would work. There have been previous
proposals to do something with DEFAULT_INEQ_SEL or maybe
DEFAULT_RANGE_INEQ_SEL. I've forgotten the exact details as it was
about 8 years ago.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-09-29 20:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-25 19:34 Partition pruning is not happening (even in PG18) Lauro Ojeda <[email protected]>
2025-09-25 20:10 ` Michał Kłeczek <[email protected]>
2025-09-25 20:13 ` Michał Kłeczek <[email protected]>
2025-09-25 20:17 ` David Rowley <[email protected]>
2025-09-29 13:49 ` Lauro Ojeda <[email protected]>
2025-09-29 20:05 ` Chetan <[email protected]>
2025-09-29 20:08 ` David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox