Optimize NOT params in IssuableFinder

In gitlab-org/gitlab-ce!31134, @cablett suggested:

To think about: WDYT about a method that goes through the scalar params that could make sure that it negates any that are searched both FOR and NOT and remove the param from the FOR, to reduce DB load.

e.g. if you search for 1st contribution and NOT 1st contribution this would trim the params down so that it only appears in the NOT params.

I think this is kind of a corner case though, so it could be in a future iteration.

I think we definitely want to look into performance there even if it's an edge case since currently doing the above for label a creates a SQL query like so:

SELECT "issues".* FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = 'Issue' INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "issues"."project_id" = 1 AND ("issues"."state" IN ('opened')) AND "labels"."title" = 'a' AND "issues"."id" NOT IN (SELECT "issues"."id" FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = 'Issue' INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "labels"."title" = 'a' ORDER BY "issues"."id" DESC) ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
Edited May 28, 2025 by 🤖 GitLab Bot 🤖
Assignee Loading
Time tracking Loading