You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 3bebbe4
Browse filesBrowse the repository at this point in the historyBrowse files
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]>
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, ",
261
287
);
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 = ");
264
294
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");
266
298
267
299
// Search is a current-state surface; old artifact snapshots cannot match.
268
300
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))");
0 commit comments