PostgreSQL Plan Regression

Investigation principle

Do not force a plan before understanding why the optimizer changed its choice.

Common wrong assumptions

• “The SQL didn’t change, so the plan didn’t.” Plans depend on statistics, parameter values, data volume, settings and version. Any of them can change while the SQL text stays the same.
• “Running ANALYZE fixed it, so we’re done.” Then find out why autoanalyze didn’t keep up. Large tables can wait a long time between automatic analyzes, and correlated columns may need extended statistics (CREATE STATISTICS).
• “EXPLAIN in psql shows what the app gets.” The application may use prepared statements, generic plans or different session settings. The sample report shows a case where psql looks fine and the application doesn’t.
• “Just force the old plan.” Disabling planner node types or pinning plans hides the cause. It also breaks again when the data shifts.

First queries to run

Read-only. Run them during the problem window if you can; most of these views show current state, not history.

-- Tables with the most changes since their last analyze
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;

-- Then, in a safe environment, compare estimated vs actual rows on each plan node:
EXPLAIN (ANALYZE, BUFFERS) <the regressed query with representative parameters>;

-- To capture the plan production actually ran, use auto_explain:
--   auto_explain.log_min_duration = '500ms'  (session_preload_libraries or shared_preload_libraries)

When a deeper investigation is justified

• The regression comes and goes, which points at parameter values, statistics timing or plan caching.
• Estimates are off by orders of magnitude and the cause isn’t obvious from one plan.
• The candidate fixes (statistics, an index, a query rewrite or plan-cache settings) each carry risk, and someone has to pick one.

See the measured generic-plan regression lab for a complete change, evidence and response sequence. If the regression started after a major version upgrade, check for missing optimizer statistics first.