A PostgreSQL plan can contain partition subplans that never execute. When a partition key predicate depends on a value unavailable during planning, the executor can apply partition pruning after that value becomes available and skip partitions whose bounds cannot match it.
This behavior matters for prepared statements, parameterized nested-loop joins, and predicates fed by subqueries. In these cases, the set of relevant partitions can become narrower after the planner has already produced the plan.
Partition bounds drive pruning
Declarative partition pruning uses partition bounds rather than indexes. A range-partitioned table can therefore eliminate partitions even when the partition key has no index.
Consider a table partitioned by event date:
CREATE TABLE events (
event_id bigint,
occurred_on date NOT NULL,
payload jsonb
) PARTITION BY RANGE (occurred_on);
CREATE TABLE events_2026_08
PARTITION OF events
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09
PARTITION OF events
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10
PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');For a predicate containing a literal date, the planner can compare that value with the partition bounds immediately:
SELECT *
FROM events
WHERE occurred_on = DATE '2026-09-15';The August and October partitions cannot contain matching rows, so they can be removed while the plan is built.
An index inside the September partition may still affect access within that partition, but it does not establish whether August or October can be excluded. Those decisions come from the partition key and its bounds.
Parameters can defer the decision
A prepared statement separates the query text from a parameter value:
PREPARE event_lookup(date) AS
SELECT *
FROM events
WHERE occurred_on = $1;The eventual value of $1 may not be usable when a reusable plan is formed. At execution, however, the parameter has a concrete value. PostgreSQL can compare that value with partition bounds during plan initialization and remove irrelevant subplans before their scans start.
This distinction prevents a generic plan from implying that every listed partition must perform work. The plan structure can retain alternatives while the executor activates only the subset compatible with the current parameter.
EXPLAIN output can expose initialization-time removal through the Subplans Removed property. A partition removed at this phase does not appear as an executed scan.
Pruning can also occur during execution
Some values arrive later than plan initialization. A parameterized nested-loop join is a common case: each outer row can supply a different value to the inner side.
Suppose a small request table carries dates used to probe the partitioned event table:
SELECT r.request_id, e.event_id
FROM event_requests AS r
JOIN events AS e
ON e.occurred_on = r.request_date;If the chosen plan parameterizes the inner partitioned scan from r.request_date, that date can change for successive outer rows. PostgreSQL can reevaluate partition pruning when the relevant execution parameter changes.
The active partition set can therefore vary across loops of the same plan node. One outer row might route inner work to the September partition, while another might select October. Partitions excluded on every iteration can appear as (never executed) in EXPLAIN ANALYZE; other partition subplans can show different loop counts.
This is an executor property, not a rewrite of the SQL statement for every row. The plan carries partition-pruning metadata that lets execution select compatible subplans as parameter values change.
Plan-time and execution-time pruning leave different evidence
Plan-time pruning removes partitions before the final plan is handed to the executor. Those partitions are absent from the executable plan.
Initialization-time pruning starts from a plan that contains candidate subplans, then removes some after execution parameters are available. Subplans Removed records this case in relevant EXPLAIN output.
Pruning during active execution is visible through execution statistics rather than a single static removed count. Different loops values across partition subplans indicate that the executor did not invoke every child on every iteration.
These forms can produce similar runtime effects while leaving different plan evidence. A short list of executed partitions does not by itself identify the phase in which exclusion occurred.
Predicate shape sets a boundary
Partition pruning requires predicates that PostgreSQL can relate to the partition key and its bounds. A filter that is semantically associated with the key does not automatically qualify if its expression shape prevents the partition machinery from deriving compatible bounds.
This boundary is separate from ordinary filtering. A partition scan can still apply a filter after reading rows even when that filter could not eliminate the partition itself.
The difference becomes significant with many partitions. Failing to prune means more child plans can remain active, with corresponding planning, initialization, or execution overhead. PostgreSQL documentation also notes that large partition hierarchies are most manageable when typical queries prune most partitions.
Pruning and local access solve separate problems
Partition pruning selects which child relations can participate. Indexes, sequential scans, bitmap scans, and other access methods determine work inside each selected child.
A query can prune to one partition and still perform a sequential scan there. It can also retain several partitions and use an index in each. Treating pruning as an index feature merges two separate optimizer decisions.
The separation is useful operationally. An unexpected scan across many partitions points first toward partition bounds, predicate form, parameters, and enable_partition_pruning. An expensive scan inside a correctly selected partition points toward local access paths, statistics, selectivity, and physical design.
Execution-time pruning extends that separation across time: partition eligibility can remain unresolved at planning and become concrete only when execution supplies the values needed to compare predicates with partition bounds.