Explain the difference between eliminating partitions when a query's plan is built and eliminating them while the query runs. Give query shapes that can only be handled at execution time, and describe what you would look for in EXPLAIN output to tell which one happened.

seniormust knowfreq 40%

answer

plan time = constants and stable functions, partitions never appear
execution time = bind params, initplans, nested-loop inner side
Subplans Removed: N on the Append
(never executed) children in EXPLAIN ANALYZE
generic plan hides pruning until run time

basics

~20 s

Plan-time pruning uses values known while planning, so unneeded partitions never enter the plan. Execution-time pruning handles values known only at run time: bind parameters of a reusable plan, subquery results, and per-row values in a nested loop. In EXPLAIN ANALYZE it shows as "Subplans Removed" or child nodes marked "never executed".

solid answer

~50 s

Plan-time pruning happens while the planner builds the plan, using constants and expressions it can evaluate then (including stable functions such as now()). Pruned partitions never appear in the plan at all, so planning is cheaper too.

Execution-time pruning applies when the deciding value only exists at run time:

a prepared statement executing a generic plan, where the key arrives as a bind parameter;
a value produced by an initplan or subquery, for example WHERE occurred_at >= (SELECT max(cutoff) FROM config);
the inner side of a nested loop, re-pruned for each outer row.

The plan then still lists all candidate partitions, but the executor skips the ones that cannot match.

To tell them apart, compare EXPLAIN with EXPLAIN (ANALYZE, BUFFERS). If a partition is absent from the plan text, it was pruned at plan time. If it appears but is annotated (never executed), or the Append reports Subplans Removed: N, that is execution-time pruning. enable_partition_pruning off disables both, and is a debugging switch only.

**Plan-time pruning** happens while the planner builds the plan, using constants and expressions it can evaluate then (including stable functions such as `now()`). Pruned partitions never appear in the plan at all, so planning is cheaper too. **Execution-time pruning** applies when the deciding value only exists at run time: - a prepared statement executing a **generic plan**, where the key arrives as a bind parameter; - a value produced by an **initplan or subquery**, for example `WHERE occurred_at >= (SELECT max(cutoff) FROM config)`; - the inner side of a **nested loop**, re-pruned for each outer row. The plan then still lists all candidate partitions, but the executor skips the ones that cannot match. To tell them apart, compare `EXPLAIN` with `EXPLAIN (ANALYZE, BUFFERS)`. If a partition is absent from the plan text, it was pruned at plan time. If it appears but is annotated `(never executed)`, or the Append reports `Subplans Removed: N`, that is execution-time pruning. `enable_partition_pruning` off disables both, and is a debugging switch only.

code

text · 7 lines

text

Append  (actual rows=812 loops=1)
  Subplans Removed: 35
  InitPlan 1 (returns $0)
    ->  Seq Scan on report_config  (actual rows=1 loops=1)
  ->  Index Scan using events_2026_03_ts_idx on events_2026_03
        Index Cond: (occurred_at >= $0)
  ->  Index Scan using events_2026_04_ts_idx on events_2026_04 (never executed)

go deeper

for a junior

Know that pruning can happen either when the plan is made or while the query runs, and that EXPLAIN ANALYZE is what shows the second kind.

for a middle

Give one concrete shape for each phase and point at the plan artefacts: missing children versus 'Subplans Removed' and '(never executed)'.

for a senior

Diagnose with it: generic versus custom plans, initplans, nested-loop re-pruning, planning-time cost with many partitions, and what enable_partition_pruning does.

for a principal

Reason about the whole workload: partition count versus plan size, plan-cache policy for parameterised statements, and when to force custom plans or restructure queries to prune at plan time.

Two different moments

Pruning is a proof that a partition cannot contribute rows. The proof needs values, and values become available at two different moments, which is why there are two mechanisms.

Plan time. The planner has the query text plus anything it can evaluate before execution: literals, folded constant expressions, and stable functions whose value is fixed for the statement (now(), current_date). It compares those against partition bounds and simply omits the impossible partitions from the plan. This is the best case: the plan is smaller, planning itself is faster, fewer relations are locked, and the executor has less to set up.

Execution time. Some deciding values only exist once the query is running. PostgreSQL 11 introduced pruning during execution for exactly these cases, and it is what makes partitioning workable with prepared statements and parameterised joins.

The shapes that need execution-time pruning

