PostgreSQL query became slow after deploy: compare plan inputs

The symptoms

• •One or a small set of queries suddenly take longer or use more I/O after a deploy, while neighboring queries on the same database remain unaffected.
• •Application latency or timeout errors appear in the same window as a code release, schema migration, or ANALYZE-adjacent change, and disappear when the deploy is reverted.
• •EXPLAIN (ANALYZE, BUFFERS) output for the regressed query shows a different node shape than before, even though the underlying tables and indexes are unchanged.
• •Row estimates from the planner diverge sharply from actual row counts in the new EXPLAIN output, signaling a stats or input drift rather than a true data-volume change.
• •pg_stat_statements totals show the same call count and total time rising for the query, indicating planner-side change rather than traffic spike.

Likely causes

• •The application or migration silently rewrote the statement text, so the planner now hashes a different normalized query and picks a different plan.
• •Bound parameter values changed in a way that pushed the planner across a plan-choice boundary, often via a prepared statement whose generic plan is now suboptimal.
• •An ALTER TABLE from the deploy skewed column statistics (for example via default expressions, type changes, or extended statistics) so row estimates no longer match reality.
• •A migration added, dropped, or rewrote an index that the regressed query used to rely on, forcing the planner into a seq scan or a worse join order.
• •Concurrent autovacuum or a manual ANALYZE that ran during the deploy window produced statistics that the planner is now reading, even though the schema itself did not change.

First ten minutes

1. 01Pin the exact statement text the application is sending to PostgreSQL by capturing it from pg_stat_statements or from application logs, so any later comparison is anchored to a real query.
2. 02Pull EXPLAIN (ANALYZE, BUFFERS) output for that exact statement against a representative dataset, and note the top-level node, join order, and row-estimate versus actual-row divergence.
3. 03Pull the same statement's EXPLAIN output from before the deploy — from a saved plan, a staging run, or a rollback environment — so you have a side-by-side of plan shape.
4. 04Diff the two EXPLAIN plans node by node: operator type, join order, scan method, and row estimates at each node, treating each difference as a hypothesis to confirm.
5. 05Inventory the planner's inputs at the moment of regression: the statement text, bound parameters, current statistics on the touched tables, and the visible index list, so each EXPLAIN difference can be matched to a concrete input change.

Evidence to collect

• •Side-by-side EXPLAIN (ANALYZE, BUFFERS) outputs for the regressed statement, captured before and after the deploy against a frozen dataset with identical parameter values.
• •pg_stats snapshots for every column the planner referenced in either plan, recording null fraction, most-common-values, and histogram bounds across the deploy boundary.
• •pg_stat_user_tables last_analyze, last_autoanalyze, and n_mod_since_analyze for the touched tables, so an ANALYZE during the deploy window is visible rather than inferred.
• •An index inventory from pg_indexes and pg_index for the touched tables, including expression and partial index predicates, before and after the deploy.
• •pg_stat_statements rows for the regressed query, showing normalized query hash, call count, and total time so the regression can be quantified against a baseline.

Where to look

• •pg_stat_statements, normalized query text, and per-call timing so you can isolate which statement changed its plan.
• •pg_stats and pg_class reltuples/relpages for the tables touched by the regressed query, since statistics are the planner's primary input.
• •pg_indexes plus pg_index.indpred for indexes on the touched tables, including expression and partial indexes that the planner may now ignore.
• •pg_stat_user_tables last_analyze, last_autoanalyze, and n_mod_since_analyze to see whether the deploy window changed the stats feeding the planner.
• •The application's parameter binding path, since prepared statement plans and generic-plan vs custom-plan choices are a common silent source of regressions.

Diagnostic steps

