Forcing a Custom Plan with plan_cache_mode #

When a prepared statement locks onto a generic plan, every execution reuses one plan shape regardless of the actual parameter value. For skewed data that is a trap: the generic plan is built from average selectivity, so a query that should scan three rows and one that should scan three million share the same access path. The plan_cache_mode GUC is the direct lever to break out of that behaviour, and force_custom_plan tells the planner to build a fresh, literal-aware plan on every EXECUTE.

What force_custom_plan Actually Changes #

A prepared statement passes through parse, rewrite, and plan phases. Normally PostgreSQL caches the plan and, after five custom executions, may switch to a reusable generic plan if its estimated cost is no worse than the average custom cost. plan_cache_mode overrides that decision:

force_custom_plan does not turn the prepared statement into an ad-hoc query. The named statement still exists, parse and rewrite are still reused, and you still call EXECUTE. Only the plan phase repeats. That distinction matters for prepared statement plan pinning as a whole: you are pinning how the plan is chosen, not the plan itself.

The diagram below maps the three modes against where you can set them and what each costs per execution.

plan_cache_mode scopes and per-execution planning cost A matrix showing three plan_cache_mode values (auto, force_generic_plan, force_custom_plan) mapped to three scopes where they can be set (SET LOCAL, ALTER ROLE SET, connection options) and the planning cost each imposes per execution. Where you set it (scope) SET LOCAL one transaction only ALTER ROLE ... SET every login of a role connection options at startup / DSN force_custom_plan re-plans every EXECUTE Cost: high planning, accurate estimates best for skewed data, low/medium exec rate auto (default) 5-exec heuristic Cost: amortized; may lock a bad generic plan fine when data is roughly uniform force_generic_plan plans once, reuses Cost: near-zero planning, value-blind best for uniform data at high exec rate Verify any choice with EXPLAIN EXECUTE: $1 means generic, a literal means custom

Setting plan_cache_mode at the Right Scope #

The three scopes trade blast radius against persistence. Pick the narrowest one that survives your connection topology.

Transaction scope with SET LOCAL #

SET LOCAL confines the change to the current transaction, so it cannot leak into the next query on a pooled connection:

BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;  -- reverts at COMMIT/ROLLBACK

EXECUTE line_items_by_warehouse(4211);          -- re-planned for this literal
COMMIT;

This is the safest option when a heavy analytical statement runs inside an otherwise cheap transactional workload — the reporting query gets a custom plan without changing planning behaviour for everything else on the connection.

Role scope with ALTER ROLE #

For a dedicated reporting or ETL role, pin the mode at login so it applies no matter which backend the role opens:

-- Applied for every new session this role starts
ALTER ROLE reporting_user SET plan_cache_mode = force_custom_plan;

This is the most robust choice behind a connection pooler, because the value is re-applied on each backend startup rather than depending on session state that a pooler might reset.

Connection scope at startup #

libpq and most drivers let you push the GUC through the startup packet, which behaves like a SET scoped to the session:

# DSN / connection options
options=-c plan_cache_mode=force_custom_plan

Use this when a specific service, rather than a database role, owns the skewed workload.

Verifying the Mode Took Effect #

The definitive test is EXPLAIN EXECUTE with a real value. Prepare a statement over line_items joined to products and warehouses, then inspect the plan before and after.

PREPARE line_items_by_warehouse (int) AS
SELECT li.line_item_id, p.product_name, li.quantity
FROM line_items li
JOIN products p ON p.product_id = li.product_id
WHERE li.warehouse_id = $1;

With the default auto mode, after the statement has flipped to a generic plan, the estimate is value-blind:

EXPLAIN EXECUTE line_items_by_warehouse(4211);
-- BEFORE: generic plan, parameter shown as $1
Hash Join  (cost=52.10..8140.55 rows=9600 width=44)   -- rows from DEFAULT selectivity, not this value
  Hash Cond: (li.product_id = p.product_id)
  ->  Seq Scan on line_items li  (cost=0.00..7100.00 rows=9600 width=12)
        Filter: (warehouse_id = $1)                   -- $1 = generic; planner cannot see 4211
  ->  Hash  (cost=30.00..30.00 rows=1600 width=36)
-- Planning Time: 0.180 ms   (planned once, reused cheaply)

Warehouse 4211 is a tiny satellite depot with 40 rows, but the generic estimate of 9600 comes from average selectivity across all warehouses. Now force a custom plan and re-check:

SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN EXECUTE line_items_by_warehouse(4211);
-- AFTER: custom plan, literal substituted for $1
Nested Loop  (cost=0.85..168.40 rows=40 width=44)     -- estimate tracks the true 40 rows
  ->  Index Scan using idx_line_items_wh on line_items li
        Index Cond: (warehouse_id = 4211)             -- literal 4211 visible to the planner
  ->  Index Scan using products_pkey on products p
        Index Cond: (product_id = li.product_id)
