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.

compound_index_plan.sql 
CREATE TABLE plan_choice AS WITH params(wanted_day) AS (VALUES ()) 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 ()) 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;
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`.