Skip to content
Tutorials Center
My learning

Topics

Lesson 62 of 90 ยท Performance Tuning

Statistics and the Query Planner

How PostgreSQL's planner guesses row counts: pg_stats, ANALYZE, default_statistics_target, extended statistics, cost settings and generic vs custom plans.

PostgreSQL never looks at your data when it plans a query. It cannot afford to: planning must take microseconds, while reading the table might take minutes. Instead it consults statistics, a compact summary of every column gathered earlier by ANALYZE. From those summaries it estimates how many rows each step will produce, multiplies by cost constants, and picks the cheapest-looking plan.

That is why the previous lesson on reading slow plans kept pointing here. When an estimate is wrong, the planner is not being stupid; it is working from a summary that does not describe your data. This lesson shows what the summary contains, how to refresh and sharpen it, how to teach the planner about related columns, and which cost settings shape its decisions.

Everything about pg_stats, CREATE STATISTICS and the planner settings is shown as read-only code, because our live engine is SQLite. The live examples use SQLite's own ANALYZE and plain SQL to demonstrate the same ideas: summaries lose information, and independence assumptions can be wrong.

What the planner knows

When you run ANALYZE (or autovacuum runs it for you), PostgreSQL reads a random sample of each table and stores per-column statistics in pg_statistic. The friendly view over it is pg_stats:

SQL
SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'status';
TEXT
-[ RECORD 1 ]-----+---------------------------
attname           | status
null_frac         | 0
n_distinct        | 3
most_common_vals  | {shipped,pending,refunded}
most_common_freqs | {0.91,0.08,0.01}
correlation       | 0.83

Each field answers a specific planning question:

Column Meaning Used for
null_frac Fraction of rows that are NULL IS NULL estimates
n_distinct Distinct values; a negative number means a fraction of the row count (-1 means all unique) GROUP BY, DISTINCT, join sizes
most_common_vals and most_common_freqs The frequent values and how common each is Equality estimates for popular values
histogram_bounds Boundaries of equal-population buckets for the remaining values Range estimates (<, >, BETWEEN)
correlation How closely physical row order matches value order (-1 to 1) Index scan versus bitmap scan cost

For WHERE status = 'refunded' the planner finds refunded in the most-common list with frequency 0.01 and multiplies by the table's row count (kept in pg_class.reltuples). For a value that is not in the list it spreads the leftover frequency over the remaining distinct values. A range predicate walks the histogram. None of this touches the table.

How the planner estimates

Sample ANALYZE reads a random slice of rows.
Summarize Per column: nulls, distinct values, histogram.
Estimate Predicates multiply selectivities.
Cost Estimated rows times cost constants.

The plan is only as good as the summary.

1 / 2

ANALYZE: keeping statistics fresh

Autovacuum runs ANALYZE on a table once enough rows have changed (by default after 10 percent of the table plus 50 rows, autovacuum_analyze_scale_factor = 0.1). That is usually enough, but a few situations need a manual ANALYZE:

  • Right after a bulk load into a new table, before the first queries run.
  • After a big batch UPDATE or DELETE that autovacuum has not yet noticed.
  • After creating an expression index or extended statistics (they stay empty until the next analyze).
SQL
ANALYZE;                 -- whole database
ANALYZE orders;          -- one table
ANALYZE orders (status); -- one column

VERBOSE prints what it does. PostgreSQL 18 also keeps planner statistics across a pg_upgrade major-version upgrade, so a freshly upgraded server no longer starts with empty statistics; older versions needed a full ANALYZE first.

The stored statistics come from a sample, not the whole table. Check when a table was last analyzed:

SQL
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

SQLite has a similar (much simpler) mechanism: ANALYZE stores a row per index in sqlite_stat1. Run it here, and notice how little a summary can say about skewed data:

SQL What a summary keeps and what it loses

Press Run (or Ctrl + Enter) to see the result here.

The index on status is summarized as 10000 5000: on average 5,000 rows per distinct value. The real counts are 9,900 paid and 100 refunded, so a single average is badly wrong for both values. SQLite can improve this with extra sampling, but the lesson is general: any summary is lossy, and skew is what hurts. PostgreSQL's answer is the most-common-values list, which records paid at about 0.99 and refunded at about 0.01 separately.

default_statistics_target

How detailed the summary is depends on default_statistics_target, default 100. It controls both the size of the sample (roughly 300 times the target, so about 30,000 rows) and the number of entries in the most-common list and histogram. For a column with a long tail of semi-popular values, 100 can be too small. Raise it for just that column rather than everywhere:

SQL
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

A larger target costs more time in ANALYZE and a little more planning time, so apply it only to columns that appear in selective filters and join keys with skewed data. A targeted change is easy to undo with SET STATISTICS -1, which returns the column to the default.

n_distinct deserves its own warning. Estimating the number of distinct values from a sample is mathematically hard, and PostgreSQL can be far off on very large tables with many distinct values, which makes GROUP BY and join estimates wrong. If you know the truth, you can override it:

SQL
ALTER TABLE events ALTER COLUMN user_id SET (n_distinct = -0.05);  -- 5% of rows are distinct
ANALYZE events;