-- Planning Time: 0.910 ms   (re-planned for THIS execution)

The cost= figures are unitless planner estimates, not milliseconds. Two things changed: the row estimate dropped from 9600 to 40 because the planner can now see the literal, and the plan switched from a hash join over a sequential scan to a nested loop over two index scans. The Planning Time in ms rose from 0.180 to 0.910 — that fivefold increase is the price you pay on every execution under force_custom_plan.

Numbered Workflow #

Step 1 — Capture the generic baseline #

Run EXPLAIN EXECUTE against the prepared statement with a value you know is skewed. Record the $1 row estimate, the actual rows, and the Planning Time.

Step 2 — Choose a scope #

Decide between SET LOCAL (one transaction), ALTER ROLE ... SET (a workload role), or connection options (a service). Narrower is safer; role scope survives poolers best.

Step 3 — Set the mode #

Apply plan_cache_mode = force_custom_plan at the chosen scope.

Step 4 — Confirm the plan changed #

Re-run EXPLAIN EXECUTE. The literal should replace $1, and the row estimate should now track the real cardinality for that value.

Step 5 — Quantify the planning cost #

Compare Planning Time before and after. Multiply the delta by your execution rate to see the aggregate planning overhead you are accepting.

Step 6 — Pick the final mode #

If skew dominates and the execution rate is modest, keep force_custom_plan. If data is uniform and the query fires thousands of times per second, force_generic_plan or auto may win because planning cost outweighs estimate accuracy.

When to Prefer Generic Instead #

force_custom_plan is not free, and there are workloads where the generic plan is the correct answer. Prefer force_generic_plan (or leave auto) when:

Common Pitfalls #

Setting force_custom_plan globally. Flipping the GUC in postgresql.conf forces re-planning for every prepared statement on the server, including cheap, high-frequency OLTP queries where planning already dominates execution. Scope it to the role or transaction that actually has skew.

A pooler that does not persist the SET. In transaction-mode pooling, a plain SET plan_cache_mode = force_custom_plan can be discarded by DISCARD ALL between checkouts, so the next statement silently reverts to auto. Use ALTER ROLE ... SET, which is re-applied at backend startup, or SET LOCAL inside the same transaction as the EXECUTE.

Assuming prepared-statement-aware poolers change the trade-off. Poolers that emulate protocol-level prepared statements still let the server pick the plan; plan_cache_mode continues to govern planning. The mode must be set on the server session, not the pooler, or it has no effect.

Forgetting the parse/rewrite cache is intact. force_custom_plan re-plans but does not re-parse. Do not reach for it expecting it to fix a stale rewrite or a changed table definition — for that you must DEALLOCATE and re-PREPARE the statement.

Before/After Summary #

-- BEFORE (auto → generic): value-blind, wrong path for skewed value
Hash Join  rows=9600   Seq Scan on line_items  Filter: (warehouse_id = $1)
Planning Time: 0.180 ms   Execution Time: 210.4 ms

-- AFTER (force_custom_plan): literal-aware, correct nested loop
Nested Loop  rows=40   Index Scan  Index Cond: (warehouse_id = 4211)
Planning Time: 0.910 ms   Execution Time: 0.7 ms

Execution time collapsed because the plan finally matched the data, and the extra 0.73 ms of planning is trivial next to the 210 ms it saved — exactly the profile where force_custom_plan pays for itself.

Frequently Asked Questions #

Does force_custom_plan disable prepared statements entirely? No. The prepared statement still exists and you still call EXECUTE against it, so parse and rewrite work is reused. Only the planning phase changes: PostgreSQL generates a fresh, literal-aware plan on every execution instead of reusing a cached generic plan. You keep the protocol-level benefits of a named statement while paying the planning cost each time.

Where should I set plan_cache_mode so a connection pooler does not lose it? Prefer ALTER ROLE reporting_user SET plan_cache_mode = force_custom_plan so the value is applied at login for every backend that role opens, independent of which pooled connection you land on. A SET LOCAL inside a transaction is safe but only lasts that transaction; a plain SET can be wiped by DISCARD ALL between checkouts in transaction-mode pooling.

How do I confirm force_custom_plan actually changed the plan? Run EXPLAIN EXECUTE with a real skewed value after setting the mode. A generic plan shows the parameter as $1 with a default-selectivity row estimate; a custom plan substitutes the literal and produces a per-value estimate that tracks the true cardinality. Watch the Planning Time line rise, since each EXECUTE now re-plans.

Related