Adding an ANN Vector Index to MIF Scenario Search

Pre-implementation research. No code changes made. Findings dated 2026-05-25.

1. ANN options in BigQuery — what exists and what to pick

Why TreeAH for us

• Scale fits the sweet spot. 12.4M rows / 92 GB / 768-dim is exactly the regime where ScaNN's product-quantized search wins over IVF's flat k-means.
• Embeddings are already L2-normalized (see utils.py / asset SQL), so cosine ≡ dot product. TreeAH's asymmetric hashing is designed for this exact case.
• Auto-refresh in 5–15 min means our daily Dagster partition writes flow into the index with no extra orchestration. IVF refresh is more periodic / coarser.
• "Stored column" optimization doesn't help us. The MIF page query already LEFT JOINs the calls table for preset/system_prompt enrichment, so we'd need a base-table join anyway. The one IVF advantage doesn't apply.
• Trade-off acknowledged: ANN gives approximate recall. For scenario exploration (humans skimming top-K) this is fine; for any "count above threshold" / histogram metric we should keep a separate exact code path (see §4 below).

2. How the index is created — operational behavior

Proposed DDL (do not run yet)

CREATE VECTOR INDEX scenario_embedding_idx
ON `sesame-prod-426417.analytics.fct_calls_scenario_embeddings`(embedding)
OPTIONS (
  index_type    = 'TREE_AH',
  distance_type = 'COSINE'
);

We can also pass tree_ah_options = '{"normalization_type":"NONE"}' — our vectors are already L2-normalized so skipping the built-in normalization is correct. Default leaf size is usually fine; tune only if recall is poor.

How to "index without exposing the search" — staging the rollout safely

This is the answer to your "is there a way to index the data without giving people access?" question. There are two layers:

1. Index creation is invisible to users by itself. Today's MIF search uses hand-written UNNEST cosine, which the planner cannot route to a vector index. So you can create the index in prod and nobody's query path changes. It just sits there, populated and idle.
2. Query path adoption is gated separately. Mirror the existing SCENARIO_SEARCH_HYBRID pattern: add a SCENARIO_SEARCH_ANN env var (or a Statsig flag) that flips handle_scenario_search from the brute-force similarities CTE to a VECTOR_SEARCH-based version. Off-by-default → no user-visible change.

3. Dev table structure — what's there and what's not

Prod table schema (the one we'd index)

Existing indexes on the prod table

4. Does the existing search logic need to change?

Yes — the dense ranking path in mif_admin.py needs to be rewritten to use VECTOR_SEARCH. Creating the index alone does nothing for it.

Today the similarities CTE does this (paraphrased):

SELECT
  e.*,
  (SELECT SUM(ev*qv) FROM UNNEST(e.embedding) ev WITH OFFSET i
   JOIN UNNEST(q.embedding) qv WITH OFFSET j ON i = j)
  / NULLIF(SQRT(...) * SQRT(...), 0) AS similarity
FROM `fct_calls_scenario_embeddings` e
CROSS JOIN query_embedding q
LEFT JOIN `calls` c ON ...
WHERE e.ds BETWEEN @start_date AND @end_date

The planner cannot route hand-written UNNEST arithmetic to a vector index. To get acceleration we'd need something like:

WITH query_embedding AS (
  SELECT ml_generate_embedding_result AS embedding
  FROM ML.GENERATE_EMBEDDING(...)
)
SELECT
  base.call_id, base.window_idx, base.ds, base.value,
  base.character_name, base.start_time_ms,
  1 - distance AS similarity
FROM VECTOR_SEARCH(
  TABLE `sesame-prod-426417.analytics.fct_calls_scenario_embeddings`,
  'embedding',
  (SELECT embedding FROM query_embedding),
  top_k          => 200,
  distance_type  => 'COSINE',
  options        => '{"fraction_leaf_nodes_to_search": 0.05}'
)

Gotcha to design around: the current query computes more than top-K

The existing handler returns three things from one SQL family:

1. Page results — top-K ranked windows. Maps cleanly to VECTOR_SEARCH
2. Distance histogram across all rows in the date range. Cannot use VECTOR_SEARCH — needs every row's distance, not top-K.
3. "Above threshold" count for the date range. Same issue.

Options for the stats query:

• Keep stats brute-force. Stats and page are already split (asyncio.gather). Accelerate only the page query — stats stays the same. Simplest, smallest blast radius. Latency on stats stays where it is today (which is the bottleneck either way, but at least no regression).
• Replace stats with index-only approximation. Pull top-N (large N) from VECTOR_SEARCH and compute histogram on that. Faster but the histogram becomes "histogram of top-N by ANN", not "histogram of all in range" — a semantic change.
• Drop stats entirely for ANN mode. Worst UX impact, simplest code.

My recommendation: do the first one — keep brute-force stats unchanged, accelerate only the page query. The page query is what drives perceived latency; stats can run in parallel and finish whenever.

Hybrid path interaction

The hybrid (RRF) path has a dense_top CTE that already takes LIMIT _HYBRID_TOP_K_PER_SIDE from similarities. That maps perfectly to VECTOR_SEARCH(top_k => _HYBRID_TOP_K_PER_SIDE). The lex side is unchanged. The BM25 search index (scenario_value_idx) already exists, so once we add the vector index both sides of RRF are accelerated.

5. Proposed sequence (no work to start yet)

1. Copy one prod partition into encoded-stage-394013.analytics.scenario_embeddings_ann_test.
2. Run the TreeAH DDL against that sandbox; verify coverage hits 100 and a hand-written VECTOR_SEARCH returns sensible top-K with acceptable recall vs brute-force.
3. Apply the same DDL to prod. Watch INFORMATION_SCHEMA.VECTOR_INDEXES until ACTIVE. No code change yet — index is dormant.
4. Add a SCENARIO_SEARCH_ANN flag in mif_admin.py. New code path uses VECTOR_SEARCH for the page (and the dense side of hybrid). Stats query stays brute-force. Default off.
5. Flip the flag in dev / internal, validate UX, then prod.

Sources: BigQuery — Manage vector indexes · BigQuery — Search embeddings with vector search · INFORMATION_SCHEMA.VECTOR_INDEXES · Google Cloud blog — TreeAH / ScaNN in BigQuery · Intro to vector search