Reading Partitions Removed in EXPLAIN #

Partition elimination leaves different fingerprints depending on when it happened, and none of them is a line that says “pruned 11 partitions”. You have to infer it from what is absent, from a counter that only appears under ANALYZE, and from the phrase never executed. This page catalogues every signal so you can read a partitioned plan without guessing.

The mechanics behind these signals are in partition pruning analysis.

The Four Signals #

1. Missing children. Plan-time pruning happens before the plan is built, so eliminated partitions never appear. Count the children under Append and compare against the real partition count.

2. No Append at all. When exactly one partition survives, the planner collapses the node and scans the partition directly. This is the strongest form of pruning, not a missing feature.

3. Subplans Removed: N on the Append. This is runtime pruning, and it appears only under EXPLAIN ANALYZE because the executor is what removed them.

4. never executed on a child. The subplan stayed in the tree but the executor skipped it — typical inside a nested loop where pruning is re-evaluated per outer row.

The four ways a plan reports partition elimination Four boxed plan fragments. The first shows an Append with fewer children than partitions. The second shows a single partition scan with no Append. The third shows Subplans Removed on an Append. The fourth shows a child marked never executed. 1 · fewer children than partitions Append (actual rows=602520) -> Seq Scan on events_2026_05 -> Seq Scan on events_2026_06 (10 partitions pruned at plan time) 2 · no Append node Aggregate -> Seq Scan on events_2026_06 events one survivor — the Append collapsed 3 · Subplans Removed (runtime) Append (actual rows=200840 loops=1) Subplans Removed: 11 -> Seq Scan on events_2026_06 only visible under EXPLAIN ANALYZE 4 · never executed Append (actual rows=70 loops=6) -> Index Scan on events_2026_06 (rows=12) -> Index Scan on events_2026_07 (never executed) re-pruned per outer row

Annotated Evidence #

A literal-valued query, pruned while planning:

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

Aggregate  (actual time=88.4..88.4 rows=1 loops=1)
  ->  Append  (actual time=0.02..62.1 rows=602520 loops=1)
        ->  Seq Scan on events_2026_05 events_1  (actual rows=401680 loops=1)
        ->  Seq Scan on events_2026_06 events_2  (actual rows=200840 loops=1)
Planning Time: 0.42 ms          -- only 2 partitions were ever considered

Two children out of twelve partitions, and a planning time under half a millisecond because the other ten were never opened or costed.

The same logic through a prepared statement:

PREPARE window_count(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE occurred_at >= $1 AND occurred_at < $2;
EXPLAIN (ANALYZE) EXECUTE window_count('2026-05-01','2026-07-01');

Aggregate  (actual time=91.2..91.2 rows=1 loops=1)
  ->  Append  (actual time=0.03..64.8 rows=602520 loops=1)
        Subplans Removed: 10                       -- runtime pruning
        ->  Seq Scan on events_2026_05 events_1  (actual rows=401680 loops=1)
        ->  Seq Scan on events_2026_06 events_2  (actual rows=200840 loops=1)
Planning Time: 3.81 ms          -- all 12 partitions were planned

Execution is nearly identical; planning is nine times slower because every partition had to be planned before ten could be discarded. That gap is the entire argument for preferring literals in reporting queries against heavily partitioned tables.

And the failure case, where nothing was eliminated:

EXPLAIN (ANALYZE) SELECT count(*) FROM events WHERE date_trunc('month', occurred_at) = '2026-06-01';

Aggregate  (actual time=4120.6..4120.6 rows=1 loops=1)
  ->  Append  (actual time=0.03..3980.2 rows=200840 loops=1)
        ->  Seq Scan on events_2026_01 events_1  (actual rows=0 loops=1)
              Filter: (date_trunc('month', occurred_at) = '2026-06-01')
              Rows Removed by Filter: 398120
        ->  Seq Scan on events_2026_02 events_2  (actual rows=0 loops=1)
        …  (all twelve present, each with Rows Removed by Filter)

No Subplans Removed, twelve executed children, and eleven of them returning zero rows after filtering everything away. The wrapped partition key is the cause — the diagnostic is Rows Removed by Filter on partitions that should never have been touched.

Step-by-Step Verification #

  1. Get the true partition count so “fewer children” means something:

    SELECT count(*) FROM pg_inherits WHERE inhparent = 'events'::regclass;

  2. Run a plain EXPLAIN first. Children present here survived plan-time pruning.

  3. Run EXPLAIN (ANALYZE) and look for Subplans Removed on the Append. Present means runtime pruning worked.

  4. Scan the children for never executed. In a nested loop the Append re-prunes per outer row, so this is the per-loop equivalent of Subplans Removed.

  5. Check Rows Removed by Filter on any child that returned zero rows. A partition that was scanned and filtered to nothing is a pruning failure, not a pruning success.

  6. Compare Planning Time with Execution Time. On a table with hundreds of partitions, runtime pruning can make planning the dominant cost even though execution is minimal.

  7. If nothing pruned, fix the predicate, not the partitioning: compare the bare key against range literals, as covered in runtime vs plan-time partition pruning.

Planning and execution split across three pruning outcomes Three stacked bars. Literal predicate has tiny planning and moderate execution. Parameterised predicate has larger planning and the same execution. A predicate that defeats pruning has small planning and very large execution. literal range 0.42 + 88 ms parameterised 3.81 + 91 ms no pruning 0.31 + 4121 ms planning execution

Before and After #

-- BEFORE: date_trunc hides the key, twelve partitions scanned
Append  (actual rows=200840)   12 children, no Subplans Removed
Execution Time: 4120.6 ms

-- AFTER: half-open range on the bare column, one partition survives
Seq Scan on events_2026_06 events  (actual rows=200840)   -- Append collapsed entirely
Execution Time: 42.8 ms

The signal that changed is structural, not numeric: the Append disappeared. That is what a fully successful prune looks like.

Common Pitfalls #

Reading a plain EXPLAIN and concluding runtime pruning failed. It cannot be shown without execution. Diagnostic signal: many children, no Subplans Removed, no ANALYZE. Fix: re-run with ANALYZE.

Treating never executed as an error. It is the per-loop proof that pruning worked. Diagnostic signal: children without actual rows inside a nested loop. Fix: none needed — it is the desired outcome.

Ignoring planning time. With hundreds of partitions, runtime pruning still plans them all. Diagnostic signal: Planning Time exceeding Execution Time. Fix: use literals for reporting queries, or reduce partition granularity.

Confusing a filtered-to-zero child with a pruned one. A scanned partition that returns nothing still cost a full scan. Diagnostic signal: Rows Removed by Filter on a child with actual rows=0. Fix: correct the predicate so the partition is eliminated instead of scanned.

Reading a Merge Append as a different mechanism. It reports elimination exactly as Append does, including Subplans Removed; the only difference is that it preserves ordering across children. Diagnostic signal: uncertainty about whether the ordered variant prunes at all. Fix: read it with the same rules — child count, removals, and never executed.

Ignoring the alias numbering. Children appear as events_1, events_2, and so on, and the numbers reflect plan position rather than partition order. Diagnostic signal: an attempt to infer which month was scanned from the alias suffix. Fix: read the physical relation name that precedes the alias.

Missing pruning inside a subquery or CTE. A materialised CTE containing a partitioned scan reports its own Append deep in the tree, easily overlooked when scanning the top of a long plan. Diagnostic signal: a plan whose top-level nodes look clean while total time is dominated by something below. Fix: search the whole plan text for Append, not just its head — and consider whether the CTE fence is preventing the outer predicate from pruning at all.

Read the plan in this order Four ordered checks: count the children, look for Subplans Removed, scan for never executed, then check Rows Removed by Filter on any child returning no rows. 1 · child countvs real partitions 2 · Subplans Removedruntime pruning 3 · never executedper-loop pruning 4 · Rows Removedscanned, not pruned step 4 is the one people skip — a scanned-then-filtered partition is a pruning failure

Frequently Asked Questions #

Why does my plan have no Append node at all? Because exactly one partition survived pruning. With a single child the planner removes the Append and plans the partition directly, so its physical name appears as the scan target. This is the best possible outcome.

Where does Subplans Removed appear? On the Append or Merge Append, and only under EXPLAIN ANALYZE, since runtime pruning happens during execution. A plain EXPLAIN of a parameterised query lists every candidate subplan with no hint of which will be skipped.

How do I see which partitions were actually scanned? Read the child node names — each names the physical partition. Children skipped at runtime are marked never executed, which distinguishes them from children that ran and legitimately returned no rows.