perf(search): rank only the newest prefix matches and plan with the real tsquery by salman1993 · Pull Request #8976 · block/buzz

salman1993 · 2026-11-03T00:17:46Z

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:

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.
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.
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 fix(search): cancel abandoned searches and bound the search pool #8954 (cancel abandoned searches, bound the search pool) and fix(search): bound every search statement with the read deadline #8964 (closed in favor of fix(search): cancel abandoned searches and bound the search pool #8954).
Builds on fix(search): skip message prefix search below three characters #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: fix(search): require a three-character last word for message search 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

…eal 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]>

loganj

🤖 Larry and Auto: an independent adversarial review of 3bebbe4. Source review only, plus EXPLAIN ANALYZE on synthetic Postgres 17 data (no relay or app run).

Verdict: COMMENT, not APPROVE, for one reason. We found no code defects. The open item is a product decision (item 1 below): the 1,000-match window applies to every message search that people run, not only to keystroke typeahead. Logan owns that decision, so this review does not approve for him yet.

1. question (product), must fix in the PR body: the window applies to all human message search. buzz-app always sends search_mode: "prefix" for message search (src/app/shell/useSearchMessages.ts, on main). Legacy desktop does the same (desktop/src-tauri/src/commands/messages.rs:184). No client has a full-text message path; full text runs only on the WS REQ path (handlers/req.rs), which agents and the CLI use. So "full-text search still finds it" is not true for people. An older, more relevant message drops out of ⌘K results. It also drops out of channel-scoped, from: and date-bounded searches whenever that scope has more than 1,000 newer matches.
Also: "The NIP-50 bridge only requests page 1 for prefix mode" is not correct. The bridge honors page (api/bridge.rs), and buzz-app loads page 2 and later at 20 per page. Results now stop at 1,000 (page 50). Paging stays consistent, but please correct the body.

2. convention (public API docs). SearchMode::Prefix (query.rs:62-67) still says "this mode changes only the candidate tsquery". It now also limits candidates to the newest 1,000 matches. SearchSort::Relevance should say the same for prefix mode.

3. advice (perf risk). Inline on the window ORDER BY. A term that is common in old messages but absent from recent ones makes the planner choose the backward primary-key scan, which reads every newer row. We each measured this independently. The worst case is now about the size of the community, not the number of matches.

4. advice (tests). No test covers the !profile_only exemption. If you remove it, all tests still pass. Inline.

5. advice (pool). The resolve step takes a second pool checkout. Inline.

6. preference. rank_window: Option<i64> is only a flag around a constant. Inline.

Checked and found correct:

