EXPLAIN QUERY PLAN, EXPLAIN, SCAN/SEARCH, and Planner Diagnostics

Learning outcomes

Index design becomes disciplined when every change starts and ends with planner evidence. SQLite exposes two related SQL diagnostics: EXPLAIN QUERY PLAN gives a high-level access-path tree, while full EXPLAIN exposes the lower-level virtual-machine program. The first belongs in everyday tuning; the second is an advanced microscope. The sqlite3 shell adds conveniences such as .eqp and experimental .expert.

EXPLAIN QUERY PLAN is the everyday tool

Prefix a SELECT—or another statement that reads tables—with EXPLAIN QUERY PLAN. SQLite returns a tree describing its selected strategy. The official documentation explicitly warns that this output is for interactive debugging and its format may change across releases. Therefore, tests should not parse strings like SEARCH maintenance_note USING INDEX... as a stable application API.

EXPLAIN QUERY PLANSELECT note_id, summaryFROM maintenance_noteWHERE device_id=42;

Diagnose a filter: SCAN → SEARCH

Start with no maintenance-note indexes. The device filter scans. Add the measured candidate and inspect again.

DROP INDEX IF EXISTS idx_note_device;DROP INDEX IF EXISTS idx_note_device_time;EXPLAIN QUERY PLANSELECT * FROM maintenance_note WHERE device_id=42;CREATE INDEX idx_note_device ON maintenance_note(device_id);EXPLAIN QUERY PLANSELECT * FROM maintenance_note WHERE device_id=42;

The important conclusion is not that SEARCH is always superior. It is that the planner has switched from visiting the whole table to a key-restricted access path for this selective predicate.

Diagnose a sort: temp B-tree → index order

A query can use an index to find rows yet still allocate a temporary B-tree for ORDER BY if the index does not produce the requested order.

-- Only idx_note_device exists here.EXPLAIN QUERY PLANSELECT occurred_at, summaryFROM maintenance_noteWHERE device_id=42ORDER BY occurred_at DESC;-- Likely SEARCH plus USE TEMP B-TREE FOR ORDER BY.CREATE INDEX idx_note_device_timeON maintenance_note(device_id, occurred_at DESC);EXPLAIN QUERY PLANSELECT occurred_at, summaryFROM maintenance_noteWHERE device_id=42ORDER BY occurred_at DESC;-- The composite index can normally remove the separate ORDER BY sort.

Join plans are nested loops

SQLite implements joins as nested scans/searches. EQP emits one SCAN/SEARCH node for each loop; node order reveals which loop is outer and which is inner. This may differ from the table order you wrote in SQL because the planner is free to reorder compatible joins.

EXPLAIN QUERY PLANSELECT d.device_code, n.occurred_at, n.summaryFROM device AS dJOIN maintenance_note AS n ON n.device_id=d.device_idWHERE d.site_id=7  AND n.status='open';

If device.site_id lacks an index, the outer side may scan devices. If maintenance_note.device_id has a suitable index, the inner loop may SEARCH notes for each matching device. The right improvement depends on measured selectivity and the complete workload.

Automatic indexes are query-time structures, not sqlite_autoindex constraints

When a statement would otherwise repeat expensive lookups and no persistent index exists, SQLite may build an automatic query-time index for that one statement. It is temporary and distinct from names like sqlite_autoindex_device_1, which are persistent internal indexes created by constraints.

DROP TABLE IF EXISTS left_side;DROP TABLE IF EXISTS right_side;CREATE TABLE left_side(k INTEGER, payload TEXT);CREATE TABLE right_side(k INTEGER, payload TEXT);WITH RECURSIVE seq(x) AS (  VALUES(1) UNION ALL SELECT x+1 FROM seq WHERE x<5000)INSERT INTO left_side SELECT x%500, printf('L%05d',x) FROM seq;WITH RECURSIVE seq(x) AS (  VALUES(1) UNION ALL SELECT x+1 FROM seq WHERE x<5000)INSERT INTO right_side SELECT x%500, printf('R%05d',x) FROM seq;EXPLAIN QUERY PLANSELECT count(*)FROM left_side AS lJOIN right_side AS r ON r.k=l.kWHERE l.k=42;

