Reading a Query Plan

Every database will tell you how it plans to run a query. This is the only reliable way to know why something is slow — guessing from the SQL is how people spend a day optimising a query that was never the problem.

SQL▶ Run
EXPLAIN QUERY PLAN
SELECT e.first_name, d.name
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id;

Read it as: scan every employee, and for each one look up its department by primary key. That is a reasonable plan for a small table on the left and an indexed lookup on the right.

The command, per database

EXPLAIN alone shows what the planner intends. EXPLAIN ANALYZE actually executes and reports what happened — which is what you want, because the gap between estimated and actual row counts is the single most useful diagnostic there is. (It really runs the query, so be careful with EXPLAIN ANALYZE on a DELETE.)

The vocabulary

Names differ by database; the concepts do not.

Access methods — how rows are found:

• Seq Scan / Table Scan / SCAN — read every row. Fine on small tables, the usual culprit on large ones.
• Index Scan / SEARCH — walk the index, fetch the matching rows.
• Index Only Scan — everything needed was in the index; the table was never touched. The fastest outcome.
• Bitmap Index Scan (PostgreSQL) — for matches too numerous for individual lookups but too few for a scan.

Join strategies:

• Nested Loop — for each row on the left, look up matches on the right. Excellent when the left side is small and the right is indexed; catastrophic when both are large.
• Hash Join — build a hash table from the smaller side, probe with the larger. The workhorse for big unsorted joins.
• Merge Join — both inputs sorted, walked together. Good when the sort is free because indexes already provide it.

Other operations:

• Sort — an ORDER BY an index could not satisfy.
• Aggregate / HashAggregate / GROUP — grouping.
• Materialize / TEMP B-TREE — an intermediate result held in memory or spilled to disk.

Sorting and grouping show up in the plan

SQL▶ Run
EXPLAIN QUERY PLAN SELECT * FROM employees ORDER BY salary DESC;

USE TEMP B-TREE FOR ORDER BY — there is no index on salary, so the database builds a temporary structure to sort. An index on salary would let it read rows in order and skip this step entirely.

SQL▶ Run
EXPLAIN QUERY PLAN SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;

Same story for grouping.

On a small table these cost nothing. The point is that the plan tells you they are happening, so when the table grows you already know where the time will go.

Subqueries in the plan

SQL▶ Run
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

SCALAR SUBQUERY appears as its own step, evaluated once — confirming lesson 24's claim that an uncorrelated subquery does not re-run per row. The plan is where that kind of assumption gets checked rather than believed.

What to look for

In rough order of usefulness:

1. A scan on a large table where you expected an index. Usually a missing index, or a query written so the index cannot be used — the next lesson.
2. A big gap between estimated and actual rows (EXPLAIN ANALYZE). The planner is working from bad statistics; the fix is often ANALYZE, not a new index.
3. A nested loop over a large outer input. Usually follows from point 2.
4. A sort that could have been an index scan.
5. The most expensive node. Plans are trees; find the node with the real cost before optimising anything else.

Cost numbers are not milliseconds

PostgreSQL's cost=0.00..35.50 is in arbitrary units for comparing plans, not a time estimate. rows= is the planner's guess. With EXPLAIN ANALYZE you also get actual time and actual rows, and comparing guess against actual is the diagnosis: if it expected 10 rows and got 100,000, everything downstream was planned wrongly.

The workflow

1. Reproduce the slow query with realistic data. Plans on a hundred test rows tell you nothing about a million.
2. EXPLAIN ANALYZE it.
3. Find the most expensive node.
4. Change one thing — an index, a rewrite.
5. Re-run and compare. Keep the change only if the plan actually improved.

Steps 1 and 5 are the ones people skip, and skipping them is how a codebase accumulates indexes nobody can justify.

Common mistakes

• Optimising without reading the plan — you will fix the wrong thing.
• Testing on toy data — the planner picks scans for small tables no matter what you do.
• Reading cost as time — it is a comparison unit.
• Adding an index because a scan appears — on a small table, the scan is correct.
• Changing several things at once — you learn nothing about which one worked.

Interview question

A query got slow. Walk me through what you do.

Reproduce it, EXPLAIN ANALYZE it, find the most expensive node, check estimated against actual rows to see whether the planner is misinformed, and only then consider an index or a rewrite — measuring the plan before and after. Naming EXPLAIN ANALYZE specifically, and the estimate-versus-actual check, is what distinguishes experience from recitation.

Check yourself

1. What is the difference between EXPLAIN and EXPLAIN ANALYZE?
2. What does a large gap between estimated and actual rows suggest?
3. Why is a sequential scan not automatically a problem?