Module 7: Statistics, Autovacuum & Planner

How the planner decides

Next →

PostgreSQL's planner seems like magic until you understand its mental model. It receives a query, knows the schema, and picks one execution plan out of many possible ones. How? The short answer: it's a deterministic calculator that takes two inputs — statistics about the data and cost parameters about the hardware — and produces the plan with the lowest estimated cost. There's no randomness, no opaque heuristics, no machine learning. It's algebra with catalog tables.

In this capsule you're going to open the black box. You'll see the exact queries you run to inspect the same catalog tables the planner consumes (pg_stats, pg_class, pg_statistic). You'll understand the basic formula it uses to estimate cost for a Seq Scan and for an Index Scan. And you'll learn to manually compare the two costs to anticipate which plan it's going to pick before running EXPLAIN.

By the end, when a plan surprises you, you'll be able to answer precisely: "the planner decided this because the statistics for column X say such and such, and with random_page_cost = 4.0 the Seq Scan cost came out lower." This turns planner debugging from a trial-and-error process into a systematic inspection.

What information does the planner use?

The planner consults two things for each candidate plan: how much data it's going to process (statistics) and how expensive it is to process that data (cost parameters).

Statistics — what the planner knows about your data

When you run ANALYZE (manually or via autovacuum), PostgreSQL scans a sample of the table and stores the results in its internal catalog. The two most important views for inspecting this are pg_stats (per-column statistics) and pg_class (table metadata).

pg_class — the table's size and shape.

SELECT
    relname,        -- table name
    reltuples,      -- estimate of live rows
    relpages,       -- 8KB pages used
    relkind         -- 'r' = regular table, 'i' = index
FROM pg_class
WHERE relname = 'orders';

Typical output:

 relname | reltuples | relpages | relkind
---------+-----------+----------+---------
 orders  |    995234 |    18432 | r

The planner reads this first: "the orders table has approximately 995 thousand rows distributed across 18,432 pages of 8KB." If you do a bulk insert of 5 million rows and don't run ANALYZE, this number stays at 995 thousand. The planner reasons as if the table were 5x smaller than it actually is.

pg_stats — the distribution of each column.

SELECT
    attname,                -- column
    null_frac,              -- fraction of NULL values
    n_distinct,             -- distinct values (estimated)
    most_common_vals,       -- top frequent values
    most_common_freqs,      -- frequency of each one (0.0-1.0)
    histogram_bounds        -- distribution buckets
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

Typical output:

 attname     | null_frac | n_distinct | most_common_vals     | most_common_freqs    | histogram_bounds
-------------+-----------+------------+----------------------+----------------------+--------------------
 customer_id |       0.0 |     -0.234 | {1234,5678,9012}     | {0.012, 0.008, 0.005}| {1,1234,5680,...}

Interpretation of the key fields:

  • n_distinct = -0.234: a negative number means "fraction of the total." -0.234 means "234 thousand distinct values" (in a 1M table, 0.234 × 1M). If it were positive (50000), it would be an absolute count. PostgreSQL picks the format according to which one stays stable as the table grows.
  • most_common_vals (MCV): the most frequent values, up to 100 by default. If customer_id = 1234 appears in 1.2% of the rows (most_common_freqs = 0.012), the planner knows that WHERE customer_id = 1234 returns approximately 12 thousand rows in a 1M table.
  • histogram_bounds: divides the non-MCV values into buckets of equal frequency (default: 100 buckets). For range predicates (WHERE customer_id BETWEEN X AND Y), the planner uses these buckets to estimate selectivity.

With this data, the planner can already estimate how many rows any simple predicate over customer_id is going to return. Without this data (a table never analyzed), the planner uses defensive heuristics (assumes default selectivity), which almost always underestimate or overestimate.

Cost parameters — how expensive each operation is

Statistics tell the planner how much it's going to process. Cost parameters tell it how expensive it is to process each unit. The main ones:

  • seq_page_cost (default 1.0): cost of reading a page sequentially from disk.
  • random_page_cost (default 4.0): cost of reading a page at random (typical of an Index Scan that jumps between non-contiguous pages).
  • cpu_tuple_cost (default 0.01): cost of processing a tuple on the CPU.
  • cpu_index_tuple_cost (default 0.005): cost of processing an index entry.
  • cpu_operator_cost (default 0.0025): cost of evaluating an operator (=, <, etc.).

