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 timings

How it works

  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.

Key points

Common mistakes

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.