Use namespace_traversal_ids in work_items finder

Issue: Use namespace_traversal_ids in WorkItemsFinder (#562320 - closed)

What does this MR do and why?

This change introduces a new, more efficient way to find work items (tasks, issues, epics) within group hierarchies.

• The main improvement is adding a new database query method that uses "traversal IDs" - a faster way to look up items across parent and child groups. Instead of making multiple complex database queries to find all work items in a group and its subgroups, this new approach can find them with a single, simpler query.

• The change is controlled by a feature flag (a toggle that can be turned on/off) so it can be safely tested and rolled out gradually. When enabled, the system will use this faster method, but only when certain conditions are met - like when the user has proper permissions and is searching within group hierarchies.

• The code also includes safety checks to ensure users can only see work items they have permission to access (admin or member of group/root_group). This optimization should make searching for work items across large group structures much faster for users.

New Query

Root group

SELECT issues.*
FROM issues
LEFT OUTER JOIN projects ON projects.id = issues.project_id
LEFT OUTER JOIN project_features ON project_features.project_id = projects.id
WHERE NOT EXISTS (
    SELECT 1
    FROM banned_users
    WHERE banned_users.user_id = (issues.author_id + 0)
  )
  AND namespace_traversal_ids[1] = 9970
  AND (
    issues.project_id IS NULL
    OR project_features.issues_access_level > 0
    OR project_features.issues_access_level IS NULL
  )
  AND issues.state_id IN (1, 2)
  AND (issues.project_id IS NULL OR projects.archived = FALSE)
  AND issues.work_item_type_id IN (1, 2, 5, 8, 7, 6, 3)
ORDER BY issues.created_at DESC, issues.id DESC
LIMIT 21

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/44224/commands/135769

Sub groups

SELECT issues.*
FROM issues
LEFT OUTER JOIN projects ON projects.id = issues.project_id
LEFT OUTER JOIN project_features ON project_features.project_id = projects.id
WHERE NOT EXISTS (
    SELECT 1
    FROM banned_users
    WHERE banned_users.user_id = (issues.author_id + 0)
  )
  AND issues.namespace_traversal_ids >= '{9970, 2287432}'
  AND issues.namespace_traversal_ids < '{9970, 2287433}'
  AND (
    issues.project_id IS NULL
    OR project_features.issues_access_level > 0
    OR project_features.issues_access_level IS NULL
  )
  AND issues.state_id IN (1, 2)
  AND (issues.project_id IS NULL OR projects.archived = FALSE)
  AND issues.work_item_type_id IN (1, 2, 5, 8, 7, 6, 3)
ORDER BY issues.created_at DESC, issues.id DESC
LIMIT 21

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/44224/commands/135770

References

• Design doc: https://handbook.gitlab.com/handbook/engineering/architecture/design-documents/traversal_ids_on_issues
• Epic: Denormalize namespace_traversal_ids on issues (&18644)

Screenshots or screen recordings

This MR does not introduce any UI changes

How to set up and validate locally

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.