Partition Pruning Analysis: Reading Partitioned Plans #

Partitioning only pays off when the planner or the executor can eliminate partitions. A query that touches one month of a five-year table should read one partition; if it reads sixty, partitioning has added cost and delivered nothing. The plan tells you which happened, but only if you know which fields to look at — and the fields differ depending on whether elimination happened at planning time or at run time.

This page shows how to prove pruning occurred, how to diagnose the predicate shapes that silently defeat it, and how partitionwise joins and aggregates change the plan tree. It extends the diagnostic reading covered across the reading and interpreting query plans section.

How the Planner Eliminates Partitions #

A partitioned table is a parent relation with child tables carrying bound constraints — FOR VALUES FROM ('2026-01-01') TO ('2026-02-01') and so on. Pruning is the process of proving that a partition’s bounds cannot satisfy the query’s predicates, and it happens in two distinct places.

Plan-time pruning runs while the plan is built, using values that are constant at that moment. Those partitions never appear in the plan at all — the Append node simply has fewer children. This is the cheapest form, because pruned partitions cost nothing to plan, lock, or open.

Runtime pruning handles values that are unknown until execution: bound parameters in a prepared statement, and values supplied by the outer side of a nested loop. The plan keeps all candidate subplans, and the executor skips the ones that cannot match. EXPLAIN ANALYZE reports how many were skipped as Subplans Removed.

Plan-time pruning versus runtime pruning A horizontal timeline with two stages. In the planning stage, constant predicates remove partitions so they never appear in the plan. In the execution stage, parameter values remove remaining subplans, which EXPLAIN ANALYZE reports as Subplans Removed. PLANNING EXECUTION constants known now parameters bound now 12 monthly partitions after plan-time pruning: 3 children in the Append after runtime pruning: 1 scanned, Subplans Removed: 2

Both mechanisms need the same thing: a predicate the planner can compare against a partition bound. That means the bare partition key column on one side of an operator belonging to the partitioning operator class — =, <, >=, BETWEEN, IN, and IS NULL all qualify.

Reading a Partitioned Plan #

Here is the same query planned two ways. First, with a literal date range, where pruning happens while planning:

EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2026-06-01' AND occurred_at < '2026-07-01';

Aggregate  (cost=18422.10..18422.11 rows=1 width=8)
  ->  Seq Scan on events_2026_06 events  (cost=0.00..17920.00 rows=200840 width=0)
        Filter: (occurred_at >= '2026-06-01' AND occurred_at < '2026-07-01')

Note what is absent: there is no Append node at all. Pruning left a single partition, so the planner collapsed the tree. Eleven partitions were eliminated before costing began — the cheapest possible outcome.

Now the same logic through a prepared statement, where the value is a parameter:

EXPLAIN (ANALYZE) EXECUTE monthly_count('2026-06-01', '2026-07-01');

Aggregate  (actual time=44.2..44.2 rows=1 loops=1)
  ->  Append  (actual time=0.03..31.8 rows=200840 loops=1)
        Subplans Removed: 11                      -- runtime pruning did the work
        ->  Seq Scan on events_2026_06 events_1  (actual rows=200840 loops=1)
              Filter: (occurred_at >= $1 AND occurred_at < $2)

Subplans Removed: 11 is the proof. All twelve children were planned — planning cost is proportional to the full partition count here — but only one was executed. If that line is missing and you see twelve Seq Scan children each with actual rows, nothing was pruned and every partition was read.

One more shape matters. When the pruning value comes from the other side of a join, the Append reports pruning on each loop:

Nested Loop  (actual rows=418 loops=1)
  ->  Seq Scan on accounts a  (actual rows=6 loops=1)
  ->  Append  (actual rows=70 loops=6)            -- re-pruned per outer row
        Subplans Removed: 11

That is runtime pruning inside a nested loop, and it is often the fastest shape available for a small outer relation over a wide partitioned fact table.

What Silently Defeats Pruning #

Pruning is a syntactic match against partition bounds, so anything that hides the key column from the comparison defeats it. In rough order of how often it happens in production:

