Skip to content

App-Wide Query Performance — Audit & Remediation Design

Date: 2026-07-22 Status: Design (grounded in measured data; pending review) Trigger: Pack seeding felt slow; investigation showed the slowness is app-wide at the query layer, not a pack problem. Scope: All D1-backed read surfaces (/api/words/*, /api/sentences, /api/contrastive/*, /api/associations/*, /api/similarity/*). Worker + D1 schema/indexes + a committed benchmark harness. Frontend fetch-depth tuning where relevant.

1. Problem — measured, not assumed

Latencies against staging (staging-api.phonolex.com), two runs each, 2026-07-22. CF Workers have no cold start, so run-to-run differences are network/query variance, not cold-isolate. Network floor ≈ 0.1–0.2s (property-metadata 0.11s, exact-word 0.17s):

Surface run 1 run 2 Verdict
words STARTS_WITH ɹ 1.56s 0.69s slow
words STARTS_WITH ɹ + has_image 0.67s 0.76s slow
words ENDS_WITH s 0.86s 0.82s slow
words CONTAINS ʃ 0.88s 0.89s slow
words CONTAINS_MEDIAL ɹ 0.72s 0.81s slow
words filter-only (aoa range) 0.87s 0.82s slow
words cv_shape CVC 0.81s 0.66s slow
words similar_to 2.84s 1.60s very slow
sentences (pattern) 2.21s 3.73s very slow
sentences (minpair) 1.76s 1.58s slow
contrastive minimal-pairs 3.72s 3.57s very slow
contrastive multiple-opposition 0.64s 0.64s ok
similarity search 0.68s 0.63s ok (in-isolate)
associations (GET) 0.31s 0.47s ok
exact word (GET) 0.17s 0.17s ok (indexed PK)
property-metadata (GET) 0.11s 0.11s ok (static)

Two corrections to earlier hypotheses, from this data: has_image is not consistently slow (the earlier 1.76s was run-to-run variance, not a filter cost), and every word search is ~0.8s regardless of pattern type — even filter-only with no phonemes_str LIKE — so the word floor is a common cost, not the LIKE alone. The high-variance surfaces (sentences 2.2→3.7s) indicate a genuinely heavy query, not a warm-up effect.

2. Root causes (grounded in code + schema)

RC1 — Fetch-all-then-paginate-in-app (worst offender). contrastive.ts /minimal-pairs: SELECT * FROM pairs WHERE … with no SQL LIMIT ("return all matches"), then fetchWordRows for every referenced word (chunked 80-at-a-time round-trips), JS-side frequency sort, then .slice(limit). For a common contrast (k/t) that is thousands of pairs → thousands of word enrichments over many D1 round-trips → 3.6s. Suspected in the maxopp/multopp word-list paths too.

RC2 — N+1 / chunked enrichment round-trips. fetchWordRows batches in 80s (D1's 100-bind-param cap). Each chunk is a round-trip; enriching hundreds–thousands of words serially multiplies D1 edge latency.

RC3 — Under-covering indexes. words is indexed on has_phonology, phoneme_count, syllable_count, initial_phoneme, final_phoneme, root, pos, is_canonical, has_image; word_properties only on frequency; pairs on (phoneme1,phoneme2) + is_canonical; corpus on rarity_score, content_lemma_sig, membership surface. Gaps: - Position patterns don't use the index that exists. STARTS_WITH/ENDS_WITH compile to phonemes_str LIKE ? (patterns.ts:69) — an unindexed scan — even though a single-phoneme STARTS_WITH is answerable by the indexed initial_phoneme column (and ENDS_WITH by final_phoneme). - Norm filters/sorts beyond frequency are unindexed (aoa, concreteness, …) → scans. - phonemes_str/variants_str/cv_shapes CONTAINS/medial are %…% LIKE → inherently unindexable; need a different structure if they must be fast.

RC4 — Word-search join floor (~0.8s). Every /api/words/search does words ⋈ word_properties + is_canonical + a sort over 48–125K rows. Even with a matching predicate, the join+scan+sort is the common ~0.8s.

RC5 — Sentence corpus join (2–3.7s). Constraint matching over corpus_sentences (2.1M membership rows) ⋈ index. Needs EXPLAIN QUERY PLAN to localize (candidate: membership join not using surface/sentence_id optimally, or per-constraint scans).

RC6 — similar_to intersection (1.6–2.8s). The word-search similarity-intersection path (chunked IN-lists over the neighbor pool) layered on the ~0.8s word floor.

RC7 — D1 edge round-trip multiplication. D1 is remote SQLite; every extra round-trip (chunked enrichment, per-constraint queries) adds fixed latency. Fewer, tighter queries beat many small ones.

3. Solution — sorting-first, keyset-paginated, never-capped

Grounded in the 2026-07-22 research spike (docs/superpowers/research/2026-07-22-query-perf-sorting-first.md; 24 verified claims, 1 refuted).

The framing (validated). This is a sorting problem, not a top-N retrieval problem: rank the whole constraint-satisfying set and page through it — never impose a total-result cap, so no word is ever unreachable. Faceted-search prior art confirms reachability is a policy choice: Typesense limit_hits defaults to "no limit"; Meilisearch maxTotalHits is a hard ceiling that permanently hides rows past it. We adopt the no-cap policy. (This retires the earlier "push LIMIT into SQL" idea — a LIMIT truncates and makes rows unreachable; the fix is keyset paging over an ordered set, not truncation.)

3.1 Benchmark harness (foundation, do first)

A committed, repeatable perf suite (scripts/perf/bench.*) that hits every surface with representative payloads, records p50/p95 over N runs, and diffs against per-surface budgets. No fix ships without a before/after number. Runnable against staging + local; optionally a non-blocking CI report.

Proposed budgets (staging, p50 over N runs — no cold start to exclude): | Surface | budget | |---|---| | exact word / metadata / associations | < 300ms | | word search (any pattern/filter) | < 300ms | | contrastive (all) | < 500ms | | sentences | < 600ms | | similarity / similar_to | < 500ms |

3.2 EXPLAIN + latency decomposition (the research's #1 open question)

EXPLAIN QUERY PLAN on every hot query plus a timing decomposition splitting each surface into query-execution vs N+1 enrichment round-trips vs the ~0.15s proximity floor. The research is explicit that the correct fix (index vs join/denormalize vs Sessions API vs SQLite-in-DO) hinges on this breakdown — so it precedes any structural change.

3.3 Keyset pagination over a deterministic total order — the core model

Replace OFFSET/LIMIT and the SELECT * + JS-sort + slice with keyset ("seek") pagination: … WHERE (rank, id) < (:lastRank, :lastId) ORDER BY rank DESC, id DESC LIMIT :pageSize. Verified properties: O(1) in page depth (vs OFFSET's O(n) scan-and-discard), insert/delete immune, and every matching row reachable by paging forward — the "never truncate" guarantee. The unique id tiebreaker makes the order a deterministic total order (no skips/dupes at page boundaries). UI = forward cursor paging ("more"), not "page N of M" (exhaustive totals are expensive — Meilisearch's own guidance). Enrich only the current page, never the whole match set (kills RC1/RC2).

Cursor contract (Phase A implementation, PHON-203 Task 5) — Phase B must inherit this, not reinvent it per surface. The cursor is always the ORDER BY key columns, verbatim, taken from the raw SQL page row (never from an enriched/post-filtered response array — a defensive filter dropping the trailing row must never regress the cursor, and an absent column must never coerce into a semantically-meaningful NULL-tail cursor value; see nextAfterFromRows in words.ts for the reference implementation). Where the response body is already object-shaped ({items, total, ...}), the cursor rides as an additive next_after field. Where the response is a legacy bare array (minimal-pairs's MinimalPairResult[], kept for frontend compatibility — JSON arrays can't carry named metadata), the cursor rides in an X-Next-After response header instead (requires exposeHeaders on the Worker's cors() config, or cross-origin browser clients silently can't read it). New Phase B/C surfaces should default to object-shaped response bodies with next_after — the header pattern is a compatibility shim for the one pre-existing bare-array endpoint, not the preferred shape going forward. Two caveats: (1) rowid-based cursors (e.g. the pairs table's SQLite-implicit rowid) are only stable within a session — a reseed can renumber rowids, so a cursor must be treated as ephemeral (safe to keep in page-session state, e.g. a "load more" button's closure) and must not be persisted across a reseed (bookmarked, stored client-side long-term, etc.). (2) The cursor is emitted on every non-empty page, including the final, short page — there is no server-side signal that a page is "the last one" short of the client trying the next fetch and getting zero rows back; that empty response is how the client detects the end, not an absent/null next_after on the prior page.

3.4 Precomputed rank columns + composite indexes (leftmost-prefix)

  • Precompute a stable rank/sort-key column per surface (frequency/rarity → a materialized order) so ranking is an index scan, not a JS sort of SELECT *. Reseed-gated (emit → R2 → manifest pipeline).
  • Composite covering indexes ordered by the leftmost-prefix rule — equality-filter columns first, rank column last (D1 uses an index only when the query constrains a left-prefix). Targets: the unindexed norm filters, and the pairs/corpus filter+rank combos.
  • Position patterns → indexed equality: single-phoneme STARTS_WITH→initial_phoneme=?, ENDS_WITH→final_phoneme=? (query-only, patterns.ts, no reseed). CONTAINS/medial substring LIKE stays unindexable — if it must be fast, evaluate a generated column / FTS5 / inverted word_phonemes(phoneme, word) membership table (reseed-gated).

3.5 Contrastive & sentences (expensive-generation + computed-rank)

  • Contrastive minimal-pairs: the 642K pairs table is already materialized — add a precomputed rank column (summed/each-word frequency) + a composite (phoneme1, phoneme2, is_canonical, rank) index, and keyset-page it; enrich only the current page. Stop SELECT * + enrich-all + JS-sort. Apply the same to maxopp/multopp word-list paths.
  • Sentences — the open wrinkle (research OQ4): ranking uses a query-time match_count, not a stored column, so a keyset seek predicate over it isn't directly expressible. Resolve in Phase A design: (a) bounded-candidate window then in-worker rank of that window (reachability within the window), or (b) precompute a stable global rank with match_count as a secondary in-page sort. Decide from 3.2's decomposition.

3.6 Layered latency levers (each with a boundary — do NOT over-claim)

  • KV hot reads <5ms → cache popular first/ranked pages (hot-path only; eventual consistency; stale on reseed).
  • D1 read replication (Sessions API, env.DB.withSession) → cuts the ~0.15s proximity floor for distant users; not the query-execution cost; a Worker code change, not query-only.
  • SQLite-in-Durable-Objects → synchronous in-thread reads make N+1 enrichment behave like one join (relevant to the chunked word-enrichment). The research refuted (0-3) the claim it eliminates the network floor — a Worker still reaches a DO over the network. Effort-heavy; an option to weigh, not a free win.

4. Phasing

  • Phase A — measure + query-only wins (no reseed): harness + budgets (3.1); EXPLAIN + latency decomposition (3.2); keyset pagination + enrich-only-current-page + position-pattern index routing (3.3, 3.4c); resolve the sentence computed-rank question (3.5). Reachability guaranteed by construction (no cap).
  • Phase B — precompute + index (one reseed): precomputed rank/sort-key columns; composite leftmost-prefix indexes on norms/pairs/corpus; optional generated-column/inverted structure for CONTAINS. Batch into one seed regen.
  • Phase C — caching + replication + CI: KV for hot ranked pages; Sessions API for proximity; consider SQLite-in-DO for the N+1 path; wire the harness into CI.

5. Acceptance

Every surface in §1 meets its §3.1 budget, proven by the committed harness (before/after numbers in the PR). No surface regresses. Reseed-gated changes (Phase B) land in one seed regen.

6. Phase A status (2026-07-22, post-implementation)

Shipped (Tasks 1-6, branch feature/query-perf-phase-a): - §3.1 harness — scripts/perf/bench.mjs, committed, budget-gated, exit-code-driven. - §3.2 EXPLAIN + latency decomposition — docs/superpowers/research/2026-07-22-query-plans-phase-a.md. Answered OQ1: round-trip-bound, not execution-bound — local SQL exec is single-digit-to-low-double-digit ms for every hot query (including the two full-table-scan LIKE queries); staging p50s (524-3121ms) are explained by D1 round-trip count, dominated by fetchMergedWordRows's chunked enrichment fan-out (⌈n/80⌉ × 5 tables per surface). - §3.4c position-pattern routing — single-phoneme STARTS_WITH/ENDS_WITH route to indexed initial_phoneme/final_phoneme equality instead of phonemes_str LIKE (patterns.ts), parity-proven against the local DB (scripts/perf/parity-patterns.mjs). - §3.5a contrastive fetch-all fix — /minimal-pairs ranks (pair_rank = summed word_properties.frequency, LEFT JOIN + COALESCE) and LIMITs in SQL instead of fetch-all + enrich-all + JS-sort + slice (buildMinimalPairsSql), parity-proven (scripts/perf/parity-minimal-pairs.mjs). - §3.3 keyset pagination — words search + minimal-pairs both take an after cursor ((rank, tiebreaker) < (:lastRank, :lastId) seek predicate), enrich only the current page, no total cap — reachability preserved by construction (scripts/perf/seek-reachability-check.mjs, seek-depth-proof.mjs).

Deferred (per §4 phasing, unchanged from the design): - §3.5b sentences computed-rank resolution — roadmap step 6, waits on generated-corpus work. - §3.4a/b precomputed rank columns + composite leftmost-prefix indexes (reseed-gated) — Phase B. Notably: Task 2 found the initial_phoneme/final_phoneme indexes Task 3 routes to already exist but the query planner doesn't pick them without ANALYZE (the seed pipeline never runs it — confirmed via sqlite_stat1 before/after diagnostic). Task 3's routing is real and necessary but its full win is contingent on a Phase B ANALYZE-in-pipeline (or index-forcing) fix. - §3.6 KV / D1 Sessions API / SQLite-in-DO — Phase C.

Measured (Task 6, 2026-07-22): AFTER bench run against a local dev worker (wrangler dev + local miniflare SQLite seeded from the same d1-seed.sql, row-count-verified against the local d1-seed.sql build product — 125,756 words / 685,705 pairs), not staging directly — staging deploy for this branch is gated on the develop merge. All 9 harness surfaces PASS their budgets locally (previously 7 FAIL / 2 PASS on staging). This number is not directly comparable to the staging BEFORE table — local dev has no network hop and no D1 edge round-trip cost, so some of the improvement (notably the two zero-code-change surfaces exact word and property-metadata, and the three words-search surfaces Task 3 didn't touch: CONTAINS, filter-only aoa, cv_shape) is environment, not code. The two surfaces Tasks 3-5 actually rewrote (words STARTS_WITH/ENDS_WITH, contrastive minimal-pairs) have same-machine old-vs-new SQL evidence (scripts/perf/README.md "Old-vs-new local SQL comparison") showing local query-exec time is flat (as OQ1 predicts) while the contrastive rewrite's pre-enrichment row count drops 844→200 (k/t) and 253→200 (s/ʃ) — the mechanism behind the eliminated enrichment fan-out. Final staging PASS confirmation happens post-merge, when bench.mjs --base https://staging-api.phonolex.com can be re-run against the deployed branch.

Full detail (numbers, test matrix, files): scripts/perf/README.md, .superpowers/sdd/task-6-report.md.

Staging acceptance run (2026-07-22, post-merge of PR #205, 7 runs): the predicted split held exactly. - Contrastive: 3.2× faster but still FAIL — k/t 3121→981ms p50, s/ʃ (n/a before)→923ms vs 500ms budget. The fetch-all fix is real (the 844→200 pre-enrichment drop shipped), but a 200-pair page still enriches ≤400 words = ⌈400/80⌉ chunks × 5 tables ≈ 25 sequential-ish D1 round trips at edge RTT. - Words surfaces: unchanged, FAIL — 485–643ms vs 300ms budget (BEFORE: 524–648ms). As predicted: Phase A never reduced the plain words-search round-trip count (count + list + enrichment). - exact word / property-metadata: PASS (94ms / 39ms). - ANALYZE experiment: dead end, question closed. One-off wrangler d1 execute phonolex --remote --command "ANALYZE words;" (12 stat rows written) then re-bench: 552ms vs 546ms p50 — no measurable change. The §3.4a/b "ANALYZE-in-pipeline" contingency is hereby demoted: index selection is not the words bottleneck at this scale; round-trip count is. - Phase B lever (confirmed, singular): collapse D1 round trips. Concretely: env.DB.batch() for count+list (one HTTP round trip), batch the enrichment chunk×table queries, skip/cache COUNT on cursor pages. No reseed required for any of these — they are Worker-code changes, so Phase B can start query-only after all.

7. Constraints / notes

  • D1 limits: 100 bind params/query, 100 columns/table, index count/cost — index additions must be justified and measured, not blanket.
  • Query-only fixes (Phase A) ship without a reseed; schema/index fixes (Phase B) require a seed regen + the R2 manifest flow.
  • No cold start (CF Workers/D1), but run-to-run variance (network/query) is real; the harness must report p50/p95 over N runs, not single shots.
  • This supersedes the ad-hoc "seeder orchestration" tuning discussed earlier — the root cause is the query layer, shared by the tools and the pack alike.

8. Phase B outcome + Phase C decomposition — CLOSED WON'T DO (2026-07-24)

Status: PHON-203 closed Won't Do. Phase A shipped and stays. Phase B shipped and stays. Phase C was designed, costed, and deliberately not built. This section exists so nobody repeats the diagnosis.

8.1 What shipped

  • Phase A (PRs #205/#206) — harness, EXPLAIN audit, keyset pagination, contrastive fetch-all fix, position-pattern index routing. Real win on contrastive (3121 → 981ms).
  • Phase B (PR #213) — fetchMergedWordRows → 2 db.batch() calls; batchCountAndList → count + page list in one call. Merged to develop, live on staging.

8.2 Phase B did not do what it was designed to do

surface Phase A Phase B budget
words (5 surfaces) 485–648 428–541 300
contrastive k/t 981 1077 (probe 5 min later: 951) 500
  • batchCountAndList (2 statements) — real win, ~1 round trip.
  • Batched enrichment (12–40 statements) — a wash. D1 batch() executes statements sequentially in one transaction, so N concurrent subrequests became N serialized executions. The savings and the cost cancel.

It is kept anyway: perf-neutral, and it cuts subrequests per request from ~50 to 2, which is headroom against the Workers subrequest limit.

8.3 The actual cost decomposition (staging, 2026-07-24)

Fixed, independent of page size:

probe p50 payload
property-metadata (no D1) 38ms 10KB
exact word cat (5 concurrent PK lookups) 111ms 3KB
search — no pattern, no filter, limit=1 307ms 3KB
browse limit=1, no sort 295ms 3KB
browse limit=1, sort_by=frequency 506ms 3KB
contrastive k/t limit=1 202ms 6KB

Marginal: latency tracks payload BYTES at ~0.5ms/KB, with the same coefficient on both surfaces (words 0.48, contrastive 0.59 ms/KB) — which the per-statement model does not achieve (28 vs 7.6 ms/stmt). Each word row is ~3KB because the API ships ~124 columns.

words STARTS_WITH r contrastive k/t
limit=1 351ms 3KB 216ms 6KB
limit=50 406ms 148KB 516ms 279KB
limit=200 558ms 604KB 1014ms 1121KB

Budget breakdown at limit=200 — words 558ms ≈ 38 network + ~270 fixed (COUNT + scan + sort) + ~250 payload; contrastive 1014ms ≈ 200 fixed + ~800 payload.

8.4 The three levers Phase C would have pulled

  1. COUNT — ~100–190ms on every words search, selectivity-independent (broad filter 580ms vs narrow 570ms at the same limit: it is a full join scan either way). Cacheable; the unfiltered browse count is a constant between reseeds. Not reseed-gated.
  2. Sort — 130–210ms of TEMP B-TREE (sort_by=frequency takes browse from 295 → 506ms). Wants a composite covering index; reseed-gated.
  3. Payload — the largest, and never previously examined. WordListTable renders 9 default columns of the ~124 we send. A fields projection would cut payload ~10×, worth ~225ms on words and ~700ms on contrastive. Costs a response-contract change: the column picker operates on already-fetched rows, so enabling a column would require a refetch.

Estimated post-fix: words ~200ms, contrastive ~280ms. Both under budget.

8.5 Why closed Won't Do

The latency that actually hurt users was sentence retrieval (2–9.4s), and that was fixed by the 50k curated corpus (PR #207) — now 150–183ms. A per-tile loading spinner covers the residual on the remaining surfaces. What is left is a 428–541ms words search and a ~1s contrastive page against budgets that were set analytically, not from user feedback. The remaining work (a response-contract change plus a reseed-gated index) is not justified by the experience it would buy.

Reopen only on a user-reported complaint, and start from §8.3 — the decomposition is done. Do not re-run the diagnosis.

8.6 Corrections to earlier conclusions in this document

  • §6 "round-trip-bound, not execution-bound" is wrong as stated. It was measured on code where every statement was its own round trip, confounding the two. Phase B decoupled them and the cost followed bytes and statements, not requests.
  • §6's demotion of the ANALYZE/index contingency was too broad. ANALYZE not helping means the existing indexes are not chosen; it does not show that a purpose-built composite index would not eliminate the 130–210ms sort.
  • The Phase B plan's dismissal of "skip COUNT on cursor pages" was wrong. It was justified by §6's single-digit-ms execution claim, which is a local SQLite artifact. On D1 remote the COUNT is ~100–190ms and is the second largest fixed cost.
  • Task 5 (JOIN-enriched page query) was never built. It remains scoped in docs/superpowers/plans/2026-07-24-phon-203-phase-b-round-trip-collapse.md. Note it addresses statement count, which §8.3 shows is the smaller lever — payload is larger. Do not build it as specified without re-reading §8.4.