Parameter · Query Tuning / Planner Method Configuration
enable_partition_pruning
Enables plan-time and execution-time partition pruning. Allows the query planner and executor to compare partition bounds to conditions in the query to determine which partitions must be scanned.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
If a table is split into partitions by date and you ask for one day, PostgreSQL only needs to read the partition for that day. Partition pruning works out which partitions can be skipped. Turning it off makes every query read every partition.
For DBAs and operators
Pruning happens in three places: during planning (constants in WHERE), at executor startup (parameters of prepared statements with a generic plan, stable functions like now()), and during execution (values from the outer side of a nested loop). EXPLAIN shows Subplans Removed: N for startup pruning and (never executed) for run-time pruning.
Measured on 18.6 with four quarterly partitions: WHERE d = '2025-05-05' planned a single Seq Scan on m2, cost 1,787. With enable_partition_pruning = off it scanned all four partitions with a Parallel Append, cost 6,212. Setting constraint_exclusion did not bring pruning back: declarative partitions rely on this switch, not on constraint exclusion.
Pruning only works when the WHERE clause is on the partition key and comparable to the partition bounds. Common pitfalls: wrapping the key in a function (date(ts) = …), or comparing with a value of another type through an operator that is only stable (for example a date key against a timestamptz): then nothing is pruned at plan time and pruning happens at executor startup instead (Subplans Removed: 3 in that test). Generic plans over many partitions are another: even in 18, executing a cached generic plan locks every partition, including those pruned at startup.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
constraint_exclusion enable_partitionwise_join enable_partitionwise_aggregate plan_cache_mode