skip to content

In PostgreSQL, what does the ANALYZE command do, how does it differ from VACUUM and EXPLAIN ANALYZE, and when should you run it by hand?

level: juniorimportance: must knowfreq 66%

answer

  1. a sample, not a full scan
  2. pg_statistic plus pg_class row counts
  3. reclaims nothing, executes nothing
  4. SHARE UPDATE EXCLUSIVE lock
  5. autovacuum never sees temp tables

basics

~20 s

ANALYZE samples a table and rewrites its planner statistics in pg_statistic (shown by pg_stats). Unlike VACUUM it reclaims no space; unlike EXPLAIN ANALYZE it runs no query. Run it by hand after bulk loads, on temporary tables and after changing statistics settings.

solid answer

~40 s

`ANALYZE` reads a random sample of a table's rows and stores per-column statistics in `pg_statistic`, readable through the `pg_stats` view: null fraction, distinct count, most-common values, histogram and physical correlation. It also refreshes `pg_class.reltuples` and `relpages`. It does not remove dead tuples (that is `VACUUM`; `VACUUM ANALYZE` does both), and it is unrelated to `EXPLAIN ANALYZE`, which executes one query and reports actual row counts. It takes only a `SHARE UPDATE EXCLUSIVE` lock, so reads and writes carry on, and unlike `VACUUM` it may run inside a transaction block. Autovacuum normally analyzes tables for you. Run it yourself right after a bulk load that queries hit immediately, on temporary tables (autovacuum cannot access them), and after `CREATE STATISTICS` or `ALTER TABLE ... SET STATISTICS`, which change nothing until the next `ANALYZE`.

code

sql · 7 lines
sql
CREATE TEMPORARY TABLE incoming_addresses (LIKE delivery_addresses);
INSERT INTO incoming_addresses SELECT * FROM staging_address_batch;
ANALYZE incoming_addresses;  -- autovacuum never sees temporary tables

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

go deeper

for a junior

Recall that ANALYZE refreshes planner statistics from a sample, that VACUUM reclaims space, and that EXPLAIN ANALYZE runs a query. Name pg_stats as the place to look.

for a middle

Explain the lock it takes and why reads and writes continue, and list the cases autovacuum does not cover: bulk loads queried at once, temporary tables, new statistics settings.

for a senior

Show you would build ANALYZE into load and restore scripts after the data lands, and confirm with last_analyze and last_autoanalyze before blaming the planner.

for a principal

Frame statistics freshness as part of the data pipeline's contract: which jobs own an ANALYZE step, and how a missing one shows up as plan regressions.

