runtime

query plans explain query plan · 12 comparisons · real output

Function filter versus a date range

Same January result, different access paths.

how it works

An index stores watched_at values in sorted order, like a phone book. SQLite can jump into that order only when the WHERE clause compares the bare column to a value. strftime('%Y-%m', watched_at) produces a new value for every row, and that value is not what the index stores, so SQLite has to compute it for every entry and check each one. Rewriting the filter as watched_at >= '2025-01-01' AND watched_at < '2025-02-01' asks for a slice of the sorted order, which the index can find directly.

what the plans show

What to notice: Wrapping an indexed column in a function hides it from the index, so SQLite reads every entry. A half-open range on the bare column lets it jump straight to January.

On this small sample both versions finish instantly, so the plan is the evidence, not the timing. With millions of rows these differences decide how fast a query runs.

How to read a query plan

SCAN reads every row (or every index entry). SEARCH jumps to just the matching rows through an index. COVERING INDEX means the index alone answers the query. USE TEMP B-TREE is an extra step where SQLite sorts or groups rows itself. An index does not make every query faster; it helps when it lets SQLite skip work.

a.sql · Function on the indexed column
setup›
CREATE INDEX idx_time ON watch_events(watched_at);
sql›
SELECT id,watched_at
FROM watch_events
WHERE strftime('%Y-%m',watched_at)='2025-01';

waiting for the comparison

b.sql · Half-open range
setup›
CREATE INDEX idx_time ON watch_events(watched_at);
sql›
SELECT id,watched_at
FROM watch_events
WHERE watched_at>='2025-01-01' AND watched_at<'2025-02-01';

waiting for the comparison