Filters. All filters are inside candidates before the window: community, deleted, artifact-head EXISTS, channel scope, kinds, authors, since and until. The outer query uses only projected columns.
tsquery round trip. (q::text)::tsquery = q held for 20 hostile inputs in both modes. Punctuation-only input returns '', which takes the early return.
Custom plan. A persistent(false) unnamed statement folds the bound tsquery to a constant, so the planner uses per-partition statistics.
Index direction. Reading the PK (community_id, created_at, id) backwards gives exactly created_at DESC, id DESC, as an ordered Append that never executes the older partitions.
Speedups. Common prefix: 2.8–8.3 s became 8–18 ms. A rare prefix and a single-channel #h scope still use the GIN bitmap.
Test. The new test fails without the window. The ts_rank_cd( matcher in pg_stat_activity matches only the search statements and excludes its own pid.
Suite. buzz-search at this head, each test in a fresh database: 26 of 27 pass. artifact_search_uses_only_live_current_head fails the same way at the merge base 949f61d.
Other callers. WS REQ (full text) and profile typeahead (kinds: [0]) are unchanged.
Pre-existing, out of scope. quote_literal returns E'...' for a lexeme that contains a backslash, and that query can never match. This PR does not change that behavior, and we found no real input where the simple parser emits such a lexeme.

Document on SearchMode::Prefix and SearchSort::Relevance that prefix relevance outside profile search ranks only the newest 1000 matches, and extend the window test so an old, best-ranked profile must still top profile typeahead. Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Salman Mohammed <[email protected]>

loganj

🤖 Larry and Auto: approving 3bebbe4. Logan confirmed the product trade: the newest-1,000 window applies only to message search (kinds 9, 40002, 40008, including all human message search from ⌘K, #h, from: and date-bounded searches). Profiles and channel names are not affected. The findings in our earlier review stay as non-blocking: please correct the PR description (paging, and no full-text fallback for people), update the SearchMode::Prefix doc comment, and include an old-only term in the planned EXPLAIN on production-shaped data.

Resolve conflicts with #8947: the tsquery resolve step now passes the prefix minimum, and a NULL tsquery (trailing prefix too short) returns no hits like an empty one instead of failing to decode. Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Salman Mohammed <[email protected]>

chatgpt-codex-connector · 2026-11-03T16:38:48Z

Codex Review Summary

This comment shows the latest Codex review activity on this pull request.

Review	Status	Commit	Review trigger
📝 Code Review	✅ Completed 2026-11-03T16:42:12.134979Z	`ff394bf`	Draft marked ready

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

Open a pull request for review
Mark a draft as ready
Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

chatgpt-codex-connector

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: ff394bf9b1

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

Open a pull request for review
Mark a draft as ready
Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

github-actions · 2026-11-03T16:42:40Z

🔐 Codex Security Review

Note: This is an automated, security-focused review generated by Codex.
Use it as a supplement to human review; false positives are possible.

Scope

Exact PR diff: 0e5a69962e5cbe5e81ea46b557770ce27a120a14...d14f256d730bdd56915eabec702e257eea8f8889

Model: gpt-6.1-sol

💡 Click "edited" above to see earlier reviews for this PR.

Review Summary

Overall Risk: NONE

No concrete security, correctness, or reliability regressions found in the authorized PR range.

Findings

No concrete security, correctness, or reliability findings were identified.

Notes

Review used read-only source and history inspection. Builds, tests, and database query plans were not executed.

_{Generated by Codex Security Review |

Requested by: @salman1993 |

Workflow run}

The resolve step checked out a pool connection, returned it, and the search checked out another, so on a saturated search pool each search queued twice and could wait two acquire timeouts before failing. cancel::fetch_all now takes the caller's connection, and search() runs both statements on one checkout. Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Salman Mohammed <[email protected]>

salman1993 · 2026-11-03T16:54:31Z

🤖 On the product question about the newest-1,000 window: Salman and I agree that capping human message search makes sense. Below is why we think 1,000 is a reasonable cap, and a possible follow-up if it needs revisiting.

Why 1,000 is reasonable

The cap only matters for common terms. A specific word like rollback has few enough matches that 1,000 can reach back months or years, so nothing is cut. The cap only applies to terms like a, th or deploy, which say little about relevance, so ranking every match is barely better than ranking the recent ones. Multi-word queries match fewer messages, so they hit the cap less often.
Cost barely depends on the size. Ranking 1,000 rows takes about 0.7 ms, and 3,000–5,000 would still take a few ms. The worst case Larry and Auto measured, a term common only in old messages, comes from reading far back to fill the window at all, not from the window being large. So the number should be tuned from data, not guessed.
Paging: 1,000 matches is 50 pages at 20 per page.

Constraint for any future tuning

The cap is cheap only because "newest first" matches an index order. Postgres reads each partition's primary key backwards and stops at 1,000. Picking candidates by a computed score (author affinity, channel membership) would make Postgres score every match before choosing, which brings back the original O(matches) cost. Personal signals should therefore come in one of these two ways, not as a score for choosing candidates.

Possible follow-up (only if we need to revisit)

Re-rank inside the window (cheap). Score = ts_rank_cd plus boosts for the searcher: their own messages or people they DM, channels they're a member of (as opposed to just able to read), and recency decay. This is arithmetic on at most 1,000 rows. It needs the searcher's identity and member channels passed into buzz-search, which today only receives the authorized channel scope.
Combine several candidate pools. Take the union of "newest 1,000 overall", "newest N in my channels" and "newest N from authors I talk to", remove duplicates, then re-rank. Each pool still stops early because the indexes already exist: idx_events_community_channel_created (community_id, channel_id, created_at DESC, id) and idx_events_community_pubkey_kind_created (community_id, pubkey, kind, created_at DESC, id). This is the only way an older message from a place the searcher cares about gets back in; re-ranking alone can't do that.

Proposed path

Land this PR as is (newest 1,000), since it fixes the slow query.
Add a metric for how often the window fills and how far back the 1,000th candidate goes. That shows whether the cap cuts off anything people would care about, before we tune it.
Treat affinity as a follow-up: re-ranking first, and extra pools only if the metric shows the window regularly cuts off fairly recent messages in the searcher's own channels.

Separate "does not change the access boundary" from the rank window in the SearchMode::Prefix docs, and note in ARCHITECTURE.md that prefix relevance may read the community-leading primary key backward, which still fences every query to its community. Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Salman Mohammed <[email protected]>

TheSentinel454

🤖 Approving d14f256. Both of my earlier comments are addressed. The PR body now covers the channel-correlated perf risk and lists it for the production-shaped EXPLAIN. The docs fix updates the SearchMode::Prefix wording and both ARCHITECTURE.md buzz-search sections. The delta since 8fc1249 is docs-only. CI is green at this head, including both buzz-search Postgres tests this PR adds or changes.

salman1993 mentioned this pull request Nov 3, 2026

fix(search): skip message prefix search below three characters #8947

Merged

loganj reviewed Nov 3, 2026

View reviewed changes

Comment thread crates/buzz-search/src/query.rs Resolved

Comment thread crates/buzz-search/src/query.rs Resolved

Comment thread crates/buzz-search/src/query.rs Resolved

Comment thread crates/buzz-search/src/query.rs Resolved

loganj approved these changes Nov 3, 2026

View reviewed changes

salman1993 marked this pull request as ready for review November 3, 2026 16:38

salman1993 requested a review from a team as a code owner November 3, 2026 16:38

salman1993 requested a review from TheSentinel454 November 3, 2026 16:38

salman1993 temporarily deployed to codex-review November 3, 2026 16:39 — with GitHub Actions Inactive

chatgpt-codex-connector Bot reviewed Nov 3, 2026

View reviewed changes

Comment thread crates/buzz-search/src/query.rs Resolved

github-actions Bot added the codex-security-review-current The posted Codex security review matches its recorded range. label Nov 3, 2026

salman1993 temporarily deployed to codex-review November 3, 2026 16:49 — with GitHub Actions Inactive

github-actions Bot removed the codex-security-review-current The posted Codex security review matches its recorded range. label Nov 3, 2026

github-actions Bot added the codex-security-review-current The posted Codex security review matches its recorded range. label Nov 3, 2026

TheSentinel454 reviewed Nov 3, 2026

View reviewed changes

Comment thread crates/buzz-search/src/query.rs Resolved

Comment thread crates/buzz-search/src/query.rs Resolved

salman1993 temporarily deployed to codex-review November 3, 2026 17:48 — with GitHub Actions Inactive

github-actions Bot added codex-security-review-current The posted Codex security review matches its recorded range. and removed codex-security-review-current The posted Codex security review matches its recorded range. labels Nov 3, 2026

salman1993 requested a review from TheSentinel454 November 3, 2026 17:51

salman1993 enabled auto-merge (squash) November 3, 2026 18:25

TheSentinel454 approved these changes Nov 3, 2026

View reviewed changes

salman1993 merged commit d3a4909 into main Nov 3, 2026
90 checks passed

salman1993 deleted the sm/search-newest-candidates branch November 3, 2026 18:25

Navigation Menu

Repository navigation

perf(search): rank only the newest prefix matches and plan with the real tsquery - #8976

perf(search): rank only the newest prefix matches and plan with the real tsquery#8976
salman1993 merged 5 commits into
mainfrom
sm/search-newest-candidates

salman1993 commented Nov 3, 2026 •

edited

Loading

Uh oh!

loganj left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

loganj left a comment

Uh oh!

chatgpt-codex-connector Bot commented Nov 3, 2026 •

edited

Loading

Uh oh!

chatgpt-codex-connector Bot left a comment

Uh oh!

Uh oh!

github-actions Bot commented Nov 3, 2026 •

edited

Loading

Uh oh!

salman1993 commented Nov 3, 2026

Uh oh!

Uh oh!

Uh oh!

TheSentinel454 left a comment

Uh oh!

Uh oh!

This branch was previously deployed

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

salman1993 commented Nov 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Behavior change: every human message search (product decision needed)

Perf risk: matches the backward scan has to walk far to find

Cost

Related issue

Testing

Uh oh!

loganj left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

loganj left a comment

Choose a reason for hiding this comment

Uh oh!

chatgpt-codex-connector Bot commented Nov 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codex Review Summary

Uh oh!

chatgpt-codex-connector Bot left a comment

Choose a reason for hiding this comment

💡 Codex Review

Uh oh!

Uh oh!

github-actions Bot commented Nov 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

🔐 Codex Security Review

Review Summary

Findings

Notes

Uh oh!

salman1993 commented Nov 3, 2026

Why 1,000 is reasonable

Constraint for any future tuning

Possible follow-up (only if we need to revisit)

Proposed path

Uh oh!

Uh oh!

Uh oh!

TheSentinel454 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

This branch was previously deployed

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

salman1993 commented Nov 3, 2026 •

edited

Loading

chatgpt-codex-connector Bot commented Nov 3, 2026 •

edited

Loading

github-actions Bot commented Nov 3, 2026 •

edited

Loading