Partitioning a large table is usually sold on one benefit: queries that filter on the partition key only scan the partitions that could possibly match, instead of the whole table. That benefit is called pruning, and it's opt-in in the sense that nothing stops you from writing a query that defeats it completely while still returning correct results — you just lose the entire point of partitioning and nobody tells you.
Here's how to tell whether it's actually happening, and the three ways people accidentally turn it off.
Checking whether pruning fired
Run EXPLAIN (you don't even need ANALYZE for this part — pruning that happens at plan time shows up without running the query):
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';
If pruning worked, you'll see only the relevant child partitions listed under the Append node:
Append
-> Seq Scan on events_2026_09 events_1
Filter: ((created_at >= ...) AND (created_at < ...))
If pruning didn't work, every partition shows up under the Append node regardless of the filter, and you're paying the planning and execution cost of scanning partitions that structurally cannot contain a matching row.
1. Wrapping the partition key in a function or cast
This is the single most common cause. If the table is partitioned on created_at (a timestamp) and you write:
WHERE created_at::date = '2026-09-15'
the planner cannot prove which partitions the cast could affect at plan time in general, so it falls back to scanning everything. Same story with date_trunc('day', created_at) = ..., or comparing a timestamp column against a value that needs an implicit cast because you passed a string in the wrong format from your ORM. The fix is always the same shape: move the transformation to the other side of the comparison, so the partition key column appears bare.
-- defeats pruning
WHERE date_trunc('day', created_at) = '2026-09-15'
-- prunes correctly
WHERE created_at >= '2026-09-15' AND created_at < '2026-09-16'
2. Parameterized queries on old Postgres versions
Before Postgres 12, pruning only happened at plan time, using whatever literal values were visible in the query text. A prepared statement with a bind parameter — exactly what every ORM and connection pool uses by default — had no literal value available at planning time, so pruning silently didn't happen for parameterized queries, full stop, no matter how well you wrote the WHERE clause. Postgres 12 added runtime pruning, which re-evaluates which partitions are needed after parameters are bound, right before execution starts. If you're still fighting this on an older major version, the practical workaround is forcing a custom plan per execution (plan_cache_mode = force_custom_plan) rather than the generic plan PG tends to switch to after five executions of the same prepared statement — but the real fix is upgrading, because this is exactly the kind of thing that gets fixed properly upstream rather than patched around forever.
3. Joins that hide the partition key behind a variable
SELECT *
FROM events e
JOIN report_windows w ON e.created_at >= w.start_at AND e.created_at < w.end_at;
Here the bound on created_at comes from another table's row, not a literal or even a simple parameter — the planner doesn't know at plan time what w.start_at will be for any given row, so static pruning can't help. Runtime pruning (PG11+ for this specific pattern, inside nested loops) can still kick in per iteration of the outer loop if the plan shape is a nested loop with the partitioned table on the inner side — check for Subplans Removed in the EXPLAIN ANALYZE output to confirm it actually happened rather than assuming it did because the feature theoretically exists. If the plan comes out as a hash or merge join instead of a nested loop, you likely won't get per-row pruning at all, and restructuring the query to pass explicit bounds (even via a CTE that pre-computes the min/max window) can be the difference between scanning 3 partitions and scanning 300.
A five-minute audit
- Pull your top 10 queries by total time against the partitioned table from
pg_stat_statements. EXPLAINeach with representative literal values substituted in for any parameters.- Count the partitions under each
Append/MergeAppendnode. If it's "all of them" for a query that clearly only needs a narrow date range, you've found a function-wrapped or implicitly-cast predicate. - For queries that only show the problem with real bind parameters (not literals), check
pg_settingsforplan_cache_modeand confirm your Postgres major version actually supports runtime pruning for the join shape you're using.
The part nobody mentions: pruning failures don't throw errors or even show up as obviously wrong results — the query is still correct, just slow in a way that scales with total table size instead of partition size. That's exactly why it tends to go unnoticed until the table is big enough to hurt, at which point someone usually blames the partitioning scheme itself rather than the one predicate that quietly opted out of it.