Skip to content

Speed up counting approvers when some are specified

Sean McGivern requested to merge speed-up-approvers-queries-again into master

What does this MR do?

https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/2032 didn't go far enough! This is now faster if you don't have any explicit approvers listed, but slow otherwise: gitlab-com/gitlab-profiler!16 (merged)

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

Is the intent clear?

Why was this MR needed?

Turns out UNION can be a lot faster than OR! From staging:

# old method
D, [2017-06-20T18:27:51.440805 #54595] DEBUG -- :    (1186.3ms)  SELECT COUNT(*) FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."ghost" = 'f' OR "users"."ghost" IS NULL) AND ("users"."support_bot" = 'f' OR "users"."support_bot" IS NULL) AND ((id IN (SELECT "project_authorizations"."user_id" FROM "project_authorizations" WHERE "project_authorizations"."project_id" = 13083 AND (access_level > 20)) OR id IN (443319)) AND id NOT IN (SELECT "approvals"."user_id" FROM "approvals" WHERE "approvals"."merge_request_id" = 2294769)) AND ("users"."id" != 840794)  [["id", 840794]]
# new method
D, [2017-06-20T19:42:36.655651 #18554] DEBUG -- :    (10.2ms)  SELECT COUNT(*) FROM (SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."ghost" = 'f' OR "users"."ghost" IS NULL) AND ("users"."support_bot" = 'f' OR "users"."support_bot" IS NULL) AND ("users"."id" NOT IN (SELECT "approvals"."user_id" FROM "approvals" WHERE "approvals"."merge_request_id" = 2294769)) AND ("users"."id" != 840794) AND "users"."id" IN (SELECT "project_authorizations"."user_id" FROM "project_authorizations" WHERE "project_authorizations"."project_id" = 13083 AND (access_level > 20))
UNION
SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."ghost" = 'f' OR "users"."ghost" IS NULL) AND ("users"."support_bot" = 'f' OR "users"."support_bot" IS NULL) AND ("users"."id" NOT IN (SELECT "approvals"."user_id" FROM "approvals" WHERE "approvals"."merge_request_id" = 2294769)) AND ("users"."id" != 840794) AND "users"."id" = 443319) subquery

Does this MR meet the acceptance criteria?

Merge request reports