Runtime vs Plan-Time Partition Pruning #

Both mechanisms eliminate partitions and both give the same answer. The difference is when the work happens, and therefore which cost you pay: plan-time pruning makes planning cheap by never considering the eliminated partitions, while runtime pruning plans everything and skips at execution. On a table with twelve partitions nobody notices. On a table with eight hundred, the choice decides whether a query takes 3 ms or 300 ms.

The signals that reveal which one you got are in reading partitions removed in EXPLAIN; this page is about choosing between them.

The Condition That Selects Each #

Plan-time pruning requires the predicate’s value to be known while the plan is built. That means:

Runtime pruning covers everything else:

The prepared-statement rule is the one that surprises people. PostgreSQL builds custom plans for the first five executions of a prepared statement, then compares the average custom cost against a generic plan and may switch. The moment it switches, pruning moves from planning to execution — see generic vs custom plan selection.

Where the work lands in each pruning mode The plan-time lane shows the planner considering only two of twelve partitions and the executor scanning those two. The runtime lane shows the planner considering all twelve and the executor removing ten subplans before scanning two. plan-time pruning — literal or custom plan planner considers 2 of 12 partitions · 0.42 ms executor scans 2 · 88 ms runtime pruning — generic plan or correlated value planner considers all 12 partitions · 3.81 ms executor Subplans Removed: 10 · scans 2 · 91 ms same rows read — the difference is entirely in what the planner had to consider

Annotated Evidence #

The prepared-statement transition is worth watching directly:

PREPARE p(timestamptz) AS SELECT count(*) FROM events WHERE occurred_at >= $1;

-- Execution 1-5: custom plans, parameter known → plan-time pruning
EXPLAIN (ANALYZE) EXECUTE p('2026-06-01');
Aggregate  (actual time=44.1..44.1 rows=1 loops=1)
  ->  Seq Scan on events_2026_06 events  (actual rows=200840 loops=1)
Planning Time: 0.51 ms

-- Execution 6+: planner may switch to a generic plan → runtime pruning
EXPLAIN (ANALYZE) EXECUTE p('2026-06-01');
Aggregate  (actual time=46.8..46.8 rows=1 loops=1)
  ->  Append  (actual time=0.03..33.2 rows=200840 loops=1)
        Subplans Removed: 11
        ->  Seq Scan on events_2026_06 events_1  (actual rows=200840 loops=1)
Planning Time: 0.04 ms          -- reused plan, but built for all partitions

Note the trade in the sixth execution: planning time drops almost to zero because the plan is cached, but the plan itself is the “all partitions” shape. On twelve partitions that is a fine deal. Scale to eight hundred and the cached generic plan carries eight hundred subplans, each of which the executor must consider skipping on every execution.

Runtime pruning inside a join is the other common shape, and here it is the only option:

Nested Loop  (actual time=0.08..2.94 rows=418 loops=1)
  ->  Seq Scan on active_accounts a  (actual rows=6 loops=1)
  ->  Append  (actual rows=70 loops=6)             -- re-pruned for each of the 6 outer rows
        Subplans Removed: 11
        ->  Index Scan using events_2026_06_account_idx on events_2026_06  (actual rows=70 loops=6)
        ->  Index Scan using events_2026_07_account_idx on events_2026_07  (never executed)

The partition key value comes from the outer row, so no plan-time analysis could have removed anything. Runtime pruning here is not a compromise — it is the feature that makes the shape viable.

Step-by-Step: Choosing Deliberately #

  1. Measure both halves separately. EXPLAIN (ANALYZE) prints Planning Time and Execution Time; the decision hinges on their ratio, not their sum.

  2. Count partitions. Below about fifty, the planning overhead of the generic shape is usually irrelevant. Above a few hundred it dominates short queries.

  3. For wide reporting scans, prefer plan-time pruning. Send literals, or force re-planning per execution:

    SET plan_cache_mode = force_custom_plan;   -- re-plan with real values each time

  4. For high-frequency OLTP statements, keep the generic plan. Saving 3 ms of planning on a statement executed 10,000 times a second matters more than the subplan bookkeeping.

  5. Watch for the switch. A statement that was fast for its first five executions and then regressed is the classic custom-to-generic transition:

    SET plan_cache_mode = force_generic_plan;  -- reproduce the regression deliberately

  6. Re-evaluate after adding partitions. A monthly-partitioned table crosses the interesting thresholds after a few years of retention, so a decision made at twelve partitions should be revisited at ninety.