These numbers are relative, they're not seconds or milliseconds. The formula compares plans in an arbitrary unit; the only thing that matters is the ratio between them.

The default random_page_cost = 4.0 is calibrated for HDD disks, where random access costs ~4x more than sequential access due to seek latency. On SSD that difference is ~1.1x. If you leave the default on SSD, the planner overestimates the cost of Index Scan and prefers Seq Scan even when it shouldn't. We're going to tune it in capsule 08.

The simplified cost formula

The planner produces a cost for each candidate plan. Here are the simplified formulas for the two most common plans.

Cost of Seq Scan

cost = startup_cost
     + (relpages × seq_page_cost)
     + (reltuples × cpu_tuple_cost)
     + (reltuples × cpu_operator_cost × num_filters)

For our orders table (995,234 rows in 18,432 pages) with a simple filter WHERE customer_id = 12345:

cost ≈ 0
     + (18432 × 1.0)        = 18,432    (read all the pages)
     + (995234 × 0.01)      = 9,952     (process all the tuples)
     + (995234 × 0.0025 × 1) = 2,488    (evaluate the filter)
     ≈ 30,872

The planner internally reports this as cost=0.00..30872.00 rows=N width=M. The two numbers are startup_cost..total_cost. For Seq Scan, startup_cost is 0 (you can start returning rows immediately).

Cost of Index Scan

cost = startup_cost (reading the index root)
     + (estimated_rows × random_page_cost)     (each row can be a different page)
     + (estimated_rows × cpu_tuple_cost)
     + (estimated_rows × cpu_index_tuple_cost)

For the same query with an index idx_orders_customer ON orders(customer_id):

First, the planner estimates how many rows WHERE customer_id = 12345 returns. If customer_id isn't in the MCV, it uses 1 / n_distinct = 1 / 234000 ≈ 0.0000043. Applied to the table: 995,234 × 0.0000043 ≈ 4 rows.

cost ≈ small startup
     + (4 × 4.0)              = 16     (read 4 random pages)
     + (4 × 0.01)              = 0.04
     + (4 × 0.005)             = 0.02
     ≈ ~20

The planner would report cost=0.43..20.43 rows=4 width=128.

The decision

Seq Scan cost = 30,872. Index Scan cost = 20. The planner picks Index Scan with absolute confidence — it's 1500x cheaper.

But now the interesting case: what happens if the statistics are bad?

When the statistics lie

Imagine you did a bulk insert of 5 million rows into orders last night. But you didn't run ANALYZE. Monday morning, the planner still believes the table has 995 thousand rows (which is what pg_class.reltuples says). Nor did n_distinct for customer_id get updated.

