Plan Invalidation After DDL and ANALYZE #

You lowered random_page_cost with ALTER SYSTEM and a reload after proving in psql that the planner now picks the right index. Hours later the application still runs the old sequential-scan plan. Meanwhile a colleague’s CREATE INDEX CONCURRENTLY on another table was picked up by the same application within seconds. Both were plan fixes; only one invalidated the cached plans.

Which plan a prepared statement uses is covered in prepared statement plan pinning. This page covers when that cached plan is thrown away.

The Condition That Triggers a Replan #

PostgreSQL caches plans for prepared statements (explicit PREPARE and the extended protocol your driver uses) and for SQL inside PL/pgSQL functions. Each cached plan records the objects it depends on. Before every execution the server checks whether any invalidation message has arrived for those dependencies; if so, it re-parses and replans.

Invalidates cached plans:

Does not invalidate cached plans:

A generic plan built under old parameters therefore survives indefinitely on a long-lived pooled connection. The custom plans that precede it are built at each execution anyway, so they see new parameters immediately — a split that makes the symptom look intermittent.

What reaches a cached plan A cached generic plan sits in the middle. On the left, arrows from DDL, CREATE or DROP INDEX, ANALYZE, and search_path changes point into it, marked as invalidating and replanning at the next execution. On the right, parameter changes such as random_page_cost and work_mem are shown blocked, marked as not invalidating, so the old plan is reused. ALTER TABLECREATE / DROP INDEX ANALYZEsearch_path change cached generic plan replanned on next EXECUTE random_page_cost, work_memALTER SYSTEM + reload no invalidation → old plan reused

Annotated Evidence #

On an application connection before the fix:

SELECT name, generic_plans, custom_plans, prepare_time
FROM pg_prepared_statements;
 name  | generic_plans | custom_plans |         prepare_time
-------+---------------+--------------+-------------------------------
 s_412 |         91820 |            5 | 2026-09-16 04:12:08.1+00
-- generic plan in use since 04:12; random_page_cost was changed at 10:30

auto_explain output from that connection after the ALTER SYSTEM:

Query Text: SELECT … FROM invoices WHERE customer_id = $1 AND status = $2
Seq Scan on invoices  (cost=0.00..418220.00 rows=410 width=96) (actual time=0.04..1840.2 rows=402 loops=1)
  Filter: ((customer_id = $1) AND (status = $2))
-- the cost figures were computed with random_page_cost = 4 at 04:12

The same statement after DISCARD PLANS on that connection:

Index Scan using invoices_customer_status_idx on invoices
    (cost=0.56..402.10 rows=410 width=96) (actual time=0.03..0.84 rows=402 loops=1)
  Index Cond: ((customer_id = $1) AND (status = $2))
Timestamps tell you whether the plan saw the change Left: pg_prepared_statements shows prepare time 04:12 and 91,820 generic executions, while random_page_cost changed at 10:30, so the plan predates the change. Right: after DISCARD PLANS the statement is prepared again after the change and uses the index scan. before DISCARD PLANS prepare_time 04:12 generic_plans 91,820 config changed 10:30 → not seen after DISCARD PLANS prepare_time 15:05 custom plans rebuilt, then generic Index Scan chosen prepare_time older than the change = the plan never saw it

Step-by-Step Resolution #

  1. Classify the fix.

    • Schema (index, constraint, column) or statistics (ANALYZE, CREATE STATISTICS + ANALYZE): invalidates automatically.
    • Configuration (any GUC): does not.

  2. For schema and statistics fixes, verify on the next execution. No action is needed beyond letting the statement run; confirm with auto_explain on the application’s role.

  3. For configuration fixes, force a replan. Options, from narrowest to broadest:

    -- on a specific session you control
DISCARD PLANS;
    • Recycle pooled server connections — PgBouncer RECONNECT or a rolling restart of the pool, as described in session reset and DISCARD ALL effects.
    • Trigger an invalidation on the affected table with a harmless statistics refresh: ANALYZE invoices;.

  4. Check counters on a real application connection. pg_prepared_statements is per session, so query it through the application’s pool or log it from the application. A prepare_time older than your configuration change means the generic plan predates it.

  5. Confirm the new plan with auto_explain sampling rather than psql, which prepares nothing and always reflects current settings — the setup is in capturing ORM plans with auto_explain.

