How to Read a Query Plan: EXPLAIN in SQL, Line by Line

A slow query invites guessing: maybe the join, maybe the sort, maybe an index somewhere. EXPLAIN replaces the guess with the engine's own account of what it will do. There are only a few words to learn, and once you can read a two-table join plan you can read most plans. Every plan below is real output from SQLite 3.37 through Python's sqlite3 module. SCAN versus SEARCH on one table is covered in the SQL indexes article; this one reads whole plans, joins included.

The problem it solves

A query says what you want. The planner decides how: which table to read first, whether to scan it or jump into an index, how to find matches in the second table, and whether to sort afterwards. The same query can get a different plan after one new index. Without reading the plan you are tuning blind.

The intuition

Read a join plan as nested loops, top to bottom:

• The first line is the outer loop. It runs once and produces rows.
• Each later line runs once per row that the lines above it produce. That is why the second table is called the inner side.
• SCAN t reads every row of t. SEARCH t USING INDEX ... jumps to the matching entries; USING INTEGER PRIMARY KEY is the same jump on the table's own key; COVERING INDEX means the index alone had every needed column.
• USE TEMP B-TREE FOR ORDER BY (or GROUP BY, DISTINCT) means rows were sorted on the fly because no index delivered them in order.
• Indented lines belong to a subquery or another nested step.

The cost is roughly outer rows times the price of each inner probe, so an inner SCAN is the expensive shape.

Watch it run

The animation reads the lesson's plan. A two-table join is slow, so rather than guess, ask the engine. The first line is the outer table, firing: whatever it produces, everything below runs once per row. And it says SCAN, which means every row of that table is read. The second line is the inner side, kiln; SEARCH means it jumps straight to the row, probed once per outer row. So the cost is the scan multiplied by the probe, and the outer side is what to fix. A third line would say the sort had nowhere to come from, rows ordered on the fly with a temp B-tree. Index the column the outer table is filtered on, CREATE INDEX i_cone ON firing(cone), read the plan again, and SCAN became SEARCH: that one word is the whole result of the change. The caveat closes it: this is the plan, not the clock, and a plan on three rows can lie about three million.

The code

A library: 500 books and 5,000 loans, about one in twenty more than two weeks late. The helper prints the plan as a tree, using the parent id that every plan row carries:

