Reading EXPLAIN ANALYZE: Why Postgres Ignores Your Index

Posted on

Every slow query investigation eventually reaches the same moment: you run EXPLAIN ANALYZE, and the plan makes no sense. Postgres is doing a sequential scan on a table with thirty million rows, on a column that has an index. The index exists. The query is simple. And yet the planner walks right past it.

This post walks through how to read an EXPLAIN plan the way the planner wrote it, why the optimizer makes the choices it does, and the most common reasons a good index goes unused. The goal is not to memorize plan node names — it is to understand the three questions the planner asks about every query, because once you can answer them yourself, most “mystery” slow queries become obvious in a minute or two.

The Three Numbers That Matter

Every line of an EXPLAIN output is a plan node, and each node reports four numbers in parentheses:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..3894.00 rows=12 width=68)
  Filter: (customer_id = 42)

The cost=0.00..3894.00 pair is startup cost and total cost, in arbitrary units where one sequential page read costs 1.0. The startup cost is time spent before any row can be returned — a sort node has high startup cost, a scan has zero. The total cost assumes the node runs to completion, which matters because parents with a LIMIT can stop children early.

Costs are cumulative: the top node’s total includes everything below it. That is the number the planner minimizes. Crucially, the cost model ignores things the planner cannot change — network transfer, output formatting, client processing. A plan can have the lowest estimated cost and still feel slow if the real bottleneck is shipping ten million rows to the application.

The rows estimate is where almost all diagnosis happens. It is the planner’s guess of how many rows this node will emit, derived from statistics, not from your data at execution time. The width is the average row size in bytes, used to estimate I/O and memory costs.

EXPLAIN ANALYZE and the Estimate Gap