Generic plans for prepared statements. PostgreSQL may reuse one plan for a prepared statement across executions with different parameter values. In a generic plan the key is a placeholder, so no partition can be excluded at plan time; the executor evaluates the parameter and skips the rest. Relevant knobs: the planner compares custom-plan cost against the generic plan after the first executions, and plan_cache_mode can force either behaviour. A forced generic plan on a partitioned table is a classic cause of "it was fast, then it got slow".
Values from an initplan or subquery. WHERE occurred_at >= (SELECT since FROM report_config WHERE id = 1) cannot prune while planning, because the value is computed as an initplan at run time. The Append then removes the subplans it does not need.
Parameterised nested loops. When a partitioned table is the inner side of a nested loop and the join key is the partition key, the inner Append is re-pruned for every outer row, typically touching a single partition per iteration. This is the mechanism that makes join-driven access to a partitioned table efficient without any literal in the query. It requires a nested loop; a hash join gives no per-row parameter and therefore no pruning of this kind.

Reading the plan

A reliable procedure:

Run plain EXPLAIN. The children of the Append node are the partitions that survived plan-time pruning. If only one survives, the Append disappears and you see that partition's scan directly.
Run EXPLAIN (ANALYZE, BUFFERS). Now look for:
- Subplans Removed: N on the Append or Merge Append node: N candidate partitions were eliminated at execution time.
- (never executed) on a child node: it stayed in the plan but the executor never opened it, which is what per-loop pruning looks like.
- loops= and buffer counts on each child, which tell you what was actually read rather than what was merely planned.
For a prepared statement, explain the prepared statement itself rather than an ad-hoc query with literals substituted, otherwise you are analysing a different plan from the one production runs.

Beware two traps. First, EXPLAIN without ANALYZE cannot show execution-time pruning, because nothing executed: a plan listing 400 partitions may still touch one. Second, planning a query over hundreds of partitions costs real time even when they are pruned at execution, so compare planning time as well as execution time.

Settings and their meaning

enable_partition_pruning (on by default) controls both phases; turning it off is only for demonstrating the difference or for a bug workaround, and finding it off in a production configuration is itself the finding. The older constraint_exclusion setting applies to the legacy inheritance mechanism and to CHECK constraints, not to declarative partition bounds, and confusing the two is a common interview stumble.

Practical consequences

Many partitions plus prepared statements is the combination to watch: the plan is large, planning is repeated or cached generically, and only execution-time pruning saves the I/O. Measure planning time separately.
Force a custom plan (plan_cache_mode = force_custom_plan) for queries where plan-time pruning is worth far more than plan reuse, typically wide-ranging analytic queries over many partitions.
Prefer literals in the range predicate for reporting queries, so the small plan is chosen up front.
Do not assume execution-time pruning covers joins: it only applies where the executor gets a value, so hash-joined access still reads every partition.

What a strong answer sounds like

Name both phases, give a concrete shape for each, say which EXPLAIN artefacts prove which, and mention that plan-time pruning also saves planning and locking overhead, not just I/O.

