A partitioned PostgreSQL table can expose one logical relation while storing rows across many physical partitions. A query that constrains the partition key does not necessarily need to inspect each child relation. Partition pruning uses the declared partition bounds to remove partitions that cannot contain matching rows.

Pruning is separate from index selection. It determines which partitions remain relevant; the planner can then choose a sequential scan, index scan, bitmap scan, or another access path inside each surviving partition.

Partition bounds drive the decision

Consider a range-partitioned event table:

CREATE TABLE events (
    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');

A predicate confined to September cannot match rows in the August partition:

SELECT id, occurred_on
FROM events
WHERE occurred_on >= DATE '2026-09-10'
  AND occurred_on < DATE '2026-09-20';

PostgreSQL can compare those conditions with the partition bounds and exclude events_2026_08. The decision does not depend on an index on occurred_on. Indexes affect access inside a selected partition, not the bound-based elimination itself.

This distinction matters for large partition sets. An index on every partition does not compensate for predicates that prevent useful pruning; PostgreSQL may still have to consider many child scans.

Pruning can happen before execution starts

When predicate values are available during planning, PostgreSQL can omit incompatible partitions from the plan. Constants in a simple range condition are the direct case.

Some values are unavailable at plan creation but become known when execution initializes. PostgreSQL can perform another pruning pass at that point. EXPLAIN can report partitions removed during initialization through the Subplans Removed property.

This allows a reusable plan to retain partition-aware behavior even when a relevant value is supplied later than a literal constant would be.

The plan text therefore needs context. A plan that initially contains several partition subplans does not imply that every one will execute for each invocation.

Execution-time parameters can trigger further pruning

Partition pruning can also occur during active execution when a parameter used for pruning changes. A parameterized nested-loop join is a representative case: values produced by the outer side can determine which inner partition is relevant for each iteration.

The set of active partitions can consequently change while the same plan is running. PostgreSQL re-evaluates pruning when the relevant execution parameter changes rather than scanning every candidate partition for every outer value.

EXPLAIN ANALYZE exposes evidence of this behavior through subplan loop counts. A partition that is excluded for every applicable parameter value can appear as (never executed). Other partitions can show different loop counts because they were active for only part of the execution.

Execution-time pruning is useful precisely because not all useful values exist at planning time. It extends partition elimination into cases where static plan construction has insufficient information.

Predicate shape still controls what can be excluded

Partitioning alone does not guarantee a narrow scan. PostgreSQL must be able to relate query conditions to the partition key and its bounds.

Direct equality conditions for list partitioning and straightforward ranges for range partitioning are easy to match against partition definitions. Expressions that obscure the partition key can prevent the optimizer from proving that a partition is irrelevant.

For example, partitioning by occurred_on does not mean every expression derived from that column can automatically participate in pruning. Schema design and query predicates need to expose a relationship the partition machinery can reason about.

This is one reason a partition key should reflect common data-access boundaries rather than merely divide a table into similarly sized pieces.

Pruning and constraint exclusion are different mechanisms

Constraint exclusion can also remove child relations, but it reasons from CHECK constraints. Declarative partition pruning uses partition bounds and has specialized machinery for that purpose.

A significant difference is timing. Constraint exclusion operates during planning, whereas partition pruning can also act during query execution. Additional CHECK constraints can still provide useful facts in some partitioned designs, but they do not replace the native pruning mechanism.

The two features can therefore appear similar in a final plan while reaching that result from different metadata and at different stages.

Too many surviving partitions still have a cost

Pruning reduces work only when predicates eliminate a meaningful part of the partition hierarchy. If a query legitimately spans most partitions, PostgreSQL still has to plan and execute access to those relations.

Large partition counts also carry planning and memory overhead. Each surviving partition introduces metadata and potential paths that must be considered. A hierarchy with thousands of partitions can work well when common queries discard nearly all of them, but the same hierarchy is less attractive when broad queries routinely retain most partitions.

Partition granularity therefore affects more than storage organization. Smaller partitions create finer elimination boundaries, but also increase the number of relations the database may need to manage.

Pruning is visible in plan behavior

EXPLAIN and EXPLAIN ANALYZE provide the clearest view of whether a partitioning scheme is eliminating work. The useful signals include which child relations appear, Subplans Removed for initialization-time pruning, and execution loop counts for partition subplans.

The enable_partition_pruning setting can disable the optimization, so unexpected plans should also account for that configuration. Under normal settings, native declarative partitioning is designed to use partition bounds as an optimization input.

Partition pruning is most effective when the partition key, partition boundaries, and common predicates describe the same data boundaries. In that arrangement, PostgreSQL can discard irrelevant storage before choosing how to scan what remains, keeping partitioning focused on elimination rather than merely splitting one table into many.

PostgreSQL Database Partitioning Query Planner