Autovacuum docs mention “N rows changed since last analyze” as the trigger, but I never knew where to see that number. Today:

SELECT
  relname,
  n_live_tup,
  n_mod_since_analyze,
  last_analyze,
  last_autoanalyze,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_mod_since_analyze DESC
LIMIT 20;

n_mod_since_analyze is the row count that autovacuum’s analyze trigger looks at. If this is large, and the ratio to n_live_tup is large, your stats are stale. Planner will probably be guessing wrong.

Threshold for autoanalyze is, by default:

autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

So it triggers when n_mod_since_analyze > 50 + 0.1 * n_live_tup. For a table of 10M rows, that’s 1M row modifications before analyze kicks in. Long time to be running on stale stats.

This query is now on our “why is this query slow” runbook. If n_mod_since_analyze / n_live_tup > 0.05, the first fix is often just ANALYZE the_table. 15 seconds of work, often multiple-x speedup on affected queries.