EXPLAIN, Statistics, and Query Planner Decisions

PostgreSQL does not run a query by reading SQL from left to right. It parses the statement, rewrites it when rules or views apply, estimates many possible execution strategies, assigns each strategy a cost, and sends the cheapest chosen plan to the executor. EXPLAIN is the window into that decision. This lesson teaches how to read that window: what the planner thinks will happen, what actually happened when ANALYZE is used, and how statistics explain surprising choices.

In the Indexes and Planning section of this course, the goal is not to force every query to use an index. The goal is to understand when an index helps, when a sequential scan is cheaper, and when PostgreSQL is making a reasonable decision from bad information. By the end, you should be able to connect plan nodes, row estimates, table statistics, and index design into one diagnosis.

Planner Purpose

The planner converts a declarative request into physical work. For a simple table query, it may compare a sequential scan, an index scan, a bitmap heap scan, or an index-only scan. For joins, it may compare nested loop, hash join, and merge join variants. For sorting and grouping, it may decide whether existing order can be reused or whether an explicit sort or hash aggregate is needed.

EXPLAIN without ANALYZE shows the chosen plan without executing the statement. EXPLAIN ANALYZE executes the statement and adds actual timing, loop counts, and row counts. For writes, that means the write really happens unless the command is wrapped in a transaction and rolled back.

How Plans Work Internally

A plan is a tree. Parent nodes consume rows produced by child nodes. In a plan such as Limit above Index Scan, the index scan produces candidate rows in index order and the limit node stops after enough rows are returned. In a hash join, one child is read into an in-memory hash table, while the other child probes that table.

The most important numbers are cost, rows, width, actual time, actual rows, and loops. Cost is an abstract unit used for comparison, not milliseconds. The first cost is startup cost: work before the node can return its first row. The second is total cost: work to return all rows. rows is the estimate per loop. actual rows is what happened per loop. If a node runs many loops, total rows processed are approximately actual rows multiplied by loops.

Statistics are the planner’s map of the data. ANALYZE samples tables and records estimates in catalogs such as pg_class, pg_stats, and extended statistics catalogs. Common values, null fraction, average width, histogram bounds, and approximate distinct counts help the planner estimate selectivity. Selectivity is the fraction of rows expected to match a predicate. If a predicate is estimated to match 1 percent of a large table, an index path may look attractive. If it is estimated to match 70 percent, reading the table sequentially may be cheaper.

The planner also considers configuration settings. random_page_cost, seq_page_cost, effective_cache_size, work_mem, and parallelism settings influence plan costs. These settings do not describe one query; they describe assumptions about the environment. A low random page cost makes index access look cheaper. A small work_mem makes large sorts and hashes more likely to spill to temporary files.

EXPLAIN Anatomy

Use the smallest option set that answers the question. EXPLAIN alone is safe for previewing a plan. EXPLAIN (ANALYZE, BUFFERS) is the usual diagnostic form for a read query because it compares estimates with reality and shows shared buffer hits and reads. FORMAT JSON is useful for tooling, while text format is easier to read during manual investigation.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, customer_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 5;

Read plans from the most-indented child upward. First ask how base rows are found. Then ask how rows are joined, sorted, grouped, filtered, and limited. A slow top node often inherits work from a child. A fast index scan can still feed a bad nested loop if the outer side has far more rows than estimated.

Example 1: Sequential Scan Is Reasonable

Start with a small table where most rows match. The planner may choose a sequential scan even if an index exists, because touching many index entries plus heap pages can cost more than reading the table once.

CREATE TEMP TABLE planner_demo_orders (
  id integer GENERATED ALWAYS AS IDENTITY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamp NOT NULL
);

INSERT INTO planner_demo_orders (customer_id, status, created_at)
SELECT (g % 10) + 1,
       CASE WHEN g % 10 = 0 THEN 'cancelled' ELSE 'paid' END,
       timestamp '2026-01-01' + (g || ' minutes')::interval