Predicate shapes that prune and shapes that do not Two columns. The left column lists predicates that allow pruning: a bare column range, an equality on the key, an IN list, and a parameter comparison. The right column lists predicates that defeat pruning: a function wrapping the key, a cast on the column, an operator outside the partitioning class, and a filter on a non-key column. Prunes Does not prune occurred_at >= '2026-06-01' occurred_at = $1 occurred_at IN ($1, $2) occurred_at BETWEEN a AND b tenant_id = 42 (hash key, equality) bare column, operator in the class date_trunc('month', occurred_at) = … occurred_at::date = '2026-06-15' tenant_name LIKE 'ac%' (hash key) account_id = 91 (non-key column) COALESCE(occurred_at, now()) > … key hidden behind an expression

Partitionwise Joins and Aggregates #

When two partitioned tables share an identical partition scheme, PostgreSQL can join them partition by partition instead of joining the combined results. That turns one enormous hash join into many small ones, each of which is far more likely to fit in memory and can run in parallel.

Both behaviors are off by default because they increase planning time:

SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;

The plan shape changes visibly — the Append moves above the join rather than below it:

-- OFF: one join over everything, hash spills
Hash Join  (actual rows=4120334 loops=1)
  ->  Append  (actual rows=48000000 loops=1)
  ->  Hash  (actual rows=12000000 loops=1)  Batches: 32  Memory Usage: 65536kB

-- ON: twelve independent per-partition joins, none of them spill
Append  (actual rows=4120334 loops=1)
  ->  Hash Join  (actual rows=343361 loops=1)   Batches: 1  Memory Usage: 5904kB
        ->  Seq Scan on events_2026_01 …
        ->  Hash  ->  Seq Scan on sessions_2026_01 …
  ->  Hash Join  (actual rows=341902 loops=1)   Batches: 1
  …

The requirements are strict: identical partition strategy, identical bounds, identical key types, and a join condition on the full partition key. A partition added to one table but not the other silently disables it for the whole query.

Step-by-Step Pruning Workflow #

  1. Count the Append children in a plain EXPLAIN. No Append and a single relation means everything but one partition was pruned at plan time.

  2. Run EXPLAIN (ANALYZE) and look for Subplans Removed. Present means runtime pruning worked; absent with many executed children means no pruning at all.

  3. Verify the predicate touches the bare key. Compare the Filter/Index Cond text against the partition key definition:

    SELECT pg_get_partkeydef('events'::regclass);

  4. Rewrite expression predicates into ranges.

    -- before: no pruning
WHERE date_trunc('month', occurred_at) = DATE '2026-06-01'
-- after: prunes
WHERE occurred_at >= DATE '2026-06-01' AND occurred_at < DATE '2026-07-01'

  5. Watch planning time as the partition count grows. With hundreds of partitions, runtime pruning still plans them all. Compare Planning Time against Execution Time; when planning dominates, prefer literals over parameters for reporting queries, or reduce partition granularity.

  6. Enable partitionwise operations for large partition-aligned joins, then confirm the Append moved above the join and that per-partition hashes report Batches: 1.

Common Pitfalls #

Believing an Append with all children present still prunes. If every partition appears and Subplans Removed is absent, all of them are scanned. Diagnostic signal: each child shows non-zero actual rows and loops=1. Fix: correct the predicate shape.

Testing pruning with EXPLAIN on a prepared statement. Runtime pruning cannot be observed without executing. Diagnostic signal: a plan full of children and no Subplans Removed line. Fix: use EXPLAIN ANALYZE EXECUTE ….

Casting the column instead of the constant. occurred_at::date = '2026-06-15' defeats pruning; occurred_at >= '2026-06-15' AND occurred_at < '2026-06-16' does not. Diagnostic signal: a Filter containing a cast on the key. Fix: move the cast to the literal side.

Over-partitioning. Thousands of partitions make planning, locking, and ANALYZE expensive even when pruning works. Diagnostic signal: Planning Time larger than Execution Time. Fix: use coarser ranges, or sub-partition only the hot ranges.

Missing indexes on new partitions. Indexes created on the parent propagate, but a partition attached with ALTER TABLE … ATTACH PARTITION may arrive without them. Diagnostic signal: one partition doing a Seq Scan while its siblings do Index Scan. Fix: create matching indexes before attaching, then attach.

