Problem

qmd search and the BM25 leg of qmd query are extremely slow on large collections when using the -c (collection) filter. A simple keyword search on a 16K-document collection takes 20 seconds instead of milliseconds.

Root cause: When searchFTS() combines documents_fts MATCH ? with d.collection = ? in the same WHERE clause, SQLite's query planner abandons the FTS5 index and falls back to a catastrophically slow execution plan. This is a known SQLite behavior — FTS5 virtual tables don't participate well in complex WHERE clauses with conditions on JOINed columns.

The underlying FTS5 index is fast. A direct SELECT ... FROM documents_fts WHERE MATCH ? completes in 8ms. The 20-second query time is entirely due to the query planner choosing the wrong plan.

Fix

Wrap the FTS5 query in a CTE so SQLite executes it first with proper index usage, then filters by collection on the materialized results:

-- Before (17s):
SELECT ... FROM documents_fts f
JOIN documents d ON d.id = f.rowid
WHERE documents_fts MATCH ? AND d.collection = ?

-- After (8ms):
WITH fts_matches AS (
  SELECT rowid, bm25(documents_fts, 10.0, 1.0) as bm25_score
  FROM documents_fts WHERE documents_fts MATCH ?
  ORDER BY bm25_score ASC LIMIT ?
)
SELECT ... FROM fts_matches fm
JOIN documents d ON d.id = fm.rowid
WHERE d.collection = ?

When filtering by collection, the CTE fetches limit * 10 candidates to ensure enough results survive filtering. Without a collection filter, the old plan was already optimal — no CTE overhead is added.

Benchmarks

Tested on a real index (44,682 documents, 3.3 GB, Bun 1.x on Linux with RTX 3070):

The qmd query hybrid pipeline also benefits since it calls searchFTS for its BM25 leg.

Reproduction

# Create a large collection (10K+ documents)
qmd search "common-term" -c large-collection -n 3  # slow before fix

The issue scales with how many documents match the search term in the FTS index, not with collection size per se. Common terms ("config", "import", "function") in large corpora are most affected.

Test plan

• All 201 existing store.test.ts tests pass, including the searchFTS filters by collection name test
• Live-tested on production index with 44K documents
• Verified results are identical before and after (same documents, same scores)
• No regression for queries without collection filter