PostgreSQL caches query execution plans for prepared statements. Sometimes the cached plan is suboptimal for different parameter values — leading to plan cache pollution and slow queries. Here is how to manage it.
How PostgreSQL Plan Caching Works
Prepared statements go through two phases:
- First 5 executions: PostgreSQL generates a custom plan using actual parameter values
- After 5 executions: PostgreSQL switches to a generic plan that ignores parameter values
The generic plan can be catastrophically wrong when data is highly skewed.
Detect Plan Cache Issues
sql
-- Check prepared statement plans
SELECT name, statement, parameter_types, from_sql
FROM pg_prepared_statements;
-- Use pg_stat_statements to find queries with high variability
SELECT query,
calls,
mean_exec_time,
max_exec_time,
stddev_exec_time
FROM pg_stat_statements
WHERE stddev_exec_time > mean_exec_time -- high variance = plan instability
ORDER BY max_exec_time DESC
LIMIT 10;plan_cache_mode Setting
sql
-- Force custom plans for a session (avoids generic plan cache)
SET plan_cache_mode = force_custom_plan;
-- Force generic plans (rarely useful)
SET plan_cache_mode = force_generic_plan;
-- Default: auto (PostgreSQL decides)
SET plan_cache_mode = auto;
-- Per query via connection parameter in app layer
-- SET LOCAL plan_cache_mode = force_custom_plan;Force Re-Planning
sql
-- Deallocate specific prepared statement
DEALLOCATE my_prepared_statement;
-- Deallocate all prepared statements in session
DEALLOCATE ALL;Application-Level Fix: Add OFFSET 0 or CTE Fence
sql
-- Force re-planning by wrapping in a CTE (optimization fence in PG 12 and earlier)
WITH data AS MATERIALIZED (
SELECT * FROM orders WHERE status = $1
)
SELECT * FROM data ORDER BY created_at DESC LIMIT 100;Update Statistics for Better Plans
sql
-- Increase stats target for high-cardinality columns
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
-- Run analyze after
ANALYZE orders;
-- Default is 100 histogram buckets; 500 gives planner more detailKey Takeaways
- Generic plans are chosen after 5 executions and can be wrong for skewed data distributions
- Set
plan_cache_mode = force_custom_planfor queries with high execution time variance - Increase
STATISTICStarget on skewed columns to give the planner better histogram data - High
stddev_exec_timeinpg_stat_statementsis the telltale sign of plan cache issues
JusDB Can Help
Query plan instability is a subtle but common cause of production performance incidents. JusDB can diagnose and resolve plan cache issues in your PostgreSQL deployment.