The EXPLAIN command shows the plan; add ANALYZE and Postgres actually executes the query, reporting the real row count and wall-clock time per node:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..3894.00 rows=12 width=68)
                     (actual time=0.410..18.442 rows=115200 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 29884800

That output contains the single most useful signal in plan reading: the estimate gap. The planner predicted 12 rows; the scan emitted 115,200. When estimates are off by four orders of magnitude, every downstream decision built on them — join algorithm, join order, memory grants — is built on sand. A nested loop chosen for “12 rows” is catastrophic for 115,200.

Also note Rows Removed by Filter. It tells you how much wasted work a scan node does. A sequential scan that removes 29.9 million rows to return 115 thousand is the planner telling you, in its own way, that this query shape might deserve an index.

One warning: EXPLAIN ANALYZE really runs the statement. An UPDATE, DELETE, or INSERT will execute for real. Wrap destructive statements in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;

Why the Planner Ignored Your Index

Index avoidance almost always reduces to one of three causes. Each has a distinct signature in the plan.

1. Bad selectivity estimates

The planner uses per-column statistics maintained by ANALYZE: most common values, histogram bounds, distinct counts. If those statistics are stale or the data distribution is skewed, the planner may estimate that an equality match returns half the table, making a sequential scan genuinely cheaper on paper. Check the estimates:

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

The fix is usually routine: run ANALYZE orders;, or increase the statistics target for that column with ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500; if the distribution is highly irregular. Autovacuum handles most tables, but bulk loads immediately before querying are the classic stale-statistics scenario.

2. A predicate the index cannot serve

B-tree indexes — the default — support equality and range comparisons: =, <, <=, >, >=, BETWEEN, IN, and anchored LIKE 'foo%' patterns. They do not help with predicates that hide the column from comparison:

-- Cannot use a B-tree index on created_at:
SELECT * FROM orders WHERE date(created_at) = '2026-09-01';

-- Can:
SELECT * FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';

Functions wrapping the indexed column make the predicate non-indexable for the plain operator class. The first query can only use an index if you create one on the expression: CREATE INDEX ON orders (date(created_at));. Range predicates on the raw column are almost always the better shape.

The same applies to other index types: GIN indexes serve containment queries on JSONB and arrays, BRIN indexes serve range scans over physically correlated data, and none of them will be selected if the query predicates do not match the operators they support. If the plan shows a Seq Scan with your filter as a plain Filter (not an Index Cond), the planner never found a usable index for that predicate.

3. The sequential scan is actually cheaper

This is the cause people resist the most. If the query matches a large fraction of the table — the rule of thumb often quoted is somewhere in the single-digit percent to ten percent range, depending on row width, correlation, and cache state — walking the index for most heap tuples is slower than reading the table once. Random I/O per row beats sequential I/O per page only when the matching fraction is small.

If the plan is a bitmap index scan followed by a bitmap heap scan, the planner already found a middle ground: index-identified pages, then sequential-ish heap access. A bitmap scan on ten percent of the table is frequently the correct plan, and converting it to a plain index scan would be slower.

Reading a Real Plan: A Worked Example

Consider a support dashboard query that got slow as data grew:

EXPLAIN ANALYZE
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

A typical bad plan for this shape:

Limit  (cost=104231.12..104231.19 rows=50 ...)
  -> Sort  (cost=104231.12..104291.87 rows=24298 width=48)
        Sort Key: o.created_at DESC
        -> Hash Join  (cost=1878.24..103712.10 rows=24298 width=48)
              Hash Cond: (o.customer_id = c.id)
              -> Seq Scan on orders o  (cost=0.00..101289.44 rows=24298 ...)
                    Filter: ((status = 'pending') AND
                             (created_at >= (now() - '7 days'::interval)))
                    Rows Removed by Filter: 29746110
              -> Hash  (cost=1258.00..1258.00 rows=49800 ...)
                    -> Seq Scan on customers c

Read it bottom-up: sequential scan on 30 million rows, filter removes 29.7 million, hash join, sort of 24 thousand rows, then limit. The planner’s own structure shows the fix — the LIMIT 50 and ORDER BY created_at DESC never propagate down. An index on (status, created_at DESC) lets the planner walk matching rows in order and stop after 50, and the plan collapses into an index scan with a nested loop join that terminates almost immediately. The sort disappears entirely, because an index scan can return rows already in sort order.

The general pattern: when a plan’s most expensive node sits underneath a LIMIT, look for an index that lets the limit apply early. When the top cost is a Sort, check whether an index already provides the order. Composite index column order matters here — equality columns first, range/sort columns after.

When Statistics Lie: Correlated Columns

Per-column statistics assume conditions are independent. If status = 'pending' matches 1% of rows and created_at >= now() - 7 days matches 2%, the planner multiplies selectivities and estimates 0.02%. But if pending orders are overwhelmingly recent, the real overlap might be 80% of recent rows. The planner underestimates massively, picks a nested loop, and the query crawls.

Postgres can capture cross-column correlation explicitly with extended statistics:

CREATE STATISTICS orders_status_created (dependencies)
  ON status, created_at FROM orders;
ANALYZE orders;

Functional dependency statistics let the planner correlate conditions on those columns, and the estimate gap on exactly this kind of query often shrinks by orders of magnitude. If your slow queries combine conditions on related columns — status and date, country and city, tenant and user — an extended statistics object is a one-line fix worth trying before anything else.

A Practical Diagnosis Routine

When a query is slow, work through this in order:

  • Run EXPLAIN (ANALYZE, BUFFERS). The BUFFERS option shows shared hit versus read per node, which distinguishes “bad plan” from “cold cache.”
  • Find the widest estimate gap between rows= predicted and actual ... rows=. That node and its parents are where the plan went wrong.
  • Check Rows Removed by Filter on scan nodes — large values point to missing or unused indexes.
  • Verify the predicates actually match an index type: B-tree for equality and ranges, GIN for containment, BRIN for correlated ranges. Rewrite functions-away-from-column predicates.
  • If estimates look sane but the plan is still bad, run ANALYZE on the table and check pg_stats for skew, then consider extended statistics for correlated columns.

None of this requires exotic tooling. The planner documents its reasoning in every plan it produces; the skill is asking, at each node, whether the row estimate is plausible and whether the chosen access method is the cheapest way to get those rows. Nine out of ten mystery slow queries resolve to a stale estimate, a non-indexable predicate, or a limit that cannot propagate — and each of those has a signature you can now spot on sight.

Leave a Reply

Your email address will not be published. Required fields are marked *