Verifying Partition Pruning with EXPLAIN ANALYZE

This guide confirms, with certainty, which partitions a given query reads — in PostgreSQL and MySQL, for literal and parameterised forms — and turns that check into a test that fails when a code change breaks pruning. It applies the diagnostics introduced in Partition Pruning & Query Planning within the wider Database Partitioning Fundamentals & Architecture section.

Prerequisites

Step 1 — Capture the statement the application really sends

The plan depends on the statement form, so the first job is to stop guessing at it. pg_stat_statements normalises literals into placeholders, which is exactly the form you want for the parameterised check:

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(mean_plan_time::numeric, 2) AS plan_ms,
       query
FROM   pg_stat_statements
WHERE  query ILIKE '%from events%'
ORDER  BY mean_exec_time * calls DESC
LIMIT  5;
 queryid   | calls  | mean_ms | plan_ms | query
-----------+--------+---------+---------+------------------------------------------------
 -84213... | 412039 |  1893.4 |   14.21 | SELECT count(*) FROM events WHERE occurred_at >= $1 AND occurred_at < $2

Operational note: mean_plan_time is populated when pg_stat_statements.track_planning is on. A plan time above one millisecond on a simple query is itself a pruning signal — it means the planner is considering many children.

DBA tip: Sort by mean_exec_time * calls rather than by mean alone. A 40 ms query called two million times a day costs far more than a 4-second report run twice.

Step 2 — Check the literal form first

Start with the simplest case, because if this does not prune, nothing else will:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*)
FROM   events
WHERE  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

A healthy plan names exactly one child and has no Append node at all, because a single surviving partition needs no appending:

 Aggregate (actual time=41.882..41.883 rows=1 loops=1)
   Buffers: shared hit=1204 read=98311
   ->  Seq Scan on events_2026_08 events (actual time=0.019..29.114 rows=31428104 loops=1)
         Filter: ((occurred_at >= '2026-08-01 00:00:00+00') AND (occurred_at < '2026-09-01 00:00:00+00'))
         Buffers: shared hit=1204 read=98311
The three markers in a plan that prove pruning happened Three annotated plan fragments. The first shows a single child scan with no Append node, which is plan-time pruning. The second shows an Append with Subplans Removed equal to 35, which is runtime pruning. The third shows an Append whose children carry never executed annotations, which is also runtime pruning. A fourth fragment shows the unhealthy case: every child with real row counts. A · plan-time pruning B · runtime pruning (counted) C · runtime pruning (annotated) D · no pruning — the bug Seq Scan on events_2026_08 (no Append node at all) Append Subplans Removed: 35 Append -> Seq Scan events_2026_07 (never executed) -> Seq Scan events_2026_08 (rows=31428104) Append -> Seq Scan events_2026_07 (rows=29881004) -> Seq Scan events_2026_08 (rows=31428104) A is the ideal: the discarded children cost nothing, not even planning. B and C are equivalent and healthy — the difference is only which PostgreSQL version and plan shape you are looking at. D means the predicate is not usable for pruning at all, and no amount of indexing will fix it. Read the annotations, not the timings: a fast plan D on a small dataset becomes a slow plan D at production scale.

Operational note: Include BUFFERS. Buffer counts are the honest measure of work done — a plan that reads 98,000 blocks is reading one month; a plan reading 3.4 million blocks is reading the table regardless of what the row counts suggest.

SRE tip: Run this against a database with production-like statistics. A staging copy with a thousand rows per partition will prune identically but tells you nothing about whether the plan choice survives real cardinalities.

Step 3 — Check the parameterised form, which is what production runs

This is the step that finds real bugs. Prepare the statement and force the generic plan:

