Fixing Queries That Defeat Partition Pruning

Once verification has found a query that reads every partition, this guide is the catalogue of rewrites — one per failure shape, each with the plan before and after. It belongs to Partition Pruning & Query Planning and assumes the partition key itself is sound; if the key does not match the query pattern at all, the problem is upstream in partition key selection.

Prerequisites

Step 1 — Replace casts and functions with a half-open range

The most common failure by a wide margin. Any expression wrapping the partition key makes it opaque:

-- unprunable: the cast wraps the column
SELECT count(*) FROM events WHERE occurred_at::date = DATE '2026-08-15';

-- prunable: bare column, half-open range
SELECT count(*) FROM events
WHERE  occurred_at >= TIMESTAMPTZ '2026-08-15 00:00:00+00'
  AND  occurred_at <  TIMESTAMPTZ '2026-08-16 00:00:00+00';

The same transformation covers date_trunc, to_char, EXTRACT(month FROM ...) and COALESCE(occurred_at, ...). Every one of them has a range equivalent, because a function of a timestamp that partitions cleanly is by definition a mapping from an interval.

Operational note: Half-open ranges (>= start AND < end) are not stylistic. BETWEEN is inclusive at both ends, so consecutive BETWEEN ranges double-count the boundary instant — a real source of duplicated rows in daily reports.

DBA tip: Watch for the cast appearing on the bound rather than the column: occurred_at >= '2026-08-15'::date is fine, because the cast is applied to the constant. Only casts on the column side break pruning.

Why the side the cast sits on changes everything In the broken form the cast is applied to the column, so the planner sees an expression whose value it cannot compare with partition bounds and must assume every partition might match. In the fixed form the cast is applied to the literal, producing a constant of the key's type, and the planner compares that constant directly against each partition's declared bounds. Cast on the column — opaque Cast on the literal — comparable WHERE occurred_at::date = '2026-08-15' WHERE occurred_at >= '2026-08-15'::timestamptz AND occurred_at < '2026-08-16'::timestamptz planner sees: f(column) = constant → cannot compare f(column) with bounds planner sees: column ≥ const AND column < const scanscanscanscanscan skipskipscanskipskip 36 partitions read · 4.1 s 1 partition read · 41 ms Both queries return the same rows. The planner is not being conservative for safety — it genuinely cannot know which partitions a function's output maps to without evaluating the function against every row, which is the scan it is trying to avoid. The rule reduces to one sentence: nothing may touch the partition key column on the left of the comparison.

Step 2 — Fix parameter type mismatches at the driver

A bigint partition key compared against a string parameter forces an implicit cast, and which side the cast lands on depends on the driver:

# psycopg: the parameter arrives as text, PostgreSQL casts the column
cur.execute("SELECT * FROM events WHERE tenant_id = %s", ("8842",))   # slow

# fixed: bind the correct Python type
cur.execute("SELECT * FROM events WHERE tenant_id = %s", (8842,))     # prunes

In Java, the equivalent is setString versus setLong; in Go, passing a string where the column is int64. The symptom is identical to the cast case and the diagnosis is the same — EXPLAIN shows a filter containing a cast of the column.

Operational note: ORMs usually get this right for model fields and usually get it wrong for raw SQL fragments and filter values arriving from HTTP query strings, which are strings by default.

DBA tip: Add an explicit cast on the parameter side ($1::bigint) rather than fixing every call site. It is a one-line change that makes the correct behaviour independent of the caller’s type discipline.

Step 3 — Rewrite OR chains as ranges or UNION ALL

An OR between predicates on different columns disables pruning even when one branch is perfectly prunable:

-- unprunable: the planner must satisfy either branch, so every partition may match
SELECT * FROM events
WHERE  (occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01')
   OR  external_ref = 'REF-99182';

-- prunable: each branch planned independently
SELECT * FROM events
WHERE  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01'
UNION ALL
SELECT * FROM events
WHERE  external_ref = 'REF-99182'
  AND  occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

The second form is not merely a rewrite — it forces a decision. The OR version was implicitly asking to search all history for that reference, and the UNION ALL version makes that scope explicit. If the reference lookup genuinely needs the whole table, it needs its own lookup table mapping reference to date.

Operational note: IN lists on the partition key prune fine (occurred_at IN (...) is a set of equality comparisons). It is OR across different columns that breaks pruning.

SRE tip: A UNION ALL of prunable branches can be dramatically faster than one OR, but it also doubles the statement count in pg_stat_statements. Give each branch a comment tag so they stay attributable.

Step 4 — Push the range onto the partitioned side of a join

Joins lose pruning when the filter lives on the other table:

-- unprunable: the date filter is on orders, not on the partitioned events table
SELECT e.*
FROM   events e
JOIN   orders o ON o.id = e.order_id
WHERE  o.placed_at >= '2026-08-01' AND o.placed_at < '2026-09-01';

-- prunable: the same range is stated on the partition key, redundantly but usefully
SELECT e.*
FROM   events e
JOIN   orders o ON o.id = e.order_id
WHERE  o.placed_at   >= '2026-08-01' AND o.placed_at   < '2026-09-01'
  AND  e.occurred_at >= '2026-08-01' AND e.occurred_at < '2026-09-02';

The extra day on the events side accounts for events that arrive shortly after their order, and it is a business decision that has to be made explicitly. That is the deeper point: the join was always relying on an unstated assumption about how far apart the two timestamps can be, and pruning forces it into the open.

Operational note: enable_partitionwise_join = on allows the planner to join matching partitions pairwise when both sides are partitioned identically. It is off by default because it increases planning time, and it is worth turning on for reporting workloads.

DBA tip: When the two timestamps can be arbitrarily far apart, the join is asking a question partitioning cannot help with. Denormalise the partition key onto the child table so it can be filtered directly.

A filter on the joined table does not reach the partition bounds The filter narrows the orders table to one month, but the join condition is on order id, which carries no information the planner can compare against partition bounds on occurred_at. Every events partition therefore remains a candidate. Restating the time range on the events side gives the planner the comparison it needs. orders WHERE placed_at in August 41,882 rows survive join on order_id an id carries no time information all 12 events partitions remain candidates AND e.occurred_at >= '2026-08-01' AND e.occurred_at < '2026-09-02' scan pruned The redundant-looking predicate is the whole fix. It also documents an assumption the query was always making: that an event follows its order within a day. If that is not true, the schema needs the partition key on both tables.

Step 5 — When the application cannot change, move the fix into the schema

Sometimes the offending SQL comes from a reporting tool or a vendor integration. Two schema-side options preserve pruning without touching the caller.

Five predicate shapes and the rewrite for each A cast on the column becomes a half-open range. A function around the column becomes a half-open range. A mismatched parameter type becomes an explicitly cast parameter. An OR across different columns becomes a UNION ALL of prunable branches. A filter that lives on the joined table gains a redundant range on the partitioned side. Unprunable shapeRewrite occurred_at::date = D occurred_at >= D AND occurred_at < D+1 date_trunc('month', occurred_at) = M occurred_at >= M AND occurred_at < M+1mo tenant_id = '8842' (text param) tenant_id = $1::bigint (time range) OR external_ref = R UNION ALL of two prunable branches JOIN o ON … WHERE o.placed_at IN range … AND e.occurred_at IN the same range Every rewrite has the same shape: get the partition key alone on the left of a comparison against a constant of its own type.

A stored generated column lets date-style predicates prune because the generated column is the partition key:

CREATE TABLE events (
    id           bigint GENERATED BY DEFAULT AS IDENTITY,
    occurred_at  timestamptz NOT NULL,
    occurred_on  date GENERATED ALWAYS AS ((occurred_at AT TIME ZONE 'UTC')::date) STORED,
    payload      jsonb,
    PRIMARY KEY (id, occurred_on)
) PARTITION BY RANGE (occurred_on);

A view can also normalise the predicate shape for a specific consumer, though it only helps when the tool filters on the view’s exposed column rather than constructing arbitrary SQL.

Operational note: The generated column costs storage on every row and must be IMMUTABLE — which is why the expression pins the time zone explicitly rather than depending on the session’s TimeZone setting.

SRE tip: Adding a generated column to an existing partitioned table requires a rewrite of every partition. Do it one detached partition at a time using the online repartitioning workflow rather than as a single ALTER TABLE.

Verification

After each rewrite, confirm the change with the same command and compare the child count:

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

Expected output — one child, and a buffer count consistent with one day rather than three years:

 Aggregate (actual time=3.114..3.115 rows=1 loops=1)
   Buffers: shared hit=41 read=1088
   ->  Index Only Scan using events_2026_08_occurred_at_idx on events_2026_08 events
         (actual time=0.028..2.401 rows=1044118 loops=1)
         Index Cond: ((occurred_at >= '2026-08-15 00:00:00+00') AND (occurred_at < '2026-08-16 00:00:00+00'))

Then confirm the row count is unchanged from the original query. A rewrite that prunes and returns different rows is a bug, and the boundary instant is where it will be.

Failure mode table

Failure mode Root cause SRE mitigation
Rewrite prunes but returns extra or missing rows inclusive/exclusive boundary changed, typically converting BETWEEN to a half-open range assert row-count parity between old and new forms across a month of data before deploying
Pruning works in staging, not in production production uses a prepared statement and a generic plan; staging used literals verify with plan_cache_mode = force_generic_plan, and pin custom plans for that workload if needed
Generated column ALTER TABLE locks the table for hours adding a stored generated column rewrites every existing row on every partition apply per partition: detach, rewrite, reattach, one child at a time during low traffic

FAQ

Can an index make an unprunable query fast enough?

It can hide the symptom on a small table and never fixes the cause. With 36 partitions, an index turns one sequential scan into 36 index probes plus 36 lock acquisitions and 36 planner considerations. The response time improves enough to stop the complaints and then degrades again with every partition added, which is why the predicate rewrite is the real fix.

Does a generated column let me keep querying by date?

Yes, and it is the cleanest fix when application code cannot be changed. Partition on a stored generated column such as occurred_on date GENERATED ALWAYS AS (occurred_at::date) STORED, and predicates written against occurred_on prune directly because the column itself is the partition key. The cost is one extra column per row and the fact that the generated column must be immutable.

Why does my join stop pruning when I add a second table?

Pruning needs a predicate on the partitioned table’s key, and a join condition to another table is not one unless the value can be resolved before or during execution. Filtering the other table by date and joining on a foreign key gives the planner nothing to compare against partition bounds. Push the same time range onto the partitioned side of the join explicitly, even though it looks redundant.

Back to Partition Pruning Query Planning