Skip to content
Coordinator DB
View as Markdown
View as Markdown

Use a targeted index for repeated queries ​

A query that repeatedly filters the same columns may benefit from a targeted index. Coordinator DB provides column, expression, partial and unique indexes; it does not provide automatically maintained aggregate projections.

sql
CREATE TABLE account_events(
    id INTEGER PRIMARY KEY,
    account_id INTEGER,
    day Date,
    event_type TEXT
);
CREATE INDEX account_events_by_day
ON account_events(account_id, day);

EXPLAIN QUERY PLAN
SELECT id, day, event_type
FROM account_events
WHERE account_id = 42 AND day >= '2025-01-01'
ORDER BY day;

Inspect the selected plan for the exact predicate and representative data. Defining an index does not guarantee it will be chosen for every query shape. Extra indexes also add work to changes in the indexed table, so measure an actual reader task before keeping them.

When the expensive operation is a grouped calculation rather than a lookup, first check filters and aggregation. A separate summary table is possible, but its update procedure belongs to the application and must be explicit. An ordinary view names a query without storing its result.

See CREATE INDEX and query measurements.