search_matters 3.3s → tens of ms — design spec

Problem (measured)

search_matters p50 is 3.3s live on the real HSE org (4.6k matters). The tool-level battery run for this spec measures 2.4–4.4s per call — including 2.4s for a query matching zero matters. Lawyers feel this as a 3-second stall every time the agent resolves "the 219 East 60th deal".

EXPLAIN (ANALYZE, BUFFERS) as app_role with the RLS GUC set shows two compounding causes:

1. JIT compilation — not row work — is ~95% of the latency. The correlated mail-count and folder-count subqueries appear in SELECT and three more times in ORDER BY, and each per-token match expression appears in WHERE and again inside the ORDER BY match-count aggregate. The planner prices the duplicated correlated subplans at ~840 cost × ~2.3k estimated rows ≈ 1.9M–3.5M total plan cost, far past jit_optimize_above_cost (500k), so Postgres LLVM-compiles 188 functions with full optimization: 2,069ms of a 2,122ms single-token query; ~3.5s of 3.95s at three tokens. Real execution underneath: 55ms.
2. Residual per-row subplan work. The folder-count subplan probes matter_drive_folder_org_idx (org-only index) then filters 3,833 rows by clio_matter_id per evaluation — ~0.85ms × 2 ORDER BY evaluations × N result rows (~340ms at 201 rows). Same pattern for the mail count.

Design

Single-file SQL restructure of the tool query. No schema change, no behavior change.

1 · Pre-aggregate both counts once (CTEs)

• mail_counts: pipeline_runs ⋈ mail_classifications filtered to user_id + projection_state = 'effective', grouped by (organization_id, matter_id).
• folder_counts: matter_drive_folder grouped by (organization_id, clio_matter_id).
• Both LEFT JOINed to matter by key, replacing all six correlated count-subplan occurrences (3 mail + 3 folder). Counts are wrapped in MAX() under the existing GROUP BY (the matter_drive_folder LEFT JOIN for deal_name matching can multiply rows; the joined count is constant within a group).

2 · Wrap ranking in a subquery

The inner query computes match_count, mail_count, folder_count once per matter; the outer ORDER BY references plain columns. No expression is evaluated twice.

3 · SET LOCAL jit = off in the tool's RLS transaction

After the rewrite the plan cost is ~19k (1 token) / ~68k (3 tokens) — below jit_above_cost — but a 5-token query still reaches ~117k and pays a 264ms JIT tax on 98ms of work. This query shape (LIMIT 25, sub-100ms row work, hashed subplans) never benefits from JIT; disabling it transaction-locally is strictly a latency floor.

Everything else — tokenization, entity/alias EXISTS matching, ranking tiers, non-identifying display-number handling, LIMIT 25, output formatting — is unchanged.

Measured result (prototype, real HSE corpus, app_role + RLS GUC)

Result identity: a 16-query SQL-level battery (single/multi-token, exact display number, client, entity, category label, apostrophe, LIKE metacharacters, no-match) returns byte-identical rows old vs new.

Alternatives rejected

• Per-token EXISTS on matter_drive_folder instead of the LEFT JOIN (drops GROUP BY): reintroduces correlated per-row subplans; measured cost 1.4M–4.7M — JIT fires again. Kept the original join + GROUP BY shape.
• Only deduplicating the count subqueries (once per row instead of 4×): leaves ~960k estimated cost at one token — still past both JIT thresholds; latency stays in seconds.
• jit = off alone, no rewrite: leaves the ~340ms+ per-call residual subplan work and keeps a fragile multi-million cost estimate. Both halves are needed.
• New composite index on matter_drive_folder (organization_id, clio_matter_id): would help the old correlated subplan, but schema is reserved by NOS-209 this wave and the CTE pre-aggregation makes it unnecessary at this corpus size. Filed as a follow-up note on NOS-210 instead.

Gates

• Byte-identical tool output vs the current implementation on the real corpus (15-query tool-level battery: same rows, same order, same payload; plus a 16-query SQL-level row comparison).
• Before/after EXPLAIN (ANALYZE, BUFFERS) as app_role with RLS GUCs in the PR body.
• Before/after tool-level latency probe in the PR body.
• packages/agent-runtime unit tests, bun run lint, bun run typecheck.