Thread content search scans every message synchronously: 140-430 ms of blocked server per search on 86k messages #18965

What happened

Typing two or more characters in the command palette or sidebar search runs ThreadSearch.search. It scans every user and assistant message with json_extract(payload_json, '$.text') LIKE '%q%'. On a copy of a real database (86,163 messages, 1,651 threads) one search takes 137-158 ms for a no-match or rare term, 228-282 ms for a two-letter term and 338-427 ms for a three-letter term. The server's SQLite client is node:sqlite DatabaseSync, so each search holds the event loop for that whole time. Provider streaming, WebSocket frames and every other request wait.

Diagnosis

• apps/server/src/orchestration-v2/ThreadSearch.ts:86-136: the candidate filter is json_extract(messages.payload_json, '$.text') LIKE ${pattern}. No index can serve it, so the plan is SCAN messages plus a JSON parse per row.
• packages/shared/src/nodeSqliteClient.ts calls statement.all(...) on DatabaseSync, so the scan runs on the server's main thread.
• The web side debounces (useThreadSearch) and needs 2 or more characters, so it is one stall per typing pause.
• The database has no sqlite_stat1 table ([Bug]: Server never collects SQLite planner statistics, so large databases full-scan and stop answering #17955). That does not change a LIKE scan.

Steps to reproduce

1. Use a database with tens of thousands of messages.
2. Search from the command palette and pause after typing.
3. Or run the CTE from ThreadSearch.ts in node:sqlite against a read-only copy with the patterns %zzqxv%, %websocket%, %the% and %ab%, and time each run.

Version

0.0.45 (commit e9221e4, upstream/main on 2026-10-13)

Environment

Linux (WSL2), Node 26.11.1, read-only copy of a real database, 3 runs per term

Evidence

messages 86163, sqlite_stat1 absent
"zzqxv"     0 hits   ms [158, 137, 143]
"websocket" 50 hits  ms [139, 154, 140]
"the"       50 hits  ms [427, 338, 375]
"ab"        50 hits  ms [282, 282, 228]
EXPLAIN QUERY PLAN: SCAN messages; SEARCH threads USING INDEX (thread_id=?); SEARCH projects

Related issues

#14701 / PR #14703 (thread list read blocks the event loop; same family, different query), #17955 (no planner statistics; does not make a LIKE scan indexed). No existing issue about content-search cost found.

Fix applied or workaround

None applied. I can open a pull request for this once triage confirms the direction. I have not written it yet; the options I would measure are an FTS5 or trigram index over message text, or running the scan off the main thread, and I would follow whichever you prefer.

Filed by

claude (sonnet-5-5) in T3 Code. The lead came from Muse Spark 1.3 Contributor (OpenCode); the timings above were run separately.