## What ANALYZE writes `ANALYZE` is PostgreSQL's command for refreshing the **planner statistics** of one table, a list of tables or, with no argument, every table in the current database the caller is allowed to maintain. It reads a **random sample** of rows instead of the whole table, and records for each column: - `null_frac`, the fraction of entries that are null; - `n_distinct`, the estimated number of distinct values (negative when stored as a ratio of the row count); - `most_common_vals` and `most_common_freqs`, the most-common-value (MCV) list and its frequencies; - `histogram_bounds`, the edges of roughly equal-population buckets for the remaining values; - `correlation`, how closely the physical row order follows the column's sort order. These rows are written to the catalog `pg_statistic`, which only superusers can read. The `pg_stats` view shows them in a readable form, limited to tables the current user may read. `ANALYZE` also refreshes `pg_class.reltuples` and `relpages`, the table's row and page counts, and from the same sample it fills any **extended statistics objects** created with `CREATE STATISTICS`. A table with no statistics at all is planned from built-in default selectivities, for example 0.5% of the rows for an equality filter, which is rarely close to the truth. ## Three commands that sound alike | Command | What it does | Executes a query? | Reclaims space? | |---|---|---|---| | `ANALYZE t` | samples `t` and rewrites its planner statistics | no | no | | `VACUUM t` | removes dead row versions so their space can be reused | no | yes | | `VACUUM ANALYZE t` | both, vacuum first | no | yes | | `EXPLAIN ANALYZE <query>` | runs one query and prints its plan with actual rows and timings | yes | no | The last row is the classic screening trap. The `ANALYZE` option of `EXPLAIN` has nothing to do with statistics: it really executes the statement (wrap DML in a transaction you roll back) and shows how far the estimates were from reality. The `ANALYZE` *command* is what changes those estimates. ## Locking and transactions `ANALYZE` takes a **`SHARE UPDATE EXCLUSIVE`** lock on each table it processes. That mode does not conflict with the `ACCESS SHARE` lock of a `SELECT` or the `ROW EXCLUSIVE` lock of `INSERT`, `UPDATE` and `DELETE`, so the application keeps reading and writing while it runs. It does conflict with itself and with stronger modes, so a concurrent `VACUUM`, another `ANALYZE`, a plain `CREATE INDEX` and most `ALTER TABLE` forms wait for it, or it waits for them. Unlike `VACUUM`, `ANALYZE` may be run inside a transaction block. ## When autovacuum is enough, and when it is not In the default configuration the **autovacuum** daemon analyzes tables automatically as their contents change; when it decides to do so is an autovacuum-tuning topic. You run `ANALYZE` by hand when you cannot wait for it: 1. **Right after a bulk load** that queries will hit straight away: a batch import, a restore, a large `INSERT ... SELECT`. Until fresh statistics exist, the planner works from stale ones or from defaults. 2. **On temporary tables.** Autovacuum cannot access them, so a session that fills one and then joins against it has to analyze it itself. 3. **After changing what is collected**: `CREATE STATISTICS`, `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS` or `SET (n_distinct = ...)`. Each only records intent, and the next `ANALYZE` does the work. 4. **When autovacuum is disabled** for the table or for the whole server. Put the `ANALYZE` **after** the load, never only before it. If the table is completely empty, `ANALYZE` records nothing new and keeps whatever statistics existed, so analyzing a freshly truncated table teaches the planner nothing about the rows you are about to insert. ## Checking that it happened - `pg_stat_user_tables` shows `last_analyze` (manual runs), `last_autoanalyze` and `n_mod_since_analyze`, the number of rows changed since the table was last analyzed. - `ANALYZE VERBOSE` prints progress and sampling details for each table it processes. - `pg_stat_progress_analyze` shows an `ANALYZE` of a large table while it is still running. ## Scope notes The thresholds and scale factors that trigger auto-analyze belong to autovacuum tuning. Analyzing a partitioned parent, which autovacuum never processes, belongs to partitioning. Reading estimated against actual rows in a plan belongs to `EXPLAIN`. This answer covers the command itself: what it writes, what it locks and when you must run it.

  • A nightly PostgreSQL job runs TRUNCATE, then ANALYZE, then a bulk INSERT into delivery_addresses. What is wrong with that order?
    The `ANALYZE` runs on an empty table, so it records nothing new and keeps the old statistics. The new rows arrive afterwards, and queries run against outdated numbers until auto-analyze catches up. Move the `ANALYZE` to after the load, in the same job, so the first queries on the new data are planned with statistics that describe it.
  • Does a manual ANALYZE on a busy PostgreSQL table block the application's INSERT and UPDATE statements?
    No. `ANALYZE` holds `SHARE UPDATE EXCLUSIVE`, which does not conflict with the `ROW EXCLUSIVE` lock that DML takes or with `SELECT`'s `ACCESS SHARE`. It does conflict with `VACUUM`, another `ANALYZE`, a plain `CREATE INDEX` and most `ALTER TABLE` forms, so expect those to wait, or the `ANALYZE` to wait behind them.

  • ANALYZE and EXPLAIN ANALYZE are the same command with different output
  • ANALYZE removes dead rows, so VACUUM is unnecessary afterwards
  • ANALYZE reads every row so its statistics are exact
  • ANALYZE locks out writes, so it needs a maintenance window
  • Autovacuum keeps temporary tables analyzed like any other table