sargable queries

an index serves a predicate only when the indexed column appears bare on one side of the comparison. wrap the column in a function and SQLite computes the expression for every row: a join on a value parsed out of a column is a full scan, however fast each statement looks in isolation. EXPLAIN QUERY PLAN shows it as SCAN where a SEARCH was expected.

joining on a parsed value

a subscriptions table that stores only a publication's at-uri (at://<did>/<collection>/<rkey>) and joins publications on the (did, rkey) decoded with substr/instr is non-sargable:

JOIN subscriptions s ON
  p.did  = substr(s.publication_uri, 6, instr(...) - 1)
  AND p.rkey = substr(substr(...), ...)

|--SCAN s
|--SEARCH p USING INDEX ...

an expression index (CREATE INDEX ON subscriptions(substr(...))) does not rescue it: SQLite matches an expression index only against a query expression that is the same expression, and nested substr/instr chains written in two places rarely are.

the fix is to compute the key once at write time. add publication_did and publication_rkey columns, populate them on every write path (live ingest, snapshot build, a one-shot backfill), index them, and join on the bare columns:

JOIN subscriptions s ON p.did = s.publication_did AND p.rkey = s.publication_rkey

|--SEARCH s USING INDEX idx_subscriptions_pub_did_rkey (publication_did=?)
|--SEARCH p USING INDEX idx_publications_did_rkey (did=? AND rkey=?)

for a publisher with 621 publications the parsed join took over 60 seconds; the materialized key made it index seeks.

index the other side too

with no (did, rkey) index on publications, the other side of the join scans as well. under concurrency a full scan costs more than its own rows, because every reader contends: 16 concurrent readers took 45.5 s without the index and 0.39 s with it, and latency grew superlinearly with reader count (8-way 5.9 s, 24-way 13.5 s). superlinear scaling on a read-only workload points at a missing index, not a slow machine.

join order with a small table

when one table is much smaller than the other, the planner can still pick the big one as the driving SCAN, especially without ANALYZE statistics. the plan to look for is SCAN small then SEARCH big USING INDEX (pk=?).

reordering the FROM clause does not change this, because SQLite reorders inner joins freely. two things do: CROSS JOIN, which SQLite treats as a fixed nesting order, or pre-aggregating the small table in a subquery or CTE so the join runs against the aggregated rows:

SELECT d.uri, agg.recommend_count
FROM (
  SELECT document_uri, COUNT(DISTINCT did) AS recommend_count
  FROM recommends
  GROUP BY document_uri
) agg
JOIN documents d ON d.uri = agg.document_uri

on a leaderboard over an 18k-document corpus with about 880 recommended documents, the pre-aggregated form cut rows read per refresh about sixfold.

keyset pagination

page on the key, WHERE key > ? ORDER BY key LIMIT n, never OFFSET, which re-walks every skipped row on each page. for a composite key, a row-value comparison keeps it one index walk:

SELECT document_uri, tag FROM document_tags
WHERE (document_uri, tag) > (?, ?)
ORDER BY document_uri, tag LIMIT 500

an unpaginated whole-table SELECT against a remote database is one unbounded response: client memory and a long server scan at once. when production shares the database, space the pages out.

OR across two indexed columns

WHERE did = ? AND (jti_a = ? OR jti_b = ?) looks like two point lookups. with an index on did and unique indexes on both jti columns, the planner can pick the did index and evaluate the OR per row, because a single equality index beats a multi-index OR in its cost model when the table is small or unanalyzed. a unary + on the column (+did = ?) makes that predicate non-indexable without changing its meaning, and the plan flips:

MULTI-INDEX OR
|--SEARCH ... USING INDEX (jti_a=?)
`--SEARCH ... USING INDEX (jti_b=?)

+col is SQLite's documented way to keep a term off an index; unlike INDEXED BY it does not fail when the index is renamed. use it when the column is a guard, not the lookup key. a partial index (WHERE col IS NOT NULL) qualifies for an OR arm when the arm's predicate implies the filter.

slow endpoint, fast queries

when per-statement spans sum to a few hundred milliseconds and the endpoint takes seconds, the gap is work the per-statement timing does not capture, such as a scan the query plan would have shown. read EXPLAIN QUERY PLAN before theorizing about pools, allocators, or machine size.

sources

• zds — src/storage/store.zig session-token lookups, +did (2026-09)
• pub-search — backend/src/server/wrapped.zig, backend/src/server/pubkey.zig, backend/src/db/LocalDb.zig; commits e21706e (publications index, the 45.5s → 0.39s measurement) and ac9dac3 (materialized join key), 2026-06-26; leaderboard query and paged reads over Turso, 2026-08
• SQLite, query planner (CROSS JOIN ordering, unary +, expression indexes, partial indexes)