FROM generate_series(1, 10000) AS g;

CREATE INDEX planner_demo_orders_status_idx
  ON planner_demo_orders (status);

ANALYZE planner_demo_orders;

EXPLAIN SELECT id
FROM planner_demo_orders
WHERE status = 'paid';

The expected behavior is a plan that often prefers Seq Scan, because about 90 percent of rows are paid. That is not an index failure. It is the planner avoiding extra random access for a predicate that is not selective. If the same query filters for cancelled, the index has a better chance because only about 10 percent of rows match.

Example 2: Index Order Avoids Sorting

Now change the access pattern. A dashboard needs the latest orders for one customer. A composite index can support both the equality predicate and the requested order.

CREATE INDEX planner_demo_orders_customer_created_idx
  ON planner_demo_orders (customer_id, created_at DESC);

ANALYZE planner_demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM planner_demo_orders
WHERE customer_id = 3
ORDER BY created_at DESC
LIMIT 5;

The expected plan is typically an Index Scan using planner_demo_orders_customer_created_idx. The key design choice is column order. customer_id is first because the query uses equality on it. created_at DESC is second because rows for that customer can be read in the requested order. With the limit, PostgreSQL can stop after five matching rows instead of sorting every order for the customer.

If the plan shows a separate Sort, inspect whether the index order matches the query, whether the query has additional filters that change selectivity, and whether the table is too small for the index to matter. A small temporary demo table may still be scanned sequentially; the principle becomes clearer as data volume grows.

Example 3: Correlated Columns Need Better Statistics

Single-column statistics can mislead the planner when columns are correlated. Suppose most shipped orders have a shipped timestamp, while draft orders do not. Estimating status = 'shipped' and shipped_at IS NOT NULL independently can produce the wrong row count.

CREATE TEMP TABLE planner_demo_shipments AS
SELECT g AS id,
       CASE WHEN g <= 9000 THEN 'shipped' ELSE 'draft' END AS status,
       CASE WHEN g <= 9000 THEN timestamp '2026-02-01' + (g || ' seconds')::interval ELSE NULL END AS shipped_at
FROM generate_series(1, 10000) AS g;

ANALYZE planner_demo_shipments;

EXPLAIN SELECT *
FROM planner_demo_shipments
WHERE status = 'shipped'
  AND shipped_at IS NOT NULL;

CREATE STATISTICS planner_demo_shipments_status_shipstats
  ON status, shipped_at
  FROM planner_demo_shipments;

ANALYZE planner_demo_shipments;

EXPLAIN SELECT *
FROM planner_demo_shipments
WHERE status = 'shipped'
  AND shipped_at IS NOT NULL;

The second plan may estimate the row count more accurately because extended statistics give the planner information about relationships between columns. Extended statistics are most useful when predicates combine columns whose values are not independent. They do not replace indexes; they help the planner choose among available paths with better estimates.

Design Choices

Choose EXPLAIN options based on risk. For read-only investigation, ANALYZE is usually appropriate. For UPDATE, DELETE, and INSERT, run inside BEGIN and ROLLBACK when investigating. Add BUFFERS when deciding whether work is CPU-bound or I/O-heavy. Add VERBOSE when you need output columns, schema qualification, or internal names.

Choose indexes from workload shape, not from individual predicates alone. A single-column index helps selective equality or range filters. A multicolumn index helps when leading columns match the query. A partial index helps when most queries filter to the same subset, such as active accounts or unprocessed jobs. Covering indexes with included columns can allow index-only scans, but they increase write cost and storage.

Do not tune planner cost constants to fix one plan unless the default assumption is broadly wrong for the server. Bad row estimates are usually fixed with ANALYZE, higher per-column statistics targets, extended statistics, or a better query/index design.

Failure Modes

