Reading a query plan with EXPLAIN
EXPLAIN shows the plan the database chose for a query — which tables it scans, which indexes it searches, how it joins and sorts — without (or, with ANALYZE, while) running it.
Why it matters
"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.
The idea
Put EXPLAIN in front of a query (in SQLite, EXPLAIN QUERY PLAN). Read each line as one step.
There are three words to look for.
SCAN means reading a whole table: fine if it is small, a warning sign if it is big.
SEARCH … USING INDEX means jumping straight to rows: good.
TEMP B-TREE or Sort means building a sort or a 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.
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.
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.
Syntax
EXPLAIN QUERY PLAN SELECT ...; -- SQLite
EXPLAIN SELECT ...; -- PostgreSQL, MySQL: the plan
EXPLAIN ANALYZE SELECT ...; -- PostgreSQL: run it, real timingsHow it works
- Before an index on orders.customer_id: SCAN o (read every order), and for each one SEARCH c by primary key.
- Add the index: now the plan is SCAN c (only 6 customers), and SEARCH o USING INDEX for each customer.
- The order of the lines is the order the tables are read in: the outer table first.
- For GROUP BY without a helpful index, you see USE TEMP B-TREE FOR GROUP BY: a temporary sort.
- Fixes usually mean: add or change an index, filter earlier, or rewrite a condition so the index can be used.
Key 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.
Not true: The plan chosen on a tiny test table is the plan the database will use on the real data.
In fact: The optimiser decides from table sizes and statistics. On a tiny table it may read everything, where on millions of rows it would use an index, so test on realistic data.
In the interview
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.
Part of Unit 7: Performance: indexes and query plans in the free SQL course.