EXPLAIN and query plans: read what the database actually does

Decode the plan that the SQLite planner really chooses for your queries with EXPLAIN QUERY PLAN.

10 min · 3 questions

SQL is declarative: you describe the result, not how to obtain it. It's the query planner that decides how to read the data. To watch it work, SQLite offers two commands: EXPLAIN QUERY PLAN gives a readable high-level plan, while EXPLAIN alone dumps the bytecode of the VDBE virtual machine. Install the sqlite3 command-line tool, or open sqliteonline.com in your browser, and replay every example that follows.

SQL

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);

The dataset to create before everything else.

Put EXPLAIN QUERY PLAN in front of any SELECT query. Each line of the result describes how a table is read. Two keywords dominate: SCAN means the table is scanned entirely, row by row, whereas SEARCH indicates a targeted access guided by an index. Without a suitable index, even a simple equality already triggers a full scan.

SQL

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

Without an index, the planner scans the entire table.

Create an index on the filtered column, then rerun the exact same query: the plan switches from SCAN to SEARCH ... USING INDEX. Other mentions are valuable too: USE TEMP B-TREE reveals a sort or grouping materialized in memory (often a sign that an ordering index is missing), and USING COVERING INDEX signals that the table wasn't even opened.

SQL

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

The same SELECT, but now guided by the index.

The planner relies on statistics. Run ANALYZE: SQLite fills the internal sqlite_stat1 table with the distribution of values, which helps it choose the right index when several are candidates. Without statistics, it applies default heuristics and can get it wrong on large tables.

EXPLAIN QUERY PLAN does not execute the query and measures no duration: it shows the *estimated* plan. That plan can change as the data grows, after an ANALYZE, or if you add an index. Always check the plan on a realistic volume, not on three test rows.

Knowledge check

Make sure you remember the key points of this lesson.

In SQLite, what does EXPLAIN QUERY PLAN return compared to EXPLAIN alone?
- The VDBE bytecode, instruction by instruction
- A high-level plan (SCAN, SEARCH, temporary sort)
- The actual measured execution time of the query
- The list of existing indexes on the table
In a SQLite plan, what does the line SCAN employees mean?
- The table is scanned entirely, row by row
- A covering index is used
- A targeted search is done via an index
- The table is loaded into the memory cache
What is the ANALYZE command used for?
- It rebuilds all the indexes of the database
- It collects statistics into sqlite_stat1 to guide the planner
- It executes the query and measures its duration
- It locks the table during the analysis