Partition Pruning & Query Planning

Partition pruning is the mechanism that makes partitioning worth doing: it is the planner’s decision to exclude child partitions that cannot contain matching rows, turning a table-wide scan into a scan of one child. This topic sits under Database Partitioning Fundamentals & Architecture and covers what pruning actually inspects, why an apparently correct predicate silently disables it, and how planning cost itself becomes the bottleneck once a table carries thousands of children. It is the operational counterpart to partition key selection and design — the key decides what can be pruned, and the query decides what is.

Problem Framing

An events table holds 1.1 billion rows across 36 monthly partitions. A report for one month reads 31 million rows and returns in 40 ms when run by hand in psql. The same report from the application takes 1.9 seconds, returns identical rows, and shows up in pg_stat_statements with a mean execution time that has been climbing since the table was partitioned.

Nothing is broken. The application uses a prepared statement with a bound parameter, and the planner produced a generic plan that touches all 36 partitions. Every partition contributes an index probe, a lock acquisition and a portion of the planner’s work — and the cost grows every month as a new child is added.

This is the characteristic failure mode of partitioned schemas: pruning failures do not produce errors, produce correct results, and degrade linearly with the thing you added partitions to control. They are only visible in the plan.

How Pruning Actually Works

The planner compares each predicate against the declared bounds of each child partition and discards children whose bounds cannot overlap the predicate. Three properties of that sentence explain nearly every pruning failure in production.

It compares against bounds, not data. The planner never looks inside a partition to decide whether to scan it. If the bounds say a child covers ['2026-08-01', '2026-09-01') and the predicate asks for August, the child is scanned even if it is empty.

It needs a directly comparable expression. The predicate must reduce to a comparison between the partition key column and a constant of the same type. Anything wrapped around the column — a cast, a function, a COALESCE — makes the expression opaque, and an opaque expression matches every partition by definition.

It happens twice, at two different times. Plan-time pruning uses constants available while planning. Runtime pruning handles values that only exist during execution: parameters in a generic plan, subquery results, and values arriving from the outer side of a nested loop.

The distinction matters operationally because the two forms are diagnosed differently. Plan-time pruning is visible as the absence of partitions from the plan. Runtime pruning is visible as their presence with a (never executed) annotation. A plan showing 36 Seq Scan nodes with no annotations is neither: it is a genuine full scan.

Verifying Pruning in Production

The only trustworthy check is the plan produced by the exact statement the application sends, with the exact parameter form it uses. Run EXPLAIN through the same connection and driver rather than retyping the query with literals — retyping is what produces the “works in psql” illusion.

-- pruned: one child appears in the plan
EXPLAIN (COSTS OFF)
SELECT tenant_id, count(*)
FROM   events
WHERE  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01'
GROUP  BY tenant_id;

 HashAggregate
   Group Key: events.tenant_id
   ->  Seq Scan on events_2026_08 events
         Filter: ((occurred_at >= '2026-08-01') AND (occurred_at < '2026-09-01'))

For prepared statements, the generic plan is what production runs after the fifth execution, and it is inspectable directly:

