Performance Diagnostics

Sort Work

Notice Temporary Ordering

A query plan can reveal when SQLite must sort rows for an ORDER BY that is not already provided by an index.

Program

Play the script to choose an event kind and inspect the plan before reading the date-ordered rows.

wanted_kind
sort_temp_plan.sql
Replay: real traced execution (multi-file project)
CREATE TABLE plan_choice AS WITH params(wanted_kind) AS (VALUES ('deploy')) SELECT wanted_kind FROM params;
CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);
INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');
CREATE INDEX idx_events_kind ON events(kind);
EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;

CREATE TABLE plan_choice AS WITH params(wanted_kind) AS (VALUES ('build')) SELECT wanted_kind FROM params;
CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);
INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');
CREATE INDEX idx_events_kind ON events(kind);
EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_kind) AS (VALUES ('deploy')) SELECT wanted_kind FROM params;2CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);
    values this step1 rowchoice

  2. events ← 4 rows

    2CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);3INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');4CREATE INDEX idx_events_kind ON events(kind);
    values this step4 rowsevents

  3. indexes ← 1 row

    3INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');4CREATE INDEX idx_events_kind ON events(kind);5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step1 rowindexes

  4. plan ← 4 rows

    4CREATE INDEX idx_events_kind ON events(kind);5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;6SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step4 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;6SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_kind) AS (VALUES ('build')) SELECT wanted_kind FROM params;2CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);
    values this step1 rowchoice

  2. events ← 4 rows

    2CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);3INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');4CREATE INDEX idx_events_kind ON events(kind);
    values this step4 rowsevents

  3. indexes ← 1 row

    3INSERT INTO events VALUES (1, '2026-05-03', 'deploy'), (2, '2026-05-01', 'build'), (3, '2026-05-02', 'deploy'), (4, '2026-05-04', 'build');4CREATE INDEX idx_events_kind ON events(kind);5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step1 rowindexes

  4. plan ← 4 rows

    4CREATE INDEX idx_events_kind ON events(kind);5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;6SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step4 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;6SELECT id, event_day FROM events WHERE kind = (SELECT wanted_kind FROM plan_choice) ORDER BY event_day;
    values this step2 rowsresult
ORDER BY `ORDER BY event_day` asks for a stable presentation order.
temporary sort When an index does not already provide that order, the plan can show extra sorting work.
planner detail The diagnostic rows help identify whether an index supports filtering, ordering, or both.