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.

Before thisReading EXPLAIN
On this page
  1. What ANALYZE collects
  2. How an estimate is made
  3. Correlated columns
  4. Keeping statistics fresh
  5. At production scale
  6. In MySQL
  7. Sources

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.

sql
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;
Result 3 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
column_namenull_fracn_distinctcommon_valueshistogram_bounds
status066NULL
total0-0.5649411001101
user_id0-0.1934811001101

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.

sql
select most_common_vals as status, most_common_freqs as frequency
from pg_stats
where tablename = 'orders' and attname = 'status';
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
statusfrequency
{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.

sql
select * from orders where status = 'pending';
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
Seq Scan on orders  (cost=0.00..2060.00 rows=4005 width=31)
  Filter: (status = 'pending'::text)
sql
select * from orders where total > 2000;
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
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 = 2 the 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 > 2400 uses a fixed default guess, because nothing is known about total * 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:

sql
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;
sql
select count(*) from addresses where city = 'city 7' and country = 'country 2';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

The 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:

sql
create statistics addresses_city_country (dependencies) on city, country from addresses;
analyze addresses;
sql
select count(*) from addresses where city = 'city 7' and country = 'country 2';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Now 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.

At production scale: Three million pending orders the planner has not heard of 2,000,000 users and 10,000,000 orders, recorded on PostgreSQL 18.3 on 2026-10-21. Each plan is the second of two runs, so the data was already in memory.
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 ms
analyze 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 ms

Before 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#

Esc
↑ ↓ to move↵ to open