Stale Statistics After Bulk Loads #

The most reproducible plan disaster in PostgreSQL is also the easiest to prevent: load millions of rows, run a query, and watch the planner choose a shape that made sense for the table as it was ten minutes ago. Nothing is broken. The planner simply has not been told that the data changed.

This page covers the fingerprint of the failure, the exact window during which it can occur, and where the fix belongs in a load job. The broader maintenance picture is in autovacuum and plan stability.

The Window Where Plans Are Wrong #

Statistics are refreshed only by ANALYZE. COPY, INSERT … SELECT, and ORM batch writers all increment n_mod_since_analyze but change nothing the planner reads until an analyze pass runs.

Autovacuum will eventually notice, once:

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples

With defaults (50 and 0.1), a table holding 200 million rows needs 20 million modifications before the daemon acts. A 10-million-row load into that table therefore triggers nothing at all — and every plan touching it is built from pre-load statistics until some later write pushes the counter over.

Two variants are worse than the general case. A newly created table has no statistics whatsoever, so the planner falls back to hard-coded defaults that assume a small relation. And a TRUNCATE followed by a reload keeps the old statistics, which now describe data that no longer exists.

The bad-plan window after a load A timeline with three marks: load start, load end, and the next autoanalyze. The shaded region between load end and autoanalyze is labelled as the window in which queries are planned from pre-load statistics. COPY starts COPY ends autoanalyze runs bad-plan window estimates describe the pre-load table put ANALYZE here — inside the load job statistics valid valid again

Annotated EXPLAIN Evidence #

The signature is a nested loop chosen on a tiny estimate, running millions of times:

-- Immediately after loading 4M rows for tenant 77
EXPLAIN (ANALYZE)
SELECT e.id, s.label FROM events e JOIN sessions s ON s.id = e.session_id
 WHERE e.tenant_id = 77;

Nested Loop  (cost=0.86..612.44 rows=12 width=64)
             (actual time=0.09..8123.66 rows=3941022 loops=1)
  ->  Index Scan using events_tenant_idx on events e  (cost=0.43..48.90 rows=6)
        Index Cond: (tenant_id = 77)
        (actual time=0.04..940.21 rows=3941022 loops=1)      -- estimate 6, actual 3.9M
  ->  Index Scan using sessions_pkey s  (actual time=0.001..0.001 rows=1 loops=3941022)
Execution Time: 8241.9 ms

Two tells distinguish this from every other kind of bad plan. First, the estimate is absurdly small and the actual is enormous — not off by 3×, off by 650,000×. Second, the estimate is small because tenant_id = 77 did not appear in the most-common-value list at all when the sample was taken, so the planner used the fallback selectivity for an unseen value.

One ANALYZE later, without touching the query:

ANALYZE events;

Hash Join  (cost=48210.11..214880.44 rows=3902118 width=64)
           (actual time=612.10..2104.82 rows=3941022 loops=1)
  Hash Cond: (e.session_id = s.id)
  ->  Seq Scan on events e  (actual rows=3941022 loops=1)
        Filter: (tenant_id = 77)
  ->  Hash  (actual rows=180422 loops=1)  Batches: 1  Memory Usage: 14208kB
Execution Time: 2210.4 ms

The planner switched from a nested loop to a hash join because the estimate became honest. That is the whole fix.

Step-by-Step Resolution #

  1. Confirm the estimate collapse. Divide actual by estimated rows on the lowest node that is wrong. Ratios in the thousands point to statistics, not to indexing.

  2. Check when the table was last analyzed:

    SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze, n_live_tup
  FROM pg_stat_user_tables WHERE relname = 'events';

  3. Run ANALYZE and re-plan to prove causation before changing any process:

    ANALYZE events;

  4. Move the ANALYZE into the load job, as its last statement, so the window never opens:

    BEGIN;
