PostgreSQL Query Slow After Bulk Insert (VACUUM ANALYZE Required)

A query that was fast before a large bulk insert but becomes slow immediately afterward almost always means PostgreSQL's query planner is working from outdated statistics — it doesn't yet know the table's actual current size and data distribution, so it makes a poor execution plan choice based on stale information from before the bulk insert happened.

The Problem

A query that previously ran quickly against a table suddenly takes significantly longer immediately after a large bulk data load:

-- Before bulk insert: query completes in milliseconds
-- After inserting 5 million new rows: same query now takes several seconds

Checking EXPLAIN for the slow query might show a plan that doesn't reflect the table's actual current size, seemingly still optimized for the much smaller table that existed before the bulk insert.

Why It Happens

PostgreSQL's query planner relies on statistics about each table — approximate row counts, data distribution, common values — to decide the most efficient way to execute a given query (whether to use an index, which join strategy to pick, and so on). These statistics are normally kept up to date automatically by autovacuum's analyze process, but this happens on its own schedule and thresholds, not instantly after every write. A large bulk insert can significantly change a table's actual size and distribution well before autovacuum's next scheduled analyze run gets around to it, leaving the planner working from statistics that no longer accurately reflect reality.

The Fix

Run ANALYZE manually immediately after a large bulk insert, rather than waiting for autovacuum's own schedule to catch up naturally:

ANALYZE orders;

This specifically refreshes the planner's statistics for the table without doing a full VACUUM's more expensive space-reclamation work — for the specific "planner is using stale statistics" problem, ANALYZE alone is usually the fast, targeted fix.

If the bulk insert also involved significant deletes or updates (not just pure inserts), running a full VACUUM ANALYZE together addresses both stale statistics and reclaims space from dead tuples in one pass:

VACUUM ANALYZE orders;

For bulk load operations that happen regularly as part of your normal workflow (nightly imports, scheduled data syncs), build an explicit ANALYZE step directly into that process, immediately following the bulk load, rather than relying on autovacuum's timing to happen to catch up before the data is actually queried:

-- As part of your bulk load script/process
COPY orders FROM '/path/to/bulk_data.csv' WITH CSV;
ANALYZE orders;

Check your autovacuum configuration to understand why it didn't already catch this automatically — the default thresholds are based on a percentage of the table's existing row count, which means a single very large bulk insert into a table that was previously small can genuinely exceed the threshold that would trigger autovacuum, but the timing of exactly when autovacuum notices and acts might still lag behind your immediate need to query the freshly loaded data:

SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;

For tables that regularly receive large bulk loads, consider tuning these thresholds specifically for that table to trigger autovacuum's analyze more aggressively, reducing the window during which stale statistics could affect query performance after a load:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Lowering this value from PostgreSQL's default (commonly 0.1, meaning a 10% change in row count triggers analyze) to something smaller means autovacuum analyzes more proactively after smaller relative changes, at the cost of slightly more frequent background analyze operations.

Still Not Working?

If you've run ANALYZE and confirmed updated statistics, but the query is still slower than expected, use EXPLAIN ANALYZE (not just EXPLAIN) to compare the planner's row estimates against the actual row counts encountered during real execution:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

If the estimated and actual row counts still diverge significantly even after a fresh ANALYZE, the default statistics sample size might be insufficient for your table's actual data distribution — increasing the statistics target for specific columns provides the planner with a more detailed, accurate picture at the cost of slightly more expensive analyze operations:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;