Statistics and Row Estimates

Every plan choice in this chapter is downstream of one number: how many rows the planner thinks a node will produce. Scan type, join strategy, join order, whether to parallelize, how much memory to expect a sort to need — all of it is priced from row counts, and none of those row counts is measured. They are computed from a handful of summary values that ANALYZE wrote the last time it looked at the table.

So "the planner is wrong" is almost always "the planner was told something wrong". The summaries are published in a system view, the arithmetic that turns them into an estimate is documented, and both are checkable in about a minute. This topic is where the Saturday's estimate of one row stops being a mystery and becomes a calculation you can redo by hand.

What ANALYZE Collects

For each column, ANALYZE records a small profile: null_frac, the fraction of entries that are null; avg_width, the average size in bytes, which is where a plan's width= comes from; n_distinct, the number of distinct values; most_common_vals with a matching most_common_freqs array; a histogram_bounds array describing everything not in the most-common list; and correlation, a number between -1 and +1 measuring how closely the column's logical order matches the rows' physical order on disk. All of it is readable in pg_stats.

SELECT attname, null_frac, n_distinct, correlation,
       array_length(histogram_bounds, 1) AS bounds
  FROM pg_stats
 WHERE schemaname = 'public' AND tablename = 'orders'
   AND attname IN ('placed_at', 'status');

  attname  | null_frac | n_distinct | correlation | bounds
-----------+-----------+------------+-------------+--------
 placed_at |         0 |         -1 |       0.312 |    101
 status    |         0 |          7 |       0.041 | (null)

That output has three things worth naming. n_distinct of -1 on placed_at is the negative convention: a negative value is the distinct count divided by the row count, so -1 means every value is unique and the planner should expect that to stay true as the table grows. correlation of 0.312 on the same column says the physical ordering of rows only loosely follows insertion time, which is what Chapter 7's non-HOT updates do to an append-ordered table when every order's status is rewritten four times on its way to delivered, and it is why an index scan over a date range here is far more expensive than its shape suggests. And status has a positive 7, meaning a fixed set of seven values, with no histogram at all: seven values all fit in the most-common-values list, so there is nothing left over to bucket.

A Sample, Not a Census

ANALYZE does not read the table. It takes a random sample, and the sample size is fixed by the largest statistics target among the columns being analyzed rather than by how big the table is. At the default default_statistics_target of 100 that works out to roughly 30,000 rows — the same 30,000 whether the relation holds 12,000 rows or the 1.2 billion in delivery_events. The target also sets the size of the summaries: up to 100 entries in the most-common-values list and 100 histogram buckets, which is 101 boundary values.

Raising the target buys resolution and costs time. ALTER TABLE orders ALTER COLUMN placed_at SET STATISTICS 1000 gives that column a thousand buckets instead of a hundred, sampled from ten times as many rows, so each bucket covers a tenth as much of the range and the last boundary sits far closer to the present. Raise it per column, on the columns whose estimates you have watched go wrong: raising default_statistics_target globally to fix one column makes every ANALYZE in the cluster more expensive and adds planning time to queries that had no problem. The price on the one table is a slower ANALYZE and slightly slower planning for every query touching the column, since those arrays are read on each plan.

How the Estimate Is Computed

The arithmetic is not a black box, and doing it once by hand permanently changes how plans read. For an equality against a value in the most-common list, the selectivity is the matching frequency — nothing more. For an equality against a value that is not in the list, Postgres subtracts all the most-common frequencies from one and divides the remainder by the number of remaining distinct values. For a range predicate it locates the value inside the histogram and interpolates: whole buckets below the value, plus the fraction of the bucket the value falls into, divided by the number of buckets. And for an equijoin between two unique columns it divides by the row count of the larger relation.

SELECT most_common_vals, most_common_freqs FROM pg_stats
 WHERE tablename = 'orders' AND attname = 'status';

 {delivered,cancelled,dispatched,picking,pending,refunded}
 {0.8378,0.0561,0.0402,0.0289,0.0201,0.0143}

SELECT (histogram_bounds::text::timestamptz[])[100:101] FROM pg_stats
 WHERE tablename = 'orders' AND attname = 'placed_at';

 {"2026-07-29 23:41:07+02","2026-07-31 22:58:44+02"}