COPY events FROM '/data/events_2026_07.csv' WITH (FORMAT csv);
COMMIT;
ANALYZE events;            -- outside the transaction, so it is visible immediately

  5. Use VACUUM ANALYZE when the loaded rows will be read by index-only scans, since only vacuum sets the visibility map bits those scans depend on.

  6. Raise the statistics target on skewed columns before re-analyzing, so rare-but-queried values stay in the most-common-value list:

    ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE events;

  7. Tighten the per-table scale factor so the next unplanned load is caught sooner:

    ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01);
Where ANALYZE belongs in a load job The upper pipeline runs COPY then commit then downstream queries, which get bad plans. The lower pipeline runs COPY, commit, ANALYZE, then downstream queries, which get correct plans. without ANALYZE COPY 4M rows COMMIT queries planned from stale stats 8242 ms with ANALYZE in the job COPY 4M rows COMMIT ANALYZE queries planned correctly 2210 ms

Before and After #

-- BEFORE ANALYZE
Nested Loop  (rows=12)  (actual rows=3941022)   inner loops=3941022
Execution Time: 8241.9 ms

-- AFTER ANALYZE
Hash Join    (rows=3902118)  (actual rows=3941022)  Batches: 1
Execution Time: 2210.4 ms

The metric to watch is the estimate ratio, which fell from roughly 650,000× to 1.01×. Execution time is a consequence; the ratio is the diagnosis.

Common Pitfalls #

Running ANALYZE inside the loading transaction. The statistics it writes are not visible to other sessions until commit, so concurrent readers still get old plans. Diagnostic signal: other backends planning badly while the job reports success. Fix: analyze after the commit.

Analyzing the parent of a partitioned table only. Statistics live per partition as well; the parent’s are used for cross-partition estimates. Diagnostic signal: bad estimates on partitioned queries after a load — see partition pruning analysis. Fix: analyze both the loaded partition and the parent.

Assuming TRUNCATE clears statistics. It does not; the old distribution survives and now describes nothing. Diagnostic signal: confident estimates on an empty or freshly reloaded table. Fix: ANALYZE after every reload, not only after growth.

Loading into a new table and querying immediately. With no statistics at all, the planner uses defaults tuned for small relations. Diagnostic signal: rows=1000-shaped estimates on a huge table. Fix: ANALYZE before the first query, always.

Loading into a table that is being queried concurrently. The bad-plan window is not hypothetical when a reporting job runs against the table mid-load; it sees a partially loaded relation described by pre-load statistics. Diagnostic signal: reports failing or timing out only during the ingestion window. Fix: load into a staging table, analyze it, then attach or swap it in — a metadata operation rather than a statistical cliff.

Analyzing only the columns you changed. ANALYZE table (col) refreshes one column’s statistics but leaves relpages and reltuples reflecting a table that no longer exists at that size. Diagnostic signal: correct selectivity estimates paired with a wildly wrong total row count. Fix: analyze the whole table after a load, and use column-level analyze only for targeted statistics-target changes.

Which loads need an explicit ANALYZE A new table needs one because it has no statistics at all. A truncate and reload needs one because the old distribution survives. An incremental append into a large table needs one because the threshold will not be reached. COPY into a new table no statistics exist at all → ANALYZE TRUNCATE + reload the old distribution survives → ANALYZE append to a large table threshold not reached for hours → ANALYZE

Frequently Asked Questions #

Does COPY update planner statistics? No. It loads rows and bumps the modified-tuple counter, but only ANALYZE — manual or from autovacuum — rewrites the statistics the planner reads.

How long is the bad-plan window? From the end of the load until the next analyze pass. That depends on the naptime and on whether the modification count crossed the per-table threshold, which on a large table with default scale factors can take hours.

Should I run VACUUM ANALYZE or just ANALYZE? ANALYZE is what the planner needs, and a pure insert load creates no dead tuples. Use VACUUM ANALYZE when the new rows will be read by index-only scans, since only vacuum sets the visibility map bits they rely on.