By default the planner treats columns as independent: for WHERE city = 'Oslo' AND zip = '0150' it multiplies the two selectivities. When the columns are related (a zip code implies a city), the product is far too small, the plan expects almost no rows, and a nested loop looks cheap until it meets reality.

You can reproduce the arithmetic with plain SQL. Here the data has 4 cities and 100 zip codes, and each zip belongs to exactly one city:

SQL The independence assumption in numbers

Press Run (or Ctrl + Enter) to see the result here.

The independent estimate is 25 rows (0.25 times 0.01 times 10,000); the actual answer is 100, four times larger, because every row with zip 3 is in city0. With more columns or more distinct values the error grows quickly.

PostgreSQL lets you declare such relationships with CREATE STATISTICS:

SQL
CREATE STATISTICS people_city_zip (dependencies, ndistinct, mcv)
  ON city, zip FROM people;

ANALYZE people;

There are three kinds:

  • dependencies records that one column determines another (functional dependency), fixing the city/zip case above.
  • ndistinct records the number of distinct combinations, fixing GROUP BY city, zip estimates.
  • mcv keeps a multi-column most-common-values list, which also handles non-exact relationships and range conditions.

Extended statistics are not free: they add ANALYZE work, and they only help when the query filters on the declared columns. Create them where EXPLAIN ANALYZE showed a misestimate caused by correlated columns, not preemptively. You can also build statistics on expressions, such as ON (lower(email)), when the planner cannot otherwise see into an expression.

Planner cost settings

Once rows are estimated, the planner prices each plan with a handful of constants. The important ones:

Setting Default Meaning
seq_page_cost 1.0 Cost of reading a page sequentially
random_page_cost 4.0 Cost of reading a page at random
cpu_tuple_cost 0.01 Cost of processing one row
cpu_index_tuple_cost 0.005 Cost of processing one index entry
cpu_operator_cost 0.0025 Cost of one operator or function call
effective_cache_size 4GB How much data the planner assumes is cached (no memory is allocated)

The 4.0 default for random_page_cost assumes spinning disks. On SSD or NVMe storage, where random reads are nearly as cheap as sequential ones, a value of 1.1 to 1.5 is common and makes index scans win more often. Set it in postgresql.conf (or per tablespace for mixed hardware), then compare plans for your important queries. These constants are relative, so the numbers in EXPLAIN have no unit; only comparisons between plans are meaningful.

Resist turning off plan types with enable_seqscan = off and friends outside experiments. They do not forbid the node, they only add a huge cost penalty to it, and they are a diagnostic tool: if a query becomes fast with enable_nestloop = off, the real problem is a statistics or cost issue to fix, not a setting to ship.

Generic and custom plans

Prepared statements (and most database drivers use them) can be planned in two ways:

  • A custom plan is made for the actual parameter values, so it can use the statistics for those exact values.
  • A generic plan is made once without knowing the values and reused, saving planning time.

With the default plan_cache_mode = auto, PostgreSQL plans the first five executions with real values, then switches to the generic plan only if its estimated cost is not worse than the average custom plan. This usually works, but skewed data can fool it: a generic plan that is fine for common values can be terrible for a rare one (or the reverse). You can look at a generic plan directly:

SQL
-- PostgreSQL 16+: plan a statement with placeholders, without running it
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE status = $1;

-- Force real-value planning for this session (to test or fix a skew problem)
SET plan_cache_mode = force_custom_plan;

If one query suffers from a bad generic plan, set plan_cache_mode = force_custom_plan for that role or session. A related issue with connection pools is covered in Connection Pooling.

A good diagnostic habit: when an estimate is wrong, first run ANALYZE on the table and re-check; next look at pg_stats for the column; only then reach for a higher statistics target or CREATE STATISTICS. Most misestimates are fixed by one of those three steps.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a negative n_distinct value such as -0.5 in pg_stats mean?

    A negative n_distinct is a fraction of the row count (-1 means every value is unique), so the estimate scales as the table grows.
  2. 2. A query filters on two columns where one determines the other, and the planner badly underestimates rows. What helps most?

    The planner multiplies selectivities as if the columns were independent. A dependencies statistic tells it one column determines the other. The other options do not address the estimate.
  3. 3. Why is a lower random_page_cost (for example 1.1) usual on SSD storage?

    The default of 4.0 models spinning disks. On fast storage the real gap is small, and a lower value lets the planner pick index scans when they are actually faster.
  4. 4. With plan_cache_mode = auto, how does PostgreSQL treat a prepared statement?

    It plans the first five executions with real values, then compares the generic plan's cost to the average custom plan before switching.

Summary

  • The planner estimates from sampled per-column statistics (pg_stats), never from the table itself.
  • ANALYZE refreshes them; autovacuum does it automatically, but run it by hand after bulk changes.
  • Raise SET STATISTICS per column for skewed data, and override n_distinct when you know the truth.
  • The planner assumes columns are independent; CREATE STATISTICS (dependencies, ndistinct, mcv) fixes related columns.
  • Cost constants such as random_page_cost are relative; lower it for SSD storage, and do not leave enable_* switches off in production.
  • Prepared statements may use generic plans; plan_cache_mode = force_custom_plan fixes skew-related bad plans.

Next: Query Tuning Patterns.