Put a Window in the SQLite Query Planner

TL;DR: SQLite Query Explainer runs SQL in your browser, shows both the high-level query plan and low-level virtual-machine instructions, then annotates the output in plain English. Use it to learn where the work goes. Verify its explanations against SQLite’s own documentation before pouring concrete around them.

Most developers meet a query plan after the room has started shaking.

I framed a room in '46 and I have not trusted an unsquared wall since, so take it plainly when I say most people are guessing here.

Four seconds is not a measurement. It is a complaint. You do not yet know where the four seconds went, and until you do, every fix you try is a man moving furniture around on a floor that is out of level.

The page takes four seconds. A table contains 80,000 rows, which sounds small until a correlated subquery walks them once per customer and the CPU begins making that dry little noise a mouse makes inside a wall.

Simon Willison built SQLite Query Explainer after Julia Evans wrote that maybe she would learn to read a query plan one day. The tool lowers the inspection window to eye level. Paste a query, run it against SQLite in the browser, and see what the planner and virtual machine intend to do.

The tool shows the plan and the bytecode, annotated

The tool shows EXPLAIN QUERY PLAN output and the lower-level EXPLAIN bytecode, with a plain-language annotation for each line.

The two views answer different construction questions. The high-level plan tells you which table SQLite scans, which index it searches, and how nested loops fit together. The bytecode shows the virtual-machine instructions carrying out the statement.

Official SQLite documentation makes the split explicit. EXPLAIN QUERY PLAN returns a broad strategy. EXPLAIN returns the sequence of virtual-machine operations that the statement would execute.

Willison’s tool runs Python in Pyodide through WebAssembly, so the database work stays in the browser. That makes experimentation easy and keeps the learning surface self-contained. Create a table, add sample data, run the bad query, add an index, then watch the framing change.

You can see the joist before the ceiling covers it.

How do you read a basic SQLite query plan?

Start with SCAN, SEARCH, index names, and any temporary B-tree the plan creates for sorting or grouping.

A SCAN record means SQLite visits all rows in the table or index represented by that step. SEARCH means it visits a subset, often through an index tied to one or more WHERE terms. A covering index means SQLite can answer the query from the index without returning to the table for additional columns.

Read the plan as a tree of nested work. In a join, the first search can become the outer loop and the next operation the inner loop. A cheap inner operation repeated 100,000 times can punch a hole through the floor.

USE TEMP B-TREE FOR ORDER BY tells you SQLite needs a separate temporary structure to sort the result. An appropriate index may remove that work. It may also make writes more expensive and add storage.

Measure the actual room. Do not order lumber from one line in a plan.

Correlated subqueries deserve a long look because they can run once for every row produced by the outer query. Materialized subqueries, compound queries, and multiple indexes carry other tradeoffs. The visual tree gives you a place to begin, then timings and representative data tell you whether the place is on fire.

The annotations sit on output SQLite never promised to keep stable

The annotations can be incomplete or mistaken because they add an interpretive layer over output that SQLite itself says may change between releases.

Willison says to approach the tool with caution because he does not know enough about query plans to verify every explanation. That warning belongs on the front wall, full contrast, where nobody can claim the paint hid it.

An explanation tool is a flashlight, not a building inspector. Confirm surprising claims in the official EXPLAIN QUERY PLAN guide.

Check the SQLite version. Run the query with data shaped like production, because ten polite sample rows will not reveal the load-bearing failure.

SQLite also warns applications against depending on the output format of either EXPLAIN form. The output exists for interactive analysis and troubleshooting. A production parser built around today’s wording can split when a future release moves the plumbing.

The tool’s safest use is education. Every dashboard on this network once read green while the homepage quietly served a 404 for six hours to the entire world, because the dashboard was answering a question nobody asked it. Confident output and correct output are not the same claim.

When should you inspect a query plan?

Inspect a plan when a query slows down, after schema or index changes, before shipping a data-heavy feature, and whenever row counts will grow far beyond the development fixture.

Plans help explain why the database made a choice. Timings show whether that choice hurts. Use both.

A full scan of twelve rows is fine. A clever index on a table rewritten every second can cost more than it saves.

Measure the query under the load it has to carry. Keep the SQL readable.

Add the narrowest index that answers a demonstrated problem. Re-run the plan and the timing.

Then write down why the index exists so the next foreman does not remove it as decorative trim.

The wall stops shaking. The mouse noise goes away. Under the new paint, one small index holds the beam where the query used to sag.

Frequently asked questions

What is the difference between EXPLAIN and EXPLAIN QUERY PLAN in SQLite?

EXPLAIN QUERY PLAN gives a high-level strategy for executing a statement. EXPLAIN returns the lower-level SQLite virtual-machine instructions.

Does a full table scan always mean a query is bad?

No. Scanning a small table can be faster and simpler than using an index. Cost depends on row count, selectivity, data shape, and how often the query runs.

Can an application parse EXPLAIN QUERY PLAN output?

SQLite advises against it. The output format is intended for interactive debugging and may change between releases.