DBAtlas · Database operations atlas

Find tables with stale statistics (ANALYZE strategy)

PostgreSQLMaintenance

Command

SELECT schemaname,
       relname AS table_name,
       n_live_tup,
       n_mod_since_analyze,
       round(100.0 * n_mod_since_analyze / NULLIF(n_live_tup, 0), 1) AS mod_pct,
       greatest(last_analyze, last_autoanalyze) AS last_analyzed,
       round(current_setting('autovacuum_analyze_threshold')::int
             + current_setting('autovacuum_analyze_scale_factor')::numeric * n_live_tup) AS autoanalyze_at
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY mod_pct DESC NULLS LAST
LIMIT 20;

-- After a bulk load, without waiting for autovacuum
ANALYZE (VERBOSE) orders;

What it does

Shows the number of rows modified since the last ANALYZE, its ratio to live rows, and the approximate modification threshold at which autovacuum will trigger ANALYZE.

When to use it

After bulk loads/deletes, when plans show wrong row estimates, or when autoanalyze is suspected of lagging on large tables.

Caution

The computed threshold uses the global settings; if table-specific autovacuum_analyze_* reloptions exist (pg_class.reloptions), the real threshold differs. On large tables the default scale_factor (0.1) triggers far too late; lower it per table. Autovacuum does not ANALYZE the parent of a partitioned table; parent-level statistics need a manual ANALYZE. Temp tables are never seen by autovacuum. ANALYZE reads a sample and takes ShareUpdateExclusiveLock (does not block DML).

Open in DBAtlas →

Related