Manual ANALYZE and stale statistics

ANALYZE is the operation that updates the statistics the planner consumes. Without it running regularly, the planner makes decisions with old information — and as you saw in capsule 02, that leads to bad plans even when the queries and indexes are perfect.

PostgreSQL has autovacuum running in the background that calls ANALYZE automatically. But there are moments where autovacuum doesn't keep up with the pace of changes — and they're exactly the critical moments: right after a bulk load, right after a schema change, right after a massive DELETE. In those moments, running ANALYZE manually is mandatory discipline, not optional.

In this capsule you're going to learn the exact symptom that tells you "the stats are old, run ANALYZE", the command to run it properly (with its variants), and the three operational patterns where you manually make it part of the workflow: deploy scripts, post-migration, post-bulk-load.

What ANALYZE does exactly

ANALYZE takes a random sample of rows from a table and computes:

• reltuples and relpages in pg_class — an estimate of total rows and pages.
• null_frac per column — the fraction of NULLs.
• n_distinct per column — estimated distinct values.
• most_common_vals and most_common_freqs — the top frequent values.
• histogram_bounds — distribution buckets for range predicates.
• correlation — how physically ordered the rows are according to the column's order (relevant for a clustered Index Scan).

The sample size is controlled by default_statistics_target (default: 100). This means ANALYZE takes 300 × 100 = 30,000 random rows per column. For small tables that's practically everything; for large tables it's a representative statistical sample.

Important: ANALYZE doesn't lock the table for writes. It takes a ShareUpdateExclusiveLock that blocks other simultaneous ANALYZE/VACUUM on the same table, but allows concurrent SELECT, INSERT, UPDATE, DELETE. It's safe to run in production.

-- ANALYZE a specific table
ANALYZE orders;

-- ANALYZE specific columns (faster if you only care about a few)
ANALYZE orders (customer_id, status);

-- ANALYZE with verbose to see what it does
ANALYZE VERBOSE orders;
-- Output:
-- INFO:  analyzing "public.orders"
-- INFO:  "orders": scanned 18432 of 18432 pages, containing 995234 live rows
--                  and 1230 dead rows; 30000 rows in sample, 995234 estimated total rows

If you have many tables, you can run ANALYZE; without arguments and it processes all of them in the current database. Useful after a pg_restore or a massive migration.

The specific symptom: estimated rows very different from actual rows

When the stats are stale, the symptom is very specific and shows up directly in EXPLAIN ANALYZE. You're going to develop an eye for detecting it in seconds.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date >= '2026-01-01';

Look at this output:

Index Scan using idx_orders_order_date on orders
  (cost=0.43..28.47 rows=50 width=128)
  (actual time=0.024..7234.89 rows=4892341 loops=1)
  Index Cond: (order_date >= '2026-01-01'::date)
Planning Time: 0.18 ms
Execution Time: 7891.42 ms

The key field: rows=50 (estimated by the planner) vs actual rows=4892341 (what it actually returned). A difference of 100,000x.

This is the symptom of stale stats. The planner thinks the query returns 50 rows, so it happily picked Index Scan. But since it actually returns almost 5 million rows, it's doing 5 million random jumps to disk — worse than a Seq Scan.

When you see this, your first command is ANALYZE:

ANALYZE orders;
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date >= '2026-01-01';

Now the output:

Seq Scan on orders
  (cost=0.00..98421.50 rows=4915432 width=128)
  (actual time=0.012..1234.56 rows=4892341 loops=1)
  Filter: (order_date >= '2026-01-01'::date)
Planning Time: 0.21 ms
Execution Time: 1842.30 ms

An estimate close to reality (rows=4915432 vs actual 4892341). A different plan (Seq Scan because the planner now knows it's going to read almost the whole table). Execution time 4x faster.

Complete case: bulk load without ANALYZE

You're going to reproduce the problem from the root. Setup:

DROP TABLE IF EXISTS test_stats;
CREATE TABLE test_stats (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10, 2)
);

CREATE INDEX idx_test_stats_date ON test_stats(order_date);

-- Insert 100k rows all from last year
INSERT INTO test_stats (customer_id, order_date, amount)
SELECT
    (random() * 1000)::INT + 1,
    '2025-01-01'::DATE + (random() * 364)::INT,
    (random() * 1000)::NUMERIC(10, 2)
