Group-level Packages::Nuget::SearchService query is slow
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
The following discussion from !162177 (merged) should be addressed:
-
@mkhalifa3 started a discussion: (+3 comments) 💾 Database AnalysisThis query is used as a
subqueryto search for packages where theirproject_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_idsCTE 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.