agent measured the query at 5ms but in prod it's 2s -- the test database has fresh stats, prod's are 3 weeks stale
Fixes query plans that are fast in test but slow in production because the planner statistics are weeks out of date. Use when EXPLAIN shows a sane plan on the test database but a bad one in production for the same query. Key trigger: pg_stat_user_tables shows last_analyze weeks old on the slow tables.
TL;DR
Your test database has fresh planner statistics and production has three-week-old ones, so the same query gets a good plan in test and a bad plan in prod. Check when the slow tables were last analyzed, run ANALYZE, and make sure autovacuum can keep up with the table size. Then compare the EXPLAIN plans: the fix is confirmed when prod picks the same plan as test.
agent measured the query at 5ms but in prod it's 2s -- the test database has fresh stats, prod's are 3 weeks stale- Check the stats age. Query pgstatusertables for lastanalyze and last_autoanalyze on the tables in the slow query. Expected: test shows recent timestamps, prod shows weeks old.
- Compare the plans. Run EXPLAIN in both environments (plain EXPLAIN, not the ANALYZE variant, if the prod query is slow). Expected: test uses an index scan with sane row estimates, prod uses a sequential scan or a nested loop with wildly wrong row estimates.
- Refresh the stats. Run ANALYZE on the stale tables in production. Expected: last_analyze updates to now, and the plan flips to the good one on the next EXPLAIN.
- Fix the cause so it stays fixed. Check autovacuum settings: on large or high-churn tables the default scale factor means autovacuum almost never fires. Lower autovacuumanalyzescalefactor for the table or schedule a periodic ANALYZE. Expected: lastautoanalyze stays within a day on the busy tables going forward.
Use this when
- The same query is fast on a fresh test database and slow in production
- EXPLAIN shows bad row-count estimates in prod (estimated 10 rows, actual 2 million)
- The slowdown appeared gradually as the table grew
Not for this skill when
- The plans are identical in both environments: then the difference is data size, hardware, or load, not stats
- The query is slow everywhere including test: that is a missing index or a bad query, not stale stats
Variant phrasings
- "postgres slow in prod fast in staging same query"
- "query suddenly slow after table grew, EXPLAIN estimates wrong"
- "how to tell if postgres planner stats are stale"
Why it happens
The planner picks plans from statistics about row counts and value distribution. If autovacuum cannot keep up with a big or fast-changing table, those statistics freeze in time. The planner then believes the table is small, picks a nested-loop plan meant for hundreds of rows, and runs it against millions.
Edge cases
- ANALYZE takes a sample: on skewed data the default sample can still mislead, and you may need to raise the statistics target on specific columns
- Restored or cloned databases often ship with no stats at all: run ANALYZE right after a restore
- Long-running transactions can block autovacuum, so the stats go stale even with sane settings
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_zh9Yowm8Z5OczHZqscZbgA
Maintainer review
No maintainer verification is recorded for this version.
This records the version a maintainer checked. It does not assert that the version is the latest upstream release.