Severity: High
search_tweets / search_tweets_exact_phrase (070_functions.sql:2417-2566) filter fts @@ to_tsquery(...) but ORDER BY created_at DESC OFFSET .. LIMIT ... No single plan is good for both rare terms (walks the created_at index scanning most of the table) and common terms (GIN bitmap + full sort) — which is why it needs SET statement_timeout TO '5min'. OFFSET makes "Load More" progressively slower. Each public search can legally occupy a DB worker up to 5 min; a few concurrent slow searches exhaust the pool (the UI already handles "Search query timed out", tweetQueries.ts:158-160).
Fix: cap timeout at 5-15s; keyset pagination (created_at < last_seen) instead of OFFSET; split into plan-stable variants (rank-ordered text search vs filter-only recency); consider ts_rank ordering so the GIN path is always chosen.
Filed from a code-quality/security/performance/robustness review. File:line refs verified against main.
Severity: High
search_tweets/search_tweets_exact_phrase(070_functions.sql:2417-2566) filterfts @@ to_tsquery(...)butORDER BY created_at DESC OFFSET .. LIMIT ... No single plan is good for both rare terms (walks the created_at index scanning most of the table) and common terms (GIN bitmap + full sort) — which is why it needsSET statement_timeout TO '5min'.OFFSETmakes "Load More" progressively slower. Each public search can legally occupy a DB worker up to 5 min; a few concurrent slow searches exhaust the pool (the UI already handles "Search query timed out",tweetQueries.ts:158-160).Fix: cap timeout at 5-15s; keyset pagination (
created_at < last_seen) instead of OFFSET; split into plan-stable variants (rank-ordered text search vs filter-only recency); considerts_rankordering so the GIN path is always chosen.Filed from a code-quality/security/performance/robustness review. File:line refs verified against
main.