EXPLAIN QUERY PLAN in SQLite

EXPLAIN QUERY PLAN (often shortened to EQP) tells you how SQLite intends to execute a query: which tables it reads, whether it uses an index, and whether it has to build a temporary structure to sort or group rows. It is the first tool to reach for when a query is slower than expected.

It is distinct from plain EXPLAIN, which dumps the low-level bytecode program the virtual machine runs. EXPLAIN QUERY PLAN is the high-level summary, and it is the one you want for day-to-day performance work.

One caveat straight from the SQLite docs: the EQP output format is meant for interactive debugging and can change between releases. It changed substantially in version 3.24.0 (2018-06-04) and again slightly in 3.36.0 (2021-06-18). Read it to understand behavior; do not parse it programmatically.

Running it

Prefix any read statement with EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;

In the command-line shell you can also flip on automatic mode so every statement prints its plan first:

sqlite> .eqp on

The shell renders the plan as an indented tree. To see the raw four-column table instead, run .explain off first.

SCAN vs SEARCH: the core distinction

For every table a query reads, the plan shows a line beginning with either SCAN or SEARCH. This single word usually tells you whether the query is efficient.

SCAN means SQLite reads every row of the table (or every entry of an index) to satisfy the query. There is no shortcut; it visits all of them.

QUERY PLAN
`--SCAN orders

A full-table SCAN is fine on a 200-row lookup table. On a million-row table filtered by a selective condition, it is the thing you usually want to eliminate.

SEARCH means SQLite uses an index (or the rowid) to jump straight to the rows it needs, reading only a contiguous subset:

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

The (customer_id=?) part shows which index columns were used as constraints. SEARCH against a selective index is the goal for most filtered queries.

A note on older SQLite: before 3.24.0 the output read SCAN TABLE orders and SEARCH TABLE orders. The word TABLE was dropped in 3.24.0, so newer versions just say SCAN orders. Both mean the same thing.

USING INDEX vs USING COVERING INDEX

When you see SEARCH ... USING INDEX, SQLite found the matching rows through the index but still had to fetch the remaining columns from the table itself. That is two lookups per row: one in the index B-tree, one in the table B-tree.

USING COVERING INDEX is better. It means the index contains every column the query needs, so SQLite never touches the table at all:

QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_cust_total (customer_id=?)

You get a covering index when the index columns (plus the rowid, which is always present) include every column referenced in the SELECT, WHERE, and ORDER BY. For example, an index on (customer_id, total) covers SELECT total FROM orders WHERE customer_id = ? because both columns it needs live in the index. Add SELECT total, status and the index no longer covers it, so the plan drops back to plain USING INDEX.

Covering indexes trade disk space for read speed. They are worth it for hot read paths.

USE TEMP B-TREE: the hidden sort cost

If a query has ORDER BY or GROUP BY on columns that are not already in index order, SQLite builds a temporary B-tree to sort the results. The plan flags it:

QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

This is not always a problem, but on large result sets it costs memory and time. If you see USE TEMP B-TREE FOR ORDER BY on a query that runs often, an index whose column order matches the ORDER BY can let SQLite read rows already sorted and skip the temp B-tree entirely. The same applies to USE TEMP B-TREE FOR GROUP BY.

Subqueries and joins

Joins produce one SCAN or SEARCH line per table, nested to show the join order. The outer (driving) table appears first; the inner table's line shows how SQLite looks up matching rows for each outer row:

QUERY PLAN
|--SCAN customers
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

That is a healthy join plan: scan the smaller customers table once, and for each customer use the index to find their orders. The danger sign is a nested SCAN on the inner table, which means SQLite re-reads the whole inner table for every outer row.

Correlated subqueries and materialized subqueries show up as their own subtrees, sometimes labelled MATERIALIZE or CO-ROUTINE, indented under the statement that uses them.

A worked example: turning SCAN into SEARCH

Start with an unindexed table:

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  created_at TEXT,
  payload TEXT
);
 
EXPLAIN QUERY PLAN
SELECT id, created_at FROM events WHERE user_id = 99;

QUERY PLAN
`--SCAN events

Full scan. Add an index on the filter column:

CREATE INDEX idx_events_user ON events(user_id);

Now the plan becomes:

QUERY PLAN
`--SEARCH events USING INDEX idx_events_user (user_id=?)

SEARCH instead of SCAN. SQLite still visits the table to read created_at. To eliminate that second lookup, index both columns the query reads:

CREATE INDEX idx_events_user_created ON events(user_id, created_at);

QUERY PLAN
`--SEARCH events USING COVERING INDEX idx_events_user_created (user_id=?)

Covering index. The query is now answered entirely from the index B-tree.

Common mistakes

• Reading EXPLAIN instead of EXPLAIN QUERY PLAN. Plain EXPLAIN shows VM bytecode, which is rarely what you want for tuning. Add the QUERY PLAN keywords.
• Assuming an index exists is enough. SQLite's planner ignores an index if it estimates a scan is cheaper, or if the WHERE clause wraps the indexed column in a function (WHERE lower(email) = ? cannot use a plain index on email; you need an expression index). Always confirm with EQP rather than assuming.
• Ignoring USE TEMP B-TREE. A query can SEARCH efficiently and still be slow because it sorts the whole result set in a temp B-tree. Match index column order to your ORDER BY to avoid it.
• Chasing covering indexes everywhere. Each index slows down writes and consumes space. Reserve covering indexes for read-heavy queries that run often, not one-off reports.
• Parsing EQP output in code. The format is explicitly documented as subject to change between releases. Use it interactively, not as a stable API.

For building the indexes that turn a SCAN into a SEARCH, see the SQLite indexes guide. For the queries you are most likely to be profiling, the joins guide covers join order, which EQP exposes directly.

When you are iterating on a query and want to see the plan change as you edit the WHERE clause, Mako's AI autocomplete can suggest the index that would convert a SCAN into a SEARCH against your live schema, so you can test it before committing to the index.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.