sargable joins — materialize the join key, don't parse it per row

found chasing a leaflet /wrapped pathology: a publisher with 621 publications took >60s on the subscriber-count query while every individual query looked sub-millisecond in isolation.

the trap: joining on a value PARSED out of a column

subscriptions only stored the publication's full at-uri (at://<did>/<collection>/<rkey>). The publication join matched on the (did, rkey) decoded from it with substr/instr:

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

This is non-sargable: an index can only be used when a column appears bare on one side of the comparison. The moment you wrap it in a function the optimizer can't seek — it must compute the expression for every row, so the join degrades to a full table scan. EXPLAIN QUERY PLAN says it plainly:

|--SCAN s ← full scan, the fire |--SEARCH p USING INDEX ...

An expression index (CREATE INDEX ON subscriptions(substr(...))) does NOT help here — SQLite only matches one against a query whose expression is textually identical, and these nested substr/instr chains never line up. Don't bother.

the fix: compute the key once at write time, store it indexed

Add publication_did / publication_rkey columns, populate them wherever rows are written (live ingest, snapshot build, a one-shot backfill for existing rows), index (publication_did, publication_rkey), 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=?)

SCAN s → SEARCH s. 45s → 0.39s at 16-way concurrency (measured). The convoy is gone because the work isn't there anymore, not because we threw cores at it.

General rule: a database is brilliant at seeking an indexed column and useless when handed a string to parse per row. Any time a join/WHERE predicate wraps a column in a function, that's the read path paying, every row, every query — move the computation to write time and index the result.

also: index the OTHER side

Same incident, sibling bug — publications had no (did, rkey) index at all, so even the trivial side scanned. 16 concurrent CLI readers = 45.5s; adding idx_publications_did_rkey alone took it to 0.39s. Under concurrency a full scan doesn't just cost its own rows — every reader contends, so latency goes superlinear (8-way 5.9s → 24-way 13.5s). Superlinear scaling on a read-only workload is the tell for a missing index, not a slow machine.

diagnosing "slow endpoint, fast queries"

The summed per-query span durations were ~280ms while the endpoint took 13s. That gap is NOT in the SQL the spans measured — it's in something the per-statement timing doesn't capture: here, the planner re-running a scan the EXPLAIN would have shown immediately. Read the query plan before theorizing about pools, allocators, span processors, or machine size. EXPLAIN QUERY PLAN is the first move, not the last.