Skip projects filter on merge requests search

What does this MR do?

It skips projects filter subquery if default one is used.

Are there points in the code the reviewer needs to double check?

Double check it's safe to ignore the projects filter if the default one is used. Double check there is no situation when merge_requests are

Why was this MR needed?

GLobal search is slow, part of the reason is that merge_requests query is slow - https://gitlab.com/snippets/1688709 Although this patch is not a complete solution for #40540 (closed) it should help speeding up the search.

Old merge requests search query:

SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "merge_requests"."deleted_at" IS NULL
  AND (EXISTS
         (SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 1642716
            AND (project_authorizations.project_id = projects.id))
       OR projects.visibility_level IN (10,
                                        20))
  AND "projects"."id" IN
    (SELECT "merge_requests"."target_project_id"
     FROM "merge_requests"
     WHERE "merge_requests"."deleted_at" IS NULL)
  AND ("project_features"."merge_requests_access_level" IN (NULL,
                                                            20)
       OR ("project_features"."merge_requests_access_level" = 10
           AND EXISTS
             (SELECT 1
              FROM "project_authorizations"
              WHERE "project_authorizations"."user_id" = 1642716
                AND (project_authorizations.project_id = projects.id))))
  AND (merge_requests.id IN
         (SELECT "merge_requests"."id"
          FROM "merge_requests"
          WHERE "merge_requests"."source_project_id" IN
              (SELECT "projects"."id"
               FROM "projects"
               WHERE (EXISTS
                        (SELECT 1
                         FROM "project_authorizations"
                         WHERE "project_authorizations"."user_id" = 1642716
                           AND (project_authorizations.project_id = projects.id))
                      OR projects.visibility_level IN (10,
                                                       20)))
          UNION SELECT "merge_requests"."id"
          FROM "merge_requests"
          WHERE "merge_requests"."target_project_id" IN
              (SELECT "projects"."id"
               FROM "projects"
               WHERE (EXISTS
                        (SELECT 1
                         FROM "project_authorizations"
                         WHERE "project_authorizations"."user_id" = 1642716
                           AND (project_authorizations.project_id = projects.id))
                      OR projects.visibility_level IN (10,
                                                       20)))))
  AND ("merge_requests"."title" ILIKE '%test1%'
       OR "merge_requests"."description" ILIKE '%test1%');

New merge request search query:

SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "merge_requests"."deleted_at" IS NULL
  AND (EXISTS
         (SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 1642716
            AND (project_authorizations.project_id = projects.id))
       OR projects.visibility_level IN (10,
                                        20))
  AND "projects"."id" IN
    (SELECT "merge_requests"."target_project_id"
     FROM "merge_requests"
     WHERE "merge_requests"."deleted_at" IS NULL)
  AND ("project_features"."merge_requests_access_level" IN (NULL,
                                                            20)
       OR ("project_features"."merge_requests_access_level" = 10
           AND EXISTS
             (SELECT 1
              FROM "project_authorizations"
              WHERE "project_authorizations"."user_id" = 1642716
                AND (project_authorizations.project_id = projects.id))))
  AND ("merge_requests"."title" ILIKE '%test1%'
       OR "merge_requests"."description" ILIKE '%test1%')

Because merge_requests are still being filtered only to target projects user has access to:

  AND ("project_features"."merge_requests_access_level" IN (NULL,
                                                            20)
       OR ("project_features"."merge_requests_access_level" = 10
           AND EXISTS
             (SELECT 1
              FROM "project_authorizations"
              WHERE "project_authorizations"."user_id" = 1642716
                AND (project_authorizations.project_id = projects.id))))
  AND ("merge_requests"."title" ILIKE '%test1%'

It should be safe to remove more generic filter:

  AND (merge_requests.id IN
         (SELECT "merge_requests"."id"
          FROM "merge_requests"
          WHERE "merge_requests"."source_project_id" IN
              (SELECT "projects"."id"
               FROM "projects"
               WHERE (EXISTS
                        (SELECT 1
                         FROM "project_authorizations"
                         WHERE "project_authorizations"."user_id" = 1642716
                           AND (project_authorizations.project_id = projects.id))
                      OR projects.visibility_level IN (10,
                                                       20)))
          UNION SELECT "merge_requests"."id"
          FROM "merge_requests"
          WHERE "merge_requests"."target_project_id" IN
              (SELECT "projects"."id"
               FROM "projects"
               WHERE (EXISTS
                        (SELECT 1
                         FROM "project_authorizations"
                         WHERE "project_authorizations"."user_id" = 1642716
                           AND (project_authorizations.project_id = projects.id))
                      OR projects.visibility_level IN (10,
                                                       20)))))

The old query execution time was 19s (https://gitlab.com/snippets/1688709), the new query execution time should be 64ms (https://gitlab.com/snippets/1688954).

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Documentation created/updated
• API support added
• Tests added for this feature/bug
• Review
  • Has been reviewed by UX
  • Has been reviewed by Frontend
  • Has been reviewed by Backend
  • Has been reviewed by Database
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together
• Internationalization required/considered

What are the relevant issue numbers?

#40540 (closed)