Query Tuning Patterns

Range Filter

Keep Predicates Searchable

Range predicates on indexed columns keep the search condition visible to the query planner.

Program

Play the script to choose a starting day and inspect the plan for an indexed range filter.

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

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

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

  1. tables ← 1 row

    1CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');
    values this step1 rowtables

  2. events ← 4 rows

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

  3. indexes ← 1 row

    2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-02')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-02')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');
    values this step1 rowtables

  2. events ← 4 rows

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

  3. indexes ← 1 row

    2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-01')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-01')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE events (id INTEGER, event_day TEXT, kind TEXT);2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');
    values this step1 rowtables

  2. events ← 4 rows

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

  3. indexes ← 1 row

    2INSERT INTO events VALUES (1, '2026-01-01', 'build'), (2, '2026-01-02', 'test'), (3, '2026-01-03', 'deploy'), (4, '2026-01-04', 'audit');3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-03')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_events_day_kind ON events(event_day, kind);4EXPLAIN QUERY PLAN WITH params(start_day) AS (VALUES ('2026-01-03')) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
    values this step6 rowsresult
range predicate `event_day >= start_day` keeps the indexed column visible in the predicate.
sargable A searchable predicate lets the planner use the indexed ordering.
bounded replay The fixed event rows keep the plan and output deterministic.