Index a filtered query ​

This example adds an index for a category predicate and checks that the selected rows remain the same. The small dataset illustrates the workflow; its timing is not a performance claim.

Prepare and query the data ​

CREATE TABLE logs(id INTEGER PRIMARY KEY, category String);
INSERT INTO logs VALUES (1, 'read'), (2, 'write'), (3, 'read');

SELECT id
FROM logs
WHERE category = 'read'
ORDER BY id
FORMAT csv;

1
3

Add an index ​

CREATE INDEX logs_category ON logs(category);
ANALYZE;

Inspect EXPLAIN QUERY PLAN for the predicate. The selected plan depends on the data and implementation version; an index is not a universal speedup.

Verify the result again ​

SELECT id
FROM logs
WHERE category = 'read'
ORDER BY id
FORMAT csv;

1
3

Compare measurements only after confirming the result. See query measurements and CREATE INDEX.