Performance Diagnostics

Compound Indexes

Match the Left Prefix

A compound index can support filters and ordering when the query uses its leading columns in the same shape.

Program

Play the script to choose a log day and see how the (day, level) index affects the plan and output order.

wanted_day
compound_index_plan.sql
Replay: real traced execution (multi-file project)
CREATE TABLE plan_choice AS WITH params(wanted_day) AS (VALUES ('2026-05-02')) SELECT wanted_day FROM params;
CREATE TABLE logs (day TEXT, level TEXT, message TEXT);
INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');
CREATE INDEX idx_logs_day_level ON logs(day, level);
EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;

CREATE TABLE plan_choice AS WITH params(wanted_day) AS (VALUES ('2026-05-01')) SELECT wanted_day FROM params;
CREATE TABLE logs (day TEXT, level TEXT, message TEXT);
INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');
CREATE INDEX idx_logs_day_level ON logs(day, level);
EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_day) AS (VALUES ('2026-05-02')) SELECT wanted_day FROM params;2CREATE TABLE logs (day TEXT, level TEXT, message TEXT);
    values this step1 rowchoice

  2. logs ← 4 rows

    2CREATE TABLE logs (day TEXT, level TEXT, message TEXT);3INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');4CREATE INDEX idx_logs_day_level ON logs(day, level);
    values this step4 rowslogs

  3. indexes ← 1 row

    3INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');4CREATE INDEX idx_logs_day_level ON logs(day, level);5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step1 rowindexes

  4. plan ← 3 rows

    4CREATE INDEX idx_logs_day_level ON logs(day, level);5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;6SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step3 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;6SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE plan_choice AS WITH params(wanted_day) AS (VALUES ('2026-05-01')) SELECT wanted_day FROM params;2CREATE TABLE logs (day TEXT, level TEXT, message TEXT);
    values this step1 rowchoice

  2. logs ← 4 rows

    2CREATE TABLE logs (day TEXT, level TEXT, message TEXT);3INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');4CREATE INDEX idx_logs_day_level ON logs(day, level);
    values this step4 rowslogs

  3. indexes ← 1 row

    3INSERT INTO logs VALUES ('2026-05-01', 'info', 'start'), ('2026-05-01', 'warn', 'retry'), ('2026-05-02', 'error', 'fail'), ('2026-05-02', 'info', 'recover');4CREATE INDEX idx_logs_day_level ON logs(day, level);5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step1 rowindexes

  4. plan ← 3 rows

    4CREATE INDEX idx_logs_day_level ON logs(day, level);5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;6SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step3 rowsplan

  5. result ← 2 rows

    5EXPLAIN QUERY PLAN SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;6SELECT level, message FROM logs WHERE day = (SELECT wanted_day FROM plan_choice) ORDER BY level;
    values this step2 rowsresult
compound index `idx_logs_day_level` stores rows ordered by day first, then level.
left prefix A filter on `day` uses the leading index column.
ordered lookup Within one day, the same index can also provide rows ordered by `level`.