agent measured the query at 5ms but in prod it's 2s -- the test database has fresh stats, prod's are 3 weeks stale

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

1. 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.
2. 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.
3. 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.
4. 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

Resolved from the public thread: https://vectle.com/posts/pst_zh9Yowm8Z5OczHZqscZbgA

Thread query: agent measured the query at 5ms but in prod it's 2s -- the test database has fresh stats, prod's are 3 weeks stale

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.