7.2.3 — Reading the Plan: EXPLAIN and Query Optimisation

A query that returned in 30 ms for a year now takes 4.2 seconds. Nothing about it changed. The table grew from 200,000 rows to 9 million.

Guessing at this point is expensive. The database will tell you exactly what it did, and it will tell you the number that matters most: what it expected to find versus what it actually found. Almost every bad plan is a bad estimate, and almost every fix follows from seeing the gap.

1. EXPLAIN versus EXPLAIN ANALYZE

EXPLAIN SELECT …;                         -- the plan, without running it
EXPLAIN ANALYZE SELECT …;                 -- runs it, reports actual timings
EXPLAIN (ANALYZE, BUFFERS) SELECT …;      -- plus how much data was read

EXPLAIN alone is a prediction. It costs nothing and can be wrong.

EXPLAIN ANALYZE executes the query. On a SELECT that is fine. On an UPDATE or DELETE it performs the write — wrap it in a transaction and roll back:

BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE …; ROLLBACK;

Always add BUFFERS. Timings vary with cache state and machine load; buffer counts do not. shared hit means the page was already in the buffer pool (Chapter 7.3.1), read means it came from disk. A query reading 200,000 buffers is doing 1.6 GB of work regardless of how fast the disk was that second.

MySQL's equivalent is EXPLAIN ANALYZE (8.0.18+) or EXPLAIN FORMAT=JSON; SQL Server has "Include Actual Execution Plan". The vocabulary differs, the reading skill transfers.

2. How to read the tree

A plan is a tree of nodes. Each node consumes rows from its children and produces rows for its parent. Read it inside-out: the most indented lines run first.

Sort  (cost=48211.10..48213.60 rows=1000 width=48)
      (actual time=4188.3..4188.5 rows=1000 loops=1)
  Sort Key: o.placed_at DESC
  Sort Method: quicksort  Memory: 130kB
  ->  Hash Join  (cost=1204.00..48161.27 rows=1000 width=48)
                 (actual time=18.1..4180.9 rows=1000 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..44120.00 rows=1000 width=32)
                                  (actual time=0.3..4102.2 rows=1000 loops=1)
              Filter: (status = 'refunded')
              Rows Removed by Filter: 8998000              ← (the whole story)
        ->  Hash  (cost=1100.00..1100.00 rows=8320 width=24)
              ->  Seq Scan on customers c  …

The indented lines are the leaves and they run first. Rows flow upward. The expensive node here is the leaf that reads nine million rows to keep two thousand.

What each number means:

• cost=48211.10..48213.60 — the planner's made-up units, not milliseconds. The first is the cost to produce the first row, the second to produce all rows. The gap matters: a node with a large start-up cost (a sort, a hash build) cannot return anything until it finishes, so a LIMIT above it saves nothing.
• rows=1000 — the estimate.
• actual … rows=1000 loops=1 — the truth. loops is how many times the node ran, and the reported actual time and rows are per loop — multiply by loops for the total. A nested loop showing actual time=0.02 rows=1 loops=900000 spent 18 seconds, not 0.02 ms.
• Rows Removed by Filter — rows read and thrown away. This is usually the largest clue on the page.

The single most useful reading habit: compare rows= with actual … rows=. If the planner expected 1,000 and got 900,000, every decision above that node was made on false information, and fixing the estimate often fixes the plan without touching the query.

3. Scan types, and when each is right

Seq Scan reads the whole table. It is not automatically bad. Reading 9 million rows to return 8 million of them is the correct plan — an index would add a lookup per row and be slower. A sequential read is also the disk's best case. Seq Scan is a problem only when the filter discards nearly everything, which is exactly what Rows Removed by Filter: 8998000 reports.

Index Scan walks the index to find matching entries, then fetches each matching row from the table. Two structures per row. Fast when few rows match.

Index Only Scan answers entirely from the index, never touching the table, because every column the query needs is in the index. This is the fastest shape and is what a covering index buys (Chapter 7.3.2). In PostgreSQL it still consults the visibility map, so a table that has not been vacuumed recently shows Heap Fetches: above zero and loses part of the benefit.

Bitmap Index Scan + Bitmap Heap Scan is the middle ground: collect all matching row locations from the index first, sort them into physical order, then read the table once in that order. This turns thousands of random reads into a mostly-sequential sweep. Seeing a bitmap scan usually means the planner thought "too many rows for an index scan, too few for a sequential scan", which is normally a sensible judgement. It can also combine two indexes with BitmapAnd / BitmapOr, which is how an OR across two indexed columns is served.

4. What makes a condition unable to use an index

The word for a condition an index can serve is sargable (from "search argument able"). The rule is short: the indexed column must appear alone on one side of the comparison. Wrap it in anything and the index is out, because the index stores the column's values, not the function's results.

-- ✗ index on placed_at unusable
WHERE DATE(placed_at) = '2026-08-02'
-- ✓ same meaning, index usable
WHERE placed_at >= '2026-08-02' AND placed_at < '2026-08-03'

-- ✗
WHERE LOWER(email) = 'ana@x.com'
-- ✓ either store it lowercased, or index the expression:
CREATE INDEX ON customers (LOWER(email));