Two paths to the same query, two different plans Typing the query into psql with literal bounds produces a custom plan with plan-time pruning and one child in the plan. The application sends the same query as a prepared statement with parameters, which after five executions becomes a generic plan listing every child. Verifying only the first path is why pruning bugs reach production. typed into psqlliteral bounds the applicationprepared, parameters custom planplanned with the real values generic plan after 5 runsplanned without values 1 child in the planlooks perfect 36 children + Subplans Removedor, when broken, 36 real scans Verify the lower path. The upper one is useful for a first sanity check and is not evidence about production behaviour, because the planner takes a different route through it. 
PREPARE ev_count(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE occurred_at >= $1 AND occurred_at < $2;

SET plan_cache_mode = force_generic_plan;

EXPLAIN (ANALYZE, COSTS OFF)
EXECUTE ev_count('2026-08-01', '2026-09-01');
 Aggregate (actual time=42.914..42.915 rows=1 loops=1)
   ->  Append (actual time=0.028..39.774 rows=31428104 loops=1)
         Subplans Removed: 35
         ->  Seq Scan on events_2026_08 events_1 (actual time=0.027..37.001 rows=31428104 loops=1)
               Filter: ((occurred_at >= $1) AND (occurred_at < $2))
 Planning Time: 13.884 ms
 Execution Time: 42.955 ms

Subplans Removed: 35 is the proof. Note also Planning Time: 13.884 ms — pruning worked, and planning still cost thirteen milliseconds because all 36 children were considered before 35 were dropped.

Operational note: Reset with SET plan_cache_mode = auto; when you are done. Leaving a session pinned to generic plans is harmless; leaving it in a connection-pooled application is not, because the setting outlives the request on a reused connection.

DBA tip: If Subplans Removed is absent and every child shows real rows, compare the generic plan against the custom plan by setting force_custom_plan instead. A large difference is the case for pinning that workload to custom plans.

Step 4 — Verify in MySQL with the partitions column

MySQL reports pruning directly, which makes the check shorter:

EXPLAIN SELECT count(*) FROM events
WHERE  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p202608
         type: range
possible_keys: idx_occurred_at
          key: idx_occurred_at
         rows: 31428104

A comma-separated list of every partition name in that column is the failure signal. For the parameterised case, MySQL prunes when the statement executes with bound values, so use the prepared-statement form to be sure:

PREPARE ev FROM 'SELECT count(*) FROM events WHERE occurred_at >= ? AND occurred_at < ?';
SET @a = '2026-08-01', @b = '2026-09-01';
EXPLAIN EXECUTE ev USING @a, @b\G

Operational note: MySQL’s EXPLAIN does not execute the statement, so unlike PostgreSQL’s EXPLAIN ANALYZE it is always safe to run against writes.

SRE tip: EXPLAIN ANALYZE exists in MySQL 8.0.18+ and reports actual timings, but the partitions column from plain EXPLAIN is the pruning check — the analyze form is for cardinality misestimates.

Step 5 — Turn the check into a test

A plan verified by hand stays verified until the next ORM upgrade. Encode the expectation instead:

# tests/test_pruning.py — fails when a query stops pruning
import re
import pytest

QUERIES = [
    ("monthly_count",
     "SELECT count(*) FROM events WHERE occurred_at >= %s AND occurred_at < %s",
     ("2026-08-01", "2026-09-01"),
     1),   # expected partitions touched
    ("tenant_recent",
     "SELECT id FROM events WHERE tenant_id = %s "
     "AND occurred_at >= %s AND occurred_at < %s LIMIT 50",
     (8842, "2026-08-01", "2026-09-01"),
     1),
]

CHILD_RE = re.compile(r"(?:Seq Scan|Index Scan|Bitmap Heap Scan)[^\n]*on (events_\d{4}_\d{2})")


def partitions_touched(cur, sql, params):
    cur.execute("EXPLAIN (ANALYZE, COSTS OFF, FORMAT TEXT) " + sql, params)
    plan = "\n".join(row[0] for row in cur.fetchall())
    touched = {
        m.group(1)
        for line, m in ((l, CHILD_RE.search(l)) for l in plan.splitlines())
        if m and "never executed" not in line
    }
    return touched


@pytest.mark.parametrize("name,sql,params,expected", QUERIES)
def test_query_prunes(db_cursor, name, sql, params, expected):
    touched = partitions_touched(db_cursor, sql, params)
    assert len(touched) == expected, (
        f"{name} touched {len(touched)} partitions ({sorted(touched)}), expected {expected}"
    )
Where the pruning assertion sits in the pipeline On every pull request, the pipeline starts a PostgreSQL service, applies migrations, seeds three months of partitions with a small number of rows each, and runs the pruning test. A query that stops pruning fails the build with the partition list it touched, so the regression is caught before merge rather than in production weeks later. pull requestany schema or query change migratereal partitioned DDL seed 3 partitions1k rows each — enough assert plan shapecount children in EXPLAIN pass → merge fail → names thepartitions it touched Row volume is irrelevant to this test — pruning is decided from bounds, not data — so the seed stays tiny and the whole check runs in a couple of seconds. That is what keeps it in the pipeline instead of being skipped for speed. Assert on the count of executed children, never on partition names: names change every month and the test would need editing forever. Counting keeps it stable across the calendar.

Operational note: The regular expression above deliberately ignores children annotated never executed, so the test passes for both plan-time and runtime pruning — the two forms are equally acceptable outcomes.

SRE tip: Run the same assertion as a scheduled job against production once a day, reading the top statements from pg_stat_statements. It catches pruning regressions introduced by data growth and statistics changes, which no pull request can predict.

Verification

Confirm the whole chain works by deliberately breaking it. Add a cast to the predicate and re-run the test:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM events WHERE occurred_at::date = '2026-08-15';

Expected output — every child scanned, which is precisely what the test must catch:

 Aggregate (actual time=4128.771..4128.772 rows=1 loops=1)
   ->  Append (actual time=0.031..4009.114 rows=1044118 loops=1)
         ->  Seq Scan on events_2024_09 events_1 (actual time=0.030..88.204 rows=0 loops=1)
               Filter: ((occurred_at)::date = '2026-08-15'::date)
         ->  Seq Scan on events_2024_10 events_2 (actual time=0.021..91.113 rows=0 loops=1)
         ... 34 more children, all executed ...

The test should fail with monthly_count touched 36 partitions, expected 1. If it passes, the assertion is not reading the plan correctly — fix the test before trusting it.

Failure mode table

Failure mode Root cause SRE mitigation
Plan verified in psql but production is slow the manual check used literals and a custom plan; production uses a bound parameter and a generic plan verify with plan_cache_mode = force_generic_plan, or capture and PREPARE the exact normalised statement from pg_stat_statements
Pruning test passes in CI, fails in production CI database has too few partitions for the plan shape to differ, or different statistics seed CI with the same number of partitions as production (rows can stay tiny) and refresh statistics after seeding
EXPLAIN ANALYZE on a write statement changes data ANALYZE executes the statement rather than simulating it wrap in BEGIN; ... ROLLBACK; and expect the locks and WAL to be real; never run it against a write path during peak

FAQ

Is EXPLAIN ANALYZE safe to run in production?

EXPLAIN ANALYZE executes the statement, so it is safe for SELECT and unsafe for anything that writes unless you wrap it in a transaction you roll back. For write statements use BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; and be aware that the work is really performed — locks are taken and WAL is written before the rollback.

Why does EXPLAIN show partitions that were never executed?

That is runtime pruning working as designed. The plan was built before the parameter value was known, so every child appears in it, and execution skipped the ones that could not match. A child annotated (never executed) read no data. What you do not want to see is children with real row counts and actual times, which means no pruning happened at all.

How do I check pruning for the SQL my ORM actually sends?

Capture the statement from the database rather than from the application: enable log_statement or read pg_stat_statements, take the exact text including the parameter placeholders, then PREPARE it and run EXPLAIN with plan_cache_mode set to force_generic_plan. Retyping the query with literals in psql tests a different planner path and is the most common reason a pruning bug survives review.

Back to Partition Pruning Query Planning