Explaining a query plan¶

Before SQLite runs a query it decides how to run it: which table to read first, whether to walk it end to end or jump straight to the rows it needs through an index, and whether the result has to be sorted afterwards. That decision is the query plan, and it is usually the answer to "why is this query slow".

To see the plan for a query:

Go to the SQL tab and put the cursor in the statement you want to look at.
Press Ctrl+L, or choose SQL → Explain Query Plan, or right-click in the editor and pick it there.

The Plan tab opens below the editor. The Results tab keeps whatever rows you last fetched, so you can move between the two without running the query again.

Nothing is executed. SQLite works the plan out while it is preparing the statement, so explaining a query reads no data, changes nothing, and is safe on a read-only database or inside an open transaction.

What gets explained¶

The same text Execute would run: the selection if you have one, otherwise the statement the cursor is in. A selection holding several statements is explained statement by statement, each under its own heading.

The line at the top of the plan says which statement it came from. Keep that in mind after editing: the plan stays on screen until you explain again, and the heading is what tells you whether it still matches the SQL in front of you.

Reading the plan¶

Column	What it holds
Operation	SQLite's own description of the step, word for word.
Object	The table, index or subquery the step reads.
Notes	A short summary added by SQLite Expert — full scan, covering index, temporary index.

Indentation is nesting: a step drawn under another one runs inside it, once for every row the outer step produces. That is why an inner full scan matters so much more than an outer one.

The wording in Operation comes straight from the engine and is never rewritten. Anything SQLite Expert has to say about a step is in the Notes column, so the two are never confused.

Findings¶

Below the plan, SQLite Expert lists what stands out:

A temporary index. SQLite decided the query needed an index that does not exist, built one for the duration of the query, and threw it away afterwards — and it will do that again every single time the query runs. This is the engine telling you an index is missing, and it names the columns, so Create Index... can offer you the exact CREATE INDEX for it. The statement opens in a new SQL tab for you to read; nothing is created until you run it.
A sort in a temporary b-tree. The rows come out in the wrong order for your ORDER BY, GROUP BY or DISTINCT, so they are collected and sorted first. An index on those columns lets SQLite read them in order and skip the step.
A correlated subquery. A subquery that has to be re-run for every row of the outer query.
A full scan. Every row of the table is read. This is listed for information rather than as a problem: for a small table, or a query with nothing to filter on, reading everything is the fastest plan.

Copy puts the whole plan on the clipboard as indented text, which is the form to paste into a bug report or a forum post.

Things worth knowing¶

The plan is what SQLite intends to do, worked out from the schema and from any statistics ANALYZE has gathered — not a measurement of what happened.
A statement that will not compile shows SQLite's error instead of a plan. That is normal while you are still editing.
Simple statements have no plan at all. INSERT INTO t VALUES (1) reads and searches nothing, so there is nothing to draw.