How to read a query execution plan
A query execution plan describes the work a database chose for a SQL statement. Read it to find which tables are scanned, which indexes are searched, how joins are nested, and whether the database creates temporary work for sorting or grouping.
- 3 minute read
- Examples run in SQLite
On this page
Four SQLite plan records to know
A scan is not always a mistake. Reading a small table, or most of a table, can cost less than using an index. The plan gives you evidence to compare with the size and shape of the data.
SCANmeans SQLite reads a table or an index from beginning to end.SEARCHmeans SQLite uses an index to find a smaller set of rows.USING COVERING INDEXmeans the index holds every column this query needs.USE TEMP B-TREEmeans SQLite builds a temporary structure to sort rows, group them, or remove duplicates.
From scan to search
The query filters products by category, and the table has no index on that column.
Schema and sample SQL
CREATE TABLE products (
id INTEGER PRIMARY KEY,
category TEXT NOT NULL,
price_cents INTEGER NOT NULL
);INSERT INTO products (id, category, price_cents) VALUES
(1, 'books', 1200),
(2, 'games', 3500),
(3, 'books', 1800);SELECT id, price_cents
FROM products
WHERE category = 'books';Without a useful index, SQLite reads every product row.
- SCAN
productsReads rows by scanning a table or index.
CREATE INDEX idx_products_categoryCREATE INDEX idx_products_category ON products(category);With an index on the filter column, SQLite can search that index.
- SEARCH
products USING INDEX idx_Finds the matching rows through an index.products_ category (category=?)
The result stays the same, but the access method changes. SQLite can go to the matching category values instead of checking every product row.
Key points
- Match every plan record to a table or subquery in the SQL.
- Check whether each filter appears in the chosen index search.
- For joins, read the records in nesting order and ask how often each inner lookup runs.
- Look for a temporary B-tree when the query sorts, groups, or removes duplicates.
- Compare the plan with the table size and the share of rows the filter returns.
- Run the query again after every change, and confirm the result first.
Plans are estimates and choices
SQLite EXPLAIN QUERY PLAN describes the chosen work, but it does not measure elapsed time. SQLite plan text does not report the same cost fields or node names as PostgreSQL or MySQL. A SEARCH record also does not prove that the query is fast. Data size, the share of rows a filter matches, caching, and repeated work still matter.
Practice this in Module 2: How queries run
Ten exercises on SCAN and SEARCH records.
See Module 2