Performance Diagnostics

Scan vs Search

Read a Query Plan

EXPLAIN QUERY PLAN describes how SQLite intends to read rows. An indexed predicate can search matching rows instead of scanning the whole table.

Program

Play the script to choose a ticket status, inspect the planner output, and compare it with the returned rows.

scan_vs_search_plan.sql 
CREATE TABLE plan_choice AS WITH params(wanted_status) AS (VALUES ()) SELECT wanted_status FROM params;
CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');
CREATE INDEX idx_tickets_status ON tickets(status);
EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;

CREATE TABLE plan_choice AS WITH params(wanted_status) AS (VALUES ()) SELECT wanted_status FROM params;
CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');
CREATE INDEX idx_tickets_status ON tickets(status);
EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
EXPLAIN QUERY PLAN The diagnostic query returns planner rows instead of application data.
index search The status index gives the planner a way to search by status.
diagnostic first The final `SELECT` uses the same parameter table as the plan, so the replay compares plan shape with result rows.