import random
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, shelf TEXT);
CREATE TABLE loan (book_id INT, member TEXT, days_late INT);
""")
rng = random.Random(15)
books = [(i, f"title {i}", rng.choice("ABCD")) for i in range(1, 501)]
loans = [(rng.randint(1, 500), rng.choice(["ana", "ben", "cy"]),
          rng.choice([0] * 9 + [rng.randint(1, 30)])) for _ in range(5_000)]
db.executemany("INSERT INTO book VALUES (?, ?, ?)", books)
db.executemany("INSERT INTO loan VALUES (?, ?, ?)", loans)

def plan(sql):
    """EXPLAIN QUERY PLAN as a tree: each row names its parent row."""
    depth = {0: -1}
    for node, parent, _, detail in db.execute("EXPLAIN QUERY PLAN " + sql):
        depth[node] = depth[parent] + 1
        print("  " * depth[node] + detail)

late = ("SELECT b.title, l.member FROM loan l JOIN book b ON b.id = l.book_id "
        "WHERE l.days_late > 14")
plan(late)
# SCAN l
# SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
plan(late + " ORDER BY b.title")
# SCAN l
# SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
# USE TEMP B-TREE FOR ORDER BY
plan("SELECT title FROM book WHERE id IN (SELECT book_id FROM loan WHERE days_late > 14)")
# SEARCH book USING INTEGER PRIMARY KEY (rowid=?)
# LIST SUBQUERY 1
#   SCAN loan

The tree matters in the IN query: the scan of loan is the subquery that builds the list of ids, not a peer of the book lookup. Next, the planner choosing the join order. Filter on the book's shelf, and the only fast probe is the book's primary key, so loans drive. Index loan on its join column and the order flips:

shelf = "SELECT b.title FROM book b JOIN loan l ON l.book_id = b.id WHERE b.shelf = 'A'"
plan(shelf)
# SCAN l
# SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
db.execute("CREATE INDEX loan_book ON loan(book_id)")
plan(shelf)
# SCAN b
# SEARCH l USING COVERING INDEX loan_book (book_id=?)
db.execute("CREATE INDEX loan_late ON loan(days_late)")
plan(late)
# SEARCH l USING INDEX loan_late (days_late>?)
# SEARCH b USING INTEGER PRIMARY KEY (rowid=?)

The order you write tables in FROM did not decide anything; the indexes did. Now the "outer rows times inner probe" rule, measured. A toy model of the executor runs the late-loans query under three plan shapes and counts every row it reads, then checks its answer against SQLite's:

import bisect
from collections import defaultdict

def nested_loop(outer, inner, outer_ok, join, outer_index=None, inner_index=None):
    """A toy executor for a two-line plan. Returns the joined rows and rows read."""
    read = 0
    if outer_index:                              # SEARCH outer: only matching entries
        keys, rows = outer_index
        candidates = rows[bisect.bisect_right(keys, outer_ok):]
    else:                                        # SCAN outer: every row
        candidates = outer
    out = []
    for o in candidates:
        read += 1
        if o[2] <= outer_ok:
            continue
        probe = inner_index.get(o[0], []) if inner_index else inner   # SEARCH vs SCAN
        for i in probe:
            read += 1
            if join(o, i):
                out.append((i[1], o[1]))
    return sorted(out), read

by_id = defaultdict(list)
for b in books:
    by_id[b[0]].append(b)
ordered = sorted(loans, key=lambda l: l[2])
late_index = ([l[2] for l in ordered], ordered)
same = lambda o, i: i[0] == o[0]

truth = sorted(db.execute(late).fetchall())
for name, args in [("SCAN l, SCAN b", {}),
                   ("SCAN l, SEARCH b", {"inner_index": by_id}),
                   ("SEARCH l, SEARCH b", {"inner_index": by_id, "outer_index": late_index})]:
    rows, read = nested_loop(loans, books, 14, same, **args)
    print(f"{name:20} rows read {read:>9,}  answer matches SQLite: {rows == truth}")
# SCAN l, SCAN b       rows read   128,500  answer matches SQLite: True
# SCAN l, SEARCH b     rows read     5,247  answer matches SQLite: True
# SEARCH l, SEARCH b   rows read       494  answer matches SQLite: True

Same answer, three costs: 247 late loans each scanning 500 books, then each finding its one book, then never touching the 4,753 loans that were not late. That is why SQLite may build a temporary AUTOMATIC COVERING INDEX rather than scan an unindexed inner table once per outer row. The seeded check: 200 random libraries with missing books and random thresholds, comparing every plan shape with a brute-force join, with SQLite, and with its read-count formula:

ok = True
for seed in range(200):
    r = random.Random(seed)
    bk = [(i, f"t{i}", "A") for i in r.sample(range(1, 40), r.randint(0, 25))]
    ln = [(r.randint(1, 40), f"m{j}", r.randint(0, 9)) for j in range(r.randint(0, 60))]
    t = r.randint(0, 9)
    brute = sorted((b[1], l[1]) for l in ln for b in bk if l[2] > t and b[0] == l[0])
    s = sqlite3.connect(":memory:")
    s.execute("CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, shelf TEXT)")
    s.execute("CREATE TABLE loan (book_id INT, member TEXT, days_late INT)")
    s.executemany("INSERT INTO book VALUES (?, ?, ?)", bk)
    s.executemany("INSERT INTO loan VALUES (?, ?, ?)", ln)
    sql = late.replace("14", "?")
    ok &= sorted(s.execute(sql, (t,)).fetchall()) == brute
    idx = defaultdict(list)
    for b in bk:
        idx[b[0]].append(b)
    srt = sorted(ln, key=lambda l: l[2])
    passing, matched = sum(l[2] > t for l in ln), len(brute)
    for args, formula in [({}, len(ln) + passing * len(bk)),
                          ({"inner_index": idx}, len(ln) + matched),
                          ({"inner_index": idx, "outer_index": ([l[2] for l in srt], srt)},
                           passing + matched)]:
        rows, read = nested_loop(ln, bk, t, same, **args)
        ok &= rows == brute and read == formula
print(ok)                                    # True

The complexity

For a two-table nested-loop join with n outer rows, of which p pass the filter, and m inner rows:

• SCAN, SCAN: n + p · m rows read, the quadratic shape.
• SCAN, SEARCH: n rows plus p index probes of O(log m) each.
• SEARCH, SEARCH: O(log n + p) to find the passing rows, plus the same p probes.
• TEMP B-TREE: an extra O(k log k) sort of the k result rows.

Where it goes wrong

• Reading the plan on toy data. Planners choose by estimated row counts, so three rows in development can produce a plan you will never see on three million. Check on realistic data, and refresh statistics (ANALYZE) after big loads.
• Expecting FROM order to matter. The planner reorders joins; the indexes decide.
• Fixing the inner side first. An outer SCAN that throws most rows away is usually the fix.
• Ignoring the sort line. An index in ORDER BY order removes it, which matters most with LIMIT.

Other engines use other words for the same ideas (from memory, as of October 2026): PostgreSQL's EXPLAIN prints Seq Scan, Index Scan, Index Only Scan, Nested Loop, Hash Join and Merge Join, with estimated costs and rows; EXPLAIN ANALYZE actually runs the query and adds real rows and timings, so wrap data-changing statements in a transaction you roll back. MySQL's EXPLAIN marks a full scan as type ALL. The SQL cheat sheet has a short EXPLAIN QUERY PLAN example.

When it shows up in interviews

As "this query is slow, what do you do?", where the expected first move is to read the plan rather than add indexes at random. Follow-ups: what SCAN versus SEARCH means, why the first table matters, and why a plan that looks fine in staging can change in production.

How to say it in an interview

"I'd run EXPLAIN first. I read a join plan as nested loops: the first line is the outer table, and every line below runs once per row it produces, so the cost is roughly outer rows times the cost of each inner probe. SCAN means a full read, SEARCH means an index jump, and a temp B-tree means a sort with no index behind it. I look for a SCAN that throws most rows away, usually index its filter or join column, and re-read the plan to confirm SCAN became SEARCH. And I'd check on realistic data, because the plan is an estimate, not a measurement."