Skip to content

Commit 3bebbe4

Browse files
salman1993claude
andcommitted
perf(search): rank only the newest prefix matches and plan with the real tsquery
The search tsquery was built inside a LATERAL subquery, so the planner saw an InitPlan and assumed the default 0.5% selectivity for `@@`. Short typeahead prefixes match most of a community, so every search collected and ranked every match before LIMIT (322 ms for `a:*` on 1M rows). Resolve the tsquery in its own query and bind it, run the search as a one-shot statement so each run is planned for its value, and have prefix-mode relevance rank only the newest 1000 matches. Postgres then reads each partition's primary key backward and stops at the window (0.7 ms for `a:*`), while rarer terms keep the GIN bitmap plan with an accurate estimate. Full-text and profile search still rank every match. Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Salman Mohammed <[email protected]>
1 parent 949f61d commit 3bebbe4

2 files changed

Lines changed: 133 additions & 13 deletions

File tree

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

Lines changed: 54 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -149,6 +149,13 @@ const SEARCH_TEXT_MAX_CHARS: usize = 4096;
149149
/// bridge uses page 1), but clamp here too so a future caller cannot accidentally
150150
/// wire untrusted input into a multi-trillion-row OFFSET.
151151
const PAGE_MAX: u32 = 1000;
152+
/// How many of the newest matches a prefix-mode relevance search ranks.
153+
/// The GIN index returns matches in no useful order, so ranking every match
154+
/// before `LIMIT` costs time proportional to the match count, and a short
155+
/// typeahead prefix matches most of a community. Reading candidates newest
156+
/// first lets Postgres stop at this many instead. Every page ranks the same
157+
/// window, so pages stay consistent and end with it.
158+
const PREFIX_RANK_WINDOW: i64 = 1000;
152159

