Skip projects filter on issues search

What does this MR do?

It skips project filter when searching for issues unless a project context is used, very similar to !15955 (merged).

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

Double check it's safe to remove projects filter if project context is not used.

Why was this MR needed?

Global search query may be slow - #40540 (closed). This patch speeds up searching of issues which is part of the global search. Search query without the patch https://gitlab.com/snippets/1690165:

 Planning time: 9.581 ms
 Execution time: 1600.658 ms


SELECT COUNT(*)
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "issues"."deleted_at" IS NULL
  AND ( issues.confidential IS NOT TRUE
       OR (issues.confidential = TRUE
           AND (issues.author_id = 1642716
                OR EXISTS
                  (SELECT TRUE
                   FROM issue_assignees
                   WHERE user_id = 1642716
                     AND issue_id = issues.id)
                OR issues.project_id IN
                  (SELECT "projects"."id"
                   FROM "projects"
                   INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                   WHERE "project_authorizations"."user_id" = 1642716
                     AND (project_authorizations.access_level >= 20)))))
  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 "issues"."project_id"
     FROM "issues"
     WHERE "issues"."deleted_at" IS NULL
       AND ( issues.confidential IS NOT TRUE
            OR (issues.confidential = TRUE
                AND (issues.author_id = 49
                     OR EXISTS
                       (SELECT TRUE
                        FROM issue_assignees
                        WHERE user_id = 49
                          AND issue_id = issues.id)
                     OR issues.project_id IN
                       (SELECT "projects"."id"
                        FROM "projects"
                        INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                        WHERE "project_authorizations"."user_id" = 49
                          AND (project_authorizations.access_level >= 20))))))
  AND ("project_features"."issues_access_level" IN (NULL,
                                                    20)
       OR ("project_features"."issues_access_level" = 10
           AND EXISTS
             (SELECT 1
              FROM "project_authorizations"
              WHERE "project_authorizations"."user_id" = 49
                AND (project_authorizations.project_id = projects.id))))
  AND "issues"."project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     WHERE (EXISTS
              (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 49
                 AND (project_authorizations.project_id = projects.id))
            OR projects.visibility_level IN (10,
                                             20)))
  AND ("issues"."title" ILIKE '%test1%'
       OR "issues"."description" ILIKE '%test1%')

Search query with this patch - https://gitlab.com/snippets/1690161:

 Planning time: 3.909 ms
 Execution time: 936.138 ms


SELECT COUNT(*)
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "issues"."deleted_at" IS NULL
  AND ( issues.confidential IS NOT TRUE
       OR (issues.confidential = TRUE
           AND (issues.author_id = 1642716
                OR EXISTS
                  (SELECT TRUE
                   FROM issue_assignees
                   WHERE user_id = 1642716
                     AND issue_id = issues.id)
                OR issues.project_id IN
                  (SELECT "projects"."id"
                   FROM "projects"
                   INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                   WHERE "project_authorizations"."user_id" = 1642716
                     AND (project_authorizations.access_level >= 20)))))
  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 "issues"."project_id"
     FROM "issues"
     WHERE "issues"."deleted_at" IS NULL
       AND ( issues.confidential IS NOT TRUE
            OR (issues.confidential = TRUE
                AND (issues.author_id = 49
                     OR EXISTS
                       (SELECT TRUE
                        FROM issue_assignees
                        WHERE user_id = 49
                          AND issue_id = issues.id)
                     OR issues.project_id IN
                       (SELECT "projects"."id"
                        FROM "projects"
                        INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                        WHERE "project_authorizations"."user_id" = 49
                          AND (project_authorizations.access_level >= 20))))))
  AND ("project_features"."issues_access_level" IN (NULL,
                                                    20)
       OR ("project_features"."issues_access_level" = 10
           AND EXISTS
             (SELECT 1
              FROM "project_authorizations"
              WHERE "project_authorizations"."user_id" = 49
                AND (project_authorizations.project_id = projects.id))))
  AND ("issues"."title" ILIKE '%test1%'
       OR "issues"."description" ILIKE '%test1%')

So the following extra filter is now removed by default:

<   AND "issues"."project_id" IN
<     (SELECT "projects"."id"
<      FROM "projects"
<      WHERE (EXISTS
<               (SELECT 1
<                FROM "project_authorizations"
<                WHERE "project_authorizations"."user_id" = 49
<                  AND (project_authorizations.project_id = projects.id))
<             OR projects.visibility_level IN (10,
<                                              20)))

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)