SQLite / Intermediate: query structure and transactional design / Lesson 10

Query plans, statistics, and measured evidence

Interpret SQLite EXPLAIN QUERY PLAN without treating its text as a stable API, and distinguish useful access paths from unsupported performance claims.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Read SEARCH, SCAN, and temporary sorting in context.
Separate a plan from runtime measurement.
Review statistics and version assumptions.

Ask which work the chosen strategy performs

EXPLAIN QUERY PLAN describes a high-level access strategy, including table/index access, join nesting, and some temporary structures. It is not an actual execution profile with universal elapsed-time or row-count metrics. Full EXPLAIN exposes virtual-machine instructions, another lower-level diagnostic representation. Do not copy another database's EXPLAIN ANALYZE assumptions into SQLite without checking support.

The official documentation warns that EQP output format can change across releases. The exact rows shown here were observed on3.45.1 and are learning evidence, not an application protocol to parse forever. Node identifiers and description wording are not business data. A tool can display them for a human, while a product should depend on query results and documented APIs.

Read a selective primary-key search

The first script looks up one INTEGER PRIMARY KEY. Its tested plan reports a search through that rowid identity and then returns alpha. The plan explains an access path, while the SELECT confirms semantic correctness. A search can still be expensive if its range is broad or many follow-up operations are required; the label alone is not a stopwatch.

Inspect the predicate, expected cardinality, projected columns, and remaining sort or filter work. A covering index can avoid table lookups, but adding many columns carries write and size costs. For nested joins, identify which source is accessed for each outer row. Repeated small lookups can dominate a workload even when every individual access appears indexed.

sql A3.45.1 primary-key plan and its result

CREATE TABLE note(id INTEGER PRIMARY KEY,body TEXT);
INSERT INTO note VALUES(1,'alpha'),(2,'beta');
EXPLAIN QUERY PLAN SELECT body FROM note WHERE id=1;
SELECT body FROM note WHERE id=1;

Expected output

id|parent|notused|detail
2|0|0|SEARCH note USING INTEGER PRIMARY KEY (rowid=?)

body
alpha

The observed plan searches the integer primary key; the result is alpha. Exact EQP wording is version-specific.

Notice when ordering needs temporary work

The second script first requests body ordering without a matching index. The observed3.45.1 plan scans the table and uses a temporary B-tree for ordering. After a body index is created, the observed plan can scan that covering structure instead. The final SELECT still declares its own order. An index never removes the need to specify the output contract.

This is a structural comparison over two rows, not a measured production speedup. A temporary sort is not inherently a defect; it may be appropriate for a small result or an uncommon report. Evaluate memory, I/O, result size, and write cost together. The useful question is whether the complete workload benefits without changing results or imposing an unacceptable mutation cost.

sql Compare observed sorting access before and after an index

CREATE TABLE note(id INTEGER PRIMARY KEY,body TEXT);
INSERT INTO note VALUES(1,'alpha'),(2,'beta');
EXPLAIN QUERY PLAN SELECT id,body FROM note ORDER BY body,id;
CREATE INDEX note_body ON note(body);
EXPLAIN QUERY PLAN SELECT id,body FROM note ORDER BY body,id;
SELECT id,body FROM note ORDER BY body,id;

Expected output

id|parent|notused|detail
3|0|0|SCAN note
10|0|0|USE TEMP B-TREE FOR ORDER BY

id|parent|notused|detail
3|0|0|SCAN note USING COVERING INDEX note_body

id|body
1|alpha
2|beta

On the tested runtime the temporary sort is replaced by covering-index traversal. The result remains the same ordered two rows.

Treat statistics and parameters as plan context

The planner uses schema information, constraints, available indexes, and statistics where present. ANALYZE gathers planner statistics; PRAGMA optimize and recommended maintenance behavior are release-dependent. Read the documentation for the deployed version instead of assuming every current recommendation describes the3.45.1 validation environment. Statistics maintenance itself is work that should have an owner and a purpose.

Compare representative parameter classes and distributions. A plan suitable for one rare customer can be inappropriate for a common value, and a tiny uniform training table cannot reproduce that skew. Reprepare and plan behavior can depend on the API and statement form. Keep the actual SQL, bound-value types, schema, and runtime recorded alongside any claimed improvement.

Measure comparable work without resetting shared systems

Benchmark only isolated synthetic or approved representative data, with identical results and controlled conditions. Record warm/cold state, query count, rows returned, host overhead, and resource limits. SQLite's C status interfaces and shell diagnostics can expose useful counters, but a Python trace callback merely showing SQL text is not an execution profile. Diagnostic text can contain sensitive values and needs appropriate access control.

Make one evidence-backed change and verify both the target query and affected writes. Avoid universal prescriptions such as forcing every index, vacuuming after every change, or clearing a shared cache for a test. A useful tuning note explains the hypothesis, observed strategy, measured workload effect, correctness checks, and limits of the conclusion, including any dependence on a specific SQLite release.

Common errors and fixes

A parser broke after a SQLite upgrade.

Why it happens: EQP diagnostic text was treated as a stable application API.
How to fix it: Use documented result/API contracts and keep plan text for version-aware diagnostics.
Prevent it next time: Label observed plans by runtime version.

A SEARCH was assumed faster than every SCAN.

Why it happens: Operator labels replaced workload measurement.
How to fix it: Inspect qualifying work and measure the complete query.
Prevent it next time: Compare results, cardinality, and resource cost.

A tiny benchmark justified a global index change.

Why it happens: Unrepresentative data hid distribution and write costs.
How to fix it: Test representative classes and affected mutations in isolation.
Prevent it next time: Record workload and rollback criteria.

Check your understanding

1. What can a temporary B-tree for ORDER BY tell you, and what can it not prove?

Show worked solution

It shows the selected strategy needs temporary ordering work for that query. It does not prove the query is slow, the sort is avoidable under all conditions, or an added index benefits the whole workload. Measure size, frequency, and mutation tradeoffs before acting.

2. Why retain the actual SQLite version with a saved EQP example?