All Your GUCs in a Row: plan_cache_mode

plan_cache_mode is the override for a decision PostgreSQL makes on your behalf, from estimates alone, the sixth time a prepared statement runs. I spent eighteen minutes of your time on that decision in The Sixth Execution and will not repeat it here. The short version: a prepared statement can run with a custom plan, built for the parameter values in hand, or a generic plan, built once without looking at them and reused. PostgreSQL builds five custom plans, compares their average estimated cost with the generic plan’s, and goes with the smaller number. This parameter is for when the smaller number is wrong. The PREPARE documentation calls it “primarily useful if the generic plan’s cost estimate is badly off for some reason,” and the reason is usually skewed data.

The default is auto, which is that policy. force_custom_plan plans every execution with the real values. force_generic_plan builds the generic plan on first use and never looks at a value again. The context is user, so any role can set it for a session, for a transaction with SET LOCAL, or have it attached to a role, a database, or a function. It arrived in PostgreSQL 12, from Pavel Stehule. The heuristic it overrides dates from 9.2, so for seven major releases there was no switch at all, and the usual workaround in PL/pgSQL was to rewrite the statement as a string and EXECUTE it (which the PL/pgSQL documentation still suggests).

The setting is read each time a statement executes, not when it is prepared:

1
PREPARE q(text) AS SELECT count(*) FROM orders WHERE status = $1;
2
SET plan_cache_mode = force_generic_plan;
3
EXECUTE q('pending');
4
SET plan_cache_mode = force_custom_plan;
5
EXECUTE q('pending');
6
EXECUTE q('shipped');
7

8
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
9
 name | generic_plans | custom_plans
10
------+---------------+--------------
11
 q    |             1 |            2

A statement that has already gone generic under auto goes custom on its next execution after SET plan_cache_mode = force_custom_plan, and back to generic after RESET, with its cost history intact.

What it reaches

A few things never notice the setting. A query sent as text over the simple protocol is never in the plan cache at all. A statement with no parameters always gets a generic plan, force_custom_plan or not, since there is nothing to customize. PL/pgSQL expressions that touch no table are exempt; a three-million-iteration arithmetic loop took the same time with and without force_custom_plan. And PL/pgSQL’s plain EXECUTE ... USING is always planned with its values, force_generic_plan or not. (The loop and cursor forms, FOR ... IN EXECUTE and OPEN ... FOR EXECUTE, are a different code path and do obey the setting. The Sixth Execution said dynamic SQL is always custom, which is true only of the plain form.)

Everything else that goes through the plan cache obeys it, and that is more than the statements you prepared yourself.

Foreign key checks are cached plans with the key as a parameter. On PostgreSQL 18.6, inserting 300,000 rows into a table with one foreign key took 1.5 seconds under auto and 3.8 seconds under force_custom_plan, because the one-row lookup in the referenced table was planned 300,000 times. PostgreSQL 19 gives that check a path that skips the plan cache when the referenced table is not partitioned, and on 19 beta 4 the insert no longer cares. The check in the other direction still does: deleting 100,000 rows from the referenced table went from 0.8 seconds to about 2.4 on both versions.

Primary-key lookups are the statements the plan cache serves best, and force_custom_plan takes that away. pgbench -S -M prepared ran at about 100,000 transactions per second under auto and 67,000 under force_custom_plan, which is where the simple protocol lands. A PL/pgSQL function doing a single primary-key SELECT went from 3 microseconds a call to 11.

SQL-language functions joined the list in PostgreSQL 18. Before that, a SQL function that was not inlined ran its queries with a generic plan every time and ignored this parameter (I checked on 16.15). In 18 they use the plan cache like everything else, so they obey plan_cache_mode, and they also now have a sixth execution of their own.

force_generic_plan reaches queries you never prepared. Any parameterized query sent over the extended protocol goes through the plan cache, including the unnamed statement that drivers use for one-off queries. Under force_generic_plan each of those gets a generic plan that is built, used once, and thrown away, so you get the blind estimate and pay for planning anyway. Counting the 2,000 pending rows in a two-million-row orders table went from 0.2 ms to somewhere between 5 and 7 that way; the generic plan started two parallel workers to do it.

Partitions, where it cuts both ways

A generic plan cannot prune on a parameter at plan time. It contains every partition, the planner costs it as though it will scan all of them, and it locks all of them before run-time pruning throws away all but one.

The costing means auto will not choose the generic plan for an indexed lookup by partition key. On a 1,000-partition table the generic plan was costed at 8,298 against 8.29 for the custom plan, and after eight executions the counters read zero generic, eight custom. (A table so small that every partition is a sequential scan of one page can still go generic. Nobody partitions those on purpose.) A prepared lookup on a partitioned table is therefore replanned on every execution, and force_generic_plan is the only way to stop that.

The locking decides whether you should. Here is SELECT v FROM pt WHERE id = $1 against a table hash-partitioned on id, one lookup per transaction, with pgbench -M prepared and two clients on 18.6:

At 1,000 partitions the generic plan takes 1,002 relation locks where the custom plan takes 4 (and 2,002 on the execution that builds it, when the planner opens every index as well). The lock table those come out of is sized by max_locks_per_transaction. Locks last until the transaction ends, so the cost is paid once per transaction: with fifty lookups per transaction the generic plan was back in front at 128 partitions, and still less than half the speed of auto at 1,000. 19 beta 4 gave the same lock counts and the same collapse.

The same costing applies to the foreign key check against a partitioned referenced table: under auto it never goes generic, so it is planned once per row. Inserting 300,000 rows that reference an 8-partition table took 5.8 seconds under auto and 2.3 under force_generic_plan on 18.6, and 6.4 against 2.2 on 19 beta 4, where the new fast path does not cover partitioned referenced tables.

Seeing which one you got

pg_prepared_statements has had generic_plans and custom_plans counters since PostgreSQL 14, for your own session only. EXPLAIN (SETTINGS) prints plan_cache_mode when it is not auto, and so does auto_explain with auto_explain.log_settings on. In a logged plan, a statement parameter such as $1 in a condition means generic and a literal in its place means custom. (On 16 and earlier, subquery results are also printed as $0, $1, so check that the number is one of yours.) EXPLAIN (GENERIC_PLAN), in 16 and later, shows what force_generic_plan would give you without setting anything.

One trap: in psql, EXPLAIN SELECT ... WHERE status = $1 with \bind shows the custom plan whatever the mode, because EXPLAIN plans the query itself with the value in hand. Use EXPLAIN EXECUTE on a named statement, or auto_explain.

PostgreSQL 19 adds generic_plan_calls and custom_plan_calls to pg_stat_statements, the first cumulative count, visible from outside the session, of which statements have gone generic. With pg_stat_statements.track = all, that includes the statements inside PL/pgSQL functions.

Leave auto in postgresql.conf. Set globally, either of the other values reaches a great deal you were not thinking about when you set it. When a statement on skewed data has gone generic and slow, put force_custom_plan on the smallest thing that contains it: the one function, or the role that runs the reporting queries. A function’s setting applies to everything the function calls, including any foreign key checks its own inserts fire before it returns, so “smallest” is meant literally. (And any ALTER FUNCTION on a PL/pgSQL function throws away its cached plans and their history in every session, so the first few calls afterwards prove nothing.) force_generic_plan earns its place in two spots: a role whose work is key lookups against tables with a handful of partitions, and a bulk load into a table whose foreign key points at a partitioned one. Measure both first.