gotchasqlpostgresqlModerate
Prepared statement caching and the generic plan problem
Viewed 0 times
prepared statementplan cachegeneric plancustom planplan_cache_modepg_prepared_statements
Problem
After 5 executions, PostgreSQL switches a prepared statement to a generic plan that ignores actual parameter values. This can cause the planner to choose a sequential scan when an index scan would be optimal for specific values.
Solution
Understand and control the plan caching behavior:
-- PREPARE creates a statement:
PREPARE find_orders(int) AS
SELECT * FROM orders WHERE customer_id = $1;
-- First 5 calls use custom plans (with actual value).
-- After 5 calls, PostgreSQL may switch to a generic plan.
-- Check which plan is used:
EXECUTE find_orders(42);
EXPLAIN EXECUTE find_orders(42);
-- Force custom plans (per session):
SET plan_cache_mode = force_custom_plan;
-- Force generic plans (consistent behavior, useful for connection poolers):
SET plan_cache_mode = force_generic_plan;
-- Check prepared statements in the current session:
SELECT name, statement, parameter_types
FROM pg_prepared_statements;
-- PREPARE creates a statement:
PREPARE find_orders(int) AS
SELECT * FROM orders WHERE customer_id = $1;
-- First 5 calls use custom plans (with actual value).
-- After 5 calls, PostgreSQL may switch to a generic plan.
-- Check which plan is used:
EXECUTE find_orders(42);
EXPLAIN EXECUTE find_orders(42);
-- Force custom plans (per session):
SET plan_cache_mode = force_custom_plan;
-- Force generic plans (consistent behavior, useful for connection poolers):
SET plan_cache_mode = force_generic_plan;
-- Check prepared statements in the current session:
SELECT name, statement, parameter_types
FROM pg_prepared_statements;
Why
PostgreSQL uses a generic plan when it estimates that the generic plan cost is within 1.1x of the average custom plan cost over 5 executions. Generic plans are faster to produce (no re-planning) but may be suboptimal for skewed distributions.
Gotchas
- PgBouncer in transaction mode deallocates prepared statements; use protocol-level prepared statements only in session mode
- plan_cache_mode GUC was added in PostgreSQL 12; older versions have no control
- Extended query protocol (binary) always uses prepared statement caching in most drivers
- High-cardinality columns with data skew are most vulnerable to the generic plan problem
Code Snippets
Observe plan caching progression after 5 executions
-- See generic vs custom plan in EXPLAIN:
PREPARE q(int) AS SELECT * FROM orders WHERE status_id = $1;
EXPLAIN EXECUTE q(1);
-- After 5 execs, check if plan changed:
EXECUTE q(1); EXECUTE q(1); EXECUTE q(1); EXECUTE q(1); EXECUTE q(1);
EXPLAIN EXECUTE q(1);Context
Applications using ORM or driver-level prepared statements on columns with skewed data distribution
Revisions (0)
No revisions yet.