Reading Query Plans in Database Indexing: EXPLAIN in Practice

Two lines of plan output decide whether a support dashboard loads in a blink or after a coffee: SCAN t and SEARCH t USING INDEX .... Reading those lines is a skill separate from knowing what indexes are. This lesson walks through eight plans for a small support-ticket database, then maps what you learn onto the vocabulary of PostgreSQL and MySQL, which print richer output than SQLite does.

If the idea of a leading column is unfamiliar, read the previous lesson first; this one builds on it.

What a plan is, and what it is not

A query plan is the database's chosen strategy: which index or table it reads first, how it joins, whether it sorts. The planner picks it using statistics about table sizes and value distributions, so a plan is an educated guess, not a measurement. EXPLAIN shows the guess. Tools that also run the query and report real row counts and timings (PostgreSQL's EXPLAIN ANALYZE) show how good the guess was.

SQLite's EXPLAIN QUERY PLAN shows only the guess, with no costs or timings, which makes it a clean place to learn the structure. The vocabulary is small: SCAN means read everything in that table or index, SEARCH means seek using an index or the row id, COVERING INDEX means the table is not needed, and USE TEMP B-TREE means an extra sort or grouping step.

Eight plans from a ticket system

The script builds 2,000 customers and 30,000 tickets, then prints plans while the schema changes. Read it top to bottom: each label says what changed.

import random
import sqlite3

random.seed(5)
db = sqlite3.connect(":memory:")
db.executescript("""
    CREATE TABLE customers (
        id    INTEGER PRIMARY KEY,
        email TEXT NOT NULL,
        plan  TEXT NOT NULL
    );
    CREATE TABLE tickets (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id),
        opened_at   TEXT    NOT NULL,
        priority    INTEGER NOT NULL,
        subject     TEXT    NOT NULL
    );
""")
db.executemany("INSERT INTO customers VALUES (?,?,?)",
               [(i, f"user{i}@example.test", random.choice(["free", "pro", "team"]))
                for i in range(1, 2001)])
db.executemany("INSERT INTO tickets VALUES (?,?,?,?,?)",
               [(i, random.randint(1, 2000),
                 f"2025-{random.randint(1, 12):02d}-{random.randint(1, 28):02d}",
                 random.randint(1, 4), f"subject {i}")
                for i in range(1, 30001)])

def show(label, sql):
    print(f"-- {label}")
    for _id, _parent, _unused, detail in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("   ", detail)

join = """SELECT c.email, t.subject
          FROM tickets t JOIN customers c ON c.id = t.customer_id
          WHERE c.email = 'user42@example.test'"""
show("A. join, only primary keys indexed", join)

db.execute("CREATE INDEX idx_customers_email ON customers (email)")
db.execute("CREATE INDEX idx_tickets_customer ON tickets (customer_id)")
show("B. after indexing email and the foreign key", join)

top = "SELECT id, subject FROM tickets WHERE priority = 1 ORDER BY opened_at DESC LIMIT 5"
show("C. top 5 urgent, no helpful index", top)
db.execute("CREATE INDEX idx_tickets_prio_opened ON tickets (priority, opened_at)")
show("D. with (priority, opened_at)", top)

db.execute("CREATE INDEX idx_tickets_opened ON tickets (opened_at)")
show("E. wrapped in a function", "SELECT id FROM tickets WHERE substr(opened_at, 1, 7) = '2025-03'")
show("F. same intent as a range", "SELECT id FROM tickets WHERE opened_at >= '2025-03-01' AND opened_at < '2025-04-01'")
show("G. leading wildcard", "SELECT id FROM customers WHERE email LIKE '%42@example.test'")
show("H. trailing wildcard (binary collation via GLOB)", "SELECT id FROM customers WHERE email GLOB 'user42*'")

-- A. join, only primary keys indexed
    SCAN t
    SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
-- B. after indexing email and the foreign key
    SEARCH c USING COVERING INDEX idx_customers_email (email=?)
    SEARCH t USING INDEX idx_tickets_customer (customer_id=?)
