ANALYZE updates the statistical model PostgreSQL uses to estimate query results. It does not rewrite the query, rebuild its indexes, remove dead tuples, or promise a faster plan. Its useful question is specific: does the planner understand the current distribution well enough to compare access paths? A bulk status change can invalidate that understanding even when the table still contains exactly the same number of live rows.

What the statistics describe

For ordinary columns, pg_stats exposes information such as null fractions, common values and their frequencies, distinct-value estimates, and histograms. The planner combines that information with table size and cost settings. Knowing that one percent of jobs are ready suggests a different amount of work from knowing that ninety percent are ready. An index can help the former while reading the table sequentially can suit the latter.

On large tables, analysis samples rows. More detailed statistics require a larger sample and additional work; sampling does not mean every analysis is cheap. Approximate statistics can change slightly between runs without any application change. Raising a particular column’s statistics target can improve a poorly represented distribution, but it cannot teach ordinary single-column statistics every relationship between columns.

When it breaks

After a bulk update, an old common-value frequency may badly understate a filter’s output. A nested loop can consequently repeat far more work than expected, or an index scan can fetch most of the table. However, an unused index alone is not evidence of failure: a broad query may legitimately prefer a sequential scan.

Repeatedly analyzing a table while a query waits on another transaction will not release that transaction’s lock. Statistics do not create missing indexes or resolve connection waits. Required ordering still needs a sort or an already ordered access path. Compare the first meaningful cardinality divergence with the observed bottleneck before deciding that statistics are the cause.

Check it quickly

Run this in psql against a disposable PostgreSQL 18 database. It uses a temporary table inside a transaction and finishes with rollback. Temporary tables are deliberately useful here because autovacuum cannot analyze them between the controlled measurements.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE jobs (id integer,ready boolean,payload text);
INSERT INTO jobs SELECT g,g<=100,repeat('x',100) FROM generate_series(1,10000) AS g;
CREATE INDEX jobs_ready ON jobs(ready);
ANALYZE jobs;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready;
UPDATE jobs SET ready=true WHERE id<=9000;
SELECT most_common_vals,most_common_freqs FROM pg_stats
WHERE schemaname=(SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema())
  AND tablename='jobs' AND attname='ready';
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready;
ANALYZE jobs;
SELECT most_common_vals,most_common_freqs FROM pg_stats
WHERE schemaname=(SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema())
  AND tablename='jobs' AND attname='ready';
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready;
DO $$ BEGIN
  IF (SELECT count(*) FROM jobs WHERE ready) <> 9000 THEN RAISE EXCEPTION 'data changed during analysis'; END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, the first query estimated and returned 100 ready jobs through jobs_ready. The update changed the ready population to 9,000. Before the second analysis, pg_stats still displayed {f,t} with frequencies {0.99,0.01}: the distribution model had not learned the change.

The intermediate plan estimated 190 rows and returned 9,000. Do not demand that the estimate remain exactly 100: PostgreSQL can scale relation estimates using current physical size, which changed after the update. The stale one-percent frequency, not one frozen row-count number, is the decisive observation.

After ANALYZE, the frequencies became {0.9,0.1} for {t,f}, and this environment selected a sequential scan estimating and returning 9,000 rows. The assertion confirms that analysis did not modify the qualifying population. Plan shapes, costs, and timings can vary elsewhere; the distribution change and returned counts explain the result without claiming a universal speedup.

Keep the refresh tied to evidence

Autovacuum normally schedules automatic analysis for ordinary tables after sufficient changes, so permanent blanket reductions in its thresholds are not a default remedy. Check whether analysis actually lagged the workload. Bulk imports and known distribution changes can justify an explicit analysis before latency-sensitive traffic resumes.

Partitioned parents need separate attention: automatic analysis of their leaf tables does not automatically maintain the parent’s hierarchy statistics. Temporary tables also require manual analysis in their owning session. In PostgreSQL 18, table maintenance privileges govern who can analyze a table; examine skipped-table messages instead of treating a completed command as proof that every intended table was refreshed. Recheck the affected plan and application latency after the refresh, then investigate other causes if the evidence no longer points to statistics.

Sources