Query plans + latency decomposition (PHON-203 Phase A, Task 2)¶
Companion to scripts/perf/explain.sh (re-runnable; produces the raw
EXPLAIN QUERY PLAN + .timer on output this doc summarizes) and to Task 1's
staging baseline (scripts/perf/README.md, scripts/perf/bench.mjs).
Method: built a local sqlite copy of the exact D1 seed
(sqlite3 /tmp/phonolex-perf.db < packages/web/workers/scripts/d1-seed.sql,
125,756 words / 685,705 pairs / 104,477 word_properties rows with frequency
non-null — same row counts and same indexes as production D1, since the seed
carries its own CREATE INDEX statements). SQL below is copied verbatim from
the live route/lib source with bind params replaced by the literal example
values scripts/perf/bench.mjs actually sends against staging, so the local
plan + exec time is for the same query staging measured.
Answering OQ1: execution-bound or round-trip-bound?¶
Round-trip-bound, not execution-bound — and by a wide margin. Local SQL
execution for every hot query is single-digit-to-low-double-digit
milliseconds, including the two queries that fall back to a full 125K-row
table scan (phonemes_str LIKE, cv_shapes LIKE). That is two to three
orders of magnitude below the staging p50s (524–3121ms). What actually
predicts the staging p50 is the number of D1 round trips per request,
which for these endpoints scales with matched-result-set size because
fetchMergedWordRows (packages/web/workers/src/lib/queries.ts:130-226)
fans out into 5 parallel per-80-row-chunk queries (words,
word_properties, word_freq_bands, word_percentiles, word_images) for
every word that needs enriching:
| Surface | Distinct words enriched | Enrichment fan-out (⌈n/80⌉ × 5 tables) | Other sequential D1 calls | Staging p50 |
|---|---|---|---|---|
| words STARTS_WITH /ɹ/ | 200 (LIMIT) | 3 × 5 = 15 | count + list = 2 | 524ms |
| words filter-only aoa | 200 (LIMIT) | 3 × 5 = 15 | count + list = 2 | 648ms |
| words cv_shape CVC | 200 (LIMIT) | 3 × 5 = 15 | count + list = 2 | 588ms |
| contrastive minpair k/t | 1,386 (no LIMIT before enrichment) | 18 × 5 = 90 | pairs query = 1 | 3121ms |
The contrastive surface is ~5–6× slower than the words-search surfaces, and
its enrichment fan-out is ~6× larger (90 vs 15 D1 statement executions) —
for the identical per-call local exec cost profile. That correlation, not
anything about SQL complexity, is the story. Root cause for contrastive
specifically: contrastive.ts (/minimal-pairs, lines 99-124) runs the
pairs query without a SQL LIMIT, enriches every word referenced by
all matching pairs, and only slices to the caller's limit in JS
after enrichment (line 142: enriched.slice(0, limit)) — so today's
enrichment fan-out is bounded by the candidate set (844 pairs → 1,386
distinct words), not by the 200 rows actually returned. This is exactly
what Task 4's replacement query (#4b below) fixes by ranking + LIMITing in
SQL first.
A secondary, compounding finding (see "Planner behavior" below): none of the
words-search plans use the phoneme/cv_shape/aoa constraint to narrow the
scan at all — SQLite/D1 picks idx_words_has_phonology, an index on a
column that is 1 for 100% of rows, because the seed pipeline never
runs ANALYZE. This doesn't change the round-trip-bound conclusion (local
exec is still low tens-of-ms even as a full scan), but it matters directly
for Task 3, below.
Per-query results¶
DB: /tmp/phonolex-perf.db, 125,756 words rows, 685,705 pairs rows, 104,477
word_properties rows with non-null frequency. Full raw output from
scripts/perf/explain.sh is not committed (regenerate on demand); numbers
below are transcribed from an actual run against this DB.
| # | Query | Source | Plan | Local exec | Staging p50 (Task 1) | Decomposition |
|---|---|---|---|---|---|---|
| 1a | words STARTS_WITH /ɹ/ (current, phonemes_str LIKE '\|ɹ\|%') |
patterns.ts:67-73 → wordFilter.ts:94-202 → words.ts:220-234,272-274 |
SEARCH w USING INDEX idx_words_has_phonology + SEARCH wp USING INDEX sqlite_autoindex_word_properties_1 + USE TEMP B-TREE FOR ORDER BY (no index touches phonemes_str) |
33ms | 524ms | ≈150ms floor + ~50ms exec (list+count) leaves ~324ms unaccounted by SQL — attributable to the 2 sequential (count, list) + 1 parallel-batch-of-15 (enrichment) D1 round trips |
| 1b | words STARTS_WITH /ɹ/ (Task 3 replacement, initial_phoneme = 'ɹ') |
not yet in codebase — Task 3 target | Same plan as 1a: idx_words_has_phonology, not idx_words_initial (see planner note below) |
24ms | n/a (not deployed) | Local exec is already ~1.4x faster than 1a even without the intended index being chosen — the real win is contingent on the ANALYZE finding below |
| 2 | words filter-only aoa BETWEEN 2 AND 6 (+ join + sort) | queries.ts:59-124 (partitionFilterColumns) → wordFilter.ts:86-92,154-169 → words.ts:42-47,229-231 |
idx_words_has_phonology + sqlite_autoindex_word_properties_1 + USE TEMP B-TREE FOR ORDER BY (no index on aoa) |
41ms | 648ms | Same shape as 1a/3; slightly higher p50 despite similar local exec (44,452 rows match the query's full WHERE — has_phonology/is_canonical/frequency IS NOT NULL/aoa range — before the sort, vs. 2,931 for 1a and 1,817 for 3; the largest pre-sort set of the three words-search surfaces) — consistent with round-trip cost dominating over the marginal sort cost |
| 3 | words cv_shapes LIKE '%|CVC|%' | wordFilter.ts:171-187 |
idx_words_has_phonology + sqlite_autoindex_word_properties_1 + USE TEMP B-TREE FOR ORDER BY (no index on cv_shapes; leading % forces a scan regardless) |
24ms | 588ms | Same shape as 1a/2 |
| 4 | contrastive minpair k/t (current, SELECT * FROM pairs WHERE is_canonical=1 AND (...), no LIMIT) |
contrastive.ts:99-116 |
SEARCH pairs USING INDEX idx_pairs_is_canonical (844-row result; see planner note — idx_pairs_phonemes would be more selective but isn't chosen) |
18ms | 3121ms | Local exec negligible; 844 pairs → 1,386 distinct words → 90-way enrichment fan-out is the dominant term (see table above) |
| 4b | contrastive minpair k/t (Task 4 replacement, join + pair_rank + ORDER BY + LIMIT 200) |
Task 4 target (shape given in brief) | SEARCH p USING INDEX idx_pairs_is_canonical + 2× SEARCH wp{1,2} USING INDEX sqlite_autoindex_word_properties_1 + USE TEMP B-TREE FOR ORDER BY |
19ms | n/a (not deployed) | Same local-exec cost as #4 (ranking folds into the existing 844-row scan, one D1 round trip) but caps the enrichment fan-out to the ≤200 winning pairs (≤400 distinct words → ≤5×5=25 round trips) instead of all 1,386 — the mechanism behind Task 4's expected win |
Row counts sanity-checked: #1a/1b/2/3 each return exactly 200 rows (the
LIMIT 200 bench.mjs sends); #4 returns 844 (no SQL LIMIT, matches
SELECT COUNT(*) FROM pairs WHERE is_canonical=1 AND ((phoneme1='k' AND
phoneme2='t') OR (phoneme1='t' AND phoneme2='k'))); #4b returns 200 (its
LIMIT).
Decomposition model applied per the brief: API total ≈ local exec +
enrichment round-trips + ~150ms network floor. Two calibration points from
Task 1's PASS surfaces refine the "~150ms" assumption: property-metadata
(meta.ts:47, a synchronous handler with zero D1 calls) has p50=39ms —
that is the actual measured client→edge→Worker→client floor on this
platform, not 150ms. exact word cat (words.ts:49-78, 5 parallel D1 calls)
has p50=93ms, i.e. ~54ms above the zero-DB floor for one 5-way-parallel D1
batch. Both numbers push the same direction as the brief's coarser model:
if the true floor is closer to 40ms than 150ms, an even larger share of the
524–3121ms surface p50s is attributable to D1 round trips, not less. We did
not have per-hop network telemetry from the staging run to split exec vs.
round-trip time exactly (Task 1 recorded end-to-end wall time only); the
round-trip counts in the table above and their correlation with p50 are
the load-bearing evidence for the headline, with the floor calibration as a
directional check.
Planner behavior (relevant to Task 3)¶
The seed pipeline never runs ANALYZE (export-to-d1.py / emit_d1_sql.py
have no ANALYZE statement, confirmed by grep; a freshly-seeded copy of this
DB has no sqlite_stat1 table). Without those stats, SQLite's planner does
not pick idx_words_initial (export-to-d1.py:102) for
initial_phoneme = 'ɹ', even when it's the only equality constraint and by
far the most selective one available (6,756/125,756 rows ≈ 5.4%). Instead it
picks idx_words_has_phonology — an index on a column that is 1 for
100% of rows (125,756/125,756; has_phonology defaults to 1 and the
seed has no phonology-less rows) — regardless of WHERE-clause order. Verified
directly:
-- before ANALYZE (matches freshly-seeded D1 today):
SEARCH w USING INDEX idx_words_has_phonology (has_phonology=?)
SEARCH wp USING INDEX sqlite_autoindex_word_properties_1 (word=?)
-- after ANALYZE (diagnostic only — not run by the deploy pipeline):
SEARCH w USING INDEX idx_words_initial (initial_phoneme=?)
SEARCH wp USING INDEX sqlite_autoindex_word_properties_1 (word=?)
This diagnostic mutates the DB (ANALYZE populates sqlite_stat1);
explain.sh immediately runs DELETE FROM sqlite_stat1 afterward in the
same invocation to restore the DB to the no-stats state, verified sufficient
to revert the planner's choice back to idx_words_has_phonology — so
running the full script leaves /tmp/phonolex-perf.db exactly as it found
it, safe for Tasks 3/4 to reuse.
This means Task 3's fix (swap phonemes_str LIKE for initial_phoneme =)
will not, by itself, change the query plan on production D1 — the index
it depends on exists but isn't chosen without ANALYZE stats (or an
INDEXED BY hint, or a WHERE-clause rewrite the planner's heuristics happen
to favor without stats — none of which we've verified separately). Task 3
should either add an ANALYZE step to the seed pipeline (D1 supports it via
wrangler d1 execute) or force the index explicitly. Given local exec is
cheap either way (single-digit-ms difference locally), this doesn't change
the Task 2 headline, but it is a real gap between "index exists" and "index
is used" that would otherwise make Task 3 land with no measurable effect.
A smaller instance of the same class of finding: query #4's pairs scan
picks idx_pairs_is_canonical (86,095/685,705 rows ≈ 12.6% selective) over
the composite idx_pairs_phonemes (phoneme1, phoneme2) even though the
(phoneme1='k' AND phoneme2='t') OR (phoneme1='t' AND phoneme2='k')
condition alone matches only 6,091/685,705 rows (≈0.9% — confirmed via
EXPLAIN QUERY PLAN on the phoneme-only condition, which correctly picks a
MULTI-INDEX OR over idx_pairs_phonemes). Same root cause (no ANALYZE).
Doesn't change #4's headline (local exec is 18ms either way; the enrichment
fan-out dominates), but worth the same fix if Task 3/4 touch the seed
pipeline.
Files¶
scripts/perf/explain.sh— re-runnable;./scripts/perf/explain.sh [db-path](defaults to/tmp/phonolex-perf.db). The 6 numbered EXPLAIN/timing queries are read-only. The trailing ANALYZE diagnostic (planner-behavior section below) is the one exception — it runsANALYZEagainst the DB and thenDELETE FROM sqlite_stat1in the same script run to restore the DB to its pre-ANALYZE state before exiting, so the DB is left unmutated end-to-end and safe for Tasks 3/4 to reuse for their own EXPLAIN verification. Verify withsqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_stat1;"== 0 after a run.- Local DB (
/tmp/phonolex-perf.db, ~557MB) and the 473MB seed it's built from are not committed (gitignored build products) — rebuild viasqlite3 /tmp/phonolex-perf.db < packages/web/workers/scripts/d1-seed.sql(~2.5–3 min).