On normal builds with automatic indexing enabled, you may see an AUTOMATIC index on one loop. Treat repeated automatic-index warnings/plans as evidence that a persistent schema index may deserve evaluation—not as a command to add every suggested index.

Temporary B-trees also appear for GROUP BY and DISTINCT

EXPLAIN QUERY PLANSELECT technician, COUNT(*)FROM maintenance_noteGROUP BY technician;EXPLAIN QUERY PLANSELECT DISTINCT technicianFROM maintenance_note;

If no suitable ordering/index exists, EQP may report a temp B-tree for GROUP BY or DISTINCT. Creating INDEX ... (technician) can change that plan, but the write/storage tradeoff still needs justification from workload frequency and measurement.

The shell makes EQP interactive

The sqlite3 CLI can automatically display a plan before each statement.

.eqp onSELECT * FROM maintenance_note WHERE device_id=42;.eqp off.explain auto-- Controls shell formatting for EXPLAIN output.

.eqp and .explain are shell commands, not SQL and not SQLite library APIs. They affect how the CLI investigates statements; they do not change query semantics.

.expert can suggest indexes—but it is experimental

Current official sqlite3 builds document .expert as an experimental index-recommendation feature. It analyzes a query and can propose candidate CREATE INDEX statements, optionally sampling data distribution. Its interface can change or disappear, and a recommendation still requires human review for write cost, storage, redundancy, and the rest of the workload.

.expertSELECT *FROM maintenance_noteWHERE device_id=? AND occurred_at>=?ORDER BY occurred_at;-- Current CLI also documents options such as:.expert --sample 100SELECT * FROM maintenance_note WHERE status=?;

Availability depends on the shell build you are actually running. Check .help expert. Never make production correctness depend on this CLI feature.

Full EXPLAIN: inspect SQLite VM bytecode only when needed

SQLite compiles statements into instructions for its virtual database engine. Prefixing with EXPLAIN returns that instruction sequence—opcodes such as opening cursors, seeking indexes, comparing values, and producing result rows. This is lower-level than most application tuning needs and even less suitable for machine parsing across releases.

EXPLAINSELECT note_id, summaryFROM maintenance_noteWHERE device_id=42ORDER BY occurred_at DESCLIMIT 5;

Use EQP to answer “which access path?” first. Escalate to full EXPLAIN when investigating detailed execution behavior, teaching internals, or diagnosing a planner case that high-level output does not explain.

Evidence-driven tuning sequence

1. Capture the exact SQL and representative bound-value shapes.
2. Record row counts and realistic data distribution.
3. Run EQP and identify SCAN/SEARCH, temp B-trees, and join order.
4. Inspect existing indexes and constraints before adding anything.
5. Create one candidate index in a disposable/staging copy.
6. Run EQP again and verify the intended plan change.
7. Measure query latency/I/O at realistic scale if performance matters.
8. Measure write/storage effects and check for redundant indexes.
9. Use statistics/PRAGMA optimize appropriately before judging final plans.
10. Keep planner output as diagnostic evidence, not a brittle application contract.

Cleanup and checkpoint

DROP TABLE IF EXISTS left_side;DROP TABLE IF EXISTS right_side;DROP INDEX IF EXISTS idx_note_device;DROP INDEX IF EXISTS idx_note_device_time;DROP INDEX IF EXISTS idx_note_technician;PRAGMA index_list('maintenance_note');

Production judgment and bridge

A healthy performance workflow leaves an evidence trail, not a collection of folklore rules. Lesson 5 adds the final missing input to the cost model: statistics. You will see a real plan change after ANALYZE, inspect sqlite_stat1, learn why sqlite_stat4 is optional, and adopt the current PRAGMA optimize lifecycle guidance.

Authoritative references

• SQLite — EXPLAIN QUERY PLAN.
• SQLite — EXPLAIN.
• SQLite — Query Optimizer Overview: automatic indexes.
• SQLite — Command-Line Shell: SQLite Expert.