Planning cost as partition count grows Two lines against partition count. Plan-time pruning stays nearly flat because only matching partitions are considered. Runtime pruning rises steeply because every partition must be planned, with a marked region where planning dominates execution. partitions on the table → planning cost plan-time pruning (literals) runtime pruning (generic plan) planning dominates short queries here 50 200 500 800

Before and After #

-- BEFORE: generic plan on an 800-partition table, OLTP lookup
Append  Subplans Removed: 799
Planning Time:   0.06 ms
Execution Time: 41.20 ms        -- executor overhead for 800 candidate subplans

-- AFTER: force_custom_plan, planner prunes to one partition
Index Scan using events_2026_06_account_idx on events_2026_06
Planning Time:  9.80 ms
Execution Time:  0.42 ms

Neither form is universally better: the first wins on planning, the second on execution. The right answer is whichever number is larger in your workload, which is why both must be measured rather than assumed.

Common Pitfalls #

Assuming a generic plan lost pruning. It moved it, not lost it. Diagnostic signal: Subplans Removed present in the plan. Fix: nothing — unless planning or executor bookkeeping is the bottleneck.

Leaving plan_cache_mode set at session level. On a pooled connection it changes every subsequent statement’s behavior. Diagnostic signal: EXPLAIN (SETTINGS) reporting it. Fix: use SET LOCAL, per session parameter drift.

Blaming the sixth execution. A statement that regresses after five runs is the custom-to-generic switch, not a data change. Diagnostic signal: an Append with Subplans Removed replacing a single-partition scan. Fix: force_custom_plan for that statement if the planning cost is affordable.

Ignoring partition growth. A retention policy that adds a partition per day silently moves you along the planning-cost curve. Diagnostic signal: Planning Time creeping up month over month. Fix: coarser partitions, or detach and archive old ones.

Comparing the two modes on different data. Plan-time pruning is usually measured with a literal date during testing and runtime pruning with production parameters, which confounds the comparison with cardinality differences. Diagnostic signal: a conclusion drawn from two runs with different bounds. Fix: hold the values constant and vary only the literal-versus-parameter form.

Assuming an ORM sends literals. Most frameworks parameterise every value by default, so a query that prunes beautifully in psql may take the generic path in the application. Diagnostic signal: production plans containing Subplans Removed for a statement you tested with literals. Fix: capture the real plan with auto_explain rather than reproducing the query by hand.

Forgetting that both modes can appear in one plan. A query filtering on a literal date range and a parameterised tenant identifier prunes on one dimension at plan time and the other at execution. Diagnostic signal: a reduced child count and a Subplans Removed line on the same Append. Fix: read both signals together — they are complementary, not alternatives.

The sixth execution is where pruning moves Six execution slots. The first five use custom plans with plan-time pruning; from the sixth the planner may switch to a generic plan, moving elimination to execution time. 12345 6th onwardgeneric plan possible custom plans — pruning at plan time Subplans Removed a statement that regresses on its sixth run is this switch, not a data change

Frequently Asked Questions #

Why is planning slower with a parameterised query? Because the planner cannot see the parameter values and must plan every partition that could qualify. The elimination happens later, at execution, so the planning cost scales with the total partition count rather than the matching one.

Does a generic plan lose partition pruning? No. It keeps all candidate subplans and the executor removes the ones that cannot match, which EXPLAIN ANALYZE reports as Subplans Removed. Only the planning-time saving is lost.

How do I force plan-time pruning for a reporting query? Use literal values for the partition-key predicate, or set plan_cache_mode = force_custom_plan so the statement is re-planned with real values each execution. Both let the planner compare the predicate against partition bounds while building the plan.