Timeline of a fix that did not arrive A timeline from 04:12 to 16:00. At 04:12 the statement is prepared and pins a generic sequential-scan plan. At 10:30 random_page_cost is lowered, but the cached plan is unchanged and the slow plan continues. At 15:05 DISCARD PLANS runs on the pool, and from the next execution the index scan plan is used. 04:1210:3015:05 prepared; generic Seq Scan pinned random_page_cost lowered; cached plan untouched DISCARD PLANS; Index Scan from next run slow plan in use fast plan

Before and After #

-- BEFORE: random_page_cost lowered at 10:30, cached generic plan from 04:12
Seq Scan on invoices  Filter: ((customer_id = $1) AND (status = $2))        Execution Time: 1840.6 ms

-- AFTER: DISCARD PLANS on pooled connections
Index Scan using invoices_customer_status_idx  Index Cond: (…)              Execution Time: 0.91 ms

Why the two fixes behaved differently #

Invalidation is a dependency mechanism, not a freshness mechanism. When a statement is planned, PostgreSQL records the relations, functions and types the plan touched. Catalog changes to any of those objects broadcast a message to every backend, and a backend that receives one marks matching cached plans as stale. Nothing in that path knows about planner parameters: random_page_cost is not a catalog object a plan depends on, so changing it produces no message and no backend reacts. The index creation, by contrast, changed the catalog entry for the table the plan scans, which is precisely what the dependency list watches.

This has a practical consequence for rollouts. A plan fix delivered as a schema or statistics change is picked up by the whole fleet within one execution of each statement. A plan fix delivered as a configuration change is picked up only by statements planned afterwards: new connections, statements not yet past their five custom-plan executions, and anything not using prepared statements at all. On a pool with connection lifetimes of days, that can be a very long tail.

Replanning and the custom-plan counters #

Revalidation rebuilds the plan, but it does not necessarily restart the warm-up. Whether a rebuilt prepared statement returns to custom plans for a while or moves straight to a fresh generic plan depends on the cost history the plan cache already holds, so do not assume a replanned statement will spend five executions on custom plans again. When the question matters — for example, after fixing statistics on a skewed column — check generic_plans and custom_plans on the connection before and after, and if needed deallocate and re-prepare the statement so its history starts from zero.

The same reasoning applies to functions. A PL/pgSQL function caches a plan per statement per session, subject to identical invalidation rules. A fix that changes the catalog reaches those plans on the next call; a fix that changes only settings waits for DISCARD PLANS, a new session, or CREATE OR REPLACE FUNCTION, which invalidates the function’s cached plans on every backend.

Common Pitfalls #

Validating a configuration change in psql. psql sends simple-protocol queries that are planned fresh every time. Diagnostic signal: psql shows the new plan while auto_explain from the application shows the old one. Fix: validate through the application’s connection path.

Expecting pg_reload_conf() to replan. Reload changes settings for new plans only. Diagnostic signal: pg_settings shows the new value while cached plans persist. Fix: discard plans or recycle connections.

Replanning storms after ANALYZE on hot tables. Every cached plan on the table is rebuilt at next use, across every connection. Diagnostic signal: brief spikes in planning CPU after autovacuum analyzes a central table. Fix: usually harmless; on extremely hot tables with expensive planning, tune autoanalyze thresholds rather than preventing invalidation.

PL/pgSQL functions holding old plans. Plans inside functions are cached per session the same way. Diagnostic signal: a function call slower than its body run directly. Fix: the same remedies apply; DISCARD PLANS also clears function plan caches.

Frequently Asked Questions #

Does creating an index make existing prepared statements use it? Yes, on their next execution. The index creation invalidates cached plans that reference the table.

Does changing random_page_cost or work_mem replan cached statements? No. Parameter changes do not invalidate cached plans; DISCARD PLANS or reconnecting applies them.

Does ANALYZE invalidate cached plans? Yes. Updating a table’s statistics sends an invalidation, so dependent cached plans are rebuilt at their next execution.

Up: Prepared Statement Plan Pinning