Reading a query plan with EXPLAIN

Step 1 Understand it

Why you need it. "This query is slow — what do you do?" The first answer is always: look at the plan. Guessing wastes time; the plan tells you whether the database is scanning a huge table, missing an index, or sorting millions of rows.

Watch it happen

Picture it

flowchart LR
  Q["slow query"] --> E["EXPLAIN it"]
  E --> S{"SCAN on a big table?"}
  S -->|"yes"| I["add an index on the<br/>filter or join column"]
  S -->|"no"| T{"big sort or<br/>TEMP B-TREE?"}
  T -->|"yes"| O["index the ORDER BY<br/>or GROUP BY columns"]
  I --> E
  O --> E

The idea

Put EXPLAIN in front of a query (in SQLite, EXPLAIN QUERY PLAN). Read each line as one step. The words to look for: SCAN (reading a whole table — fine if small, a red flag if big), SEARCH … USING INDEX (jumping to rows — good), and TEMP B-TREE or Sort (building a sort or grouping — costly on big data). For a join, each line is one table, in the order the database reads them: it scans one and searches the other for each row. PostgreSQL's EXPLAIN ANALYZE also runs the query and shows real times and row counts — compare estimated rows with actual rows to spot bad estimates.

The shape of it

EXPLAIN QUERY PLAN SELECT ...;   -- SQLite
EXPLAIN SELECT ...;              -- PostgreSQL, MySQL: the plan
EXPLAIN ANALYZE SELECT ...;      -- PostgreSQL: run it, real timings

Words in lower case are the parts you fill in.

How it works, step by step

1. Before an index on orders.customer_id: SCAN o (read every order), and for each one SEARCH c by primary key.
2. Add the index: now the plan is SCAN c (only 6 customers), and SEARCH o USING INDEX for each customer.
3. The order of the lines is the order the tables are read in: the outer table first.
4. For GROUP BY without a helpful index, you see USE TEMP B-TREE FOR GROUP BY: a temporary sort.
5. Fixes usually mean: add or change an index, filter earlier, or rewrite a condition so the index can be used.

Analogy

A satnav showing the route before you drive. You can see it plans to go through the city centre (a full scan) instead of the bypass (an index), and fix it before you set off.

Where the analogy breaks

A satnav's route is the same every time. A query plan can change as the data grows or statistics update — a plan that is fine on 10 rows may be terrible on 10 million. Always check plans on realistic data.

Worked examples

EXPLAIN QUERY PLAN
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Leeds';

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Leeds';

EXPLAIN QUERY PLAN
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Must-know points

• EXPLAIN shows the chosen plan; EXPLAIN ANALYZE (PostgreSQL) runs it and shows real times and rows.
• SCAN = read everything; SEARCH / Index Scan = jump via an index.
• Sorts and temp B-trees for ORDER BY, GROUP BY or DISTINCT are expensive on big data.
• In a join plan, the first table is the outer loop; the inner table should be searched by index.
• Big gaps between estimated and actual row counts point to stale statistics (run ANALYZE).

Common mistakes

• Tuning by guesswork instead of reading the plan.
• Judging a plan on a tiny test table — the optimiser may choose differently on real data.
• Running EXPLAIN ANALYZE on a DELETE or UPDATE in production. It really runs the statement.

In other databases

• PostgreSQL plan words: Seq Scan (full scan), Index Scan, Index Only Scan, Bitmap Heap Scan, Nested Loop, Hash Join, Merge Join, Sort.
• MySQL: EXPLAIN gives a table with a type column — ALL is a full scan, ref/range/const use an index. EXPLAIN ANALYZE exists from 8.0.18.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  SQLite docs — EXPLAIN QUERY PLAN

  official documentation, free

• Read

  PostgreSQL docs — Using EXPLAIN

  official documentation, free

• Read

  Use The Index, Luke — a guide to database performance for developers

  Markus Winand, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what SCAN and SEARCH mean in a plan, and which one you want on a big table.

   Show answer

   SCAN means the database reads every row of the table (or of an index) from start to finish. SEARCH means it uses an index to jump straight to the matching rows, like using a book's index instead of reading every page.

   On a big table you want SEARCH for selective filters and joins. A SCAN of a million-row table on every request is usually the reason a query is slow. A SCAN is fine when the table is tiny, or when you really need most of its rows (then reading in order beats jumping around).

2. Writefrom a blank page, then run it

   Write EXPLAIN QUERY PLAN for "employees in department 2", then add an index and show the plan changes.

   Show answer

   Before: SCAN employees. The index below holds dept_id and name together, so the plan becomes a SEARCH with a COVERING INDEX: the query needs only name, which is already in the index, so the database never reads the table itself.

   An index on dept_id alone would also turn the SCAN into a SEARCH, but each match would then need a second lookup into the table to fetch the name.

   sqlCopy

   CREATE INDEX idx_employees_dept_name ON employees(dept_id, name);
   EXPLAIN QUERY PLAN SELECT name FROM employees WHERE dept_id = 2;

   Try it

   1 row

   id	parent	notused	detail
   2	0	55	SEARCH employees USING COVERING INDEX idx_employees_dept_name (dept_id=?)

3. Predictsay it before you run it

   What plan do you expect for SELECT * FROM products WHERE product_id = 3? Check it.

   Show answer

   A SEARCH by the primary key: product_id is an INTEGER PRIMARY KEY, which in SQLite is the rowid, so it goes straight to the one row. No index needs to be created.

   sqlCopy

   EXPLAIN QUERY PLAN SELECT * FROM products WHERE product_id = 3;

   Try it

   1 row

   id	parent	notused	detail
   2	0	33	SEARCH products USING INTEGER PRIMARY KEY (rowid=?)

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Add an index on orders(customer_id) and re-run the GROUP BY plan. Does the temporary B-tree disappear? Why might it still be there?

   Show answer

   Here it disappears. Before the index, the plan is SCAN orders plus USE TEMP B-TREE FOR GROUP BY: the rows come out in any order, so SQLite builds a temporary sorted structure to group them. With the index, it reads orders through idx_orders_customer, which already returns rows sorted by customer_id, so the groups arrive in order and no sort is needed.

   Why it might still be there:
   • The index doesn't cover the query: amount isn't in it, so every row needs an extra lookup into the table. On a big table the optimiser may decide a plain scan plus a sort is cheaper, and keep the temp B-tree. An index on (customer_id, amount) covers the query and removes that doubt.
   • With statistics (after ANALYZE), the optimiser might prefer the scan anyway.
   • The ORDER BY or GROUP BY doesn't match the index order (a different column, or a different direction).

   sqlCopy

   CREATE INDEX idx_orders_customer ON orders(customer_id);
   EXPLAIN QUERY PLAN
   SELECT customer_id, SUM(amount)
   FROM orders
   GROUP BY customer_id
   ORDER BY customer_id;

   Try it

   1 row

   id	parent	notused	detail
   8	0	223	SCAN orders USING INDEX idx_orders_customer

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

My first step with a slow query is EXPLAIN, or EXPLAIN ANALYZE in PostgreSQL to see real timings and row counts. I look for sequential scans on large tables, expensive sorts, nested loops over big inputs, and estimates that are far from actual counts. Typical fixes are an index on the filter or join columns, rewriting a non-sargable condition, filtering earlier, or refreshing statistics — then I re-check the plan.

Follow-up questions

Tick each check when you can do it without notes