The planner and statistics

PostgreSQL uses a cost based planner. For each query, it estimates how expensive different plans would be and picks the cheapest. Those estimates depend on statistics about your data. When statistics are good, plans are usually good. When they are wrong, the planner can make expensive mistakes.

What the planner estimates

For each step in a plan, the planner estimates:

• Rows: how many rows the step will produce.
• Cost: an abstract number combining estimated disk reads and CPU work. It is not milliseconds; it is useful only for comparing plans.

Row estimates are the most important input. If the planner thinks a filter returns 10 rows, an index scan and a nested loop join look attractive. If the filter really returns 500,000 rows, that plan may be disastrous.

Where statistics come from

ANALYZE, run manually or automatically by autovacuum, samples each table and stores statistics in the pg_statistic catalog, readable through the pg_stats view:

• The fraction of NULL values.
• The number of distinct values.
• Most common values and their frequencies.
• A histogram describing the distribution of other values.
• Correlation between physical row order and column order.

SELECT attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders';

When estimates go wrong

• Stale statistics after large data changes. Running ANALYZE often fixes this immediately.
• Correlated columns: the planner assumes conditions are independent. If country = 'JP' and currency = 'JPY' always go together, it underestimates how many rows match both.
• Skewed data beyond the default sample.
• Functions and expressions in filters, where the planner has no statistics.

Tools to improve estimates

• Increase the statistics target for a skewed column: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; then ANALYZE.
• Create extended statistics for correlated columns:

CREATE STATISTICS orders_country_currency (dependencies, ndistinct)
ON country, currency FROM orders;
ANALYZE orders;

• Create an expression index, which also gathers statistics on the expression.

Do not fight the planner blindly

PostgreSQL does not support query hints in core. Settings like enable_seqscan = off can be useful in a session to test alternatives, but should not be used to force plans in production. If the planner chooses badly, find out why its estimates are wrong and fix the cause.

Compare estimated and actual rows for a query filtering orders by country and currency, then add extended statistics and compare again.