## 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.

```text
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 pg_stat_user_tables for last_analyze 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 autovacuum_analyze_scale_factor for the table or schedule a periodic ANALYZE. Expected: last_autoanalyze 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