Statistics Maintenance

Statistics collected today describe the data that exists today. But databases are living systems—rows are inserted, updated, and deleted continuously. A histogram built last month for a 10-million row table might be wildly inaccurate if the table now contains 50 million rows with entirely different value distributions.

The challenge is clear: statistics must be maintained as data evolves. Too frequent updates waste resources. Too infrequent updates lead to optimizer decisions based on outdated information. Finding the right balance is both science and art.

Statistics decay through several mechanisms, each with different implications for optimizer accuracy.

The Drift Window:

        Statistics Quality Over Time
        
100% ─┬────────────────────────────────
      │    Excellent estimates
      │ ╔═══════════════════╗
 80% ─┼─║   Statistics       ║───────────
      │ ║   collected here   ║
 60% ─┼─║                    ║───────────
      │ ╚═════════╤═════════╝
 40% ─┼───────────│─────────────────────
      │           │  ← Drift begins
 20% ─┼───────────│─────────────────────
      │           ▼
  0% ─┴─────────────────────────────────
      T₀         T₁          T₂        Time
                 ↑
          Heavy data modifications
          cause accelerated drift

The goal of statistics maintenance is to refresh statistics before quality degrades to the point of causing bad query plans.

Modern databases include sophisticated automatic statistics collection. Understanding how they work helps you configure them appropriately.

While automatic maintenance handles steady-state operations, many scenarios require deliberate manual intervention.

Proactively identifying stale statistics prevents performance surprises. Multiple approaches help detect staleness at different levels.

Approach 1: Modification Counters

Most databases track how many rows have been modified since the last statistics collection.

-- PostgreSQL: Modifications since analyze
SELECT 
    relname,
    n_live_tup AS current_rows,
    n_mod_since_analyze AS modifications,
    ROUND(100.0 * n_mod_since_analyze / NULLIF(n_live_tup, 0), 1) AS mod_pct,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > n_live_tup * 0.1  -- More than 10% modified
ORDER BY mod_pct DESC;

Approach 2: Age-Based Detection

Simple but effective: statistics older than a threshold are suspect.

-- PostgreSQL: Statistics older than 7 days
SELECT 
    relname,
    GREATEST(last_analyze, last_autoanalyze) AS last_stats,
    NOW() - GREATEST(last_analyze, last_autoanalyze) AS age
FROM pg_stat_user_tables
WHERE GREATEST(last_analyze, last_autoanalyze) < NOW() - INTERVAL '7 days'
ORDER BY age DESC;

-- Oracle: Stale stats flag
SELECT table_name, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE stale_stats = 'YES' AND owner = 'SALES';

Approach 3: Estimate vs. Actual Comparison

The gold standard: compare optimizer estimates to actual execution results.

-- PostgreSQL: EXPLAIN ANALYZE shows both
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending';

-- Look for: "rows=1000 actual rows=150000"
-- 150x difference indicates serious statistics problem!

-- SQL Server: Include Actual Execution Plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Execute query and compare EstimatedRows vs ActualRows

Different workload patterns require different statistics maintenance strategies. Here are battle-tested best practices.

Special Considerations for Partitioned Tables:

-- PostgreSQL 14+: Incremental statistics inheritance
-- Child partition stats roll up to parent automatically

-- Oracle: Incremental statistics for partitions
EXEC DBMS_STATS.SET_TABLE_PREFS(
    'SALES', 'ORDERS',
    'INCREMENTAL', 'TRUE'
);
-- Only new/modified partitions are scanned; global stats updated incrementally

-- SQL Server: Partition-level statistics
CREATE STATISTICS stat_orders_p202401
ON orders(order_date)
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
WITH FULLSCAN;

Fresh statistics are generally desirable, but they can cause plan instability—queries suddenly using different plans after statistics refresh. This is both a feature (adapting to new data) and a risk (regression).

The Plan Regression Problem:

Before ANALYZE:
  Query uses Hash Join (runs in 2 seconds)
  Estimated rows: 1,000

After ANALYZE:
  Statistics show actual rows: 500,000
  Optimizer now chooses Sort-Merge Join (runs in 30 seconds!)
  
The 'better' statistics led to a worse plan.
How is this possible?

Causes of Post-Statistics Regression:

1. Statistics are now accurate, but cost model is wrong — Hash join was actually better despite higher cardinality
2. Parameter sniffing interaction — Cached plan for old stats, recompiled with new stats for different parameter
3. Histogram cliff — Query value moved to different histogram bucket with wildly different frequency
4. Missing index that was previously masked — Old plan hid an indexing problem

Mitigation Strategies:

-- 1. Lock statistics on stable tables
-- PostgreSQL (not directly supported; control via autovacuum settings)
ALTER TABLE stable_table SET (autovacuum_enabled = false);

-- Oracle: Explicit lock
EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'REFERENCE_DATA');

-- SQL Server: Disable auto-update on specific stats
ALTER INDEX ALL ON stable_table SET (STATISTICS_NORECOMPUTE = ON);

-- 2. Plan guides/baselines to enforce specific plans
-- SQL Server: Plan Guide
sp_create_plan_guide ...

-- Oracle: SQL Plan Baseline
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(...);

-- PostgreSQL: Hint extension or pg_plan_guarantee (3rd party)

-- 3. Test statistics before promoting
-- Oracle: Pending statistics
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');
-- Test queries with pending stats
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
-- If good, publish
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES', 'ORDERS');

Proactive monitoring prevents statistics-related performance incidents. Set up automated checks for early warning.