Use traversal_ids for group search project lookup

Terri Churequested to merge
tchu-fix-project-group-query into master

What does this MR do and why?

This query takes 2.5 seconds for me in production when group searches are done. The inside_path is pretty inefficient.

This MR:

  • Uses traversal_ids for group search project lookup rather than ILIKE.
  • removes the order, search does not care about the order of the projects

Honestly, I think all use of inside_path could be replaced by the new scope but want to see what folks think

AI Summary

This code is used in a few places that inside_path is called. I'm proposing to replace all of them with this new scope, but I can do the full replace in a follow up if search works out well.

This code change improves how the application searches for and loads project data. The main improvements are:

  1. Added new helper methods to make database queries more efficient when working with projects that have topics (tags/categories) and when searching within specific groups or namespaces.

  2. Replaced an old method (inside_path) with a newer, better one (inside_namespace) for finding projects within a particular group or organization.

  3. Streamlined database queries by using the new helper methods instead of manually specifying what data to load, which makes the code cleaner and potentially faster.

  4. Marked an old method as deprecated to guide developers toward using the improved approach.

The changes focus on making project searches more efficient and the code easier to maintain, particularly when dealing with project topics and group-based searches.

References

Screenshots or screen recordings

Before After

How to set up and validate locally

These are the queries run when doing global and group searches. They can be replicated by searching with basic search or advanced search (requires Elasticsearch enabled in gdk).

The queries below can be generated with the rails code I gave.

Enable the feature flag search_project_list_lookup

Before

cold: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/48565/commands/145926

warm: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/48565/commands/145927

group = Group.find(24)
current_user = User.find_by_username('test-non-admin-user')

::ProjectsFinder.new(current_user: current_user).execute.preload(:topics, :project_topics, :route).inside_path(group.full_path)

After

cold: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/48565/commands/145924

warm: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/48565/commands/145925

group = Group.find(24)
current_user = User.find_by_username('test-non-admin-user')

::ProjectsFinder.new(current_user: current_user).execute.preload(:topics, :project_topics, :route).in_namespace(group.self_and_descendants.as_ids).without_order

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Terri Chu

Merge request reports