1. 01Capture the current EXPLAIN (ANALYZE, BUFFERS) output for the regressed statement and label it 'after', and obtain the pre-deploy 'before' EXPLAIN for the same logical query on the same dataset.
2. 02Diff the two plan trees top-down: at every node compare operation type, relation, index, join strategy, and estimated rows versus actual rows; mark each difference with the hypothesis it implies.
3. 03Confirm whether the statement text itself changed by hashing the normalized query string before and after; if hashes differ, the planner is genuinely looking at a new statement and the rest of the diff is suspect.
4. 04Inspect pg_stats for every column the planner referenced in both plans; record null fraction, most-common-values list length, and histogram bounds, and compare them across the deploy boundary.
5. 05List the indexes available to each plan via pg_indexes and pg_index, then mark which indexes disappeared, appeared, or were redefined in the deploy; correlate each disappearance with a plan node change.
6. 06Reproduce both plans on a frozen copy of the data using SET statement_timeout and SET work_mem to controlled values, so the comparison isolates planner inputs from runtime resource effects.

Common mistakes

• •Comparing EXPLAIN output without ANALYZE, so actual row counts are missing and you cannot tell a stats drift from a real data-volume change.
• •Diffing plans that were run against different parameter values or different datasets, so a plan-shape change is misattributed to the deploy instead of to the inputs.
• •Chasing the wrong regression by assuming the planner changed when in fact the application rewrote the statement text under the same logical name.
• •Treating an ANALYZE as a fix without proving it changed the specific statistics that explain the row-estimate divergence in the new plan.
• •Dropping and recreating indexes reactively, which masks the original input change and prevents a clean before/after proof.

Safe fixes

• •If statement text changed, treat that as the regression boundary: revert the application change that rewrote the query, since plan-shape diffs downstream of a text change are usually symptoms, not causes.
• •If row estimates diverge but the statement and indexes are unchanged, run ANALYZE on the touched tables and re-capture EXPLAIN (ANALYZE, BUFFERS); proceed only if the new plan's estimates now align with actual rows.
• •If an index disappeared or was rewritten, do not drop and rebuild speculatively; instead, in a non-production environment, recreate the prior index definition and confirm the old plan returns before touching production.
• •If a prepared statement is using a generic plan that is now suboptimal, force a custom plan via SET plan_cache_mode, then re-capture EXPLAIN to confirm the regression is tied to plan caching and not to schema or stats.
• •If no input can be shown to explain the plan change, revert the deploy as the default safe action and reopen the investigation with a frozen dataset that supports a controlled before/after comparison.

Prove the fix

1. 01EXPLAIN (ANALYZE, BUFFERS) for the regressed query, run on a fixed dataset and fixed parameter values, shows the original plan shape, with estimated rows within a small multiple of actual rows at every node.
2. 02pg_stat_statements reports total execution time for the regressed query returning to within a documented tolerance of its pre-deploy baseline over a representative time window.
3. 03Each planner input identified in the diff — statement text, statistics snapshot, and visible index set — can be shown to match the pre-deploy value, so the plan match is explained rather than coincidental.
4. 04Application-side latency and timeout-error counters for the affected code path return to their pre-deploy baseline during a verification window that excludes unrelated traffic variation.

Prevention and next steps

• •Capture and store EXPLAIN (ANALYZE, BUFFERS) output for known-critical queries as part of the deploy artifact so regressions can be diffed immediately, not reconstructed under pressure.
• •Treat statement text as a contract: any change to the normalized query string should be visible in code review as its own boundary, separate from data or schema changes.
• •Run ANALYZE explicitly after data-loading migrations so the planner's statistics move on the schedule you control, rather than waiting for autovacuum during the deploy window.
• •Track per-query timing in pg_stat_statements as a deploy-time signal so a plan regression is visible against a baseline rather than inferred from user complaints.

Safe commands and checks

SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements WHERE query ILIKE '%<signature>%' ORDER BY total_exec_time DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS) <regressed query with bound literals substituted>;
SELECT schemaname, relname, last_analyze, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname IN (<table_a>, <table_b>);
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE tablename IN (<table_a>, <table_b>) ORDER BY indexname;
SELECT tablename, attname, null_frac, most_common_vals, histogram_bounds FROM pg_stats WHERE tablename IN (<table_a>, <table_b>) AND attname IN (<col_1>, <col_2>);
SELECT pg_relation_size('<index_name>'), pg_size_pretty(pg_relation_size('<index_name>'));