The Query Planner and Statistics

~12 min · operations, planner

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The planner picks the cheapest plan it can find

For every query, PostgreSQL's planner enumerates several execution plans and picks the one with the lowest estimated cost. The estimate is based on statistics: how many rows in each table, how many distinct values per column, value distribution. Stale or missing statistics → wrong estimates → bad plans.

ANALYZE updates statistics

Autovacuum runs ANALYZE periodically. After bulk loads or major data shifts, run ANALYZE manually so the planner doesn't keep using stale numbers. Cheap and fast — rare to over-do it.

Extended statistics for correlated columns

The planner assumes columns are independent unless told otherwise. If city and country are highly correlated (every "Paris" implies "France"), the planner under-estimates. CREATE STATISTICS teaches it the correlation.

Code

Refresh statistics·sql

ANALYZE;                  -- whole database
ANALYZE products;         -- one table
ANALYZE products (price); -- one column

Inspect what the planner knows·sql

SELECT relname, reltuples, relpages
FROM   pg_class
WHERE  relname = 'orders';

SELECT attname, n_distinct, null_frac, most_common_vals
FROM   pg_stats
WHERE  tablename = 'orders'
  AND  attname IN ('status', 'customer_id');

Extended statistics for correlated columns·sql

CREATE STATISTICS orders_status_customer_stats (dependencies)
ON status, customer_id
FROM orders;
ANALYZE orders;

-- The planner now knows status and customer_id are not independent.

External links

Exercise

On a real database, run an EXPLAIN ANALYZE for a slow query and look at the (estimated rows) vs (actual rows) for each node. If they're more than 10× apart, run ANALYZE on the relevant tables and re-run EXPLAIN. Note the plan changes.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousVACUUM and Autovacuum in Plain English Next →When PostgreSQL Is the Wrong Tool

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.