### Summary `SearchController#autocomplete` times out on `recent_issues_autocomplete` for common search terms — the recent-items code path routes through `IssuesFinder`'s PG full-text search against the partitioned `issue_search_data` table without a partition-pruning predicate, so PG ends up scanning all 64 partitions for a query that operates on at most 100 known IDs. Sentry shows a `PG::QueryCanceled` with `db_duration_s = 15.014` for params `filter=generic`, `term="terri chu"`. Captured `EXPLAIN (ANALYZE, BUFFERS)` against postgres.ai confirms a single partitioned tsvector scan can burn over 10 seconds before the join with `issues` discards everything. ### Stack trace ``` PG::QueryCanceled: ERROR: canceling statement due to statement timeout active_record/connection_adapters/postgresql_adapter.rb:899:in `exec_params' … app/helpers/search_helper.rb:533:in `recent_issues_autocomplete' app/helpers/search_helper.rb:74:in `recent_items_autocomplete' app/helpers/search_helper.rb:27:in `search_autocomplete_opts' app/controllers/search_controller.rb:143:in `block in autocomplete' ``` ### Root cause `Gitlab::Search::RecentItems#query_items_by_ids` (`lib/gitlab/search/recent_items.rb:70-76`) builds the query as: ```ruby def query_items_by_ids(term, ids) return finder.new(user).klass.none if ids.empty? finder.new(user, search: term, in: 'title', skip_full_text_search_project_condition: true) .execute .limit(SEARCH_LIMIT).without_order.id_in_ordered(ids) end ``` The `skip_full_text_search_project_condition: true` flag was introduced in commit `27e51673286b` (Nov 2022, "Improve issue search performance for recent items") to side-step a different bad plan. In `IssuesFinder#filter_by_full_text_search` (`app/finders/issues_finder.rb:167-175`): ```ruby override :filter_by_full_text_search def filter_by_full_text_search(items) # This project condition is used as a hint to PG about the partitions that need searching # because the search data is partitioned by project. # In certain cases, like the recent items search, the query plan is much better without this condition. return super if params[:skip_full_text_search_project_condition].present? super.with_projects_matching_search_data end ``` The skipped predicate (`issue_search_data.project_id = issues.project_id`) is the partition-pruning hint for `issue_search_data`, which is partitioned by `project_id`. With it missing, the join from `issues` to `issue_search_data` cannot prune partitions. For terms whose tsquery is selective in some partitions but broad in others (common-name queries like `"terri chu"`), PG ends up scanning every partition's GIN index and rechecking the heap for candidates that the subsequent merge join with the ≤100 `issues.id` set will throw away anyway. The fundamental mismatch: this code path operates on a **bounded set of ≤100 issue IDs** pre-fetched from Redis, but uses full-text search machinery designed for unbounded global queries. Tsvector search adds no value once we have 100 candidate rows in hand. ### Plan comparison - BEFORE: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/51813/commands/152847 - AFTER: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/51813/commands/152848 | Metric | BEFORE (current) | AFTER (proposed fix) | Delta | |---|---|---|---| | Total execution time | **10,807 ms** | **210 ms** | **~51× faster** | | Planner cost | 2,782.97 | 869.45 | ~3.2× lower | | Shared buffer hits | 5,295 | 151 | 35× fewer | | Shared buffer reads | 18,467 (≈144 MiB) | 172 (≈1.3 MiB) | ~107× less I/O | | `issue_search_data` partitions scanned | 64 (all of them) | 0 | — | ### Proposed fix In `lib/gitlab/search/recent_items.rb`: ```ruby def query_items_by_ids(term, ids) return finder.new(user).klass.none if ids.empty? items = finder.new(user).execute.without_order.id_in_ordered(ids) items = items.where('LOWER(title) LIKE ?', "%#{ActiveRecord::Base.sanitize_sql_like(term.downcase)}%") if term.present? items.limit(SEARCH_LIMIT) end ``` Behaviour change: - **Filter happens against ≤100 rows, never against the tsvector index.** No partition-prune problem to debate. - **Substring match instead of stemmed tsquery.** For autocomplete the user is matching what they literally typed; substring match is closer to expected UX than stemming. `pg_full_text_search` would tokenize `"terri chu"` into `'terri' & 'chu'` with prefix matching, which is fine for global search but unnecessary for the recent-items popover. - **Removes the need for `skip_full_text_search_project_condition`.** Follow-up MR can delete the flag plumbing in `IssuesFinder#filter_by_full_text_search` since `RecentItems` is the only caller. - **Shared by `RecentIssues`, `RecentMergeRequests`, `RecentWikiPages`.** MR and wiki paths aren't affected by the same partitioned-tsvector bug (MR has no `pg_full_text_searchable`, wiki uses a different finder path), but the simpler query is a net positive for all three. ### Alternatives considered - **Re-enable the partition predicate** (i.e. drop the flag). The MR that introduced the flag found that turned the query into a different bad plan; without re-running that experiment we don't know if index/partition changes since 2022 have shifted things. Dropping FTS altogether removes the question. - **Filter titles in Ruby instead of SQL.** Same plan benefit, but requires moving preload chains (`preload_namespace`, `preload_routables`) from the callers into `RecentItems`. Bigger blast radius for the same win. - **Wrap autocomplete in `with_fast_read_statement_timeout`.** Not pursuing — disallowed by `Performance/ActiveRecordSubtransactionMethods` (relies on subtransactions, see gitlab-org/gitlab#338346), and only bounds the damage rather than fixing the bad plan. ### Suggested labels ~"type::bug" ~"bug::performance" ~"group::global search"