Reading Query Plans and Tuning

You will be able to read a whole EXPLAIN QUERY PLAN tree, name the lines SQLite 3.27 prints, and run the loop that turns a bad plan into a good one.

In SQL Advanced Indexes you learned why an index is or is not usable. This lesson is how to read the plan that tells you, and what to change when it is bad.

Reading the tree

Lines at the same depth are nested loops: the first is the outer loop (the driver), and each later sibling runs once per row above it. Examples use branches(id, label, city), members(id, name, branch_id), loans(id, branch_id, title) and notes(id, loan_id, body), indexed on branches(city) and members(branch_id).

SELECT m.name, b.label FROM branches b JOIN members m ON m.branch_id = b.id WHERE b.city = 'Cork';
|--SEARCH TABLE branches AS b USING INDEX idx_branches_city (city=?)
`--SEARCH TABLE members AS m USING INDEX idx_members_branch (branch_id=?)

branches drives because its filter is selective; your FROM order does not decide. A first-line SCAN is one pass, often fine; a SCAN as a later sibling is a full pass per outer row. Drop the members.branch_id index and the planner flips the join, making members the driver and branches a primary-key SEARCH, which hides the missing index without supplying it. When no flip helps, SQLite builds a throwaway index. Joining loans l to members m ON m.branch_id = l.branch_id without that index prints:

|--SCAN TABLE loans AS l
`--SEARCH TABLE members AS m USING AUTOMATIC COVERING INDEX (branch_id=?)

AUTOMATIC means the index is rebuilt on every run; create a real one.

The other nodes

Plans carry no row counts or timings, so judge by shape.

The tuning loop

Read the plan, change one thing, read the plan again. A report of notes per loan, with no index on notes.loan_id:

SELECT l.title, (SELECT COUNT(*) FROM notes n WHERE n.loan_id = l.id) FROM loans l;
|--SCAN TABLE loans AS l
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN TABLE notes AS n

Every loans row triggers a full pass over notes. Change one thing, an index on notes(loan_id), and the inner line becomes SEARCH TABLE notes AS n USING COVERING INDEX idx_notes_loan (loan_id=?), still one probe per loan. A second change is a rewrite to a pre-aggregated derived table, which does its GROUP BY once:

... FROM loans l JOIN (SELECT loan_id, COUNT(*) AS n FROM notes GROUP BY loan_id) t ON t.loan_id = l.id;
|--MATERIALIZE 1
|  `--SCAN TABLE notes USING COVERING INDEX idx_notes_loan
|--SCAN SUBQUERY 1 AS t
`--SEARCH TABLE loans AS l USING INTEGER PRIMARY KEY (rowid=?)

notes is read once. A rewrite can also trade one problem for another. If loans without notes must stay in the report, the query needs LEFT JOIN and COALESCE(t.n, 0), the join can no longer flip, and the plan ends:

|--SCAN TABLE loans AS l
`--SEARCH SUBQUERY 1 AS t USING AUTOMATIC COVERING INDEX (loan_id=?)

The throwaway index is built over the materialized result. You cannot index a derived table, so no CREATE INDEX removes that line; only a different shape of query does.

OFFSET paging has a plan symptom too: ORDER BY id LIMIT 10 OFFSET 5000 prints a bare SCAN, hiding 5,000 discarded rows, while keyset paging, WHERE id > 5000 ORDER BY id LIMIT 10, prints SEARCH ... (rowid>?). Functions around columns and SELECT * defeating COVERING show up as SCAN or plain USING INDEX; Indexes has the cures.

PostgreSQL output

PostgreSQL's EXPLAIN (ANALYZE) adds estimates and timings. This editor does not run it, so read it as printed output:

Hash Join  (cost=312.00..4890.55 rows=120) (actual time=3.1..71.4 rows=48210 loops=1)
  ->  Seq Scan on loans l
  ->  Hash

Seq Scan, Index Scan and Index Only Scan correspond to SCAN, SEARCH and COVERING. cost=a..b is a guess in arbitrary units; actual time is milliseconds; loops is how often the node ran. Estimated rows=120 against actual rows=48210 means stale statistics: run ANALYZE. Hash Join probes an in-memory table built from the Hash child; Merge Join walks two sorted inputs.

Common mistakes

• Stopping at the first rewrite that removes a bad line: a derived table removes CORRELATED SCALAR SUBQUERY and can add MATERIALIZE plus an AUTOMATIC index.
• Changing two things at once, so you cannot tell which edit improved the plan.
• Accepting a clean plan whose rows changed: compare the result, not only the plan.

Your exercise

The script prints a repeat-buyer report's plan and rows, then the same for a copy you edit. Rewrite the copy until its plan meets the list in the description and its rows stay the same; the most likely mistake is a rewrite that removes one bad line and leaves another.