Missing Statistics (No ANALYZE)

Overview

The Postgres query planner uses column statistics (histograms, most-common-values, null fractions) from pg_statistic to estimate row counts and choose optimal query plans. Stale statistics cause the planner to generate bad plans—for example, choosing a sequential scan when an index would be faster, or choosing a nested loop join where a hash join would be better.

Statistics become stale when a significant fraction of rows are modified since the last ANALYZE. The autoanalyze daemon should handle this automatically, but it can fall behind on high-write tables or be blocked by long transactions. insightral flags tables where more than 10% of rows have been modified since the last successful ANALYZE.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Tables with stale or missing statistics relative to row count
SELECT
  s.schemaname,
  s.relname                   AS table_name,
  s.n_live_tup,
  s.n_mod_since_analyze,
  round(
    s.n_mod_since_analyze::numeric / NULLIF(s.n_live_tup, 0) * 100,
    1
  )                           AS modified_pct,
  s.last_autoanalyze,
  s.last_manual_analyze,
  now() - s.last_autoanalyze  AS since_autoanalyze
FROM pg_stat_user_tables s
WHERE s.n_mod_since_analyze > 10000
  AND (
    s.last_autoanalyze IS NULL
    OR now() - s.last_autoanalyze > interval '1 day'
  )
  AND (
    s.last_manual_analyze IS NULL
    OR now() - s.last_manual_analyze > interval '1 day'
  )
ORDER BY s.n_mod_since_analyze DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Refresh statistics immediately
ANALYZE <table_name>;

-- For tables with very uneven data distribution, increase statistics target
ALTER TABLE <table_name>
  ALTER COLUMN <column_name> SET STATISTICS 500;
ANALYZE <table_name>;

Worked example

Common false positives

• Bulk loads that complete and then trigger autoanalyze within minutes — the statistics are stale only briefly.
• Tables where all modifications are in a narrow range and the planner's estimates are still accurate despite high n_mod_since_analyze — verify with EXPLAIN ANALYZE before tuning statistics target.