Runtime vs Plan-Time Partition Pruning

Two mechanisms with the same name do different work at different times, and confusing them leads to tuning the wrong thing. This guide separates them, shows how to tell which one a query is getting, and explains the plan-cache settings that decide it — a deeper look at the distinction introduced in Partition Pruning & Query Planning, part of Database Partitioning Fundamentals & Architecture.

Prerequisites

Step 1 — Understand what each mechanism can see

Plan-time pruning runs while the plan is being built. It has access to constants written into the SQL and to values the planner can fold, such as '2026-08-01'::timestamptz. It removes partitions before they are costed, so they contribute nothing to planning time either.

Runtime pruning runs during execution. It handles three sources of values the planner cannot know: parameters supplied at EXECUTE time, results of subqueries evaluated during the query, and values arriving from the outer side of a nested loop join. It skips reading the partition but cannot undo the planning work already spent on it.

-- plan-time: constants are visible during planning
SELECT count(*) FROM events
WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

-- runtime: value arrives at execution
PREPARE p(timestamptz) AS SELECT count(*) FROM events WHERE occurred_at >= $1;

-- runtime: value comes from a subquery
SELECT count(*) FROM events
WHERE occurred_at >= (SELECT max(closed_at) FROM billing_periods);

Operational note: now() and CURRENT_DATE are stable rather than immutable, so predicates built from them prune at runtime, not plan time. That is usually invisible and occasionally important — see the DELETE case in step 4.

DBA tip: There is no setting that converts runtime pruning into plan-time pruning. The only lever is whether the value is a constant in the statement text, which is a decision made by the application or the plan cache.

Where each pruning mechanism acts in the query lifecycle A statement passes through parse, plan and execute phases. Plan-time pruning removes partitions during the plan phase, so the executor never sees them and planning cost is avoided. Runtime pruning acts at the start of execution and inside nested loops, so the plan still contains every child and planning cost is fully paid. parse plancost every surviving child executeopen only what is needed rows plan-time pruning constants in the statement discarded children cost nothing runtime pruning parameters, subqueries, join keys planning already paid for all children Both avoid reading data, and on a table with a handful of partitions the difference is not measurable. The gap grows with partition count: at 36 children plan-time saves about 13 ms per execution, at 1,000 it saves tens of milliseconds. Runtime pruning is not a degraded mode — it is the only thing that can work when the value genuinely is not known yet.

Step 2 — Watch the plan cache switch modes

PostgreSQL plans a prepared statement with real values for its first five executions, then compares the cost of a generic plan against the average custom plan cost. If the generic plan is not clearly worse, it wins permanently:

The sixth execution is where planning time jumps Executions one to five are planned with the actual parameter values, so plan-time pruning applies and planning takes about one millisecond. From the sixth execution the cached generic plan is used: pruning moves to runtime, execution time is unchanged, and planning time rises to fourteen milliseconds because all thirty-six children were considered when the generic plan was built. 16 ms10 ms4 ms0 12345 67891011 generic plan takes over Execution time is flat across all eleven runs. Only planning changed, which is why this looks like a mystery regression until `pg_stat_statements` is read with `track_planning` on. 
PREPARE tenant_events(bigint, timestamptz) AS
  SELECT id, payload FROM events WHERE tenant_id = $1 AND occurred_at >= $2 LIMIT 100;

-- executions 1..5: custom plans, plan-time pruning
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_events(8842, '2026-08-01');

-- from execution 6: generic plan may take over
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_events(8842, '2026-08-01');

The switch is visible as a jump in Planning Time and, in the plan text, as the appearance of Subplans Removed where previously a single child was named directly.

Operational note: Connection pools in transaction mode may re-prepare on each new backend, resetting the counter and hiding the effect during testing. In session mode the fifth execution is genuine and the change is permanent for that connection.

DBA tip: pg_prepared_statements shows generic_plans and custom_plans counters per prepared statement, which is the fastest way to confirm what a live session is actually using.

Step 3 — Choose plan_cache_mode deliberately

Three values, and the right one depends entirely on the ratio of planning to execution time:

-- per session, per role, or in postgresql.conf
SET plan_cache_mode = auto;                -- default: decide by estimated cost
SET plan_cache_mode = force_custom_plan;   -- always re-plan → plan-time pruning
SET plan_cache_mode = force_generic_plan;  -- always reuse → runtime pruning
Setting Planning cost Pruning available Fits
auto mixed both most workloads; safe default
force_custom_plan paid on every execution plan-time few partitions, expensive queries, skewed parameter distributions
force_generic_plan paid once runtime many partitions, cheap queries, uniform parameters

