Query execution plans

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.

Four SQLite plan records to know

A scan is not always a mistake. Reading a small table or most of a table can be cheaper than using an index. The plan gives you evidence to compare with the size and shape of the data.

SCAN means SQLite reads a table or index from beginning to end.
SEARCH means SQLite uses an index to find a smaller set of rows.
USING COVERING INDEX means the index contains every column this query needs.
USE TEMP B-TREE means SQLite builds a temporary structure for sorting, grouping, or distinct work.

From scan to search

Schema

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

Sample data

INSERT INTO products (id, category, price_cents) VALUES
  (1, 'books', 1200),
  (2, 'games', 3500),
  (3, 'books', 1800);

Before query

SELECT id, price_cents
FROM products
WHERE category = 'books';

Without a useful index:

Before plan

SCAN products

Add an index on the filter column:

Change

CREATE INDEX idx_products_category ON products(category);

After query

SELECT id, price_cents
FROM products
WHERE category = 'books';

The new plan can be:

After plan

SEARCH products USING INDEX idx_products_category (category=?)

The result does not change. The access method does. SQLite can go to the matching category values instead of checking every product row.

Read plans with the query beside you

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 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 plan text does not report the same cost fields or node names as PostgreSQL or MySQL. A SEARCH record also does not prove the query is fast. Data size, selectivity, caching, and repeated work still matter.