Why Did My Partitioned Query Scan Every Partition?

You partitioned the table so a query would touch one partition instead of two hundred. The query got slower. The plan shows every partition scanned, and nobody changed the schema.

Partitioning buys you nothing on its own. The payoff comes from pruning — the planner proving, from the WHERE clause, that a partition cannot contain a matching row and skipping it. When pruning works, a query against a table split into daily partitions reads one child table. When it silently fails, it reads all of them, and you have added planning overhead and lock acquisition on top of the full scan you were trying to avoid. The failure is quiet because nothing errors. The rows come back correct. Only the plan tells you what happened.

This is a PostgreSQL 17 walk-through: reproduce the all-partitions scan, read it in EXPLAIN, and fix the two causes that account for almost every case I see — an expression wrapped around the partition key, and a prepared statement that has switched to a generic plan.

Prove it first: read the plan, not the timing

Start with a range-partitioned table and a query that filters on the key. The honest test is the plan, not a stopwatch — wall-clock timing on a warm cache will mislead you.

-- Range-partitioned by day on a timestamptz key
CREATE TABLE events (
    id        bigint      GENERATED ALWAYS AS IDENTITY,
    occurred  timestamptz NOT NULL,
    payload   jsonb
) PARTITION BY RANGE (occurred);

-- Daily children exist: events_2026_07_01 ... events_2026_07_31

-- Pruning works: a half-open range on the raw key
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM   events
WHERE  occurred >= TIMESTAMPTZ '2026-07-15 00:00+00'
  AND  occurred <  TIMESTAMPTZ '2026-07-16 00:00+00';

--                    QUERY PLAN
-- Aggregate
--   ->  Seq Scan on events_2026_07_15 events   -- one partition

One child in the plan. Partitions removed at plan time do not appear in EXPLAIN at all — they are gone before the plan is printed. That is the state you want. Now break it.

Cause one: an expression around the partition key

The single most common cause is wrapping the key in a function or a cast. Pruning is driven by the constraints implied by the partition bounds; the planner can only match those bounds against the bare key column. The moment you write date(occurred) or occurred::date, the planner is no longer comparing the partition key — it is comparing the result of a function it cannot see inside, so it keeps every partition.

-- Same intent, but the function defeats pruning
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM   events
WHERE  date(occurred) = DATE '2026-07-15';

--                    QUERY PLAN
-- Aggregate
--   ->  Append
--         ->  Seq Scan on events_2026_07_01 events_1
--         ->  Seq Scan on events_2026_07_02 events_2
--         ...                                          -- every partition
--         ->  Seq Scan on events_2026_07_31 events_31

The Append over all thirty-one children is the tell. The fix is to leave the column bare and move the transformation to the literal: express the predicate as a half-open range on occurred, exactly as in the working query above. This is the same discipline that makes an index on the column usable — WHERE f(col) = x cannot use a plain index on col either, and cannot prune.

A quieter variant of the same fault is a type mismatch. If the key is timestamptz and you compare against a value whose implicit cast is not a member of the partitioning operator family, the planner may decline to prune. Match the literal to the key type — TIMESTAMPTZ against a timestamptz key — and confirm in the plan rather than assuming the cast was free. Getting the predicate shape right at the source is the same instinct that keeps reporting cheap: BI should be a query, not a project, and a query the engine can prune is what makes that affordable.

Cause two: the prepared statement went generic

This one is nastier because the query text is correct and it works in psql. It fails only under an ORM or a driver that uses server-side prepared statements. When you PREPARE a statement, PostgreSQL builds a custom plan for the first executions using the actual parameter values — so plan-time pruning happens. After at least five executions it compares the average custom plan cost against a generic plan built without knowing the values, and if the generic plan is not estimated to be meaningfully worse it switches to it and stops re-planning.

A generic plan cannot do plan-time pruning — the values are unknown at plan time — so all partitions reappear in the plan. PostgreSQL then falls back to execution-time pruning, and here the EXPLAIN signature changes.

PREPARE q(timestamptz, timestamptz) AS
SELECT count(*) FROM events WHERE occurred >= $1 AND occurred < $2;

-- Run it six times, then inspect the sixth
EXPLAIN (ANALYZE, COSTS OFF)
EXECUTE q(TIMESTAMPTZ '2026-07-15 00:00+00',
          TIMESTAMPTZ '2026-07-16 00:00+00');

--                    QUERY PLAN
-- Aggregate (actual rows=1 ...)
--   ->  Append (actual rows=... loops=1)
--         Subplans Removed: 30            -- run-time pruning
--         ->  Seq Scan on events_2026_07_15 (actual rows=... )

Subplans Removed: 30 means execution-time pruning saved you — the other partitions show as (never executed) if they are printed at all. That is often fine. It becomes a problem when there are hundreds of partitions: building and holding a generic plan over all of them costs real planning time and memory on every execute, and the cost model that chose the generic plan does not account for the pruning it will later do, so it can pick the worse option. When you see the plan flip to generic on a many-partition table, force custom plans:

-- Re-plan on every EXECUTE so plan-time pruning returns.
-- Valid values: auto (default), force_custom_plan, force_generic_plan
SET plan_cache_mode = force_custom_plan;

You give up the cached-plan saving, but you get plan-time pruning back on every call. Set it at the session or role level for the workloads that hit wide partitioned tables, not globally — most statements are better off with auto. It is the same trade-off you weigh whenever a retry or a re-plan buys correctness at a small, bounded cost, the kind of deliberate choice behind making POST safe to retry.

Make the regression visible before it ships

Both failures are invisible in application logs and obvious in the plan. The cheap insurance is to assert on the plan, not the latency: capture EXPLAIN (COSTS OFF) for your hot partitioned queries in a test and fail the build if an Append over more than one partition appears where you expect pruning. That turns a silent full scan into a caught regression, and it is exactly the kind of signal worth watching in production alongside your delivery and reliability metrics — a query that quietly stopped pruning is a change-failure waiting to page someone.

Partitioning is not a performance feature. Pruning is. If you cannot see the pruning in the plan, you do not have it — you have a more complicated table that reads slower than the one you started with.