A partitioned PostgreSQL table can represent many physical child tables behind one logical relation. A query against the parent does not necessarily scan every child. When a predicate conflicts with a partition’s bounds, PostgreSQL can remove that partition from the plan or execution path.
This behavior is partition pruning. It depends on the partition key and partition bounds rather than an index on the key. The distinction matters because pruning decides which relations can be ignored before access methods inside the remaining relations become relevant.
Partition bounds provide exclusion facts
Consider a table partitioned by month:
CREATE TABLE events (
event_id bigint NOT NULL,
recorded_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (recorded_at);
CREATE TABLE events_2026_08 PARTITION OF events
FOR VALUES FROM ('2026-08-01 00:00:00+00')
TO ('2026-09-01 00:00:00+00');
CREATE TABLE events_2026_09 PARTITION OF events
FOR VALUES FROM ('2026-09-01 00:00:00+00')
TO ('2026-10-01 00:00:00+00');The range bounds are inclusive at the lower edge and exclusive at the upper edge. A query restricted to September cannot match rows in events_2026_08:
SELECT event_id, recorded_at
FROM events
WHERE recorded_at >= TIMESTAMPTZ '2026-09-10 00:00:00+00'
AND recorded_at < TIMESTAMPTZ '2026-09-11 00:00:00+00';PostgreSQL can compare the predicate with each partition’s bounds. The August partition is incompatible with the requested interval, so scanning it cannot produce a qualifying row.
No index is required for that exclusion. An index can still affect access inside events_2026_09, but it is separate from the decision to omit events_2026_08.
Pruning can happen at more than one phase
Some predicate values are available while PostgreSQL constructs the plan. In those cases, partitions can be removed during planning. The resulting plan contains only the child relations that remain possible candidates.
Other values become available later. Prepared statements, parameterized nested-loop paths, and values obtained from subqueries can leave useful information unavailable at initial planning time. PostgreSQL can perform additional partition pruning during query execution when those values become known.
Execution-time pruning can occur during plan initialization and again when relevant execution parameters change. A partition excluded at one of these phases avoids tuple scanning for that execution state even though it may have been represented in the original plan structure.
EXPLAIN and EXPLAIN ANALYZE expose clues for these cases. Subplans Removed can show partitions discarded during initialization. For pruning that occurs repeatedly during execution, child nodes can have different loop counts, and a child that is always excluded can appear as never executed.
Predicate shape affects what PostgreSQL can prove
Pruning requires PostgreSQL to establish that a partition cannot satisfy the query condition. Predicates that align directly with the partition key make that proof straightforward.
A range-partitioned timestamp table works naturally with bounded timestamp comparisons. Equality conditions fit list partitioning in a similar manner. Conditions that obscure the partition key behind expressions can prevent the planner from deriving a useful relation to the partition bounds.
For example, a table partitioned directly on recorded_at exposes different information to the planner in these two conditions:
WHERE recorded_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'WHERE date_trunc('month', recorded_at) = TIMESTAMPTZ '2026-09-01 00:00:00+00'The first condition directly constrains the partition key. The second applies a function to that key. Even when an application developer can infer the intended month, PostgreSQL needs a form it can match against partition bounds to prune reliably.
Partition-key design therefore has a direct connection to common query predicates. A partitioning scheme that groups data neatly for maintenance can still offer weak query pruning if application conditions rarely constrain the chosen key in a compatible form.
Index selection begins after partitions remain
Partition pruning and index scanning solve different parts of query execution. Pruning removes whole relations. Indexes locate rows or heap pages within a relation that remains in the candidate set.
A monthly partition may contain millions of rows. Pruning eleven other months from a yearly query scope still leaves PostgreSQL with the task of accessing rows inside the selected month. A B-tree on recorded_at may be useful for a narrow interval, while a sequential scan may be cheaper when the condition selects most of that partition.
This separation also means an index on the partition key is not a prerequisite for pruning. PostgreSQL uses the declarative partition metadata for relation exclusion. Indexes should be chosen according to access patterns within the partitions rather than added solely to activate pruning.
Partition count shifts work toward planning
Partitioning replaces one physical table with multiple relations that PostgreSQL must manage. Pruning can sharply reduce execution work when predicates isolate a small subset, but the planner still has partition metadata and candidate paths to consider.
Very fine partition granularity can therefore exchange scan work for planning and management overhead. More partitions also mean more child indexes, statistics, schema objects, and maintenance operations. The useful partition interval follows data retention and query shapes rather than a universal row-count threshold.
A time-series table queried mostly by day does not automatically need daily partitions. Monthly partitions combined with suitable indexes may provide a better boundary if retention operations occur monthly and most predicates already isolate a small date range. The partition unit should reflect the physical operations that benefit from whole-table boundaries as well as the predicates that permit pruning.
Pruning also shapes partition maintenance choices
Declarative partitioning has operational effects beyond query scans. Old data can be detached or dropped as a whole partition instead of deleting its rows individually. New ranges can be added as separate relations. These operations make the partition boundary part of data lifecycle design.
That boundary should still remain compatible with query access. A retention policy based on months pairs naturally with monthly range partitions when queries also constrain event time. If retention groups data by one attribute while queries filter primarily on another, partition maintenance can remain convenient without producing much pruning during reads.
Partition pruning is most useful when the partition definition encodes facts that ordinary query predicates can exploit. The mechanism does not make every partitioned query faster by itself; it removes work only when PostgreSQL can prove that some child relations cannot contribute rows.