PREPARE monthly(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE occurred_at >= $1 AND occurred_at < $2;

-- force the generic plan and inspect it
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE monthly('2026-08-01', '2026-09-01');

 Append (actual time=0.031..38.902 rows=1 loops=1)
   Subplans Removed: 35
   ->  Seq Scan on events_2026_08 (actual time=0.030..38.874 rows=31428104 loops=1)

Subplans Removed: 35 is the signature of successful runtime pruning. Its absence — 36 child nodes with real row counts — is a genuine full scan and needs the predicate fixed.

What Breaks Pruning

The failures cluster into five shapes, and each has a mechanical rewrite.

The now() row is worth dwelling on because it looks like a bug and is not. Relative time predicates prune at runtime, which is usually fine. They only become a problem inside UPDATE and DELETE, where runtime pruning does not apply — a nightly cleanup written as DELETE FROM events WHERE occurred_at < now() - interval '13 months' opens every partition, takes a lock on each, and does its work in one.

Planner Cost at Scale

Pruning solves the execution problem and creates a planning one. Before a partition can be excluded it must be considered, and consideration means opening the relation, taking a lock, and evaluating its bounds. That work is proportional to the number of children, and it happens on every execution of every query — including the ones that prune perfectly.

Three settings and one design decision control the damage.

-- how many partitions does each table carry?
SELECT   parent.relname AS table_name, count(*) AS children
FROM     pg_inherits i
JOIN     pg_class c      ON c.oid = i.inhrelid
JOIN     pg_class parent ON parent.oid = i.inhparent
GROUP BY parent.relname
ORDER BY children DESC;

enable_partition_pruning must stay on — it is on by default and there is no production reason to disable it outside of a debugging session. plan_cache_mode decides whether prepared statements get custom plans (planned per execution with literals, so plan-time pruning applies) or generic plans (planned once, runtime pruning). force_custom_plan is the right setting for workloads with few partitions and expensive queries; auto is right almost everywhere else.

The design decision is partition count itself. A table with 3,000 daily partitions and a two-year retention is paying planning cost on every query to serve a workload that would fit comfortably in 24 monthly partitions with hash sub-partitioning underneath. When planning time becomes material, the answer is usually coarser granularity plus a second level rather than planner tuning — a trade explored in depth in range partitioning strategies.

Pruning in MySQL

MySQL prunes on the same principle with different diagnostics. EXPLAIN includes a partitions column listing exactly which partitions the optimiser will read, which is more direct than PostgreSQL’s plan shape:

EXPLAIN SELECT count(*) FROM events
WHERE  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01'\G

           table: events
      partitions: p202608
            type: range

Two MySQL-specific behaviours are worth knowing. Pruning applies to RANGE, LIST, HASH and KEY partitioning, but for HASH and KEY it only works with equality on the full partitioning expression — a range predicate on a hash-partitioned column prunes nothing, exactly as in PostgreSQL. And MySQL prunes at optimisation time only; there is no runtime equivalent, so a prepared statement with a parameter is pruned when the statement is executed with its bound values, not when it is prepared.

Pruning and Indexes Are Different Tools

A recurring confusion is worth settling explicitly: pruning decides which partitions to open, and indexes decide which rows to read inside a partition. They solve different problems, and using one where the other is needed is the most common wasted optimisation in a partitioned schema.

Consider the same query under four combinations. With pruning and an index, one partition is opened and an index range scan returns the rows — the intended design. With pruning but no index, one partition is opened and scanned sequentially, which is often perfectly acceptable if the partition is sized for it. With an index but no pruning, thirty-six index probes run, each cheap but each carrying a lock, a plan node and a buffer lookup. With neither, thirty-six sequential scans run and the response time is measured in seconds.

The third combination is the trap, because it looks healthy. Response times are tolerable at current volumes, the plan is full of index scans, and nothing in the monitoring says “this query reads every partition”. Then the table grows another year, six more children appear, and the same query is 20% slower with no change to the code. Adding indexes to a schema whose queries do not prune is treating a structural problem with a linear palliative.

The practical rule is to fix pruning first and index second. Once a query reliably opens one partition, whether that partition needs an index is a normal indexing decision made on normal grounds: selectivity, write amplification and maintenance cost. Before pruning works, no indexing decision can be made sensibly, because the cost being measured is dominated by the number of children rather than by anything about the data.

Failure Modes

Common Mistakes

• Testing pruning with literals when production uses parameters. The two take different planner paths. Always verify through the driver the application uses.
• Adding an index to fix a pruning problem. An index makes a full scan of 36 partitions into 36 index probes. It reduces the symptom enough to hide the cause and does nothing about planning cost.
• Treating (never executed) as a problem. It is the opposite: it is proof that runtime pruning worked.
• Assuming pruning implies partition-wise joins. They are separate features with separate settings; a pruned query can still produce a join plan that ignores partition alignment unless enable_partitionwise_join is on.
• Letting partition count grow without a ceiling. Nothing fails at 5,000 partitions — everything just gets slower, which is much harder to notice than an error.

FAQ

Related

• Verifying Partition Pruning with EXPLAIN ANALYZE — the exact commands and output patterns for confirming pruning in each engine
• Fixing Queries That Defeat Partition Pruning — rewrites for casts, functions, parameter types and ORM-generated SQL
• Runtime vs Plan-Time Partition Pruning — when each applies, and how to choose the plan cache mode
• Tuning Planner Settings for Large Partition Counts — planning cost, partition-wise joins and lock behaviour at scale
• Partition Key Selection & Design — the upstream decision that determines what can be pruned at all