seekdb hybrid search tuning

A diagnosis-first workflow for making seekdb vector and hybrid search fast without lowering recall. Work through the steps in order; do not change index parameters before step 2 tells you what the plan is actually doing.

When to use this skill

A query with ORDER BY l2_distance(...) / cosine_distance(...) / inner_product(...) takes far longer than expected, or got much slower after adding a WHERE filter.
APPROXIMATE search returns poor Recall@K compared with exact search.
You need to pick HNSW parameters (m, ef_construction, ef_search), a distance metric, or decide between filtered ANN and materialized subsets.
Two semantically equivalent filters behave very differently (for example a range predicate versus a long OR chain of equalities).
You are designing a hybrid query that combines vector similarity with full-text search or scalar predicates inside seekdb.

Step 1 - collect the facts before changing anything

Always start from these four inputs. Ask the user (or read from the session) if any is missing:

-- 1. the exact statement, including LIMIT and ORDER BY
-- 2. the table and index DDL
SHOW CREATE TABLE <table>;
-- 3. the plan
EXPLAIN <statement>;
-- 4. the data shape
SELECT COUNT(*) FROM <table>;
-- plus: vector dimension, index type/lib, and the session's ef_search
SHOW VARIABLES LIKE 'ob_hnsw_ef_search';

Record the measurement setup as well: row count, top_k, ef_search, median and p95 latency over repeated runs, and whether the plan was warm.

Step 2 - read the plan for these patterns

Run scripts/analyze_explain.py on the EXPLAIN output, or check by eye:

| Pattern in the plan | What it means | Typical fix | |---|---|---| | TABLE FULL SCAN on a query with APPROXIMATE | the vector index was not used at all | check the index exists, the ORDER BY uses the indexed column, and the distance function matches the index metric | | filter([(T_OP_OR, ...)]) with many branches | the filter is evaluated branch by branch for every row | rewrite as an IN list or a range predicate; see step 3 | | filtered ANN much slower than unfiltered | pre-filter scans many rows, or the filter is applied after the scan | compare with the equivalent unfiltered query; consider a raised ef_search or a materialized subset | | EXACT/full scan with a huge WHERE that keeps most rows | the optimizer chose a scan because the filter is not selective | usually fine; verify with the eligibility ratio | | LIMIT larger than the index can serve | recall drops instead of time rising | raise ef_search first, then m | | no APPROXIMATE in a "vector search" | exact kNN by definition, it must scan everything | add APPROXIMATE only when approximate results are acceptable |

Two rules that catch most incidents:

A filter must not change the semantics of the search. Two equivalent filters should select the same rows and cost roughly the same. If they do not, the difference is in how the predicate is evaluated, not in the index.
Never accept a recall drop as an optimisation. Measure Recall@K against exact search on the same query set before and after every change.

Step 3 - fix the filter shape first, it is the cheapest win

The executor evaluates the filter once per candidate row, so the shape of the predicate matters as much as its selectivity:

-- slow: one comparison per branch, per row
SELECT id FROM items
WHERE tag = 1 OR tag = 2 OR tag = 3 OR tag = 4 OR tag = 5 OR tag = 6
ORDER BY l2_distance(embedding, :q) APPROXIMATE LIMIT 10;

-- fast: one membership search
SELECT id FROM items
WHERE tag IN (1, 2, 3, 4, 5, 6)
ORDER BY l2_distance(embedding, :q) APPROXIMATE LIMIT 10;

-- also fine when the domain is contiguous
SELECT id FROM items
WHERE tag >= 1 AND tag <= 6
ORDER BY l2_distance(embedding, :q) APPROXIMATE LIMIT 10;

Other filter rules:

Put the most selective scalar predicate first only if it lets the optimizer pick a better pre-filter path; otherwise leave the order alone.
LIKE '%value%' cannot use an index; for text lookups prefer a full-text index and MATCH ... AGAINST, or a vector column when semantic matching is what you need.
Avoid functions on the indexed column inside the predicate (DATE(created) = ...), they defeat range extraction.

Step 4 - size the index parameters to the data

Guidance for HNSW (WITH (distance=..., type=hnsw, lib=vsag)):

| Parameter | Starting point | Raise it when | Cost | |---|---|---|---| | m | 16 | recall still low after ef_search tuning, high-dimensional data | memory and build time grow roughly linearly | | ef_construction | 128 | the graph quality caps recall even at high ef_search | build time grows | | ef_search (session) | 16 for top_k=10 | recall requirement is strict, or the filter removes many rows | latency grows, memory flat | | top_k | 10 | - | latency grows with top_k |

Practical loop:

Build with m=16, ef_construction=128.
Sweep ef_search in {16, 32, 64, 128} and record Recall@10 and p50/p95.
Pick the smallest ef_search that meets the recall target.
Only if recall still misses the target, raise ef_construction (rebuild) and then m.

Metric rules of thumb: l2 for normalised-and-unnormalised Euclidean data, cosine when direction matters (text embeddings are usually normalised, so l2, cosine and inner_product rank identically there - pick the one the index was built with), inner_product when magnitude carries meaning.

Step 5 - hybrid queries inside the database

Keep the combination inside seekdb; do not fetch two result sets separately and merge them in the application:

one statement may combine a vector search with scalar predicates and full-text conditions;
for ranked fusion of two retrievals, use the database's fusion support (for example RRF) rather than post-processing in the client;
when the same columns serve both a vector index and a full-text index, keep the index definitions on separate columns unless the version explicitly supports both on one column.

Step 6 - verify and report

Always finish with numbers, not opinions:

setup:      rows, dimension, index params, ef_search, top_k
before:     p50 / p95 / Recall@K / plan summary
change:     exactly one thing
after:      p50 / p95 / Recall@K / plan summary
verdict:    speedup, recall delta, eligibility-set equality check

Run at least 5 repetitions and report the median; interleave the before/after runs so that thermal or cache effects cannot favour one side.

Reference files

scripts/analyze_explain.py - flags the plan patterns from step 2 automatically.
references/tuning-cheatsheet.md - parameter ranges, metric selection, and the common failure modes with their root causes.
examples/hybrid_query.sql - a ready-to-adapt hybrid query skeleton.

Common mistakes

Tuning m/ef_construction when the plan never used the vector index.
Comparing ANN latency across runs with different recall.
Measuring with a cold plan cache only.
Rewriting a filter into a form that changes the result set (a gap in an OR chain folded into a range, for example).
Forgetting that ORDER BY ... APPROXIMATE without LIMIT is not a useful shape.