## Two different moments Pruning is a proof that a partition cannot contribute rows. The proof needs values, and values become available at two different moments, which is why there are two mechanisms. **Plan time.** The planner has the query text plus anything it can evaluate before execution: literals, folded constant expressions, and stable functions whose value is fixed for the statement (`now()`, `current_date`). It compares those against partition bounds and simply omits the impossible partitions from the plan. This is the best case: the plan is smaller, planning itself is faster, fewer relations are locked, and the executor has less to set up. **Execution time.** Some deciding values only exist once the query is running. PostgreSQL 11 introduced pruning during execution for exactly these cases, and it is what makes partitioning workable with prepared statements and parameterised joins. ## The shapes that need execution-time pruning 1. **Generic plans for prepared statements.** PostgreSQL may reuse one plan for a prepared statement across executions with different parameter values. In a generic plan the key is a placeholder, so no partition can be excluded at plan time; the executor evaluates the parameter and skips the rest. Relevant knobs: the planner compares custom-plan cost against the generic plan after the first executions, and `plan_cache_mode` can force either behaviour. A forced generic plan on a partitioned table is a classic cause of "it was fast, then it got slow". 2. **Values from an initplan or subquery.** `WHERE occurred_at >= (SELECT since FROM report_config WHERE id = 1)` cannot prune while planning, because the value is computed as an initplan at run time. The Append then removes the subplans it does not need. 3. **Parameterised nested loops.** When a partitioned table is the inner side of a nested loop and the join key is the partition key, the inner Append is re-pruned for **every outer row**, typically touching a single partition per iteration. This is the mechanism that makes join-driven access to a partitioned table efficient without any literal in the query. It requires a nested loop; a hash join gives no per-row parameter and therefore no pruning of this kind. ## Reading the plan A reliable procedure: 1. Run plain `EXPLAIN`. The children of the Append node are the partitions that survived **plan-time** pruning. If only one survives, the Append disappears and you see that partition's scan directly. 2. Run `EXPLAIN (ANALYZE, BUFFERS)`. Now look for: - **`Subplans Removed: N`** on the Append or Merge Append node: N candidate partitions were eliminated at execution time. - **`(never executed)`** on a child node: it stayed in the plan but the executor never opened it, which is what per-loop pruning looks like. - **`loops=`** and buffer counts on each child, which tell you what was actually read rather than what was merely planned. 3. For a prepared statement, explain the prepared statement itself rather than an ad-hoc query with literals substituted, otherwise you are analysing a different plan from the one production runs. Beware two traps. First, `EXPLAIN` without ANALYZE cannot show execution-time pruning, because nothing executed: a plan listing 400 partitions may still touch one. Second, planning a query over hundreds of partitions costs real time even when they are pruned at execution, so compare planning time as well as execution time. ## Settings and their meaning `enable_partition_pruning` (on by default) controls both phases; turning it off is only for demonstrating the difference or for a bug workaround, and finding it off in a production configuration is itself the finding. The older `constraint_exclusion` setting applies to the legacy inheritance mechanism and to CHECK constraints, not to declarative partition bounds, and confusing the two is a common interview stumble. ## Practical consequences - **Many partitions plus prepared statements** is the combination to watch: the plan is large, planning is repeated or cached generically, and only execution-time pruning saves the I/O. Measure planning time separately. - **Force a custom plan** (`plan_cache_mode = force_custom_plan`) for queries where plan-time pruning is worth far more than plan reuse, typically wide-ranging analytic queries over many partitions. - **Prefer literals in the range predicate** for reporting queries, so the small plan is chosen up front. - **Do not assume execution-time pruning covers joins**: it only applies where the executor gets a value, so hash-joined access still reads every partition. ## What a strong answer sounds like Name both phases, give a concrete shape for each, say which EXPLAIN artefacts prove which, and mention that plan-time pruning also saves planning and locking overhead, not just I/O.

A reporting query on a 400-partition table was fast for the first few executions of a prepared statement and then became slow. What is the likely cause?
The plan cache switched from per-execution custom plans to a reusable generic plan. Custom plans embed the parameter values and prune at plan time to a couple of partitions; the generic plan keeps all 400 candidates and relies on execution-time pruning, which saves the I/O but leaves a much larger plan and higher per-execution overhead, and can also lose index and join choices that depended on the actual values. Forcing a custom plan for that statement usually restores the original behaviour.
Why does a nested loop join sometimes prune a partitioned inner table while a hash join over the same query does not?
A parameterised nested loop supplies a concrete partition-key value for each outer row, so the executor can re-prune the inner Append per iteration and normally touch one partition. A hash join builds a hash table over the whole inner side once, with no per-row parameter, so nothing narrows the set of partitions and all of them are read. If the join key is the partition key and the outer side is small, encouraging the nested loop, or supplying an explicit range predicate, restores pruning.

saying these in an interview costs you the question

Concluding from a plain EXPLAIN that all partitions are read, without running EXPLAIN ANALYZE to see execution-time pruning.
Thinking execution-time pruning makes plan-time pruning unnecessary; plan-time pruning also cuts planning and locking cost.
Confusing constraint_exclusion with enable_partition_pruning.
Assuming any join to a partitioned table prunes, regardless of join method.
Explaining a prepared statement by pasting literals into an ad-hoc query, which analyses a different plan.

Explain the difference between eliminating partitions when a query's plan is built and eliminating them while the query runs. Give query shapes that can only be handled at execution time, and describe what you would look for in EXPLAIN output to tell which one happened.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two different moments

The shapes that need execution-time pruning

Reading the plan

Settings and their meaning

Practical consequences

What a strong answer sounds like

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

Two different moments

The shapes that need execution-time pruning

Reading the plan

Settings and their meaning

Practical consequences

What a strong answer sounds like

answer

go deeper

context