[Bug]: memory search takes ~8s when filtering by container_tag (vector_top_k + JOIN bad query plan)

[evilhikari]

Environment

• opencode-mem 2.24.3
• Node.js v26.5.0, Windows
• Local embedding (Xenova/nomic-embed-text-v1), local libsql shard (no network)

Symptom

Searching memories in the Web UI hangs for ~40s total; the frontend eventually aborts the fetch with Error: signal is aborted without reason. Profiling shows the bottleneck is searchKind() in dist/services/turso/vector-search.js: a single ANN search against a 337-row shard takes ~8 seconds (3 runs: 7982/8113/8064 ms) instead of tens of milliseconds.

Root cause

searchKind() combines vector_top_k() with INNER JOIN memories m ON m.rowid = v.id and an optional container_tag filter:

SELECT m.id AS id, vector_distance_cos(m.content_vec, vector32(?)) AS dist
FROM vector_top_k('memories_content_vec_idx', vector32(?), ?) AS v
JOIN memories m ON m.rowid = v.id
WHERE m.content_vec IS NOT NULL AND m.container_tag = ?

EXPLAIN QUERY PLAN shows SQLite's planner drives from the memories table (using idx_container_tag) and then evaluates the vector_top_k virtual table once per memory row — turning one ANN lookup into N nested-loop evaluations (337 rows = 337 ANN calls):

SEARCH m USING INDEX idx_container_tag (container_tag=?)
SCAN v VIRTUAL TABLE INDEX 1:

Fix

All timings below were benchmarked on the unmodified codebase by running the candidate SQL directly against the database in a test script; the source was not modified during investigation.

Changing JOIN to CROSS JOIN forces the intended TVF-first plan and produces the correct result in ~28 ms (re-run: 29/29/29 ms, 199 rows):

SCAN v VIRTUAL TABLE INDEX 1:
SEARCH m USING INTEGER PRIMARY KEY (rowid=?)

Benchmark (same query vector, 337-row shard):

Variant	Time
INNER JOIN (current code path)	8409 / 8534 / 8881 ms
CROSS JOIN (suggested fix)	29 / 29 / 29 ms
Two-step (top_k then rowid IN)	29 ms (content) + 28 ms (tags)

Semantics are unchanged (WHERE m.vector IS NOT NULL / container_tag filters still apply).

Suggested patch in dist/services/turso/vector-search.js searchKind() (both branches):

-          JOIN memories m ON m.rowid = v.id
+          CROSS JOIN memories m ON m.rowid = v.id

Alternative: two-step query (top_k first, then SELECT ... WHERE rowid IN (...)) also works, but CROSS JOIN is a one-word change and is therefore preferred.