-- ✗ leading wildcard cannot use a B-tree
WHERE name LIKE '%smith'
-- ✓ trailing wildcard is a prefix range, and works
WHERE name LIKE 'smith%'

-- ✗ arithmetic on the column
WHERE total_minor / 100 > 50
-- ✓ move the arithmetic to the constant
WHERE total_minor > 5000

Implicit type casts are the invisible version of this. If user_id is bigint and the application sends a string, or a column is varchar and the parameter is an integer, the engine may cast the column to match — which is a function on the column, which loses the index. The plan shows it as Filter: ((user_id)::text = '42'::text). A parameter type mismatch that silently costs a sequential scan is one of the most common ORM-caused slow queries, and it is invisible in the application code.

Two more index defeaters:

• OR across different columns. WHERE a = 1 OR b = 2 cannot use a single composite index. The engine may bitmap-combine two separate indexes; if not, rewrite as UNION of two indexed queries.
• Low selectivity. An index on a boolean column with a 50/50 split will not be used, and should not be — half the table is a sequential scan. A partial index fixes the useful half: CREATE INDEX ON jobs (created_at) WHERE status = 'pending' is tiny when 0.1% of jobs are pending, and serves the only query anyone runs.

5. The plan shapes that mean trouble

Nested loop with a large outer side. loops=900000 on the inner node. The inner side runs nine hundred thousand times. Either an index is missing on the inner join column, or the planner underestimated the outer row count. This is the most common catastrophic plan.

Sort with Sort Method: external merge Disk: 84000kB. The sort did not fit in work_mem and spilled to disk. Either raise work_mem for that session, or — much better — provide an index that returns rows already in the required order, which removes the sort entirely.

Hash join with Batches: 8. The hash table did not fit either, so the join was split into eight passes over both inputs. Same causes, same fixes.

A big Rows Removed by Filter on a leaf. Reading a lot to keep a little: index the filtered column.

rows=1 estimated, millions actual. Stale or missing statistics. Run ANALYZE tablename;. If it persists, the cause is usually correlated columns — the planner assumes independence, so WHERE city = 'Paris' AND country = 'France' is estimated as (fraction Paris) × (fraction France), which is far too small. PostgreSQL's fix is an extended statistics object:

CREATE STATISTICS city_country (dependencies) ON city, country FROM addresses;
ANALYZE addresses;

A LIMIT that is slower than expected. The planner sees LIMIT 10 and picks a plan optimised to return the first ten rows quickly, often an index scan in ORDER BY order with a filter applied afterwards. If very few rows match the filter, it walks most of the index to find ten. This is the classic "adding LIMIT made it slower" surprise, and the fix is an index that serves the filter and the order together.

6. Fixing the opening query

The plan said: sequential scan on 9 million orders, 8,998,000 removed by a filter on status.

CREATE INDEX orders_refunded_idx
  ON orders (customer_id, placed_at DESC)
  WHERE status = 'refunded';                       -- (1)

(1) A partial index on the 0.02% of rows that are refunds, with the columns in an order that serves both the join and the sort. Re-run:

Limit  (actual time=0.09..1.24 rows=1000 loops=1)
  ->  Nested Loop  (actual time=0.08..1.18 rows=1000 loops=1)
        ->  Index Scan using orders_refunded_idx on orders o
              (actual time=0.04..0.31 rows=1000 loops=1)
        ->  Index Scan using customers_pkey on customers c
              (actual time=0.001..0.001 rows=1 loops=1000)
Execution Time: 1.4 ms

4,188 ms to 1.4 ms. Read what actually changed: the leaf no longer discards nine million rows, the sort disappeared because the index already returns placed_at DESC, and the join became a nested loop with a primary-key lookup — which is now the right choice, because the outer side is a thousand rows rather than nine million.

That is the general shape of every real fix: make the leaf read less, and the plan above it improves on its own.

7. Statistics, vacuum and why plans change on their own

The planner chooses using statistics: how many rows a table has, how many distinct values a column has, a histogram of the value distribution, and how physically ordered the table is with respect to each index (the correlation).

ANALYZE refreshes those. autovacuum runs it automatically after enough changes, but a bulk load followed immediately by a query runs against statistics describing the old table — which is why a nightly import job's first query is sometimes catastrophically slow. Run ANALYZE at the end of any bulk load.

VACUUM is separate and matters for a different reason. Under the MVCC model in Chapter 7.4.2, an UPDATE in PostgreSQL writes a new row version and leaves the old one behind. VACUUM reclaims those dead rows. Without it, tables and indexes bloat: the same live data spread over far more pages, so every scan reads more. A table that is 80% dead rows costs five times as much to read, and this is a leading cause of "the database got slow over six months with no code change".

Two related knobs worth knowing exist: default_statistics_target raises histogram resolution for columns with skewed distributions, and random_page_cost tells the planner how much more expensive a random read is than a sequential one — its default assumes spinning disks, and lowering it on SSD storage makes the planner willing to use indexes it was avoiding.

8. Application-level causes that no index fixes

