[Bug]: Conversation-content search scans all canonical assistant turns on large histories

[justelson]

Before submitting

• I searched existing issues and did not find a duplicate of this performance problem.
• I included enough detail to reproduce or investigate the problem.

Area

apps/server

Steps to reproduce

1. Use the isolated 1,000,000-message fixture and reproduction script in the benchmark evidence.
2. Run the existing conversation-content search query with its global canonical-assistant-message IN subquery for the fixture's rare and common searches.
3. Record timings and inspect EXPLAIN QUERY PLAN. The evidence includes 12 rounds in the same process/database, raw samples, query plans, checksums, and resource limits.

This is a database-query benchmark; the timings below are not end-to-end UI measurements.

Expected behavior

Checking whether an assistant message is canonical should use an indexed lookup instead of materializing every canonical assistant message ID for each content search.

Actual behavior

The assistant-message membership filter builds a global list from projection_turns. SQLite reports LIST SUBQUERY and SCAN turns, adding work across the turn history even though the search RPC returns at most 50 matches.

Original fixture measurements:

Query	p50	p95
Rare search	7,387 ms	12,830 ms
Common search	7,256 ms	12,034 ms

Impact

Major search-latency degradation on the large benchmark fixture. The slowdown makes conversation-content retrieval increasingly costly as history grows; it does not imply that all users experience these absolute timings.

Version or commit

The pre-fix query documented in #8969. The same global IN membership query is present on main at 39802c06117fae0b3da43624b0d54309c5437c72.

Environment

Isolated SQLite fixture with exactly 1,000,000 messages; database size 405.92 MiB. The original benchmark used a low-priority process restricted to one logical CPU. Full reproduction details are in the linked evidence.

Supporting evidence

Exact script, raw samples, query plans, checksums, and resource limits.

Workaround

No application-level workaround verified. A proposed targeted fix is available in #8969. The leading-wildcard message scan remains a separate limitation.

[justelson]

Proposed fix: #8969 (current head 31f157c). It replaces the global IN list with an indexed correlated EXISTS lookup and adds migration 048, preserving canonical-message membership semantics.

The original same-fixture benchmark reduced rare-search p50 from 7.39s to 2.11s and common-search p50 from 7.26s to 3.31s, with identical result rows across 12 rounds. The index added 11.63 MiB (2.86%); incremental write overhead was not measured, and the leading-wildcard scan remains.

The branch has been updated against main: 27 projection/search tests plus the migration test pass, along with server typecheck and targeted lint/format checks. The PR remains open for review; this issue is not yet resolved.

[qwertie]

Wait, you're saying that it's possible to search conversation content? How?? I've been wanting that feature for ages! Control-F doesn't work and now I'm right-clicking on things, not seeing a search option anywhere.

[juliusmarminge]

Thanks for the benchmark and query-plan analysis. Orchestrator V2 has merged in #2829.

V2 search reads its message projections directly and no longer builds the global canonical-assistant membership list from projection_turns. We are closing this specific query issue as obsolete. Leading-wildcard text search still has its own scaling limits.

If the problem persists on a build containing V2, please open a fresh issue with the T3 version, provider version, reproduction steps, and relevant logs, and link this report.