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.

wanted_status
scan_vs_search_plan.sql
Replay: real traced execution (multi-file project)
CREATE TABLE plan_choice AS WITH params(wanted_status) AS (VALUES ('open')) 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 ('closed')) 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;

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_status) AS (VALUES ('open')) SELECT wanted_status FROM params;2CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
    values this step1 rowchoice

  2. tickets ← 4 rows

    2CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);3INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');4CREATE INDEX idx_tickets_status ON tickets(status);
    values this step4 rowstickets

  3. indexes ← 1 row

    3INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');4CREATE INDEX idx_tickets_status ON tickets(status);5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step1 rowindexes

  4. plan ← 4 rows

    4CREATE INDEX idx_tickets_status ON tickets(status);5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;6SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step4 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;6SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_status) AS (VALUES ('closed')) SELECT wanted_status FROM params;2CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
    values this step1 rowchoice

  2. tickets ← 4 rows

    2CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);3INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');4CREATE INDEX idx_tickets_status ON tickets(status);
    values this step4 rowstickets

  3. indexes ← 1 row

    3INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Noor');4CREATE INDEX idx_tickets_status ON tickets(status);5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step1 rowindexes

  4. plan ← 4 rows

    4CREATE INDEX idx_tickets_status ON tickets(status);5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;6SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step4 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;6SELECT id, owner FROM tickets WHERE status = (SELECT wanted_status FROM plan_choice) ORDER BY id;
    values this step2 rowsresult
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.