lesson 68 Performance & query plans · advanced

SARGable ranges, not speed commandments

Compare range filters and functions on indexed columns.

the idea

A direct range on an indexed column can permit an efficient lookup. Wrapping that column in a function can prevent a normal index lookup; expression indexes are a separate option. Compare real plans and preserve the result. OR, correlated subqueries, and joins are not universally slow.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

watch_events5 columns · 26 rows

idinteger · key	viewer_idinteger	video_idinteger	watched_attext	watch_secondsinteger
1	1	1	2025-01-01 10:00:00	300
2	1	2	2025-01-01 11:00:00	450
3	2	1	2025-01-02 12:00:00	600
4	3	3	2025-01-02 13:00:00	120
5	1	4	2025-01-02 14:00:00	700

showing 5 of 26 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

CREATE INDEX idx_watch_time ON watch_events(watched_at);
SELECT id,watched_at
FROM watch_events
WHERE watched_at>='2025-01-01' AND watched_at<'2025-02-01';

Watch for: SARGability makes an index strategy possible; statistics, selectivity, and workload determine whether it wins.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Filter the timestamp column directly. watched_at is the column an index can search. Wrapping it in a function is a different question.
step-1.sql
sql›
```
SELECT id, watched_at
FROM watch_events
ORDER BY watched_at
LIMIT 8;
```

Execute a complete script. All setup and the final result run together in one disposable database.

step-2.sql

CREATE INDEX idx_watch_time ON watch_events(watched_at);
SELECT id,watched_at
FROM watch_events
WHERE watched_at>='2025-01-01' AND watched_at<'2025-02-01';