What this usually means
Partition pruning fails when the query planner cannot statically determine which partitions satisfy the filter at planning time. This happens when the filter expression is not a simple constant comparison against the partition key, or when the query uses parameters, joins, or subqueries that hide the constant. Another common cause is a type mismatch between the partition key and the filter value (e.g., text vs. integer) that prevents the planner from matching the partition bounds. Also, certain partition strategies (e.g., list partitioning with DEFAULT) or complex WHERE clauses (OR, NOT, function calls on the partition key) defeat pruning entirely.
The first ten minutes — establish facts before touching code.
- 1Run EXPLAIN (ANALYZE, BUFFERS) on the query with literal filter values. Look for 'Partition Pruned: X' in the output.
- 2Check partition key data type with \d+ table_name; compare to filter value type (e.g., '2024-01-01' vs. '2024-01-01'::date).
- 3Test query with constant literal instead of parameter: if pruning works with literal but not with parameter, it's a planner limitation.
- 4Run EXPLAIN (SUMMARY) for a parameterized query using PREPARE/EXECUTE to see if pruning happens at execution time.
- 5Review partition range/list definitions for gaps, overlapping ranges, or a DEFAULT partition that may absorb all rows.
- 6Check PostgreSQL version: pruning improved significantly in v11 (declarative partitioning), v12 (list pruning), v13 (partition-wise join).
The specific files, logs, configs, and dashboards that usually own this bug.
- searchEXPLAIN (ANALYZE, BUFFERS) output — look for 'Subplans Removed' or 'Partition Pruned' lines.
- searchPostgreSQL server log with log_planner_stats and log_executor_stats enabled to see pruning decisions.
- searchpg_partitions or pg_partitioned_table catalog views for partition key and bounds.
- searchApplication code: parameterized query patterns, ORM-generated SQL (e.g., Rails ActiveRecord, Hibernate).
- searchpg_stat_user_tables — check seq_scan vs. idx_scan on each partition to see if pruning is happening.
- searchConnection pool configuration: prepared statement caching may cause generic plans that skip pruning.
Practical causes, not theory. These are the things you will actually find.
- warningFilter value is a function call or expression (e.g., WHERE date_col = now()::date) that planner cannot fold to constant at planning time.
- warningParameterized query (PREPARE/$1) leads to a generic plan that prunes no partitions; planner chooses generic plan for small tables.
- warningType mismatch: partition key is date but filter is string '2024-01-01' without explicit cast.
- warningPartition key part of a composite index but not used in filter; planner may not prune if index-only scan is possible.
- warningDEFAULT partition exists; planner may decide to scan all partitions if DEFAULT could match.
- warningQuery uses OR conditions that span multiple partitions, forcing an Append of all partitions.
- warningPartition key is a computed column or expression, not a base column.
Concrete fix directions. Pick the one that matches your root cause.
- buildReplace function calls with literal values: WHERE date_col >= '2024-01-01' AND date_col < '2024-01-02'.
- buildUse explicit type cast: WHERE date_col = '2024-01-01'::date.
- buildFor parameterized queries, force custom plan by using EXECUTE with literal or set plan_cache_mode = force_custom_plan.
- buildRemove DEFAULT partition if possible, or ensure it is empty and never matched.
- buildRewrite OR conditions as UNION ALL with separate literal branches.
- buildUpgrade PostgreSQL to a version with better partition pruning (v12+).
- buildUse partition-wise join for queries joining two partitioned tables (set enable_partitionwise_join = on).
A fix you cannot prove is a guess. Close the loop.
- verifiedRun EXPLAIN (ANALYZE, BUFFERS) and confirm 'Partition Pruned: X' matches expected partitions.
- verifiedCompare query runtime before and after fix; expect 10-100x improvement for large tables.
- verifiedCheck pg_stat_user_tables for each partition: seq_scan should drop on non-matching partitions.
- verifiedUse EXPLAIN (SUMMARY) on parameterized queries to see if pruning occurs at execution time.
- verifiedTest with edge cases: boundary values, NULLs, and out-of-range values to ensure pruning is correct.
Things that make this bug worse or harder to find.
- warningAdding more indexes instead of fixing pruning; indexes on each partition do not help if all partitions are scanned.
- warningUsing DEFAULT partition as a catch-all; it often disables pruning for all queries.
- warningAssuming that partition pruning works for all query patterns; it does not for subqueries, CTEs, or joins.
- warningSetting enable_partition_pruning = off (default is on) — check with show enable_partition_pruning;.
- warningConfusing partition pruning with partition elimination in parallel queries; they are different mechanisms.
- warningNot checking PostgreSQL version-specific documentation for pruning limitations.
The 10x Query Slowdown After Partitioning
Timeline
- 09:15PagerDuty alert: 'orders API endpoint p99 latency > 5s' (normally 200ms).
- 09:18Checked slow query log; found a query on orders table taking 12s.
- 09:22Orders table was partitioned by created_at into monthly partitions last night.
- 09:25EXPLAIN on the query: Sequential Scan on all 12 partitions, no pruning.
- 09:30Identified filter: WHERE created_at >= $1 AND created_at < $2 (parameterized via ActiveRecord).
- 09:35Tested with literal dates: pruning worked, only 1 partition scanned.
- 09:40Found generic plan in pg_prepared_statements; plan_cache_mode default.
- 09:45Set plan_cache_mode = force_custom_plan via ALTER DATABASE; restarted app servers.
- 09:50Verified: query now prunes correctly, p99 back to 300ms.
We had been running a monolithic Rails app on a single orders table for years. To handle growth, we partitioned by month on created_at. The migration ran overnight, and by morning, the p99 latency for order queries jumped from 200ms to over 5 seconds. My first thought was indexing, but each partition had the same indexes as the original table. Something else was wrong.
I grabbed the slowest query from pg_stat_activity: a typical range scan for recent orders. EXPLAIN (ANALYZE, BUFFERS) showed a Sequential Scan on all 12 partitions. No 'Partition Pruned' line. The filter used parameterized placeholders ($1, $2) from ActiveRecord's prepared statement cache. I tested with literal dates and pruning worked instantly—only one partition scanned.
The root cause was PostgreSQL's generic plan caching. For prepared statements with parameters, the planner may generate a generic plan that assumes all partitions could match. Since our app used prepared statements by default, every query got the generic plan. I forced custom plans by setting plan_cache_mode = force_custom_plan database-wide. After restarting the app servers, pruning kicked in, and latency dropped back to normal. I also added a monitoring check for generic plans on partitioned tables.
Root cause
PostgreSQL's generic plan for parameterized queries disabled partition pruning because the planner could not determine which partition would satisfy the filter at planning time.
The fix
Set plan_cache_mode to force_custom_plan (or use EXECUTE with literals) so that the planner generates a custom plan per parameter value, enabling pruning.
The lesson
Partition pruning does not work with generic plans. Always test parameterized queries separately, and consider plan_cache_mode for apps that use prepared statements heavily.
The PostgreSQL planner performs partition pruning at planning time by comparing filter clauses against partition bounds. It only prunes when the filter is a simple comparison (>, <, =, >=, <=) between a partition key column and a constant or a stable expression that can be folded to a constant at planning time. The constant must have the same data type as the partition key, or an implicit cast must be available.
For list partitioning, the planner builds a set of allowed values; for range partitioning, it computes intersection with partition ranges. Subquery or function-based filters are typically not folded early enough. Also, the planner prunes only for base tables, not for views or subquery scans in certain cases.
One of the most common non-obvious causes is a type mismatch between the partition key and the filter value. For example, a partition key defined as date but a filter string '2024-01-01' without an explicit cast. PostgreSQL will cast the string to date for the comparison, but the planner may not recognize that the cast is constant and thus fails to prune. The fix is to always cast literals explicitly: WHERE date_col = '2024-01-01'::date.
Similarly, integer vs. bigint mismatches can occur. Use \d+ to verify the partition key type and ensure the filter value matches exactly. In ORM-generated queries, the ORM may pass a string for a date column; check the query logs for implicit casts.
When a query uses parameters (e.g., via PREPARE or a driver that uses binary protocol), PostgreSQL may generate a generic plan that is reused across executions. The generic plan does not know the actual parameter values, so it cannot prune any partitions. This is a deliberate trade-off: generic plans avoid planning overhead but lose pruning.
To force a custom plan per execution, set plan_cache_mode = force_custom_plan (PostgreSQL 12+) or use EXECUTE with literal values. Alternatively, you can use the pg_hint_plan extension to add a hint like /*+ Set(plan_cache_mode force_custom_plan) */. Note that for very large tables, custom plans are almost always better despite planning overhead.
Adding a DEFAULT partition to a list or range partition set is often used as a catch-all for data that does not match any explicit partition. However, the presence of a DEFAULT partition can disable pruning for queries that might match it, because the planner must check if the filter could also match the DEFAULT partition. In many cases, the planner gives up and scans all partitions.
Best practice: avoid DEFAULT partitions unless absolutely necessary. If you must have one, ensure it is empty and consider using triggers to prevent insertion of unmatched data. For range partitions, ensure your ranges are contiguous and cover all expected values.
Frequently asked questions
Why does partition pruning work with literals but not with parameters?
PostgreSQL's planner needs to know the exact value at plan time to prune. Parameters are unknown until execution, so the planner cannot use them for pruning unless it generates a custom plan per execution. By default, PostgreSQL may use a generic plan that does not prune. To fix, set plan_cache_mode = force_custom_plan or use dynamic SQL with literals.
How do I check if partition pruning is happening in a query?
Run EXPLAIN (ANALYZE, BUFFERS) on your query. Look for lines like 'Partition Pruned: X' or 'Subplans Removed: Y'. If you see only 'Append' with all partitions listed, pruning is not occurring. You can also check the number of partitions scanned in the 'loops' count.
Can partition pruning work with JOINs?
Yes, but only if partition-wise join is enabled (enable_partitionwise_join = on) and both tables are partitioned on the join key. Even then, pruning on the inner side may be limited. For best results, ensure the join condition includes a filter on the partition key.
Does partition pruning work with subqueries or CTEs?
Generally, no. The planner cannot push filter conditions into subqueries or CTEs before planning the outer query. Rewrite the query to push the filter into the subquery, or use a lateral join if applicable.
What PostgreSQL version introduced partition pruning?
Basic partition pruning for declarative partitioning was introduced in PostgreSQL 10 (range) and 11 (list). Significant improvements came in v12 (pruning for list with multiple bounds) and v13 (pruning for partitioned tables in partition-wise join). Always check the release notes for your version.