Stale statistics on the parent. The parent’s own statistics feed estimates for queries that span partitions. Diagnostic signal: wildly wrong row estimates on the Append. Fix: ANALYZE the parent table explicitly — see autovacuum and plan stability for why partitioned parents are often skipped.

Choosing the partition key from the data model rather than the queries. Partitioning by a natural identifier feels tidy and prunes nothing if every query filters on a timestamp. The key must match the predicate your workload actually uses, because a partition scheme that no query aligns with is a pure cost: more planning, more locking, more indexes, more ANALYZE, and no elimination. Diagnostic signal: every plan showing all partitions scanned with a filter on a non-key column. Fix: re-partition on the column your WHERE clauses use, or drop the partitioning entirely.

Ignoring the lock footprint of a partitioned scan. Every partition the plan retains is locked at execution, so a query against a table with 800 partitions takes 800 locks even if runtime pruning skips most of them. That consumes lock table entries and lengthens the lock-acquisition phase for every backend. Diagnostic signal: max_locks_per_transaction errors, or lock-acquisition time visible in Planning Time. Fix: prune at plan time where possible, and keep the partition count proportionate.

Attaching a partition without its indexes and constraints. ALTER TABLE … ATTACH PARTITION verifies the constraint by scanning the child unless a matching CHECK already proves it, and a partition attached without matching indexes silently becomes the slow child in every plan. Diagnostic signal: one Seq Scan among eleven Index Scan children. Fix: create indexes and an equivalent CHECK constraint on the child before attaching, so the attach is a metadata operation and the plan stays uniform.

Treating detach as free. DETACH PARTITION takes a strong lock unless the concurrent form is used, and the concurrent form has its own two-transaction protocol that must be completed. Diagnostic signal: a retention job blocking writers for the duration of the detach. Fix: use DETACH PARTITION CONCURRENTLY and verify the operation finished rather than assuming it did.

Four conditions for a predicate to prune The partition key must appear bare, the operator must belong to the partitioning operator class, the value must be comparable to the bounds, and the predicate must be conjunctive rather than an OR branch. 1 the partition key column appears bare — no function, no cast 2 the operator belongs to the partitioning operator class 3 the value is known at plan time, or bound before execution 4 the predicate is conjunctive — an OR branch prunes nothing

Partitioning a table that no query filters on the key. Partitioning is a data-management feature first: it makes retention nearly free, lets maintenance run per partition, and bounds the size of any single index. Query speed is a consequence of pruning, and pruning only happens when predicates align with the key. Diagnostic signal: every plan scanning every partition. Fix: either re-partition on the column the workload filters by, or accept that the benefit here is operational rather than analytical.

Adding partitions faster than you retire them. A retention policy that creates a partition per day and never detaches the old ones grows the planning cost of every query against the table, whether or not those partitions are ever scanned. Diagnostic signal: planning time creeping upward month over month with unchanged queries. Fix: make detaching part of the same job that creates, and archive detached partitions rather than leaving them attached.

Frequently Asked Questions #

How do I prove partition pruning happened? Plan-time pruning removes partitions before the plan exists, so they never appear as Append children — a single-partition query may not even have an Append. Runtime pruning leaves them in the plan and reports Subplans Removed: N under EXPLAIN ANALYZE. If neither signal is present, every partition was scanned.

Why does pruning fail when I use a function on the partition key? Pruning matches predicates against partition bounds, which requires the bare key column beside an operator from the partitioning operator class. A function or cast around the column hides it, so no bound comparison is possible. Rewrite the predicate as a half-open range on the raw column.

What is the difference between plan-time and runtime pruning? Plan-time pruning uses values that are constant during planning and costs nothing at execution. Runtime pruning handles parameters and values from the outer side of a join, keeps all subplans in the plan, and skips them during execution — so planning cost still scales with the total partition count.

Does partitioning always make queries faster? No. It helps queries aligned with the partition key and makes retention operations nearly free, but unaligned queries must touch every partition and pay extra planning and locking overhead. Partitioning is a data-management strategy first and a performance strategy second.