FROM generate_series(1, 100000);

-- Fresh stats
ANALYZE test_stats;

Check the current stats:

SELECT relname, reltuples, relpages
FROM pg_class WHERE relname = 'test_stats';
-- relname    | reltuples | relpages
-- test_stats |    100000 |      637

The planner thinks the table has 100k rows. And it thinks (correctly) that almost all of them are from 2025.

-- Typical query: last year's orders (correct given what the planner knows)
EXPLAIN ANALYZE SELECT * FROM test_stats WHERE order_date >= '2025-01-01';
-- Seq Scan ... rows=99876 actual rows=100000  ✓ correct

Now simulate a bulk load without ANALYZE:

-- Insert 500k rows from the current year
INSERT INTO test_stats (customer_id, order_date, amount)
SELECT
    (random() * 1000)::INT + 1,
    '2026-01-01'::DATE + (random() * 100)::INT,
    (random() * 1000)::NUMERIC(10, 2)
FROM generate_series(1, 500000);

-- We DON'T run ANALYZE — simulating a deploy script without the discipline

Check the stats:

SELECT relname, reltuples, relpages
FROM pg_class WHERE relname = 'test_stats';
-- relname    | reltuples | relpages   <-- still says 100000
-- test_stats |    100000 |     3821

Notice that relpages gets updated (PostgreSQL knows the physical size) but reltuples doesn't — that's a statistic that ANALYZE computes. The planner still believes there are 100k rows (reality is now 600k).

Query for the current year:

EXPLAIN ANALYZE SELECT * FROM test_stats WHERE order_date >= '2026-01-01';

Output with old stats:

Index Scan using idx_test_stats_date on test_stats
  (cost=0.29..4.31 rows=1 width=24)
  (actual time=0.012..423.81 rows=499876 loops=1)
  Index Cond: (order_date >= '2026-01-01'::date)

rows=1 (estimated) vs actual rows=499876. A massive discrepancy. And the plan is an Index Scan doing 500k random jumps.

Solution:

ANALYZE test_stats;
EXPLAIN ANALYZE SELECT * FROM test_stats WHERE order_date >= '2026-01-01';

Now:

Seq Scan on test_stats
  (cost=0.00..15234.00 rows=498234 width=24)
  (actual time=0.011..89.34 rows=499876 loops=1)

Correct plan. Time dropped 4-5x.

When to run ANALYZE manually

Autovacuum is good but it has latency. By default it runs ANALYZE when autovacuum_analyze_scale_factor = 0.1 (10%) of the table's rows have been modified. In these three scenarios, that latency burns you:

1. After a bulk load (massive INSERT, COPY, pg_restore)

You inserted a million rows. Autovacuum will eventually run ANALYZE. But until then, any query that touches that new data has wrong stats.

Correct pattern: put ANALYZE at the end of the bulk load script.

# Example: deploy script that loads data
psql -d production -f load_data.sql
psql -d production -c "ANALYZE orders;"
psql -d production -c "ANALYZE customers;"

Or in a migration with code:

async def load_historical_data(session: AsyncSession):
    # Bulk insert
    await session.execute(insert(Order).values(massive_list))
    await session.commit()

    # Immediate ANALYZE — discipline
    await session.execute(text("ANALYZE orders"))

2. After a schema change (DROP/CREATE INDEX, ADD COLUMN, etc.)

Schema changes partially invalidate the stats. For example, adding a NOT NULL column with a default and backfilling it changes the distribution; adding a new index requires the planner to know it exists (it knows that automatically, but the correlations can change).

Correct pattern: run ANALYZE after migrations that touch the data distribution.

# Alembic migration
def upgrade():
    op.add_column('orders', sa.Column('priority', sa.Integer(), nullable=False, server_default='0'))
    op.execute("ANALYZE orders")  # Fresh stats for the new column

3. After a massive DELETE

You deleted 30% of the table. The distribution changed drastically — e.g., if you deleted old orders, the planner still believes there are 1M rows before 2024 when there are now only 200k.

Correct pattern: VACUUM ANALYZE after a massive DELETE (combines dead-tuple cleanup + fresh statistics).

