A query filters on the very column a table is partitioned by, yet the plan still reads every partition. What common mistakes in how the predicate is written prevent pruning, and how would you rewrite them?

## Why this is the most common partitioning bug Pruning is a proof, and proofs are fragile. The optimizer must show that no row in a partition can satisfy the predicate, and it can only do that when the predicate is a comparison between the **raw partition key** and something it can evaluate. Any transformation of the key breaks the link between the predicate and the declared bounds, and the plan quietly falls back to reading everything. It is quiet because the results are still correct, so the bug shows up only as latency. ## Wrapping the key in a function or cast The classic: ``` WHERE date_trunc('day', occurred_at) = DATE '2026-03-14' ``` The bounds are declared on `occurred_at`, not on `date_trunc('day', occurred_at)`, so the planner has no theorem connecting them, and every partition stays in the plan. The same applies to `cast(occurred_at AS date)`, `to_char(occurred_at, 'YYYY-MM')`, `occurred_at AT TIME ZONE 'UTC'`, `upper(region_code)`, and arithmetic like `id / 1000`. The rewrite is always the same shape: express the filter as a half-open range on the untouched column. ``` WHERE occurred_at >= TIMESTAMPTZ '2026-03-14 00:00+00' AND occurred_at < TIMESTAMPTZ '2026-03-15 00:00+00' ``` Half-open ranges also avoid boundary bugs with fractional seconds, and they match how range bounds are declared (inclusive lower, exclusive upper). ## Type mismatches and implicit casts If the literal's type does not match the key's, the engine may cast one side. Casting the literal is harmless; casting the **column** is fatal, because that is again a function on the key. This bites when a timestamptz key is compared against a string from an ORM, or when a numeric literal meets a bigint key. It also matters for correctness: comparing a timestamptz to a bare date literal resolves in the session time zone, so a query run from a differently configured client can silently address a different day. Bind the parameter with the right type. ## Filtering on a correlated but different column `created_at` and `occurred_at` may be within milliseconds of each other, and a human knows the query means the same thing, but the optimizer will not infer it. Only a predicate on the actual partition key prunes. If the application naturally filters on the other column, either partition by that column instead or have the query supply both. ## Operator versus partitioning strategy - **Range** partitioning prunes for equality, ranges, BETWEEN, IN, and prefix-anchored patterns that the planner can convert to a range. - **List** partitioning prunes for equality and IN, and for NOT IN by exclusion. - **Hash** partitioning prunes **only** for equality (and IN) on the key, because hashing destroys order. `WHERE customer_id > 5000` on a hash-partitioned table reads every partition, by construction rather than by mistake. With a composite partition key, predicates must generally constrain the leading column for pruning to be effective, the same way a composite index needs its leading column. ## No constant to compare against Sometimes there is nothing wrong with the predicate, there simply is no value at planning time: - the key value comes from a join to another table; - it comes from a scalar subquery; - it arrives as a bind parameter in a prepared statement that has settled on a reusable generic plan. Modern PostgreSQL handles several of these at execution time rather than plan time, so the plan may list every partition while the executor skips most of them. But some shapes get no help at all, notably a join whose driving side is scanned by a hash join. Practical fixes: pass the literal range in the query in addition to the join, restrict the query to a materialised value computed in the application, or, for co-partitioned tables, enable partition-wise joins. ## Other pruning killers worth naming - **OR across unrelated columns**: `WHERE occurred_at >= x OR status = 'urgent'` cannot prune, because rows matching the second branch could live anywhere. An OR of two ranges on the key does prune. - **Volatile functions** in the predicate: the planner cannot fold them to a constant. Note that `now()` and `current_timestamp` are stable within a statement and do allow pruning in PostgreSQL, whereas `random()` or a volatile user-defined function do not. - **Views and functions that hide the filter**, where the effective predicate is applied above the partitioned scan rather than pushed into it. ## Verify, do not assume Every item above is checkable in seconds by reading the plan: count the partition children under the Append node and compare against the partitions you expect. Make that check part of reviewing any query against a large partitioned table.