-- C. top 5 urgent, no helpful index
    SCAN tickets
    USE TEMP B-TREE FOR ORDER BY
-- D. with (priority, opened_at)
    SEARCH tickets USING INDEX idx_tickets_prio_opened (priority=?)
-- E. wrapped in a function
    SCAN tickets USING COVERING INDEX idx_tickets_opened
-- F. same intent as a range
    SEARCH tickets USING COVERING INDEX idx_tickets_opened (opened_at>? AND opened_at<?)
-- G. leading wildcard
    SCAN customers USING COVERING INDEX idx_customers_email
-- H. trailing wildcard (binary collation via GLOB)
    SEARCH customers USING COVERING INDEX idx_customers_email (email>? AND email<?)

Plans A and B: the join

In a join, plan lines are nested loops, outermost first. Plan A scans all 30,000 tickets and, for each one, looks up the customer by primary key to check the email, which is thirty thousand lookups to find one customer's tickets. Plan B flips it: find the customer through the email index, then use the foreign-key index to fetch only that customer's tickets. Same SQL, opposite cost. Nothing indexed tickets.customer_id until we created it.

Plans C and D: top-N with ORDER BY

Plan C reads every ticket, then builds a temporary sorted structure just to keep five rows. With (priority, opened_at) the matching tickets are already in date order, so the planner seeks to priority 1 and walks the index backwards from the end. The temp sort disappears and the work no longer depends on how many tickets exist in total.

Plans E to H: queries that cannot use the index

E and F ask for the same March rows. E wraps the column in substr(...), so the engine must compute it for every row and can only scan; F states the same condition as a range and gets a SEARCH. G starts with a % wildcard, so there is no sorted starting point and it can only walk the whole index. H, a prefix pattern, becomes a range (email>? AND email<?). Whether LIKE gets this treatment depends on the engine and its collation settings, which is why this demo uses GLOB for the prefix case.

Same ideas, other databases

I have not run PostgreSQL or MySQL for this course, so the table below describes how their output is organised, not sample results. The skills carry over directly: find the biggest cost, check whether an index is used, and look for a sort or a scan you did not expect.

PostgreSQL's output is a tree of nodes, each with estimates in the shape below (a format template, not real output). With ANALYZE each node gains the actual time, rows and loop count, and the gap between estimated and actual rows is the first thing to check.

Index Scan using <index_name> on <table>  (cost=<startup>..<total> rows=<estimated> width=<bytes>)
  Index Cond: (<column> = <value>)
-- with ANALYZE, each node also shows:
  (actual time=<first row>..<last row> rows=<actual> loops=<times run>)

A repeatable routine

1. Capture the slow statement with its real parameter values, not a simplified version.
2. Run the plan command and find the line or node that reads the most rows. That is the one to fix.
3. Check whether an index you expect is used; if not, look for a function on the column, a leading wildcard, a type mismatch or a missing leading column.
4. Compare estimated and actual rows where your database reports both. A large gap usually means stale statistics: refresh them and re-run.
5. Change one thing, re-run the plan, then time the real query. Keep the change only if both improve.

Common mistakes

• Tuning on a ten-row dev database. Symptom: the planner chooses SCAN because the table is tiny, and you wrongly conclude your index is useless. Load realistic volumes first.
• Stale statistics. Symptom: PostgreSQL's estimated rows and actual rows differ by orders of magnitude and the planner picks a bad join order; running ANALYZE changes the plan.
• Reading only the plan, never the timing. Symptom: you celebrate a SEARCH line while the query still returns hundreds of thousands of rows to the application.
• Comparing a column to a value of a different type. Symptom: an indexed column shows a scan because the engine converts every stored value before comparing; cast the parameter instead of the column.

How this shows up in projects and interviews

When a ticket says the report is slow, the first useful comment on it is a plan and the row counts. Interviewers asking how you would debug a slow query want that routine, and they want you to say what you would look for in a plan: scans on big tables, sorts you did not expect, estimates far from actuals. Expect a follow-up on why a plan may differ between your laptop and production, with the answer being data volume, statistics and configuration. If you can explain plans A and B above in your own words, you can handle most of those questions.