Indexes and evidence-based query tuning

The idea

An index provides an additional access structure that can reduce the rows inspected by a query. A composite index orders entries by its first key and then later keys within equal prefixes. Its usefulness depends on the query’s filters, ordering, and data distribution. An index on (team, created_at) naturally supports narrowing to one team before considering a time range within that team.

Use EXPLAIN QUERY PLAN and representative data to examine the optimizer’s chosen strategy. A small table may reasonably be scanned even when an index exists, and a plan is not by itself a timing measurement. Indexes also consume storage and add maintenance work to inserts, deletes, and indexed-column updates. Prefer evidence tied to an important query over adding an index to every column without a workload model.

CREATE TABLE events(id INTEGER PRIMARY KEY,team TEXT,created_at TEXT);
CREATE INDEX events_team_time ON events(team,created_at);
-- This predicate matches the index key order:
SELECT id FROM events WHERE team='A' AND created_at>='2026-01-01' ORDER BY created_at;

No rows: the example table is empty.

The example establishes a useful index/query relationship; an actual chosen plan should still be inspected with representative data.