Skip to content

Commit d3a4909

Browse files
salman1993claude
andauthored
perf(search): rank only the newest prefix matches and plan with the real tsquery (#8976)
## Summary Message search is one of the slowest relay queries. Short typeahead prefixes make it worst: `a:*` matches most of a community. **Why it was slow:** the tsquery was built inside a `CROSS JOIN LATERAL` subquery, so the planner saw an InitPlan and couldn't read the `search_tsv` statistics. It fell back to the default 0.5% selectivity for `@@`: for `a:*` on 1M rows it estimated 4,497 rows when 517,065 matched. Every search therefore took every GIN match, ran `ts_rank_cd` on each one, and top-N sorted them before `LIMIT 20`. This PR: 1. **Binds the real tsquery.** `push_tsquery` is unchanged. A separate `SELECT (…)::text` round trip resolves the tsquery, and the search binds it as `$q::tsquery`. An empty tsquery returns no hits without running the search. 2. **Plans each search for its own tsquery.** The search runs with `.persistent(false)`. A cached prepared statement can switch to a generic plan after five runs, and a generic plan can't see the tsquery. Forcing a generic plan lost the gain in testing. 3. **Ranks only the newest 1,000 prefix matches.** Prefix-mode relevance search ranks the newest `PREFIX_RANK_WINDOW` (1,000) matches. The inner `ORDER BY created_at DESC, id DESC LIMIT 1000` matches each partition's primary key read backwards, so for common prefixes Postgres streams the newest rows and stops at 1,000. For rarer terms it keeps the GIN bitmap plan, now with an accurate estimate. Full-text search and profile (kind:0) typeahead still rank every match. ### Behavior change: every human message search (product decision needed) Prefix mode isn't limited to keystroke typeahead. buzz-app (`useSearchMessages.ts`) and legacy desktop (`desktop/src-tauri/src/commands/messages.rs:184`) send `search_mode: "prefix"` for **all** message search. Full-text search runs only on the WS REQ path, which agents and the CLI use. So for people: - Results are the best-ranked of the newest 1,000 matches in the search's scope, not of all matches. If more than 1,000 newer messages also match, an older, more relevant message drops out of ⌘K results. The scope includes `#channel`, `from:` and date bounds, so a narrower search has the same limit within it. People have no full-text fallback. - The bridge honors `page`, and buzz-app loads later pages at 20 per page. Every page ranks the same window, so paging stays consistent, but results end after 1,000 matches (page 50). Full-text search (agents/CLI) and profile (kind:0) typeahead still rank every match. ### Perf risk: matches the backward scan has to walk far to find The planner picks the backward scan when it expects 1,000 matches near the top of the community, and it estimates that by treating each filter as independent. Two known shapes break that assumption. **Terms common in old messages but absent from recent ones.** The planner assumes matches are spread evenly over time. For a term that was common long ago but doesn't appear recently (an old project name, a former teammate), it can pick the backward primary-key scan and walk every newer row before reaching the first match. Reviewers measured this on 1.2M synthetic rows: about 2.05–2.1 s, against 0.7–1.8 s for the old query shape and 1.2 s with the GIN plan forced. The 20 s statement timeout and the #8954 cancel still bound it. The worst case is close to the old `a:*` worst case, but it now scales with community size, not match count. The planned EXPLAIN on production-shaped data will include such a term. **Terms common in channels the searcher can't read.** Human search always adds `channel_id = ANY(<readable channels>) OR channel_id IS NULL` (`build_search_channel_scope_filter`). A term that is common in private channels or other people's DMs can get a high match estimate, take the backward scan, and walk the community's rows to find a few readable matches, where the old shape used the GIN bitmap. This hasn't been reproduced yet; the production-shaped EXPLAIN will include such a term next to the old-only one. The same timeout and cancel bound it. ### Cost - One extra round trip per search to resolve the tsquery (about 0.03 ms of execution). It runs on the same pool checkout as the search (`cancel::fetch_all` takes the caller's connection), so a busy pool queues each search once. An IMMUTABLE SQL function could remove the round trip, but that needs a migration, so it's not done here. ### Related issue - Follows #8954 (cancel abandoned searches, bound the search pool) and #8964 (closed in favor of #8954). - Builds on #8947 (3-character server-side prefix minimum), merged from main. The resolve step passes its minimum. When the trailing prefix is too short, the resolved tsquery is NULL, and the search returns no hits without running the second query. - Client-side pairing: block/buzz-app#2210 (3-character last-word minimum for ⌘K message search). ## Testing **EXPLAIN ANALYZE on a synthetic 1M-row `events` table** (monthly partitions, GIN on `search_tsv`, kinds 9/40002/40008, custom plans, warm cache): | Prefix | Matches | Before | After | Plan after | |---|---|---|---|---| | `a` | 517k | 322 ms | **0.7 ms** | backward primary-key scan per partition, stops at 1,000 | | `th` | 137k | 127 ms | **1.2 ms** | backward primary-key scan, stops at 1,000 | | `a` (community with 10% of rows) | — | ~100 ms | **1–2 ms** | backward primary-key scan | | `deploy` | 8.2k | 15 ms | 5–10 ms | GIN bitmap, accurate estimate | | `d278` | 500 | 1.1 ms | 0.5 ms | GIN bitmap | | rare word | 0 | 0.07 ms | 0.03 ms | GIN bitmap | **Postgres integration tests:** - New test `prefix_relevance_ranks_only_the_newest_matches`. An old, highly relevant message drops out of prefix results behind 1,000 newer matches but still ranks first in full-text search. Prefix pages end at page 50. An old, best-ranked profile still tops profile typeahead behind 1,000 newer profile matches. Removing the window fails the test, and so does applying it to profiles. - `abandoned_search_cancels_its_statement`: the `pg_stat_activity` matcher now looks for `ts_rank_cd(`, because the old `search_query.query` text no longer exists. - Locally, each test ran against a fresh database because `cargo-nextest` isn't installed, so the hosted lane hasn't run yet. 26 of 27 pass. `artifact_search_uses_only_live_current_head` also fails on unmodified main in that local setup, so the failure isn't from this change. **Checks:** `cargo fmt --check` and `cargo clippy -p buzz-search --all-targets -D warnings` are clean. **Not yet done:** - hosted CI; - a manual check against a local relay with ⌘K typeahead; - EXPLAIN against production-shaped data, including a term common only in old messages and a term common only in channels the searcher can't read. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Signed-off-by: Salman Mohammed <[email protected]> Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent 3f03aa0 commit d3a4909

4 files changed

Lines changed: 177 additions & 26 deletions

File tree

‎ARCHITECTURE.md‎

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -582,12 +582,16 @@ re-authorizes every candidate hit before returning it.
582582
**Key behaviors:**
583583
- `SearchService::new(pool)` wraps a `PgPool`; `search(&SearchQuery)` runs a
584584
parameterized FTS query against the `events.search_tsv` GIN index and returns
585-
`SearchResult` (candidate `SearchHit`s).
585+
`SearchResult` (candidate `SearchHit`s). Prefix-mode relevance (outside
586+
profile search) ranks only the newest 1000 matches, which Postgres may read
587+
from the primary key backward instead of the GIN index.
586588
- `ChannelScope` makes the channel constraint explicit (`Any` /
587589
`ChannelLessOnly` / `Channels` / `ChannelsOrChannelLess`), closing the
588590
ambiguity the old `Option<Vec<Uuid>> + bool` matrix could not express.
589-
- Every query carries `community_id`; the FTS predicate is BitmapAnd-ed with
590-
the community-leading btree filters so a query never crosses tenants.
591+
- Every query carries `community_id`, and every access path leads with it (the
592+
FTS predicate BitmapAnd-ed with community-leading btree filters, or the
593+
`(community_id, created_at, id)` primary key read backward), so a query never
594+
crosses tenants.
591595
- Permission filtering is **caller's responsibility** — `buzz-search` returns
592596
candidate hits; the relay re-authorizes each one (channel membership, `#p`,
593597
owner gates) before delivering it.
@@ -959,7 +963,10 @@ kinds via `CASE WHEN kind IN (1059, 30300, 30622) THEN NULL`, so those rows are
959963
storage-level unsearchable (a `NULL` tsvector never matches `@@`). A GIN index
960964
(`idx_events_search_tsv`) backs the `@@` probe; in multi-community mode the
961965
community-leading btree filters BitmapAnd with the GIN probe so every query is
962-
fenced to its `community_id`.
966+
fenced to its `community_id`. Prefix-mode relevance ranks only the newest 1000
967+
matches; for a common prefix Postgres reads the `(community_id, created_at, id)`
968+
primary key backward and stops there, still fenced by its leading
969+
`community_id`.
963970
964971
---
965972

‎crates/buzz-search/src/cancel.rs‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -57,13 +57,15 @@ pub const CANCEL_SQL: &str = concat!(
5757
" = $2"
5858
);
5959

60-
/// Run `query` on a connection from `pool`, cancelling its statement on the
61-
/// server if the returned future is dropped before it completes.
60+
/// Run `query` on `conn`, a connection from `pool`, cancelling its statement
61+
/// on the server if the returned future is dropped before it completes.
62+
/// Callers pass the connection so earlier steps of the same search reuse one
63+
/// pool checkout.
6264
pub(crate) async fn fetch_all(
6365
pool: &PgPool,
66+
mut conn: PoolConnection<Postgres>,
6467
query: Query<'_, Postgres, PgArguments>,
6568
) -> Result<Vec<PgRow>, sqlx::Error> {
66-
let mut conn = pool.acquire().await?;
6769
let backend: (i32, i64) = sqlx::query_as(BACKEND_SQL).fetch_one(&mut *conn).await?;
6870
let mut armed = CancelOnDrop {
6971
pool: pool.clone(),

‎crates/buzz-search/src/query.rs‎

Lines changed: 65 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -62,17 +62,20 @@ pub enum SearchMode {
6262
/// Prefix-match the trailing normalized query token (`pro` matches `project`).
6363
///
6464
/// Intended for bounded typeahead surfaces such as the desktop topbar. The
65-
/// relay still refetches and re-authorizes every hit; this mode changes only
66-
/// the candidate tsquery, not the access boundary. A trailing token that
67-
/// yields a lexeme shorter than three characters returns no hits unless the
68-
/// query is kind:0-only (see `PREFIX_MIN_CHARS`).
65+
/// relay still refetches and re-authorizes every hit; this mode changes the
66+
/// candidate tsquery and, for relevance outside profile search, ranks only
67+
/// the newest 1000 matches. It does not change the access boundary. A
68+
/// trailing token that yields a lexeme shorter than three characters
69+
/// returns no hits unless the query is kind:0-only (see `PREFIX_MIN_CHARS`).
6970
Prefix,
7071
}
7172

7273
/// Result ordering for a search.
7374
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
7475
pub enum SearchSort {
75-
/// `ts_rank_cd` relevance, newest first among equal ranks.
76+
/// `ts_rank_cd` relevance, newest first among equal ranks. In
77+
/// [`SearchMode::Prefix`] outside profile search, only the newest 1000
78+
/// matches are ranked, so pages end with them.
7679
Relevance,
7780
/// Newest first, ignoring relevance; ties ordered by event id.
7881
Newest,
@@ -151,6 +154,13 @@ const SEARCH_TEXT_MAX_CHARS: usize = 4096;
151154
/// bridge uses page 1), but clamp here too so a future caller cannot accidentally
152155
/// wire untrusted input into a multi-trillion-row OFFSET.
153156
const PAGE_MAX: u32 = 1000;
157+
/// How many of the newest matches a prefix-mode relevance search ranks.
158+
/// The GIN index returns matches in no useful order, so ranking every match
159+
/// before `LIMIT` costs time proportional to the match count, and a short
160+
/// typeahead prefix matches most of a community. Reading candidates newest
161+
/// first lets Postgres stop at this many instead. Every page ranks the same
162+
/// window, so pages stay consistent and end with it.
163+
const PREFIX_RANK_WINDOW: i64 = 1000;
154164
/// Shortest lexeme [`SearchMode::Prefix`] gives `:*` for anything but a
155165
/// kind:0-only (profile) query. If the trailing token yields a shorter one,
156166
/// the tsquery is NULL and the search returns no hits, whatever completed
@@ -236,15 +246,18 @@ fn normalized_search_text(q: &str) -> Option<String> {
236246

237247
/// Execute a community-scoped FTS query.
238248
///
239-
/// SQL shape (always):
249+
/// The mode-specific tsquery is resolved first and bound as a value, so the
250+
/// planner estimates its selectivity instead of assuming a rare match. The
251+
/// search itself is a one-shot statement, planned for that value every time.
252+
///
253+
/// SQL shape:
240254
/// ```sql
241255
/// SELECT id, kind, pubkey, channel_id, EXTRACT(EPOCH FROM created_at)::bigint AS created_at_s,
242-
/// ts_rank_cd(search_tsv, query) AS rank
243-
/// FROM events,
244-
/// <mode-specific tsquery> AS query
256+
/// ts_rank_cd(search_tsv, $q) AS rank
257+
/// FROM events -- prefix relevance: the newest PREFIX_RANK_WINDOW matches
245258
/// WHERE community_id = $ctx
246259
/// AND deleted_at IS NULL
247-
/// AND search_tsv @@ query
260+
/// AND search_tsv @@ $q
248261
/// [+ channel scope, kinds, authors, since, until]
249262
/// ORDER BY rank DESC, created_at DESC, id -- SearchSort::Newest: created_at DESC, id
250263
/// LIMIT $per_page OFFSET (($page - 1) * $per_page)
@@ -279,18 +292,42 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
279292
&& query.mode == SearchMode::Prefix
280293
&& profile_only
281294
&& search_text.chars().count() <= 2;
295+
// Profiles are few, so profile typeahead keeps ranking every match.
296+
let rank_window =
297+
(query.sort == SearchSort::Relevance && query.mode == SearchMode::Prefix && !profile_only)
298+
.then_some(PREFIX_RANK_WINDOW);
299+
300+
let min_prefix_chars = if profile_only { 0 } else { PREFIX_MIN_CHARS };
301+
// One checkout for both statements, so a busy pool queues a search once.
302+
let mut conn = pool.acquire().await?;
303+
let mut resolve: QueryBuilder<sqlx::Postgres> = QueryBuilder::new("SELECT (");
304+
push_tsquery(&mut resolve, query.mode, &search_text, min_prefix_chars);
305+
resolve.push(")::text");
306+
let tsquery: Option<String> = resolve.build_query_scalar().fetch_one(&mut *conn).await?;
307+
let Some(tsquery) = tsquery.filter(|tsquery| !tsquery.is_empty()) else {
308+
// A too-short trailing prefix (NULL) or nothing searchable after
309+
// normalization (empty) matches nothing.
310+
return Ok(SearchResult {
311+
hits: Vec::new(),
312+
page,
313+
});
314+
};
282315

283316
let mut qb: QueryBuilder<sqlx::Postgres> = QueryBuilder::new(
284317
"SELECT id, kind, pubkey, channel_id, \
285318
EXTRACT(EPOCH FROM created_at)::bigint AS created_at_s, \
286-
ts_rank_cd(search_tsv, search_query.query) AS rank \
287-
FROM events CROSS JOIN LATERAL (SELECT ",
319+
ts_rank_cd(search_tsv, ",
288320
);
289-
let min_prefix_chars = if profile_only { 0 } else { PREFIX_MIN_CHARS };
290-
push_tsquery(&mut qb, query.mode, &search_text, min_prefix_chars);
291-
qb.push(" AS query) AS search_query WHERE community_id = ");
321+
qb.push_bind(&tsquery);
322+
qb.push("::tsquery) AS rank FROM ");
323+
if rank_window.is_some() {
324+
qb.push("(SELECT id, kind, pubkey, channel_id, created_at, search_tsv FROM ");
325+
}
326+
qb.push("events WHERE community_id = ");
292327
qb.push_bind(*query.community.as_uuid());
293-
qb.push(" AND deleted_at IS NULL AND search_tsv @@ search_query.query");
328+
qb.push(" AND deleted_at IS NULL AND search_tsv @@ ");
329+
qb.push_bind(&tsquery);
330+
qb.push("::tsquery");
294331

295332
// Search is a current-state surface; old artifact snapshots cannot match.
296333
qb.push(" AND (events.kind <> 45010 OR EXISTS (SELECT 1 FROM artifact_heads ah WHERE ah.community_id=events.community_id AND ah.event_id=events.id AND NOT ah.deleted))");
@@ -346,6 +383,14 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
346383
qb.push(")");
347384
}
348385

386+
if let Some(window) = rank_window {
387+
// Matches the backward scan of each partition's primary key, so the
388+
// newest candidates stream in order and the scan stops at `window`.
389+
qb.push(" ORDER BY created_at DESC, id DESC LIMIT ");
390+
qb.push_bind(window);
391+
qb.push(") AS candidates");
392+
}
393+
349394
if query.sort == SearchSort::Newest {
350395
qb.push(" ORDER BY created_at DESC, id LIMIT ");
351396
} else if prioritize_exact_profile_lexeme {
@@ -359,7 +404,9 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
359404
qb.push(" OFFSET ");
360405
qb.push_bind(offset);
361406

362-
let rows = crate::cancel::fetch_all(pool, qb.build()).await?;
407+
// Not a cached prepared statement: after five runs Postgres may switch a
408+
// cached statement to a generic plan, which cannot see the tsquery.
409+
let rows = crate::cancel::fetch_all(pool, conn, qb.build().persistent(false)).await?;
363410

364411
let mut hits = Vec::with_capacity(rows.len());
365412
for row in rows {
@@ -477,7 +524,7 @@ pub async fn search_profile_names(
477524
qb.push(" OFFSET ");
478525
qb.push_bind(offset);
479526

480-
let rows = crate::cancel::fetch_all(pool, qb.build()).await?;
527+
let rows = crate::cancel::fetch_all(pool, pool.acquire().await?, qb.build()).await?;
481528
let mut hits = Vec::with_capacity(rows.len());
482529
for row in rows {
483530
let id_bytes: Vec<u8> = row.try_get("id")?;

‎crates/buzz-search/tests/postgres_fts_integration.rs‎

Lines changed: 96 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -745,6 +745,101 @@ async fn prefix_mode_preserves_storage_level_privacy_exclusions() {
745745
teardown(pool, &schema).await;
746746
}
747747

748+
/// Prefix relevance (typeahead) ranks only the newest 1000 matches, so an
749+
/// older, better-ranked message drops out, and its pages end with that
750+
/// window; full-text and profile relevance still rank every match.
751+
/// Mutation: rank every prefix match → the old message tops the first page;
752+
/// window profiles too → the old profile drops off the first page.
753+
#[tokio::test]
754+
#[ignore = "requires Postgres"]
755+
async fn prefix_relevance_ranks_only_the_newest_matches() {
756+
let (pool, schema) = setup().await;
757+
let c = mk_community(&pool, "rank-window.example").await;
758+
let (old, old_profile) = (rand_bytes32(), rand_bytes32());
759+
for (id, kind) in [(old, 9), (old_profile, 0)] {
760+
insert_event(
761+
&pool,
762+
c,
763+
id,
764+
rand_bytes32(),
765+
kind,
766+
"planning planning planning planning",
767+
None,
768+
1_700_000_000,
769+
)
770+
.await;
771+
// 1000 newer, weaker matches fill the window.
772+
sqlx::query(
773+
"INSERT INTO events (community_id, id, pubkey, created_at, kind, tags, content, sig) \
774+
SELECT $1, sha256((g || '/' || $2)::bytea), sha256((g || '/' || $2)::bytea), \
775+
to_timestamp(1700000001 + g), $2, '[]'::jsonb, 'planning notes ' || g, 'signature' \
776+
FROM generate_series(1, 1000) g",
777+
)
778+
.bind(c.as_uuid())
779+
.bind(kind)
780+
.execute(&pool)
781+
.await
782+
.expect("insert newer matches");
783+
}
784+
785+
let svc = SearchService::new(pool.clone());
786+
let query = |q: &str, mode, page| SearchQuery {
787+
community: c,
788+
q: q.into(),
789+
channel_scope: ChannelScope::Any,
790+
kinds: Some(vec![9]),
791+
authors: None,
792+
since: None,
793+
until: None,
794+
page,
795+
per_page: 20,
796+
mode,
797+
sort: buzz_search::SearchSort::Relevance,
798+
};
799+
let ids = |r: buzz_search::SearchResult| r.hits.iter().map(|h| h.event_id).collect::<Vec<_>>();
800+
801+
let typeahead = ids(svc
802+
.search(&query("plan", buzz_search::SearchMode::Prefix, 1))
803+
.await
804+
.expect("prefix"));
805+
assert_eq!(typeahead.len(), 20);
806+
assert!(!typeahead.contains(&old), "outside the newest-1000 window");
807+
808+
let full_text = ids(svc
809+
.search(&query("planning", buzz_search::SearchMode::FullText, 1))
810+
.await
811+
.expect("full text"));
812+
assert_eq!(full_text.first(), Some(&old), "full text ranks every match");
813+
814+
// Every page ranks the same window, so prefix pages end with it.
815+
let last = ids(svc
816+
.search(&query("plan", buzz_search::SearchMode::Prefix, 50))
817+
.await
818+
.expect("page 50"));
819+
assert_eq!(last.len(), 20);
820+
assert!(!last.contains(&old));
821+
let past = svc
822+
.search(&query("plan", buzz_search::SearchMode::Prefix, 51))
823+
.await
824+
.expect("page 51");
825+
assert!(past.hits.is_empty(), "no page past the window");
826+
827+
let profiles = ids(svc
828+
.search(&SearchQuery {
829+
kinds: Some(vec![0]),
830+
..query("plan", buzz_search::SearchMode::Prefix, 1)
831+
})
832+
.await
833+
.expect("profile prefix"));
834+
assert_eq!(
835+
profiles.first(),
836+
Some(&old_profile),
837+
"profile typeahead ranks every match"
838+
);
839+
840+
teardown(pool, &schema).await;
841+
}
842+
748843
#[tokio::test]
749844
#[ignore = "requires Postgres"]
750845
async fn channel_scope_restricts_results() {
@@ -1990,7 +2085,7 @@ async fn waiting_searches(pool: &PgPool) -> i64 {
19902085
sqlx::query_scalar(
19912086
"SELECT count(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() \
19922087
AND datname = current_database() \
1993-
AND wait_event_type = 'Lock' AND query LIKE '%search_query.query%'",
2088+
AND wait_event_type = 'Lock' AND query LIKE '%ts_rank_cd(%'",
19942089
)
19952090
.fetch_one(pool)
19962091
.await

0 commit comments

Comments
 (0)