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.

range_filter_plan.sql 
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 ()) 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 ()) 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 ()) SELECT id, kind FROM events WHERE event_day >= (SELECT start_day FROM params) ORDER BY event_day, id;
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.