Repository navigation
[Bug]: Conversation-content search scans all canonical assistant turns on large histories #10091
Description
Activity
Proposed fix: #8969 (current head
31f157c). It replaces the globalINlist with an indexed correlatedEXISTSlookup 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.
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.
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.
Before submitting
Area
apps/server
Steps to reproduce
INsubquery for the fixture's rare and common searches.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 reportsLIST SUBQUERYandSCAN turns, adding work across the turn history even though the search RPC returns at most 50 matches.Original fixture measurements:
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
INmembership query is present on main at39802c06117fae0b3da43624b0d54309c5437c72.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.