Partition Pruning: The Optimization That Vanishes When You Touch the Key

Partition pruning is the optimizer trick that makes partitioning worth doing. The planner inspects the query's WHERE clause, compares it against each partition's constraint, and skips any partition whose range cannot possibly satisfy the predicate. A 24-month logs table with monthly partitions can answer a one-day query by touching one partition out of 24.

Pruning has rules. The planner needs to see the partition key directly in the predicate. List partitioning needs the explicit value. Range partitioning needs a comparison against a constant the planner can fold at plan time. The moment you wrap the column in a function call, the planner stops being able to reason about it and falls back to a full scan across every partition.

The trap is that the query still returns the correct result. The optimization is silent. Nothing logs a warning. The only signal is that the query is slow, and unless you run EXPLAIN, you have no idea the planner gave up.

The production failure I saw: a logs table was range-partitioned by event_date, one partition per month, two years of history. The on-call dashboard wanted "errors in April 2026," so a junior engineer wrote WHERE EXTRACT(month FROM event_date) = 4 AND EXTRACT(year FROM event_date) = 2026. The query was correct. The planner could not prove that EXTRACT(month FROM event_date) = 4 excluded any partition, because EXTRACT is opaque to the constraint exclusion logic. So the planner read all 24 partitions, decompressed every page, and returned the right answer in eight minutes. Eight minutes is forever when the dashboard auto-refreshes.

The fix was a one-line rewrite: WHERE event_date >= '2026-04-01' AND event_date < '2026-05-01'. Same result, exactly one partition scanned, query dropped to 90 ms. The team also added a CI lint that flagged any query with a function call wrapped around a known partition column, because once you have been burned once you do not want to be burned again.

Related traps that bite the same way. Implicit type casts: comparing a timestamptz column to a text literal forces a cast that hides the partition key. Parameterized queries where the driver substitutes the value too late for the planner to fold. Views that join across partitioned and non-partitioned tables without preserving the partition predicate.

Whenever a partitioned table query is slow, the first thing to run is EXPLAIN (ANALYZE, BUFFERS). If the output lists every partition under the scan node, the planner is not pruning. The query is wrong, even if the result is right.