Set it per role rather than globally when only one workload needs it:

ALTER ROLE reporting SET plan_cache_mode = force_custom_plan;

Operational note: force_custom_plan also helps when parameter values are highly skewed — a tenant id that is either a whale or a small account produces very different optimal plans, and a generic plan is wrong for one of them.

SRE tip: Change this per role, never globally, and measure mean_plan_time from pg_stat_statements before and after. It is one of the few settings where the wrong choice makes a workload slower in a way that looks like a data problem.

Step 4 — Know where runtime pruning does not apply

Runtime pruning covers SELECT and the query part of INSERT ... SELECT. It does not cover the target of UPDATE and DELETE, which is why a parameterised cleanup opens every partition:

-- opens all 36 partitions and locks each one, then deletes from one
DELETE FROM events WHERE occurred_at < now() - interval '13 months';

-- computes the cutoff in the application, sends a literal → plan-time pruning
DELETE FROM events WHERE occurred_at < '2025-07-01';

-- better still for whole-partition retention: no DELETE at all
ALTER TABLE events DETACH PARTITION events_2025_06;
DROP TABLE events_2025_06;
Lock footprint of three ways to remove a month of data A relative-time DELETE takes a row exclusive lock on all 36 partitions for the duration of the statement. A literal-bound DELETE prunes at plan time and locks one. Detaching and dropping the partition takes a brief exclusive lock on the parent and none on the others, and completes in milliseconds instead of minutes. DELETE … < now() - interval '13 months' DELETE … < '2025-07-01' DETACH + DROP PARTITION 36 locks 1 lock 1 brief lock on the parent runtime pruning does not apply to DELETE targets — every child is opened and locked for the statement's duration plan-time pruning applies because the bound is a constant; the other 35 partitions are never touched no rows are read or written at all; the space is returned by the DROP in a single catalog operation

Operational note: The lock taken by DELETE on non-matching partitions is only ROW EXCLUSIVE, which does not block reads or writes — but it does block DDL, so a nightly cleanup can stall the partition-creation job that runs at the same hour.

DBA tip: If retention removes whole partitions, never write a DELETE at all. The detach-and-drop path is covered in detaching and archiving old partitions.

Verification

Confirm which mechanism a live statement is using without changing it:

SELECT name,
       generic_plans,
       custom_plans,
       statement
FROM   pg_prepared_statements;
     name      | generic_plans | custom_plans | statement
---------------+---------------+--------------+---------------------------------------------
 tenant_events |          8814 |            5 | PREPARE tenant_events(bigint, timestamptz) AS ...

A high generic_plans count with a high mean_plan_time in pg_stat_statements is the signature of a workload that would benefit from force_custom_plan. A high custom_plans count with high plan time means the opposite — planning is being repeated needlessly.

Failure mode table

Failure mode Root cause SRE mitigation
Latency step-change with no deploy plan cache switched to a generic plan on the sixth execution, losing plan-time pruning confirm via pg_prepared_statements; set plan_cache_mode = force_custom_plan for that role and re-measure planning time
force_custom_plan made things slower planning cost now paid on every execution and exceeds the pruning saving at high partition counts revert to auto; reduce partition count or add a sub-partition level instead
Nightly DELETE blocks partition creation the relative-time predicate opens and locks every child, colliding with DDL from the maintenance job replace with DETACH/DROP, or send a literal cutoff and schedule the two jobs in different windows

FAQ

Should I always force custom plans on partitioned tables?

Only where planning is cheap relative to execution. force_custom_plan re-plans on every execution, which buys plan-time pruning and costs the full planning time each call. On a table with 40 partitions and 30 ms queries that trade is clearly worth it; on a table with 2,000 partitions and 2 ms queries it makes things worse, because planning now dominates and is paid every time.

Why did my query get slower after the fifth execution?

That is PostgreSQL’s plan cache switching from custom plans to a generic plan. The first five executions of a prepared statement are planned with the actual parameter values; from the sixth, if the generic plan’s estimated cost is not worse, it is reused. On a partitioned table the generic plan loses plan-time pruning, so planning work jumps even though execution stays similar.

Does runtime pruning work inside joins?

Yes — it is one of the places it matters most. In a nested loop join where the partitioned table is on the inner side, each outer row supplies a value that runtime pruning uses to skip partitions for that iteration. The plan shows every child, and loops counts reveal which ones were actually entered.

Back to Partition Pruning Query Planning