DELETE FROM orders WHERE order_date < '2023-01-01';
VACUUM ANALYZE orders;

VACUUM is necessary because DELETE doesn't free space immediately (you're going to understand why in capsule 05 about MVCC).

Configuring default_statistics_target

default_statistics_target controls how detailed the sample ANALYZE takes is. Default: 100 (takes 30,000 rows and stores up to 100 entries in most_common_vals and the histogram).

Raising it to 1000 (a reasonable maximum) makes ANALYZE take longer but produces much more precise statistics. Useful on columns with complex distributions.

-- Globally (affects all tables)
ALTER SYSTEM SET default_statistics_target = 500;
SELECT pg_reload_conf();

-- Per-column (more selective, recommended)
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

When to raise it per-column:

• Columns with very skewed distributions (some ultra-frequent values, many rare ones).
• Columns used in filters with complex predicates (BETWEEN, LIKE, date ranges).
• When you consistently see the estimate being wrong even though the stats are "fresh".

When not to raise it: if ANALYZE is already precise enough (estimated close to actual), raising the target just makes it take longer with no benefit. The rule is to adjust reactively when you detect problems, not prophylactically.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS): the golden combination

For deep stats diagnosis, the command you're going to use is:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;

• ANALYZE executes the query and measures real times.
• VERBOSE shows the exact columns selected and schemas (useful with complex queries).
• BUFFERS shows how many pages were read from disk vs cache (shared hit/read/written).

BUFFERS helps you distinguish between "slow query because it reads a lot of disk" vs "slow query because it processes a lot on CPU." If you see Buffers: shared hit=5234 read=89234, almost everything is disk reads — a good index or stats adjustment can help. If you see Buffers: shared hit=89234 read=12, almost everything is in cache — the problem is CPU, possibly an expensive join or a costly ordering.

Traps and common mistakes

1. Confusing ANALYZE with VACUUM.

They're distinct operations, even though autovacuum runs them together:

• VACUUM: cleans up dead tuples (free space that MVCC left from UPDATE/DELETE), updates the visibility map. It doesn't update the planner's stats.
• ANALYZE: updates the planner's stats. It doesn't clean up dead tuples.
• VACUUM ANALYZE: does both.

If your slow query is due to old stats and you run only VACUUM, it doesn't get fixed. If it's due to bloat (dead tuples) and you run only ANALYZE, it doesn't either. Knowing which problem you have determines which command you use.

2. Assuming autovacuum is enough for stats.

Autovacuum runs ANALYZE when ~10% of the rows have been modified. On a 1M table, that's 100k modifications — it can take minutes or hours to accumulate. At the critical moment (right after a bulk load), the stats are old. Explicit discipline beats relying on automation.

3. Running ANALYZE during the bulk load, not after.

ANALYZE takes a sample of the current state. If you run it in the middle of a massive INSERT, it captures an intermediate state that doesn't represent the final state. Always after the load has finished, not in the middle.

4. Forgetting ANALYZE after pg_restore.

pg_restore recreates tables from a dump but doesn't run ANALYZE. If you don't do it, all queries on the new instance are going to have default (terribly wrong) estimates until autovacuum takes a while to catch up. After any pg_restore, run ANALYZE; (without arguments) on the whole database.

5. Raising default_statistics_target globally "just in case".

Raising the target makes ANALYZE slower (more memory, more I/O to sample). Globally it can make autovacuum take longer on each pass and degrade general performance. Raising it per-column when you detect a specific problem is much better than a blanket setting.

6. Confusing last_analyze with last_autoanalyze.

In pg_stat_user_tables:

• last_analyze: the last manual ANALYZE (run by a human or a script).
• last_autoanalyze: the last ANALYZE run by autovacuum.

If you never ran a manual ANALYZE, last_analyze is NULL. It's not a sign of a problem — it just means only autovacuum has done it. To know when the last analyze was (manual or auto), compare the two and take the more recent one, or use GREATEST(last_analyze, last_autoanalyze).

Exercise: detect and fix old stats

You're going to reproduce a realistic scenario and diagnose it step by step.

Setup:

