ANALYZE Command in PostgreSQL Explained

The PostgreSQL ANALYZE command collects statistics about the contents of tables and indexes, and stores them in the system catalog. These statistics let the query planner estimate how many rows a query will return, which is the basis for choosing efficient join orders, join methods, and index usage. Without fresh statistics, the planner works blind and can pick a terrible plan on a table that has grown or changed significantly.

What Statistics Does ANALYZE Collect?

When you run ANALYZE on a table, PostgreSQL scans a sample of rows and records:

• Number of rows and number of pages the table occupies.
• Null fractions — how many rows have NULL in each column.
• Average data width for variable-length columns.
• Most common values (MCV) and their frequencies, for non-uniform distributions.
• Histogram bounds for range queries on non-uniform data.
• Correlation between physical row order and logical column order (helps index scans).
• For expression indexes, statistics on the expression’s output.

These numbers are stored in pg_statistic (visible only to superusers) and exposed in readable form via pg_stats. The planner reads them to estimate selectivity — the fraction of rows that match a WHERE clause.

Why the Planner Needs Statistics

Query planning is a cost-based optimization problem. For each possible plan, PostgreSQL estimates the cost of reading pages and processing rows. The central input is the expected number of rows at each step. For example, to decide between a sequential scan and an index scan on a WHERE price < 100 predicate, the planner needs to know what fraction of rows have price below 100. That fraction comes from the histogram. If the table has grown from 1,000 rows to 10,000,000 rows since the last ANALYZE, the planner still thinks it has 1,000 rows, so it may choose a nested loop join that runs for hours instead of a hash join that finishes in seconds.

Statistics also drive join cardinality estimates. For a join on two columns, the planner uses the per-column statistics and assumes independence unless extended statistics are created. That assumption is often wrong, which is why PostgreSQL 10 introduced CREATE STATISTICS for functional dependencies, MCV lists on multiple columns, and ndistinct coefficients.

How to Run ANALYZE

The basic command is simple:

ANALYZE;

This analyzes all tables in the current database that the user has permission to analyze. You can target specific tables and columns:

ANALYZE orders;
ANALYZE orders (customer_id, total_amount);

Running ANALYZE on a column list is useful when you only care about statistics for particular columns, saving time on very wide tables. You can also combine it with VERBOSE to see what was processed:

ANALYZE VERBOSE orders;

Note that ANALYZE does not lock the table against reads or writes. It takes a share update exclusive lock briefly, which blocks DDL but not regular SELECT, INSERT, UPDATE, or DELETE. It also does not rebuild indexes or vacuum dead tuples — that is VACUUM’s job. The two commands are often run together as VACUUM ANALYZE.

Automatic Statistics Collection

You rarely need to run ANALYZE manually because PostgreSQL has an autovacuum daemon that, by default, runs ANALYZE automatically. The daemon wakes up periodically (default autovacuum_naptime = 60 seconds) and checks each table. It triggers an analyze when the number of rows changed since the last analyze exceeds a threshold:

threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Defaults are autovacuum_analyze_threshold = 50 and autovacuum_analyze_scale_factor = 0.1, meaning a table with 1,000,000 rows gets analyzed after about 100,050 row changes. For small tables, the threshold of 50 means they get analyzed frequently, which is fine. For huge tables, 10% changes can be a lot; you may want to lower the scale factor or set a per-table autovacuum_analyze_scale_factor to 0.01 or even 0.001 for critical tables.

Autovacuum also runs after bulk operations like COPY or CREATE TABLE AS SELECT if the table is large enough, but not always. After a major data load, it is wise to run ANALYZE manually.

When to Run ANALYZE Manually

Besides the obvious case of a freshly loaded table, manual ANALYZE is useful in several scenarios:

• After bulk updates or deletes that affect a large fraction of rows, even if autovacuum hasn’t kicked in yet.
• After creating a new index — the index itself doesn’t need statistics, but the planner needs updated table statistics to decide whether to use it.
• After changing default_statistics_target or per-column statistics targets. The new target only takes effect after the next analyze.
• After restoring a dump or running pg_restore — the restored statistics are often stale or absent.
• When you notice a query plan is bad and the table has changed significantly since the last analyze. Check pg_stat_user_tables for last_analyze and n_mod_since_analyze.

Common Problems and Misconceptions

