PostgreSQL’s query planner is genuinely impressive at figuring out the most efficient way to execute a query, but it can only make good decisions if it has accurate information about your data. That information comes from statistics — things like how many rows are in a table, how values are distributed across a column, and how selective a given filter is likely to be. The ANALYZE command is what generates and refreshes those statistics.
In this article, I’ll cover what ANALYZE actually does, its syntax and options, how it interacts with the query planner, practical examples, and how to troubleshoot cases where stale or inaccurate statistics are silently hurting your query performance.
What Is the ANALYZE Command?
ANALYZE collects statistics about the contents of tables and stores them in PostgreSQL’s system catalogs, specifically pg_statistic (with a more human-readable view available via pg_stats). The query planner uses these statistics to estimate how many rows a given query condition is likely to match, which in turn drives decisions like whether to use an index scan or a sequential scan, which join algorithm to pick, and what order to join tables in.
If statistics are missing or badly out of date, the planner is essentially guessing, and a bad guess can lead to a dramatically inefficient query plan — even when the correct indexes exist and are perfectly healthy.
Importantly, ANALYZE doesn’t examine every single row in a table. It takes a statistically representative random sample, controlled by a configurable target, which keeps the operation fast even on very large tables.
Basic Syntax
ANALYZE [ VERBOSE ] [ table_name [ (column_name [, ...]) ] ];
Running it with no arguments analyzes every table in the current database:
ANALYZE;
Targeting a specific table:
ANALYZE orders;
Targeting specific columns within a table:
ANALYZE orders (customer_id, order_date);
VERBOSE
Prints progress and summary information as it runs, which is useful on large databases so you can track what’s happening rather than staring at a blank prompt.
ANALYZE VERBOSE orders;
How ANALYZE Fits Into the Bigger Picture
You’ll often see ANALYZE mentioned alongside VACUUM, and for good reason — they’re frequently run together, since both matter for a healthy, performant table, but they solve different problems. VACUUM reclaims space from dead tuples; ANALYZE refreshes the planner’s understanding of the data’s shape and distribution. You can run them independently, or combine them in a single command:
VACUUM ANALYZE orders;
Autovacuum, PostgreSQL’s background maintenance process, actually handles both jobs automatically for most tables based on configurable thresholds — a table gets auto-analyzed once enough rows have changed since the last analysis, similar to how autovacuum’s vacuum side triggers on dead tuple thresholds.
You can check when a table was last analyzed with:
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY greatest(last_analyze, last_autoanalyze) NULLS FIRST;
Understanding Statistics Target
PostgreSQL controls how detailed the collected statistics are via a setting called default_statistics_target, which defaults to 100. This roughly controls how many “buckets” are used to represent a column’s value distribution, and how many rows get sampled. Higher values mean more accurate statistics (particularly helpful for columns with skewed or unusual distributions) at the cost of slower ANALYZE runs and slightly larger catalog storage.
You can check the current global setting:
SHOW default_statistics_target;
And override it for a specific column when the default isn’t giving the planner enough detail to work with:
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;
This is particularly useful for columns with highly skewed distributions — for example, a status column where 95% of rows are 'completed' and the rest are split across several rarer statuses. A low statistics target might not capture that skew accurately, leading the planner to misjudge how selective a filter on the rare values actually is.
Practical Examples
Example 1: Refreshing Statistics After a Large Data Load
ANALYZE VERBOSE orders;
Run this after bulk-loading a large batch of new rows — for example, via COPY — since the planner’s existing statistics won’t reflect the new data until you refresh them.
Example 2: Analyzing the Whole Database After a Migration
ANALYZE VERBOSE;
A sensible step after a major data migration, restore from backup, or bulk import affecting many tables at once, since restoring a database dump does not automatically populate planner statistics.
Example 3: Increasing Statistics Detail on a Skewed Column
ALTER TABLE events ALTER COLUMN event_type SET STATISTICS 300;
ANALYZE events (event_type);
Useful when you notice the planner consistently underestimating or overestimating row counts for queries filtering on a column where values aren’t evenly distributed.
Example 4: Comparing the Planner’s Estimate to Reality
This is one of the most useful diagnostic habits to build. Run EXPLAIN ANALYZE on a query and compare the “estimated rows” against “actual rows”:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
If the estimate and the actual row count are wildly different, that’s a strong signal that statistics are stale or insufficiently detailed for that column, and re-running ANALYZE (possibly with a higher statistics target) is worth trying before assuming there’s a deeper problem.
Example 5: Analyzing After Changing a Column’s Data Distribution Substantially
Say you ran a large UPDATE that changed the status column for a huge portion of rows:
UPDATE orders SET status = 'archived' WHERE order_date < '2020-01-01';
ANALYZE orders (status);
Without this, the planner would keep using pre-update statistics that no longer reflect how common each status value actually is, potentially leading to poor plan choices for queries filtering on status.
Example 6: Checking Existing Statistics for a Column
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
This shows you exactly what the planner currently believes about the distribution of values in that column — genuinely useful when trying to understand why the planner is making a particular decision.
Common Use Cases
- After bulk inserts, updates, or deletes that significantly change a table’s size or the distribution of values within it.
- After restoring a database from a backup or dump, since statistics aren’t automatically populated by a restore.
- When diagnosing unexpectedly slow queries, as a first check before assuming an index or query rewrite is needed.
- After schema changes like adding a new column with a default value across a large existing table.
- Tuning statistics detail on specific columns known to have skewed or unusual value distributions.
- Before running performance benchmarks, to ensure the planner isn’t working from outdated information that would skew results.
Troubleshooting Common Issues
Query Plans Look Wrong Even Though Indexes Exist
Before assuming an index problem, check whether statistics are stale:
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
A high n_mod_since_analyze relative to the table’s total row count suggests it’s overdue for a fresh ANALYZE.
Autovacuum-Triggered Analyze Isn’t Keeping Up
Check the relevant threshold settings:
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_analyze_threshold;
For very large or very high-churn tables, the default scale factor (a percentage of the table) can mean a long time passes before enough rows change to trigger auto-analysis. Consider lowering these thresholds for specific tables:
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);
Planner Consistently Misestimates Rows for a Specific Column
This often points to needing a higher statistics target for that column, especially if it has a skewed distribution or many distinct values that don’t fit well into the default number of histogram buckets:
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;
ANALYZE Is Slow on a Very Large Table
ANALYZE samples rows rather than scanning the whole table, so it’s normally fast even on large tables — but a very high statistics target increases the sample size needed, which can slow things down. If you’ve set a high target on a huge table and are seeing slow analyze times, weigh whether the target really needs to be that high for that particular column.
Statistics Look Fine But Query Is Still Slow
Not every slow query is a statistics problem. Once you’ve confirmed the planner’s row estimates roughly match reality, the bottleneck is more likely to be a missing index, an inefficient query structure, or a genuinely large amount of data that has to be processed regardless of the plan chosen. ANALYZE fixes bad estimates — it can’t fix a query that’s fundamentally doing more work than it needs to.
Best Practices
- Run
ANALYZEafter any bulk data operation — loads, large updates, deletes, or restores — rather than assuming autovacuum will catch up quickly enough. - Use
EXPLAIN ANALYZEto check estimate accuracy as a standard diagnostic step before reaching for more invasive fixes like new indexes. - Raise statistics targets selectively, not globally, for specific columns known to have skewed distributions — a blanket increase in
default_statistics_targetslows down every analyze operation for marginal benefit on well-distributed columns. - Monitor
n_mod_since_analyzefor your most important, highest-traffic tables to catch cases where autovacuum’s analyze threshold isn’t aggressive enough. - Don’t skip
ANALYZEafter a restore. A freshly restored database has no statistics at all until you run it, which can make early queries against a restored database perform very poorly until this is addressed. - Combine with
VACUUMin routine maintenance, since both matter together for a genuinely healthy, performant table. - Treat inaccurate row estimates as a real signal, not noise — persistent large gaps between estimated and actual rows in
EXPLAIN ANALYZEoutput are usually fixable and worth chasing down.
Extended Statistics for Correlated Columns
By default, PostgreSQL’s statistics are collected independently, one column at a time. This works fine for most queries, but it can lead the planner astray when two or more columns are correlated in ways that aren’t obvious from looking at each column individually. A classic example is a city column and a state column — knowing a row’s city often tells you a lot about its state, but the planner, looking at each column’s statistics separately, has no way to know that unless you tell it.
PostgreSQL addresses this with extended statistics objects, created via CREATE STATISTICS:
CREATE STATISTICS orders_city_state_stats (dependencies)
ON city, state FROM orders;
ANALYZE orders;
After running ANALYZE, the planner can use this extended statistics object to make better row estimates for queries that filter on both city and state together, rather than naively multiplying the individual selectivities of each column (which tends to badly underestimate the actual row count when the columns are correlated).
You can also collect statistics on the number of distinct combinations of columns (useful for GROUP BY estimates) or most-common-values combinations:
CREATE STATISTICS orders_city_state_ndistinct (ndistinct)
ON city, state FROM orders;
CREATE STATISTICS orders_city_state_mcv (mcv)
ON city, state FROM orders;
This is a more advanced feature than most day-to-day ANALYZE usage requires, but it’s worth knowing about when you’ve confirmed via EXPLAIN ANALYZE that the planner is significantly misestimating rows for queries involving multiple correlated columns together, even after routine ANALYZE and appropriate per-column statistics targets.
Manually Inspecting the Histogram and Most Common Values
For a genuinely deep dive into what the planner believes about a column, pg_stats exposes the full picture:
SELECT
attname,
null_frac,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
null_frac— the estimated fraction of rows where this column is null.n_distinct— the estimated number of distinct values (a negative number here means it’s expressed as a negative fraction of total rows, indicating the distinct count scales with table size, which is typical for something like a foreign key).most_common_vals/most_common_freqs— the most frequently occurring values and their observed frequency, directly informing selectivity estimates for equality filters on those specific values.histogram_bounds— for values not among the most common, a set of boundary values dividing the column’s remaining data into roughly equal-sized buckets, used to estimate selectivity for range queries.
Understanding this output is genuinely useful when you’re trying to figure out exactly why the planner made a particular row estimate, rather than just accepting “the estimate was wrong” without understanding the mechanism behind it.
Analyzing Partitioned Tables
If you’re working with a partitioned table (declarative partitioning), it’s worth understanding how ANALYZE behaves across the partition hierarchy. Running ANALYZE on the parent table collects statistics both for the parent itself (an aggregate view useful for planning queries against the whole partitioned table) and cascades down to analyze each individual partition:
ANALYZE orders; -- the partitioned parent table
You can also target a specific partition directly if you know only that partition’s data has changed significantly, avoiding the overhead of re-analyzing every partition unnecessarily:
ANALYZE orders_2026_01;
Autovacuum handles both levels similarly — it can trigger analysis on individual partitions independently as their own data changes, while also periodically refreshing the parent-level aggregate statistics. When diagnosing planner issues on partitioned tables specifically, it’s worth checking statistics freshness at both levels rather than assuming that analyzing the parent alone is sufficient, or that analyzing partitions alone keeps the parent-level statistics current — in some PostgreSQL versions, parent-level statistics for a partitioned table require an explicit ANALYZE on the parent to refresh.
The Relationship Between ANALYZE and Query Plan Stability
A somewhat counterintuitive scenario worth knowing about: refreshing statistics with ANALYZE can occasionally cause a previously fast query to suddenly get a different, sometimes worse, execution plan — not because anything is broken, but because the planner’s understanding of the data genuinely changed, and its new estimate leads it toward a different (and not always better) plan choice than before.
This is one of the reasons some teams are cautious about running ANALYZE on a lightly-tested basis in production without first validating plan changes in a staging environment for their most performance-critical queries. If you notice a query’s plan shift unexpectedly right after a manual or automatic ANALYZE, comparing EXPLAIN ANALYZE output before and after (if you have the old plan captured, for instance via query logging) is the most direct way to understand exactly what changed in the planner’s row estimates that led to the different decision.
Sample Size and the Statistics Target Trade-off in Practice
It’s worth being concrete about what raising default_statistics_target (or a per-column SET STATISTICS) actually costs versus what it buys you. A higher target means ANALYZE samples more rows from the table to build its histogram and most-common-values lists, which means:
- More accurate representation of the true data distribution, particularly for columns with many distinct values or unusual skew.
- Larger entries stored in
pg_statistic, meaning marginally more catalog storage and slightly more planning time per query referencing that column, since the planner has more statistical data to consult. - A longer-running
ANALYZEoperation for that table, since a larger sample needs to be read and processed.
For most columns on most tables, the default target of 100 is genuinely sufficient, and raising it indiscriminately across an entire large database mostly just slows down routine maintenance for little practical benefit. Reserve higher targets for the specific columns where you’ve actually observed poor estimates in EXPLAIN ANALYZE output, rather than applying a blanket increase preemptively.
Wrapping Up
ANALYZE doesn’t get as much attention as indexing strategy or query rewriting, but it’s foundational to both. A perfectly designed index is only as useful as the planner’s ability to recognize when to use it, and that recognition depends entirely on accurate, up-to-date statistics. Making ANALYZE a routine part of your maintenance process — after bulk operations, after restores, and as a first diagnostic step for unexpectedly slow queries — is one of the simplest, lowest-risk things you can do to keep PostgreSQL’s query planner making good decisions.