Parameter · Query Tuning / Other Planner Options
plan_cache_mode
Controls the planner's selection of custom or generic plan. Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better. This can be set to override the default behavior.
- Default:
auto - Type: enum
- Values: auto · force_generic_plan · force_custom_plan
- Applies: Session (any user)
In plain words
A prepared statement can be planned fresh for every set of parameter values (a custom plan) or planned once with placeholders and reused (a generic plan). By default PostgreSQL tries custom plans five times, then switches to the generic plan if it is not noticeably worse. You can force either behaviour.
For DBAs and operators
In auto mode the first five executions use custom plans. From the sixth on, the generic plan is used if its estimated cost is lower than the average cost of the custom plans, which includes an estimate of their planning overhead. pg_prepared_statements shows generic_plans and custom_plans counters; EXPLAIN of a generic plan shows $1 instead of values:
PREPARE q(int) AS SELECT * FROM t WHERE a = $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE q(5);
-- Bitmap Heap Scan on t (cost=5.84..608.94 rows=200 width=63)
-- Recheck Cond: (a = $1)The classic problem: skewed data, where the generic plan (based on average selectivity) is terrible for some values, appears after exactly five fast executions, so a query becomes slow "after a while" through a driver that prepares statements (JDBC after prepareThreshold, PL/pgSQL, pgbouncer in transaction mode with prepared statements). force_custom_plan fixes that at the cost of planning every time; force_generic_plan saves planning time for very frequent, uniform queries. It also applies to PL/pgSQL statements, which are prepared internally.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
cursor_tuple_fraction enable_partition_pruning default_statistics_target