hybrid-retrieval

Hybrid retrieval (BM25 + vector)

Phase 8d worked example. Combines bm25_score (Phase 8 plan — lexical, exact-term matching) with vec_distance_cosine (Phase 7 plan — semantic, embedding-space proximity) into a single ORDER BY. Same corpus, three rankings, one short Rust file.

cargo run --example hybrid-retrieval

Expected output (truncated to the rankings):

===  1. Pure BM25 (lexical) ===
  1. doc3  "sqlite is an embedded database engine"
  2. doc4  "postgres is a powerful relational database server"

===  2. Pure vector (semantic) ===
  1. doc3  "sqlite is an embedded database engine"
  2. doc4  "postgres is a powerful relational database server"
  3. doc6  "redis caches data in memory for fast lookups"

===  3. Hybrid (50% BM25 + 50% inverted cosine) ===
  1. doc3  "sqlite is an embedded database engine"
  2. doc4  "postgres is a powerful relational database server"

What the example shows

The query is "small embedded database". The corpus is six tech blurbs with hand-picked 4-dim embeddings (axes are roughly: systems / scripting / database / web). The pre-baked vectors stand in for what an embedding model would give you in production — the math is identical; the worked example just doesn't pull in a 1 GB transformer.

• Pure BM25 finds two docs that literally contain embedded or database. It cannot return a third — no other row shares any query term, and the FTS optimizer hook serves the top-k from rows that do match. The "small" token finds zero hits because nothing in the corpus uses that word.
• Pure vector ranks every doc by cosine distance to the query embedding [0.0, 0.0, 0.9, 0.2]. It surfaces doc6 ("redis … in memory for fast lookups") in third place — semantically related to "database/storage" but containing none of the literal query terms. Lexical search would never return it.
• Hybrid sums a normalized BM25 score with 1.0 - vec_distance_cosine (cosine returns distance, lower = closer, so we invert it for "higher is better"). It picks the same top-2 as pure BM25 because those are the only docs in the FTS-match set. The fusion's value isn't visible on this query — a deliberate choice; see "When hybrid wins" below.

When each shape wins

Scenario	Pure BM25	Pure vector	Hybrid
Query has rare exact terms ("redis", a SKU, an error code)	✅ Wins	❌ Spurious neighbours	✅ BM25 dominates the score sum
Query is conceptual with no overlap ("in-memory cache" vs corpus that uses "lookup table")	❌ Zero hits	✅ Finds the analog	(degenerate — WHERE fts_match returns ∅)
Query has both terms and semantic intent ("fast embedded SQL")	Returns several near-tied lexical hits	Reorders by closeness to true intent	✅ Best of both — wins consistently
LLM-generated paraphrases of the user's question	❌ Vocabulary drift kills recall	✅ Survives paraphrase	✅
Code search, log search, SKU lookup	✅	❌	✅

The 50/50 weight is a default, not a decision. Most production RAG stacks tune the weights per workload (e.g. 0.3/0.7 vector-heavy for paraphrased queries, 0.7/0.3 lexical-heavy for technical docs). Different aggregations also work — MAX, MIN, reciprocal rank fusion — and SQLRite's arithmetic composition is flexible enough to express any of them. We picked plain weighted sum because it's the most-obvious default and lets you change weights by editing two numbers.

Two gotchas

1. Cosine returns distance, not similarity

vec_distance_cosine returns 1 - cos(a, b): 0 for identical, 1 for orthogonal, 2 for diametrically opposite. Lower is closer. Hybrid scoring assumes "higher is better" everywhere, so we invert: 1.0 - vec_distance_cosine(...). Forgetting this flip is the most common mistake — the resulting ranking is the opposite of what you want, and it'll look subtly broken.

2. WHERE fts_match filters before the score is computed

The optimizer hook recognizes WHERE fts_match(col, 'q') ORDER BY bm25_score(col, 'q') DESC LIMIT k and serves it from the FTS index — fast, scales to millions of rows. The catch: when the hybrid ORDER BY mixes in vec_distance_cosine, the query still requires a WHERE fts_match(...) clause for the optimizer to recognize the FTS shape. That filter eliminates rows with no lexical match before the vector half of the score gets a chance to rank them.

If your goal is "find docs that match either the lexical OR the semantic query", drop the WHERE clause and let the engine score every row (slower, but correct). For most RAG workloads, the WHERE filter is a feature — you want lexical pre-filtering to keep latency tractable on large corpora.

Tuning the weights

The example uses a 50/50 weight as a starting point. To experiment:

-- 70% lexical, 30% semantic (technical-doc bias)
ORDER BY 0.7 * bm25_score(body, 'q') + 0.3 * (1.0 - vec_distance_cosine(embedding, [...])) DESC

-- 30% lexical, 70% semantic (paraphrase / RAG bias)
ORDER BY 0.3 * bm25_score(body, 'q') + 0.7 * (1.0 - vec_distance_cosine(embedding, [...])) DESC

-- Three-way: add a recency boost
ORDER BY 0.5 * bm25_score(body, 'q')
       + 0.4 * (1.0 - vec_distance_cosine(embedding, [...]))
       + 0.1 * (julianday('now') - julianday(created_at))
DESC

(The julianday function isn't in SQLRite yet — that's just a sketch of how the SQL composition extends naturally.)

Production checklist

• Normalize the BM25 score range. BM25 is unbounded above; cosine distance is in [0, 2]. Without normalization, a high-IDF rare term can dominate the sum. The example skips normalization because the corpus is six docs and the BM25 scores are already ~[0, 2]. Real corpora need a min-max or z-score normalization step (computed offline or via a sliding window).
• Use real embeddings. Pre-baked toy vectors are for the example. In production you'll call an embedding model — sqlrite-ask's LLM adapters can host one, or use the fastembed-rs family for a local model.
• Tune k end-to-end. The LIMIT k clamps the result set. Hybrid retrieval typically needs to over-fetch (e.g. LIMIT 50) and re-rank with a cross-encoder for the final top 5 or 10.

See also

• docs/phase-8-plan.md — Q8 (arithmetic vs typed hybrid_score(...)) explains why SQLRite does this with raw arithmetic instead of a dedicated function.
• docs/phase-7-plan.md — vector indexing context.