Symptom: a query suddenly switches from an index scan to a sequential scan after a bulk load. Cause: statistics are stale, so row counts and value distributions no longer resemble the table. Diagnose: compare estimated rows with actual rows using EXPLAIN (ANALYZE, BUFFERS), then inspect last_analyze in pg_stat_user_tables. Correct: run ANALYZE for the table and confirm autovacuum analyze thresholds fit the write pattern.

Symptom: a nested loop join runs for minutes while the estimate predicted a few rows. Cause: the outer relation is much larger than estimated, so the inner scan repeats too many times. Diagnose: look for a large difference between estimated rows and actual rows on the join inputs, multiplied by loops. Correct: refresh statistics, add extended statistics for correlated filters, or add an index that makes repeated inner lookups cheap.

Symptom: a sort or hash aggregate is slow and writes temporary files. Cause: the operation exceeds available memory for that node. Diagnose: use EXPLAIN (ANALYZE, BUFFERS) and look for sort methods, disk usage, temp reads, or temp writes. Correct: reduce input rows earlier, add an index that provides order, or adjust work_mem carefully for the session or workload.

Reliability And Performance Implications

EXPLAIN ANALYZE measures a real execution, so it can warm caches, take locks, change data for write statements, and add overhead from instrumentation. Run it against representative data, but avoid running expensive diagnostics repeatedly on a busy production system. When production evidence is needed, capture the exact query shape, parameters, timing, and plan, then reproduce in a controlled environment when possible.

Security matters because plans can expose table names, index names, predicates, and sometimes parameter values recorded by surrounding tooling. Share plans with the same care as schema and query logs. Performance diagnosis should use bounded examples and transactions for write statements.

Hands-On Lab

Prerequisites: a PostgreSQL session where you can create temporary tables. The lab uses only temporary objects, so cleanup happens automatically at session end. If you run it in a shared database, keep the temporary table names as written.

BEGIN;

CREATE TEMP TABLE planner_lab_orders (
  id integer GENERATED ALWAYS AS IDENTITY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  total_cents integer NOT NULL,
  created_at timestamp NOT NULL
);

INSERT INTO planner_lab_orders (customer_id, status, total_cents, created_at)
SELECT (g % 100) + 1,
       CASE WHEN g % 20 = 0 THEN 'refunded' ELSE 'paid' END,
       1000 + (g % 5000),
       timestamp '2026-03-01' + (g || ' seconds')::interval
FROM generate_series(1, 50000) AS g;

ANALYZE planner_lab_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM planner_lab_orders
WHERE customer_id = 17
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

CREATE INDEX planner_lab_customer_status_created_idx
  ON planner_lab_orders (customer_id, status, created_at DESC);

ANALYZE planner_lab_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM planner_lab_orders
WHERE customer_id = 17
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

ROLLBACK;

Steps: run the first plan before the index, record the scan type and whether a sort appears, create the composite index, run the second plan, and compare estimated rows, actual rows, buffers, and sort behavior. Verification succeeds when the second plan can use the composite index to find rows for one customer and status in descending creation order. Cleanup is the final ROLLBACK; temporary objects also disappear when the session closes.

Assessment

1. A query matches 80 percent of a table but has an index on the filtered column. Explain why a sequential scan may be cheaper than an index scan.
2. In an EXPLAIN ANALYZE plan, a node estimates 10 rows but returns 100000 rows. Describe two planner decisions that this error could distort.
3. Design an index for a query that filters by account_id, filters by status, orders by created_at DESC, and returns 25 rows. Explain your column order.
4. A join uses a nested loop and the inner node shows loops=50000. What would you inspect next, and what fixes might be appropriate?
5. When would extended statistics be a better first experiment than adding another index?

Summary

EXPLAIN teaches how PostgreSQL intends to execute a query. Statistics explain why the planner believes that plan is cheap. The strongest diagnoses compare estimates with actual execution, then trace errors back to selectivity, correlation, indexes, memory, or stale table statistics. Good index work is therefore evidence-driven: read the plan tree, understand the row estimates, change one design variable, and verify the new plan against representative data.