One common misconception is that ANALYZE fixes slow queries caused by missing indexes. It does not create indexes; it only improves the planner’s estimates. If a query is slow because of a full scan on a large table, an index might help, but ANALYZE alone won’t speed it up — it might just make the planner choose the index that already exists.

Another issue is that statistics can be misleading on columns with skewed data. The default default_statistics_target is 100, meaning the histogram has up to 100 bins and the MCV list up to 100 entries. For highly skewed columns (e.g., a status column where 99% of rows are ‘completed’), the MCV list may not capture the rare values well. You can increase the target per column:

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

This gives the planner a finer histogram and a longer MCV list, improving estimates for rare values.

A third problem is that statistics are per-table, not per-query. If you have a query that joins two tables on a column with a strong correlation to another column, the planner assumes independence and may misestimate. Extended statistics (CREATE STATISTICS) can capture functional dependencies and multivariate MCVs.

Finally, remember that ANALYZE only samples rows, not the whole table. The default sample is 30,000 rows (controlled by default_statistics_target times 300). For very large tables, this sample is usually enough, but for pathological distributions it can be off. Increasing the statistics target also increases the sample size.

ANALYZE vs. VACUUM vs. REINDEX

These three maintenance commands are often confused:

• VACUUM reclaims storage occupied by dead tuples and updates the visibility map. It does not update statistics (unless you run VACUUM ANALYZE).
• ANALYZE updates statistics only. It does not reclaim space or remove dead tuples.
• REINDEX rebuilds an index from scratch, removing bloat and improving index performance. It does not update table statistics.

Running VACUUM ANALYZE does both in one command, which is common after bulk loads. REINDEX is separate and usually needed only after heavy updates or if an index becomes corrupted.

Cost and Performance Impact

ANALYZE reads a sample of rows, so its cost is proportional to the sample size, not the whole table. For a typical table with default settings, it reads a few thousand pages. It also writes the statistics to the system catalog, which is small. In practice, ANALYZE is fast — often under a second for tables up to millions of rows. The main cost is I/O on the sampled pages, which can compete with production queries. Running it during low traffic is a good practice, but it is safe to run anytime because it does not block reads or writes.

The benefit far outweighs the cost: a single bad plan due to stale statistics can cost hours of query time. Setting a sensible autovacuum analyze threshold and manually analyzing after bulk changes is one of the highest-ROI maintenance tasks in PostgreSQL.

Frequently Asked Questions

How often does PostgreSQL run ANALYZE automatically?

Autovacuum runs ANALYZE when the number of rows changed since the last analyze exceeds autovacuum_analyze_threshold (default 50) plus autovacuum_analyze_scale_factor (default 0.1) times the current row count. The daemon checks every autovacuum_naptime (default 60 seconds). You can see the last analyze time in pg_stat_user_tables.

Does ANALYZE lock the table?

It takes a brief SHARE UPDATE EXCLUSIVE lock, which blocks concurrent DDL (like ALTER TABLE) but not SELECT, INSERT, UPDATE, or DELETE. The lock is held only for the duration of the analyze, which is usually short.

What is the difference between ANALYZE and VACUUM?

VACUUM cleans up dead rows and updates the visibility map, freeing space for reuse. ANALYZE updates planner statistics. They are independent; you can run one without the other. VACUUM ANALYZE does both.

Why is my query still slow after running ANALYZE?

If the statistics are fresh but the query is still slow, the problem is likely a missing index, a poorly written query, or a planner limitation like correlation between columns. ANALYZE only improves estimates; it cannot fix a query that requires a different join strategy or index. Check the execution plan with EXPLAIN to see where the cost is high.

Can I run ANALYZE on a single column?

Yes, ANALYZE table (column1, column2) analyzes only those columns. This is useful for wide tables where you only need statistics for a few columns, saving time. However, if you later add a new column, you need to run ANALYZE again to include it.

Does ANALYZE work on partitioned tables?

Yes. Running ANALYZE on a partitioned table analyzes all partitions. You can also analyze individual partitions. The parent table’s statistics are aggregated from the partitions. In PostgreSQL 14 and later, partitioned tables have their own statistics that are updated when you analyze the parent.

Related

• Starlink Satellite Texting: First Nationwide Service?
• Uninstall Skype for Business: Full Removal Guide
• Export iCloud Contacts to CSV: Full Guide