The N+1 query problem. Fetch 100 orders, then loop and fetch each order's customer: 101 round trips. Each is fast; the total is 101 network latencies. It is the most common performance bug in ORM-heavy code, it never appears in a slow-query log because every individual query is fast, and Chapter 7.3.2 covers detecting and fixing it.

Offset pagination. LIMIT 20 OFFSET 100000 makes the engine produce and discard 100,000 rows. Page 1 is instant and page 5,000 times out. Chapter 9.6.2 derives keyset pagination as the fix.

SELECT * on wide rows. Fetching a 40 KB description column to display a title costs network and buffer pool. It also prevents an index-only scan.

Missing connection pooling. Chapter 7.2.4 covers it; the symptom is a query that is fast in isolation and slow under load because each request pays a full connection setup.

9. A repeatable method

1. Find the query. pg_stat_statements ranks by total time, which correctly surfaces the 5 ms query run 200,000 times ahead of the 2-second nightly report.
2. EXPLAIN (ANALYZE, BUFFERS) it with realistic parameters — a plan for WHERE country = 'Vatican City' tells you nothing about 'United States'.
3. Find the node where estimated and actual diverge, and the node with the biggest Rows Removed by Filter or loops.
4. Ask which of three things is wrong: a missing index, a non-sargable condition, or a bad estimate.
5. Change one thing and re-measure. Two changes at once teach you nothing.
6. Check the index is actually used afterwards, and check what it costs on writes (Chapter 7.3.2) before keeping it.

What the interviewer will push on

"A query got slow. Walk me through what you do." They want a method, not a guess. pg_stat_statements to find it by total time, EXPLAIN (ANALYZE, BUFFERS) with real parameters, find the node where estimated rows and actual rows diverge, then decide between missing index, non-sargable predicate, and bad statistics. Saying "I'd add an index" first is the weak answer.

"Is a sequential scan bad?" No. It is correct when the query returns most of the table, and it is the disk's best case. It is bad when Rows Removed by Filter is huge. The tell is knowing that an index scan costs a lookup per row and therefore loses at high selectivity.

"Why would an index not be used?" A function or cast wrapping the column, a leading wildcard, low selectivity, OR across columns, or a stale estimate that made the planner think a scan was cheaper. Then volunteer the invisible one: an implicit type cast from an ORM parameter, which appears in the plan as (col)::text and is untraceable from the application code.

"What is EXPLAIN ANALYZE doing that EXPLAIN is not?" Running the query and reporting actual timings and row counts. Then add the two operational details: it performs writes for a DELETE, so wrap it in a transaction and roll back, and actual numbers are per loop, so a nested loop's inner node must be multiplied by loops.

"Why do plans change without a deploy?" Statistics change as data grows, autovacuum re-analyses, and the planner crosses a threshold — usually the point where an index scan stops being cheaper than a sequential scan. Then mention bloat: without vacuum, dead row versions make the same data cost several times as much to read.

"When would you add a partial index?" When the query always filters on the same small subset — pending jobs, refunded orders, non-deleted rows. It is smaller, so it is cheaper to maintain on writes and more of it stays in memory. That last point about write cost is what distinguishes an answer from a definition.

One thing to volunteer: mention that the biggest wins usually are not query rewrites at all — N+1 loops and offset pagination are application-shaped problems where every individual query is fast and the total is terrible, so they never appear in a slow-query log. Naming that gap shows you have debugged a real system rather than a single statement.

Recall

• EXPLAIN predicts; EXPLAIN ANALYZE runs it and reports the truth. Add BUFFERS — buffer counts are stable where timings are not. Wrap ANALYZE of a write in a transaction and roll back.
• Read the tree inside-out. cost=start..total, rows= is the estimate, actual … rows= is the truth, and actual is per loop — multiply by loops.
• The most useful signal is estimated rows versus actual rows. A large gap means every decision above that node was made on false information; ANALYZE, or extended statistics for correlated columns, often fixes the plan without touching the query.
• Seq Scan is correct when most rows are returned. It is a problem when Rows Removed by Filter is huge. Index Only Scan is the fastest shape and needs a covering index; a bitmap scan is the sensible middle.
• A condition is sargable only when the column stands alone: no DATE(col), no LOWER(col), no leading %, no arithmetic on the column, and no implicit cast — which is the invisible ORM version.
• Trouble shapes: nested loop with huge loops, external merge Disk on a sort, hash Batches > 1, big Rows Removed by Filter, and rows=1 against millions actual.
• ANALYZE after every bulk load. Without VACUUM, dead row versions bloat tables and indexes, so the same data costs several times as much to read — the usual cause of "slow after six months with no code change".
• The largest wins are often not in SQL: N+1 loops and offset pagination are fast per query and terrible in total, so they never show in a slow-query log.

Self-test: What does loops=900000 on an inner node tell you? · Why is a sequential scan sometimes the right plan? · Name three ways to accidentally disable an index · What does Rows Removed by Filter point at? · Why does a bulk load make the next query slow? · Why does LIMIT 10 sometimes make a query slower?

Next: 7.2.4 covers the decisions made before any of this — key types, soft deletes, migrations, connection pooling, and how honest to be about your ORM.