Filter recent items autocomplete with ILIKE on bounded id set

What does this MR do and why?

Replaces the IssuesFinder full-text search call in Gitlab::Search::RecentItems#query_items_by_ids with a direct title ILIKE filter on the bounded id set pulled from Redis (≤100 ids).

The current code path joins the partitioned issue_search_data table without the project-id predicate (the skip_full_text_search_project_condition: true flag deliberately strips it), which makes partition pruning impossible. For selective tsqueries, PG scans all 64 partitions before merge-joining to the bounded id set and discarding everything.

The fix routes through each subclass's existing per-type ILIKE scope:

• Issuable.search(term) (via Gitlab::SQL::Pattern#fuzzy_search) for RecentIssues and RecentMergeRequests
• WikiPage::Meta.search_by_title(term) for RecentWikiPages

Same shape that RecentWorkItems and RecentEpics already use. No new scopes introduced, no inline SQL, no rubocop disables.

Gated behind search_autocomplete_use_ilike (gitlab_com_derisk flag, default off).

Plan comparison

Click to expand used to get plans

current_user = User.find_by_username('terrichu')
term  = 'terri chu'

# before
recent_issues = ::Gitlab::Search::RecentIssues.new(user: current_user)
ids = recent_issues.send(:latest_ids)
puts recent_issues.send(:query_items_by_ids, term, ids).to_sql

# after
rel = IssuesFinder.new(current_user).execute.without_order.id_in_ordered(ids)
rel = rel.search(term)
puts rel.limit(5).to_sql

Took plans with a few users:

• BEFORE:

  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/51813/commands/152847
  • https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51813/commands/152866

• AFTER:

  • https://console.postgres.ai/gitlab/gitlab-production-main/sessions/51813/commands/152848
  • https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51813/commands/152867

Metric	BEFORE	AFTER	Delta
Execution time	10,807 ms	210 ms	~51x
Planner cost	2,782.97	869.45	~3.2x
Shared buffer hits	5,295	151	35x fewer
Shared buffer reads	18,467 (≈144 MiB)	172 (≈1.3 MiB)	~107x less
issue_search_data partitions scanned	64	0	—

References

• Issue: #600604
• Rollout: #600567
• Flag introduced by 27e51673286b (Nov 2022, "Improve issue search performance for recent items") — that fix dodged a different bad plan, this one removes the FTS path entirely

How to set up and validate locally

1. In a rails console, enable the flag for your user:

   Feature.enable(:search_autocomplete_use_ilike, User.find_by_username('your-handle'))

2. Visit a group/project where you have several recently-viewed issues, MRs, or wiki pages.
3. Open the search bar and type a multi-word query that matches a title (e.g. someone's name).
4. Confirm the recent-items popover returns the expected results.
5. Compare timing with the flag disabled vs enabled.

Follow-ups (separate MRs)

• Remove the skip_full_text_search_project_condition flag plumbing in IssuesFinder#filter_by_full_text_search and the unused with_projects_matching_search_data scope on Issue, once this rolls to 100%.
• Delete the redundant #search overrides in RecentWorkItems and RecentEpics (they duplicate the base class).
• Consider unifying the bypass-finder pattern across all five RecentItems subclasses.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.