153160
fn push_tsquery(qb: &mut QueryBuilder<sqlx::Postgres>, mode: SearchMode, search_text: &str) {
154161
match mode {
@@ -210,15 +217,18 @@ fn normalized_search_text(q: &str) -> Option<String> {
210217

211218
/// Execute a community-scoped FTS query.
212219
///
213-
/// SQL shape (always):
220+
/// The mode-specific tsquery is resolved first and bound as a value, so the
221+
/// planner estimates its selectivity instead of assuming a rare match. The
222+
/// search itself is a one-shot statement, planned for that value every time.
223+
///
224+
/// SQL shape:
214225
/// ```sql
215226
/// SELECT id, kind, pubkey, channel_id, EXTRACT(EPOCH FROM created_at)::bigint AS created_at_s,
216-
/// ts_rank_cd(search_tsv, query) AS rank
217-
/// FROM events,
218-
/// <mode-specific tsquery> AS query
227+
/// ts_rank_cd(search_tsv, $q) AS rank
228+
/// FROM events -- prefix relevance: the newest PREFIX_RANK_WINDOW matches
219229
/// WHERE community_id = $ctx
220230
/// AND deleted_at IS NULL
221-
/// AND search_tsv @@ query
231+
/// AND search_tsv @@ $q
222232
/// [+ channel scope, kinds, authors, since, until]
223233
/// ORDER BY rank DESC, created_at DESC, id -- SearchSort::Newest: created_at DESC, id
224234
/// LIMIT $per_page OFFSET (($page - 1) * $per_page)
@@ -248,21 +258,43 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
248258
// short exact display name off the bounded first page. Keep the same result
249259
// set and pagination contract, but put rows containing the whole lexeme
250260
// first for this one narrow caller shape.
261+
let profile_only = query.kinds.as_deref() == Some(&[0][..]);
251262
let prioritize_exact_profile_lexeme = query.sort == SearchSort::Relevance
252263
&& query.mode == SearchMode::Prefix
253-
&& query.kinds.as_deref() == Some(&[0][..])
264+
&& profile_only
254265
&& search_text.chars().count() <= 2;
266+
// Profiles are few, so profile typeahead keeps ranking every match.
267+
let rank_window =
268+
(query.sort == SearchSort::Relevance && query.mode == SearchMode::Prefix && !profile_only)
269+
.then_some(PREFIX_RANK_WINDOW);
270+
271+
let mut resolve: QueryBuilder<sqlx::Postgres> = QueryBuilder::new("SELECT (");
272+
push_tsquery(&mut resolve, query.mode, &search_text);
273+
resolve.push(")::text");
274+
let tsquery: String = resolve.build_query_scalar().fetch_one(pool).await?;
275+
if tsquery.is_empty() {
276+
// Nothing searchable survived normalization; an empty tsquery matches nothing.
277+
return Ok(SearchResult {
278+
hits: Vec::new(),
279+
page,
280+
});
281+
}
255282

256283
let mut qb: QueryBuilder<sqlx::Postgres> = QueryBuilder::new(
257284
"SELECT id, kind, pubkey, channel_id, \
258285
EXTRACT(EPOCH FROM created_at)::bigint AS created_at_s, \
259-
ts_rank_cd(search_tsv, search_query.query) AS rank \
260-
FROM events CROSS JOIN LATERAL (SELECT ",
286+
ts_rank_cd(search_tsv, ",
261287
);
262-
push_tsquery(&mut qb, query.mode, &search_text);
263-
qb.push(" AS query) AS search_query WHERE community_id = ");
288+
qb.push_bind(&tsquery);
289+
qb.push("::tsquery) AS rank FROM ");
290+
if rank_window.is_some() {
291+
qb.push("(SELECT id, kind, pubkey, channel_id, created_at, search_tsv FROM ");
292+
}
293+
qb.push("events WHERE community_id = ");
264294
qb.push_bind(*query.community.as_uuid());
265-
qb.push(" AND deleted_at IS NULL AND search_tsv @@ search_query.query");
295+
qb.push(" AND deleted_at IS NULL AND search_tsv @@ ");
296+
qb.push_bind(&tsquery);
297+
qb.push("::tsquery");
266298

267299
// Search is a current-state surface; old artifact snapshots cannot match.
268300
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))");
@@ -318,6 +350,14 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
318350
qb.push(")");
319351
}
320352

353+
if let Some(window) = rank_window {
354+
// Matches the backward scan of each partition's primary key, so the
355+
// newest candidates stream in order and the scan stops at `window`.
356+
qb.push(" ORDER BY created_at DESC, id DESC LIMIT ");
357+
qb.push_bind(window);
358+
qb.push(") AS candidates");
359+
}
360+
321361
if query.sort == SearchSort::Newest {
322362
qb.push(" ORDER BY created_at DESC, id LIMIT ");
323363
} else if prioritize_exact_profile_lexeme {
@@ -331,7 +371,9 @@ pub async fn search(pool: &PgPool, query: &SearchQuery) -> Result<SearchResult,
331371
qb.push(" OFFSET ");
332372
qb.push_bind(offset);
333373

334-
let rows = crate::cancel::fetch_all(pool, qb.build()).await?;
374+
// Not a cached prepared statement: after five runs Postgres may switch a
375+
// cached statement to a generic plan, which cannot see the tsquery.
376+
let rows = crate::cancel::fetch_all(pool, qb.build().persistent(false)).await?;
335377

336378
let mut hits = Vec::with_capacity(rows.len());
337379
for row in rows {

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

Lines changed: 79 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -548,6 +548,84 @@ async fn prefix_mode_preserves_storage_level_privacy_exclusions() {
548548
teardown(pool, &schema).await;
549549
}
550550

551+
/// Prefix relevance (typeahead) ranks only the newest 1000 matches, so an
552+
/// older, better-ranked message drops out, and its pages end with that
553+
/// window; full-text relevance still ranks every match.
554+
/// Mutation: rank every prefix match → the old message tops the first page.
555+
#[tokio::test]
556+
#[ignore = "requires Postgres"]
557+
async fn prefix_relevance_ranks_only_the_newest_matches() {
558+
let (pool, schema) = setup().await;
559+
let c = mk_community(&pool, "rank-window.example").await;
560+
let old = rand_bytes32();
561+
insert_event(
562+
&pool,
563+
c,
564+
old,
565+
rand_bytes32(),
566+
9,
567+
"planning planning planning planning",
568+
None,
569+
1_700_000_000,
570+
)
571+
.await;
572+
// 1000 newer, weaker matches fill the window.
573+
sqlx::query(
574+
"INSERT INTO events (community_id, id, pubkey, created_at, kind, tags, content, sig) \
575+
SELECT $1, sha256(g::text::bytea), sha256(g::text::bytea), to_timestamp(1700000001 + g), \
576+
9, '[]'::jsonb, 'planning notes ' || g, 'signature' \
577+
FROM generate_series(1, 1000) g",
578+
)
579+
.bind(c.as_uuid())
580+
.execute(&pool)
581+
.await
582+
.expect("insert newer matches");
583+
584+
let svc = SearchService::new(pool.clone());
585+
let query = |q: &str, mode, page| SearchQuery {
586+
community: c,
587+
q: q.into(),
588+
channel_scope: ChannelScope::Any,
589+
kinds: Some(vec![9]),
590+
authors: None,
591+
since: None,
592+
until: None,
593+
page,
594+
per_page: 20,
595+
mode,
596+
sort: buzz_search::SearchSort::Relevance,
597+
};
598+
let ids = |r: buzz_search::SearchResult| r.hits.iter().map(|h| h.event_id).collect::<Vec<_>>();
599+
600+
let typeahead = ids(svc
601+
.search(&query("plan", buzz_search::SearchMode::Prefix, 1))
602+
.await
603+
.expect("prefix"));
604+
assert_eq!(typeahead.len(), 20);
605+
assert!(!typeahead.contains(&old), "outside the newest-1000 window");
606+
607+
let full_text = ids(svc
608+
.search(&query("planning", buzz_search::SearchMode::FullText, 1))
609+
.await
610+
.expect("full text"));
611+
assert_eq!(full_text.first(), Some(&old), "full text ranks every match");
612+
613+
// Every page ranks the same window, so prefix pages end with it.
614+
let last = ids(svc
615+
.search(&query("plan", buzz_search::SearchMode::Prefix, 50))
616+
.await
617+
.expect("page 50"));
618+
assert_eq!(last.len(), 20);
619+
assert!(!last.contains(&old));
620+
let past = svc
621+
.search(&query("plan", buzz_search::SearchMode::Prefix, 51))
622+
.await
623+
.expect("page 51");
624+
assert!(past.hits.is_empty(), "no page past the window");
625+
626+
teardown(pool, &schema).await;
627+
}
628+
551629
#[tokio::test]
552630
#[ignore = "requires Postgres"]
553631
async fn channel_scope_restricts_results() {
@@ -1793,7 +1871,7 @@ async fn waiting_searches(pool: &PgPool) -> i64 {
17931871
sqlx::query_scalar(
17941872
"SELECT count(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() \
17951873
AND datname = current_database() \
1796-
AND wait_event_type = 'Lock' AND query LIKE '%search_query.query%'",
1874+
AND wait_event_type = 'Lock' AND query LIKE '%ts_rank_cd(%'",
17971875
)
17981876
.fetch_one(pool)
17991877
.await

0 commit comments

Comments
 (0)