For the same query WHERE customer_id = 12345:

  • Planner's estimate: 4 rows (based on old stats).
  • Reality: 18 rows (that customer's new orders among the 5M new ones).

Does the difference between 4 and 18 matter? For this specific query, no — both cases pick Index Scan with confidence. But now consider another query:

SELECT * FROM orders WHERE order_date >= '2026-01-01';
  • Estimate with old stats (the table "has" 995k rows, almost all of them before 2026): 50 rows.
  • Reality (5M new ones, all from 2026): 5,000,000 rows.

Cost of Index Scan with an estimate of 50 rows:

cost ≈ 50 × 4.0 + 50 × 0.015 ≈ 200

Cost of Seq Scan:

cost ≈ 18432 + 9952 + 2488 ≈ 30,872

The planner picks Index Scan because it thinks it's going to read 50 rows (200 < 30,872). But the query actually reads 5 million rows via Index Scan, which means 5 million random jumps to disk. The query the planner thought would take 200ms takes 12 seconds.

This is exactly what happened in the capsule 01 scenario: the planner lying to itself based on statistics from a world that no longer exists.

When you see EXPLAIN ANALYZE and the rows= field (estimated) is far off from the actual rows= field, the problem is almost always stale statistics. Capsule 03 teaches you how to fix it.

Manually inspecting what the planner sees

For any query that surprises you, this is the diagnostic pattern you're going to use for the rest of the module (and the rest of your career).

Step 1: See the chosen plan and the estimates.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

Look at the main scan line: cost=X.XX..Y.YY rows=N. The rows=N number is the planner's estimate. Compare it against actual rows=M loops=L that appears after actual time=.

Step 2: See the statistics of the filter column.

SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    array_length(histogram_bounds, 1) AS num_buckets
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

If n_distinct is very different from reality (you can verify with SELECT COUNT(DISTINCT customer_id) FROM orders even if it's expensive), the statistics are old.

Step 3: See the size the planner thinks the table has.

SELECT
    relname,
    reltuples,
    relpages,
    pg_size_pretty(relpages * 8192::bigint) AS estimated_size
FROM pg_class
WHERE relname = 'orders';

Compare it with the real size:

SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS actual_size,
    (SELECT COUNT(*) FROM orders) AS actual_rows;

If reltuples is very different from actual_rows, the table's global statistics are old.

Step 4: See when the last ANALYZE was.

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

n_mod_since_analyze tells you how many UPDATE/INSERT/DELETE happened since the last analyze. If it's high and last_analyze/last_autoanalyze is old, autovacuum isn't keeping up.

This four-query pattern is one you're going to use a lot. It's worth saving in your snippets.

Traps and common mistakes

1. Confusing n_distinct (statistic) with n_live_tup (activity).

n_distinct is in pg_stats and is metadata for the planner — how many unique values there are in a column. It's updated with ANALYZE.

n_live_tup is in pg_stat_user_tables and is an activity counter — how many live rows (not dead due to MVCC) there are. It's updated in real time with every operation.

They're distinct concepts. n_distinct affects estimated selectivity. n_live_tup affects when autovacuum decides to run.

2. Assuming that EXPLAIN ANALYZE changes or invalidates the planner's cache.

EXPLAIN ANALYZE actually executes the query and measures real times. It doesn't "regenerate" statistics or invalidate anything. If the stats are bad, EXPLAIN ANALYZE run 10 times keeps showing the same bad plan. The only way to make the planner see updated statistics is to run ANALYZE (or wait for autovacuum).

3. Thinking the planner "learns" from past queries.

PostgreSQL is stateless between queries. The planner doesn't remember that your previous query took 8 seconds. It doesn't adjust its behavior based on history. Each query is re-planned from scratch using the catalog's current statistics. If the stats are still bad, the plan is still bad, no matter how many times you've run the query.

4. Looking only at the plan, not the estimates.

It's tempting to read EXPLAIN looking only at "which scan it uses." The rows=N field (estimated) is just as important. A query that uses Index Scan with an estimate of 1 row when reality is 100,000 rows is going to be doing 100,000 random jumps to disk — worse than a Seq Scan. The plan isn't good on its own; it's good for the right number of rows.

5. Assuming that fresh stats always fix everything.

ANALYZE fixes basic statistics (n_distinct, MCV, histogram). But if your query depends on correlation between columns (e.g., WHERE country = 'Mexico' AND city = 'Mexico City' where city implies country), ANALYZE doesn't capture that correlation. You need extended statistics (CREATE STATISTICS), the topic of capsule 04. Without them, the planner keeps underestimating even if the individual stats are perfect.

Exercise: predict the plan before running it

You're going to create a test table, run ANALYZE, inspect statistics, and predict which plan the planner will pick before running EXPLAIN.

Setup:

DROP TABLE IF EXISTS test_orders;
CREATE TABLE test_orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount NUMERIC(10, 2),
    status TEXT
);

-- Insert 100,000 rows with customer_id distributed between 1-1000
INSERT INTO test_orders (customer_id, amount, status)
SELECT
    (random() * 1000)::INT + 1,
    (random() * 1000)::NUMERIC(10, 2),
    CASE WHEN random() < 0.7 THEN 'shipped' ELSE 'pending' END
FROM generate_series(1, 100000);

-- Create index
CREATE INDEX idx_test_customer ON test_orders(customer_id);

-- IMPORTANT: update statistics
ANALYZE test_orders;

Inspection:

Inspect what the planner knows:

-- Table size
SELECT relname, reltuples, relpages
FROM pg_class WHERE relname = 'test_orders';

-- Statistics for customer_id
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'test_orders' AND attname = 'customer_id';

Prediction:

Before running EXPLAIN, predict what the planner is going to do for these two queries:

  1. SELECT * FROM test_orders WHERE customer_id = 500; (a single customer)
  2. SELECT * FROM test_orders WHERE customer_id < 500; (a large range)

For each one:

  • How many rows does it estimate returning?
  • Is it going to pick Seq Scan or Index Scan?
  • Why?

Validation:

Run EXPLAIN (without ANALYZE, you don't need real times — just the estimated plan):

EXPLAIN SELECT * FROM test_orders WHERE customer_id = 500;
EXPLAIN SELECT * FROM test_orders WHERE customer_id < 500;

Did you get it right?

See solution

Query 1 (customer_id = 500):

  • Estimated rows: with n_distinct ≈ 1000 (uniformly distributed), the planner estimates 100,000 / 1000 = ~100 rows.
  • Chosen plan: Index Scan almost certainly. Index Scan cost ≈ 100 × 4.0 = 400. Seq Scan cost ≈ relpages + reltuples × 0.01. On a small table (~640 pages, 100k tuples) that comes to ~1640. Since 400 < 1640, Index Scan wins.
  • Likely real plan: 
    Index Scan using idx_test_customer on test_orders
  (cost=0.29..359.34 rows=98 width=...)
  Index Cond: (customer_id = 500)

Query 2 (customer_id < 500):

  • Estimated rows: approximately half the table, ~50,000 rows. The planner uses the histogram for this.
  • Chosen plan: Seq Scan. For 50,000 rows, a plain Index Scan cost would be 50000 × 4.0 = 200,000 (overloaded by random page reads). The Seq Scan cost stays at ~1640. Seq Scan wins by a wide margin.
  • Likely real plan: 
    Seq Scan on test_orders
  (cost=0.00..1885.00 rows=50124 width=...)
  Filter: (customer_id < 500)

The important thing: the same index is used or not used depending on how many rows the planner thinks it's going to return. It's not "the index is good" or "the index is bad" — it's "for this estimated number of rows, the index is the best option." If the stats lie about the quantity, the planner picks badly.

Note: the exact scan shape depends on your PostgreSQL version, cost parameters, and the random data distribution. On some versions/configs the planner reaches the same conclusion via a Bitmap Heap Scan (which reads the heap in page order) instead of a plain Index Scan or a Seq Scan. The teaching point is the same: for a single value it uses the index, for a large range it avoids random per-row index lookups.

Summary and next step

What you learned:

  • The planner is deterministic: given the same statistics and the same configuration, it always picks the same plan.
  • The two inputs it controls are: statistics (pg_class, pg_stats) and cost parameters (seq_page_cost, random_page_cost, etc.).
  • The basic formula: cost = pages × page_cost + rows × tuple_cost. Compare costs between candidate plans to pick the cheapest.
  • The statistics can be stale after bulk operations, schema changes, or delayed autovacuum. When they are, the chosen plan is going to be bad even if the query and the index are perfect.
  • The four diagnostic queries: pg_class (size), pg_stats (distribution), EXPLAIN ANALYZE (plan + estimated/actual), pg_stat_user_tables (when the last analyze was).

Before moving on, you should be able to:

  • Look at an EXPLAIN ANALYZE and detect a discrepancy between rows=N (estimated) and actual rows=M.
  • Know where the information the planner sees for a specific column is (pg_stats WHERE tablename = X AND attname = Y).
  • Distinguish n_distinct (statistic for the planner) from n_live_tup (activity counter).
  • Predict whether a query is going to use Seq Scan or Index Scan by looking at statistics and estimated selectivity.

In the next capsule we're going to close the loop: you already know what information the planner uses, now you're going to learn how to make sure that information is fresh. When to run a manual ANALYZE, what exactly happens when autovacuum makes its pass, and how to detect the specific "stale statistics" symptom in EXPLAIN ANALYZE to react before the problem explodes in production.

Resources

  1. PostgreSQL Docs — Planner / Optimizer — official overview of the planner.
  2. PostgreSQL Docs — pg_stats — reference for the pg_stats view and all its columns.
  3. PostgreSQL Docs — Statistics Used by the Planner — explanation of how the planner consumes statistics.
  4. PostgreSQL Docs — Planner Cost Constants — reference for cost parameters.
  5. Bruce Momjian — "Explaining the Postgres Query Optimizer" — deep talks with cases.
  6. Lukas Fittl (pganalyze) — How the PostgreSQL Query Planner Works — a step-by-step visualization of the planning process.

Capsule 02 of 08 — Module 7 — Database Performance & Query Tuning Guide

← PreviousModule 7: Statistics, Autovacuum & PlannerNext →Manual ANALYZE and stale statistics