DROP TABLE IF EXISTS events_log;
CREATE TABLE events_log (
    id SERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_events_user ON events_log(user_id);

-- Initial load: 50k events, 1000 users distributed
INSERT INTO events_log (event_type, user_id)
SELECT
    (ARRAY['login', 'logout', 'click', 'purchase'])[floor(random() * 4)::INT + 1],
    (random() * 999)::INT + 1
FROM generate_series(1, 50000);

ANALYZE events_log;

Step 1: check the current stats.

SELECT relname, reltuples FROM pg_class WHERE relname = 'events_log';
SELECT attname, n_distinct FROM pg_stats
WHERE tablename = 'events_log' AND attname = 'user_id';

Write down the values. Expected: reltuples ≈ 50000, n_distinct ≈ 1000 (it can be negative: -0.02 ≈ 2% of the total).

Step 2: simulate a massive load without ANALYZE.

-- Insert 500k events only from users 1-10 (very different distribution)
INSERT INTO events_log (event_type, user_id)
SELECT 'click', (random() * 9)::INT + 1
FROM generate_series(1, 500000);

-- Don't run ANALYZE

Step 3: run a query that depends on the new distribution and observe.

EXPLAIN ANALYZE SELECT * FROM events_log WHERE user_id = 5;

Question: what does rows= estimated say vs actual rows=? Which plan did it pick? Is it a good choice?

Step 4: fix it and compare.

ANALYZE events_log;
EXPLAIN ANALYZE SELECT * FROM events_log WHERE user_id = 5;

Question: did the estimate change? Did the plan change? Did the time change?

Step 5: propose an operational pattern.

If your app loaded data like this regularly (bulk inserts every hour with a very different distribution from the existing base), what pattern would you put in place so the planner always has fresh stats?

Index Scan using idx_events_user on events_log
  (cost=0.29..187.45 rows=50 width=...)
  (actual time=0.024..892.34 rows=49876 loops=1)

Seq Scan on events_log
  (cost=0.00..12834.50 rows=49892 width=...)
  (actual time=0.011..89.23 rows=49876 loops=1)

# Wrapper for bulk loads with automatic ANALYZE
async def bulk_insert_with_analyze(
    session: AsyncSession,
    table_name: str,
    rows: list,
):
    await session.execute(insert(table).values(rows))
    await session.commit()
    # ANALYZE post-commit
    await session.execute(text(f"ANALYZE {table_name}"))

Summary and next step

What you learned:

• ANALYZE updates the statistics the planner consumes (pg_class.reltuples, pg_stats.n_distinct, MCV, histogram).
• The specific symptom of stale stats: estimated rows very different from actual rows in EXPLAIN ANALYZE.
• Three critical moments to run a manual ANALYZE: post bulk load, post schema change, post massive DELETE. Autovacuum doesn't keep up at those moments.
• ANALYZE doesn't lock for writes — it's safe in production.
• Per-column default_statistics_target raises precision when necessary; raising it globally "just in case" isn't a good idea.
• VACUUM and ANALYZE are distinct operations — knowing which one you need depends on the problem.

Before moving on, you should be able to:

• Look at an EXPLAIN ANALYZE and detect in under 10 seconds whether the stats are old.
• Know where to put ANALYZE in your deploy script and why.
• Distinguish last_analyze (manual) from last_autoanalyze (auto) in pg_stat_user_tables.
• Decide between ANALYZE, VACUUM, and VACUUM ANALYZE depending on the problem.

In the next capsule we go to a case that ANALYZE can't fix: when two columns are correlated (e.g., country and city), the basic statistics assume independence and dramatically underestimate the result. You're going to learn CREATE STATISTICS (PostgreSQL 10+), a little-known feature that solves this elegantly and often gives 10x speedups on specific queries.

Resources

1. PostgreSQL Docs — ANALYZE — reference for the command with all its options.
2. PostgreSQL Docs — Updating Planner Statistics — official section on when and why to run ANALYZE.
3. Hubert "depesz" Lubaczewski — When to ANALYZE manually — analysis with real cases.
4. pganalyze — How to analyze statistics in Postgres — a modern operational guide.
5. Brandur Leach — pg_stat_statements — the relationship between query profiling and planner stats.
6. PostgreSQL Wiki — Don't Do This: default_statistics_target — anti-patterns of stats tuning.

Capsule 03 of 08 — Module 7 — Database Performance & Query Tuning Guide