Reading a query plan · System design

The planner decides, not you

SQL says what you want, not how to get it. For each query, the database's planner considers several ways to run it (scan the table or use an index? which index? which table first in a join?), estimates the cost of each from statistics it keeps about the data, and picks the cheapest. Usually it's right. When a query is slow, the first step is to see what it chose.

In Postgres, EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. (The outputs below are trimmed and illustrative; real ones have more detail.)

A slow plan, then a fast one

Snip's dashboard query, before the index from 04.02 Indexes and B-trees existed:

text

EXPLAIN ANALYZE
SELECT code, long_url FROM links WHERE owner_id = 42 ORDER BY created_at DESC LIMIT 50;

Limit  (actual time=14210.3..14210.4 rows=50)
  ->  Sort  (cost=…  rows=1840)  (actual rows=50)
        Sort Key: created_at DESC
        ->  Seq Scan on links  (rows=1840)  (actual rows=1912)
              Filter: (owner_id = 42)
              Rows Removed by Filter: 599998088
Execution Time: 14210.6 ms

Read plans from the innermost line outwards. The bottom node ran first: a Seq Scan (sequential scan: every row of links), throwing away almost 600 million rows to keep 1,912. Then a Sort, then Limit took 50. Fourteen seconds.

After CREATE INDEX links_owner_created ON links (owner_id, created_at DESC):

text

Limit  (actual time=0.04..0.21 rows=50)
  ->  Index Scan using links_owner_created on links  (actual rows=50)
        Index Cond: (owner_id = 42)
Execution Time: 0.25 ms

One Index Scan, already in the right order, so no Sort, and it stops after 50 rows. Fifty thousand times faster.

The words you'll see most

Node	What it does	Good or bad?
Seq Scan	Reads the whole table	Fine for small tables or when most rows match; bad for finding a few rows in a big one
Index Scan	Walks an index, then fetches each matching row	Good for few rows
Index Only Scan	Answers from the index alone (a covering index)	Best, when possible
Bitmap Heap Scan	Collects matches from an index, then reads their pages in order	Good for a medium number of rows
Nested Loop	For each row on one side, look up matches on the other	Great when the outer side is small and the inner is indexed
Hash Join	Builds a hash table from one side, probes it with the other	Good for large, unsorted joins
Sort	Sorts rows	Costly on many rows; an index in the right order can avoid it

Why plans go wrong

Most slow queries come from a handful of causes. The plan usually shows which:

No usable index. A Seq Scan with a huge "Rows Removed by Filter". Add an index, in the right column order.
A function on the column. WHERE lower(email) = 'ana@example.com' can't use an index on email. Index the expression, or store the value already lowercased.
Bad estimates. Compare estimated rows= with actual rows=. If the planner expected 10 rows and got 2 million, it chose a plan for the wrong situation. Statistics may be stale (run ANALYZE), or two columns may be correlated in a way it can't know.
Not selective enough. WHERE plan = 'free' matches 90% of users; a scan really is cheaper than an index there, and the planner is right to ignore the index.
Deep offsets. OFFSET 100000 reads and discards 100,000 rows. Use cursor pagination (03.04 API design: resources, pagination, versioning, errors).

A habit worth having

For any query on a hot path, run EXPLAIN ANALYZE on production-sized data before shipping it, and watch the database's slowest-queries log (Postgres's pg_stat_statements) afterwards. A query that's fast on a developer's 10,000-row table can be a full scan on 600 million.

Predict first

Snip has an index on users (email). The query SELECT * FROM users WHERE lower(email) = 'ana@example.com' is slow, and EXPLAIN shows a Seq Scan on users. Why isn't the index used?

Commit to an answer before you read on.

Recap

Interview questions

MidfreqMust-knowScenario

A query that used to be fast is now slow. How do you investigate?