Skip to content

0006 — Retrieval latency finding: missing HNSW index + sparse_search over-fetch (and the fix)

Status

Accepted (v0.1.0). Added after the fact: this ADR was not anticipated when ADRs 0001-0005 were scoped, but the finding is significant enough to the platform's correctness-of-numbers story (and to how future performance regressions should be root-caused here) that it belongs in the same record, not just in a task report buried in .superpowers/.

Context

The PRD's latency gate is hybrid retrieval p95 < 120ms. The first honest measurement (eval/latency_bench.py, 200 queries, the seeded 100,000-chunk / 10-tenant / 10,000- chunks-per-tenant corpus) reported:

p50_ms: 656.238, p95_ms: 751.429, p99_ms: 767.778 — FAIL (751ms vs. 120ms budget, ~6x over)

This project's own convention (see the multi-agent-research and R9 self-review practices this codebase's task history follows) is to record a "green but meaningless" or "red and real" result honestly rather than adjust the target or quietly tune the benchmark. This ADR records that finding and the fix exactly as measured — not rounded up, not softened.

Root cause (found by profiling, not guessed)

Two independent problems, both real, both required to close the gap:

  1. No ANN index on chunks.embedding. db/migrations/versions/0001_initial_schema.py created the column as an undimensioned pgvector Vector() with no vector index at all — only ordinary B-tree indexes on tenant_id/document_id. retrieval/dense_search.py's ORDER BY embedding <=> :query_embedding LIMIT :top_k therefore ran an exact sequential scan + sort over every row RLS made visible on every single call — the primary driver of the 751ms figure.
  2. retrieval/sparse_search.py over-fetch, found in two layers while measuring the actual effect of fixing (1) — not anticipated up front:
  3. Column-level: search() originally ran select(Chunk) (every column, including text and embedding) over the tenant's entire chunk corpus (10,000 rows) purely to get positional alignment with the BM25 corpus for scoring — the ranking function (_rank_by_bm25_score) never actually reads any column off those rows.
  4. ORM-hydration level: even after narrowing the SQL to a single column (select(Chunk).options(load_only(Chunk.id))), SQLAlchemy's per-row ORM entity construction and identity-map bookkeeping for 10,000 Chunk instances was — measured via EXPLAIN (ANALYZE, BUFFERS) (~3.2ms actual Postgres execution time) vs. wall-clock Python timing (~50-60ms) for the identical query — the dominant remaining cost, not the database.

Decision / fix

  1. Migration 0002_chunks_embedding_hnsw_index.py: narrow chunks.embedding to the fixed dimension pgvector's HNSW index type requires (vector(384) — BAAI/bge-small-en-v1.5's output dimension, see ADR-0003; a data-preserving ALTER COLUMN ... TYPE vector(384) USING embedding::vector(384) cast, discovered as a hard prerequisite only by actually running CREATE INDEX ... USING hnsw (...) against the undimensioned column and watching it fail with asyncpg.exceptions.InvalidParameterValueError: column does not have dimensions), then CREATE INDEX ix_chunks_embedding_hnsw ON chunks USING hnsw (embedding vector_cosine_ops) — vector_cosine_ops chosen specifically to match dense_search.py's cosine_distance comparator; a mismatched opclass (e.g. vector_l2_ops) would silently never be used by that query. HNSW over IVFFlat because HNSW needs no training/lists step and its recall/speed tradeoff is tunable at query time, whereas IVFFlat's index quality depends on row count at CREATE INDEX time.
  2. retrieval/sparse_search.py::search: switched to a two-pass fetch — a Core-style scalar column select (select(Chunk.id), not an ORM entity select) over the whole tenant corpus for BM25-alignment ranking (skipping ORM entity-construction machinery entirely), then a second query that hydrates full Chunk rows for only the winning top_k ids. Public signature unchanged; _rank_by_bm25_score needed no change since it never reads Chunk-specific attributes, only zips items against scores positionally.

Real before/after numbers (independently re-verified twice; not rounded up or softened)

Run p50 p95 p99 Result
Before (Task 7 baseline) 656.2ms 751.4ms 767.8ms FAIL — 6.3x over the 120ms budget
Intermediate (HNSW + column-level fix only, still full ORM entities) — 145.2ms — FAIL — still ~25ms over budget
After, run 1 (HNSW + full sparse_search fix) 39.5ms 113.5ms 115.8ms PASS
After, run 2 (immediate re-run, same corpus) 39.5ms 113.4ms 117.4ms PASS

p95 improved from 751ms to ~113.4-113.5ms — a ~6.6x improvement. The PRD's <120ms gate is p95 specifically, and both real runs pass it with a modest, honestly-reported margin (~5%, ~6.5ms of headroom) — this is not a comfortable multiple-of-budget pass, and is reported as such rather than rounded to "well within target." p99 was observed as high as ~128ms in some measurement runs referenced elsewhere in this project's task history, which is expected and acceptable: the PRD gate is p95, not p99, and a p99 modestly above the p95 budget is a normal shape for a latency distribution, not a second failure.

Rerank p95 (eval/rerank_bench.py, unaffected by this fix, included here for the complete retrieval-path picture): 20.3ms against an 80ms budget — passes comfortably, was never part of this finding.

Recall@5/10/25 (eval/retrieval_eval.py against the hand-labeled eval/fixtures/qrels.json) was re-checked after the sparse_search fix and is unchanged: 1.0/1.0/1.0 both before and after cross-encoder reranking — the speed fix changed neither which chunks come back nor their order, only how expensively they were fetched.

Consequences

  • Isolated timing after both fixes attributes hybrid_retriever.search()'s remaining ~113ms roughly as: dense search ~19ms (HNSW query + query-embedding compute), sparse search ~70-80ms (still the larger half — Postgres round-trip + BM25 get_scores() + rank_bm25's own Python-level scoring loop over the tenant's corpus), RRF fuse <1ms. This is real work, not over-fetch artifact, at this corpus scale.
  • The margin over budget is real but not large (~5%). A further optimization lever exists and is explicitly not implemented here: caching the ordered chunk-id list alongside the BM25 index itself (mirroring sparse_index's own cache-and-invalidate-on-rebuild pattern), which would avoid re-querying/re-sorting ids on every call. Flagged as future work, not done in this release.
  • The dimension-narrowing migration required a follow-on, disclosed test-data fix: two hand-written 3-element toy embedding literals in tests/integration/test_tenant_isolation.py (used only to construct an artificial "another tenant's chunk is the closer cosine match" scenario) had to be zero-padded to 384 dimensions once the column enforced that width. Padding does not change either vector's norm or their relative distance ordering, so the property the test proves is unchanged — only the literal's length changed. This is the only place outside indexing/dense.py that constructs a literal embedding vector (confirmed by search), so no other test needed the same fix.
  • This finding is a concrete instance of why this project measures against the real 100k-chunk corpus rather than trusting an untested assumption about index coverage — the original schema reviewed cleanly and had no logic bug; the gap was a missing index, only visible under load.