Take status = 'delivered'. It sits at the head of the most-common list with a frequency of 0.8378, so the estimate is 0.8378 times 40 million, which is 33,512,400 — exactly the number the sequential scan in the scan-nodes topic reported, and exactly why that scan is sequential. Take status = 'refunded' instead and the frequency is 0.0143, giving 572,000 across the table or about 4,300 within a single month, which is a bitmap scan's territory. Now take the second query's answer, which is the setup for the fourth mechanism on this page. The histogram's last boundary is 22:58 on the 31st of July, so the histogram describes nothing after that instant, and the whole of August is off the end of the array.

Correlated Columns Multiply Wrongly

The planner assumes predicates on different columns are independent, so it estimates two of them by multiplying their selectivities. When the columns really are independent that is correct. When they are not, the result is wrong by whatever factor the correlation is worth, and always in the direction of underestimating. On orders the pair that bites is status and placed_at: an order placed in the last hour is almost certainly pending or picking, and an order from March is certainly not. Ask for pending orders from the last day and the planner multiplies 2.01% by the fraction of the table one day represents, arrives at a few hundred rows, and gets a nested loop. The real answer is nearly every pending order there is.

CREATE STATISTICS orders_status_time (ndistinct, dependencies, mcv)
    ON status, placed_at FROM orders;

ANALYZE orders;   -- nothing is collected until this runs

An extended statistics object is a table-level object you create deliberately, and it stays empty until the next ANALYZE fills it. Three kinds are available and they solve different problems. ndistinct records how many distinct combinations the columns have, which is what fixes a bad estimate on GROUP BY status, placed_at. dependencies records that one column's value implies another's, and it applies only to simple equality against constants and IN lists — not to range clauses, which is exactly why it does nothing for the pair above on its own. mcv stores the actual common combinations with their real frequencies, so the planner looks the pair up instead of multiplying, and that is the piece that fixes a query mixing an equality with a date range. Statistics on an expression are also possible since 14: CREATE STATISTICS ON (date_trunc('month', placed_at)) FROM orders gives the planner a distribution for a computed value it would otherwise have to guess at.

The Ascending-Key Problem

Here is the mechanism that runs Cartwheel's Saturday. A histogram describes the values that existed when it was built. Rows inserted afterwards with ever-increasing keys, this month's orders and today's delivery_events, lie past the last boundary, in a region the statistics say nothing about. A predicate selecting only that region therefore gets the smallest estimate the planner is willing to produce, which is one row. Not a small number: one. And the plan built on top of that one row is the plan for a single-row lookup.

The predicate does not even have to look suspicious. placed_at >= date_trunc('month', now()) is a stable expression, folded to a constant while the statement is planned, and on the first of the month that constant lands past the histogram's final boundary of 22:58 on the 31st of July. Every day of the month after that, the value is still ahead of the histogram until the next ANALYZE arrives — the difference is that after a few days the estimate being wrong no longer changes which plan wins, because the correct plan is cheap by a wide enough margin. This is a scheduling problem, not a cost-model one: the fix is analyzing the table more often than once per four million changed rows.

Keeping Statistics Fresh

Autovacuum runs an ANALYZE when the number of inserted, updated and deleted tuples since the last one exceeds autovacuum_analyze_threshold plus autovacuum_analyze_scale_factor times the table's row count. The defaults are 50 and 0.1, so on a 40-million-row table that is 4,000,050 changes before statistics are refreshed. On orders, at Cartwheel's volume, that is days. Both settings can be overridden per table, and on a large fast-growing table with a date filter in its hot path, the per-table override is the actual fix rather than a tweak.

The remaining gaps close on three habits. Run ANALYZE explicitly at the end of every bulk load, restore and migration — a freshly loaded table has no statistics at all, and the first queries against it are planned as though it were empty. Watch partitioned tables: autovacuum does not process a partitioned parent, so the parent's own statistics exist only if somebody runs ANALYZE against it. And know what an upgrade carries. Since 18 pg_upgrade preserves most optimizer statistics, where every earlier version left the new cluster with none. Extended statistics are the exception, and the objects created above need a fresh ANALYZE on the other side.