Planner statistics
What ANALYZE records, how estimates are made, and why they go wrong.
Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.
On this page
Every choice the planner makes, which index, which join, how much memory, starts from one number: how many rows it expects each step to produce. It gets that number from statistics that ANALYZE gathers about each column. When a plan is bad, a bad estimate is the most common reason, so it pays to know what the planner knows and what it has to guess.
What ANALYZE collects#
For each column, ANALYZE stores a summary in pg_stats: the fraction of nulls, an estimate of how many distinct values there are, the most common values with their frequencies, and a histogram of the remaining values split into buckets of equal population.
select attname as column_name, null_frac, n_distinct,
array_length(most_common_vals::text::text[], 1) as common_values,
array_length(histogram_bounds::text::text[], 1) as histogram_bounds
from pg_stats
where tablename = 'orders' and attname in ('status', 'user_id', 'total')
order by attname;| column_name | null_frac | n_distinct | common_values | histogram_bounds |
|---|---|---|---|---|
| status | 0 | 6 | 6 | NULL |
| total | 0 | -0.56494 | 1100 | 1101 |
| user_id | 0 | -0.19348 | 1100 | 1101 |
A negative n_distinct is a fraction of the row count (−1 means every value is unique). For status every value fits in the most-common list, so the planner knows the exact share of each.
select most_common_vals as status, most_common_freqs as frequency
from pg_stats
where tablename = 'orders' and attname = 'status';| status | frequency |
|---|---|
| {delivered,shipped,paid,pending,cancelled,refunded} | 0.70011,0.14983,0.07993,0.04005,0.01989,0.01019 |
How an estimate is made#
For status = 'pending' the planner multiplies the table's row count by the frequency of pending in the most-common list. For a range such as total > 2000 it finds where 2,000 falls in the histogram. For a value that is in neither, it spreads what is left evenly over the remaining distinct values.
select * from orders where status = 'pending';Seq Scan on orders (cost=0.00..2060.00 rows=4005 width=31) Filter: (status = 'pending'::text)
select * from orders where total > 2000;Seq Scan on orders (cost=0.00..2060.00 rows=81 width=31) Filter: (total > '2000'::numeric)
Two simplifications cause most bad estimates:
- Columns are assumed independent. For
a = 1 and b = 2the planner multiplies the two selectivities. When the columns are related, such as a city and its country, that underestimates badly. - Expressions have no statistics.
where total * 1.18 > 2400uses a fixed default guess, because nothing is known abouttotal * 1.18(see reading plans). An index on the expression gets its own statistics.
Correlated columns#
A small table of addresses in 30 cities spread over 10 countries, three cities to each country, so the city decides the country. It is created and analysed inside this example:
create table addresses as
select g as id,
'city ' || (g % 30) as city,
'country ' || (g % 30 / 3) as country
from generate_series(1, 60000) g;
analyze addresses;select count(*) from addresses where city = 'city 7' and country = 'country 2';Aggregate (cost=1284.50..1284.51 rows=1 width=8) (actual time=7.488..7.490 rows=1.00 loops=1)
Buffers: shared hit=384
-> Seq Scan on addresses (cost=0.00..1284.00 rows=200 width=0) (actual time=0.013..6.985 rows=2000.00 loops=1)
Filter: ((city = 'city 7'::text) AND (country = 'country 2'::text))
Rows Removed by Filter: 58000
Buffers: shared hit=384
Planning:
Buffers: shared hit=8
Planning Time: 0.125 ms
Execution Time: 7.520 msThe planner multiplied one-thirtieth (the city) by one-tenth (the country) and expected a tenth of the real count: every address in city 7 is in country 2, so the second condition removes nothing. Extended statistics tell it the columns depend on each other:
create statistics addresses_city_country (dependencies) on city, country from addresses;
analyze addresses;select count(*) from addresses where city = 'city 7' and country = 'country 2';Aggregate (cost=1289.00..1289.01 rows=1 width=8) (actual time=7.507..7.509 rows=1.00 loops=1)
Buffers: shared hit=384
-> Seq Scan on addresses (cost=0.00..1284.00 rows=2000 width=0) (actual time=0.016..7.019 rows=2000.00 loops=1)
Filter: ((city = 'city 7'::text) AND (country = 'country 2'::text))
Rows Removed by Filter: 58000
Buffers: shared hit=384
Planning:
Buffers: shared hit=16
Planning Time: 0.615 ms
Execution Time: 7.540 msNow the estimate matches. CREATE STATISTICS also supports ndistinct (for grouping by several columns) and mcv (most common combinations).
Keeping statistics fresh#
Autovacuum runs ANALYZE on a table after enough of it has changed. Between those runs, after a large import or delete, the statistics describe data that is no longer there. Run ANALYZE yourself after bulk changes, before the queries that depend on them. The stale statistics exercise shows a 30,000-row import turning a good plan into a terrible one until the table is analysed.
At production scale#
The same import, scaled up: three million new pending orders on top of ten million, recorded on a real PostgreSQL server before and after ANALYZE.
insert into orders select g, 1 + g % 2000000, 'pending', timestamptz '2025-01-01' + (g - 10000000) * interval '10 seconds', 10 from generate_series(10000001, 13000000) g;
Straight after the import
select o.id, u.email from orders o join users u on u.id = o.user_id where o.status = 'pending' and o.created_at >= '2025-01-01';
Gather (cost=1000.43..183677.64 rows=49 width=27) (actual time=275.598..2501.253 rows=3000000.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=12094393 read=8573
-> Nested Loop (cost=0.43..182672.74 rows=20 width=27) (actual time=253.273..2326.624 rows=1000000.00 loops=3)
Buffers: shared hit=12094393 read=8573
-> Parallel Seq Scan on orders o (cost=0.00..182503.84 rows=20 width=8) (actual time=253.179..415.773 rows=1000000.00 loops=3)
Filter: ((created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (status = 'pending'::text))
Rows Removed by Filter: 3333333
Buffers: shared hit=94391 read=8573
-> Index Scan using users_pkey on users u (cost=0.43..8.45 rows=1 width=27) (actual time=0.002..0.002 rows=1.00 loops=3000000)
Index Cond: (id = o.user_id)
Index Searches: 3000000
Buffers: shared hit=12000002
Planning:
Buffers: shared hit=8
Planning Time: 0.228 ms
JIT:
Functions: 24
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.720 ms (Deform 0.516 ms), Inlining 0.000 ms, Optimization 1.840 ms, Emission 21.064 ms, Total 24.623 ms
Execution Time: 2629.065 msanalyze orders;
After ANALYZE
select o.id, u.email from orders o join users u on u.id = o.user_id where o.status = 'pending' and o.created_at >= '2025-01-01';
Gather (cost=48089.00..318632.51 rows=772767 width=27) (actual time=983.005..1577.288 rows=3000000.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=119017 read=6589, temp read=21708 written=22004
-> Parallel Hash Join (cost=47089.00..240355.81 rows=321986 width=27) (actual time=957.495..1351.609 rows=1000000.00 loops=3)
Hash Cond: (o.user_id = u.id)
Buffers: shared hit=119017 read=6589, temp read=21708 written=22004
-> Parallel Seq Scan on orders o (cost=0.00..184208.60 rows=321986 width=8) (actual time=226.444..395.512 rows=1000000.00 loops=3)
Filter: ((created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (status = 'pending'::text))
Rows Removed by Filter: 3333333
Buffers: shared hit=96375 read=6589
-> Parallel Hash (cost=30975.33..30975.33 rows=833333 width=27) (actual time=292.782..292.784 rows=666666.67 loops=3)
Buckets: 131072 Batches: 32 Memory Usage: 4992kB
Buffers: shared hit=22642, temp written=11532
-> Parallel Seq Scan on users u (cost=0.00..30975.33 rows=833333 width=27) (actual time=7.180..81.096 rows=666666.67 loops=3)
Buffers: shared hit=22642
Planning:
Buffers: shared hit=8
Planning Time: 0.211 ms
JIT:
Functions: 36
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 2.007 ms (Deform 0.632 ms), Inlining 0.000 ms, Optimization 1.056 ms, Emission 20.804 ms, Total 23.868 ms
Execution Time: 1693.259 msBefore ANALYZE the planner expected 49 matching orders and found 3,000,000. It chose a nested loop that looked up a user three million times, touching 12,094,393 buffers. After ANALYZE it estimated the rows within a factor of four and built one hash table of users instead, touching about 125,000 buffers and spilling the hash to disk in batches.
The time only went from 2.6 to 1.7 seconds. Every page was already in memory, so each of those twelve million buffer accesses was cheap. On a server where those pages have to come from disk, or that is busy with other queries, the bad plan's nearly hundred times more page reads are what you pay for. That is why Buffers, not time on a quiet machine, is the number to compare.
By default ANALYZE reads a random sample of 300 × default_statistics_target rows (30,000 with the default target of 100), and keeps up to 100 most-common values and histogram buckets per column. For a column with a skewed distribution, raise the target for that column only: alter table orders alter column user_id set statistics 500. (The sample database on this site sets the target high enough to read every row, so that estimates are identical on every machine.)
In MySQL#
Not run here; from the MySQL reference manual. InnoDB samples index pages to estimate row counts (persistent statistics, refreshed by ANALYZE TABLE or automatically after about 10% of a table changes). Since 8.0, ANALYZE TABLE … UPDATE HISTOGRAM ON column adds histograms for columns, which the optimizer uses for conditions on columns without an index.
Sources#
- PostgreSQL documentation: Statistics Used by the Planner, Row Estimation Examples, pg_stats, ANALYZE, CREATE STATISTICS, Extended Statistics, The Autovacuum Daemon,
default_statistics_target. - MySQL reference manual: Configuring Persistent Optimizer Statistics Parameters, Optimizer Statistics (histograms).