Group-level Packages::Nuget::SearchService query is slow

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

The following discussion from !162177 (merged) should be addressed:

• @mkhalifa3 started a discussion: (+3 comments)

  💾 Database Analysis

  This query is used as a subquery to search for packages where their project_id = subquery.

  Plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/30724/commands/95572

  It's the same query as before, except for this added part in project_ids CTE to include projects that have "project_features"."package_registry_access_level" = 30.

  OR EXISTS (
    SELECT
      1
    FROM
      "project_features"
    WHERE
      "project_features"."project_id" = "projects"."id"
      AND "project_features"."package_registry_access_level" = 30
  )

  It's slow. However, the original query (without the above existence condition) is also slow:

  Original query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/30724/commands/95574

  Improving the query's performance seems a bit challenging, but I think the modification done in this MR doesn't add that much to its slowness.