Skip to content

Reduce N+1 queries when approvals by committers are not allowed

Patrick Bajao requested to merge 441204-reduce-mergeable-graphql-committers-n-plus-1 into master

What does this MR do and why?

Preload merge_request_diff_commits association of each MR when mergeRequests are queried via GraphQL.

We also need to batch load committers and lazy load the value of mergeable and detailed_merge_status to prevent N+1 queries for querying committers by their emails.

Query plans - `merge_request_diff_commits`: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26474/commands/82964 - `merge_request_diff_commit_users`: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26474/commands/82967 - `committers`: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26474/commands/82966 (still just like before but instead of querying per MR, they're batch loaded in a single query)
N+1 queries I see locally SELECT "merge_request_diff_commits".* FROM "merge_request_diff_commits"...

-- (expected: 0, got: 1) WHERE "merge_request_diff_commits"."merge_request_diff_id" = 695 ORDER BY "merge_request_diff_commits"."merge_request_diff_id" ASC, "merge_request_diff_commits"."relative_order" ASC

-- (expected: 0, got: 1) WHERE "merge_request_diff_commits"."merge_request_diff_id" = 694 ORDER BY "merge_request_diff_commits"."merge_request_diff_id" ASC, "merge_request_diff_commits"."relative_order" ASC

-- (expected: 0, got: 1) WHERE "merge_request_diff_commits"."merge_request_diff_id" = 693 ORDER BY "merge_request_diff_commits"."merge_request_diff_id" ASC, "merge_request_diff_commits"."relative_order" ASC

SELECT "merge_request_diff_commit_users".* FROM "merge_request_diff_commit_users"...

-- (expected: 0, got: 1) WHERE "merge_request_diff_commit_users"."id" = 930

-- (expected: 0, got: 1) WHERE "merge_request_diff_commit_users"."id" = 929

-- (expected: 0, got: 1) WHERE "merge_request_diff_commit_users"."id" = 928

SELECT "users".* FROM ((SELECT "users".* FROM "users" WHERE (LOWER("users"."email") IN (LOWER('user34@example.org')))) UNION (SELECT "users".* FROM "users"...

-- (expected: 0, got: 1) INNER JOIN "emails" ON "emails"."user_id" = "users"."id" WHERE "emails"."email" = 'user34@example.org' AND "emails"."confirmed_at" IS NOT NULL)) users

SELECT "users".* FROM ((SELECT "users".* FROM "users" WHERE (LOWER("users"."email") IN (LOWER('user33@example.org')))) UNION (SELECT "users".* FROM "users"...

-- (expected: 0, got: 1) INNER JOIN "emails" ON "emails"."user_id" = "users"."id" WHERE "emails"."email" = 'user33@example.org' AND "emails"."confirmed_at" IS NOT NULL)) users

SELECT "users".* FROM ((SELECT "users".* FROM "users" WHERE (LOWER("users"."email") IN (LOWER('user32@example.org')))) UNION (SELECT "users".* FROM "users"...

-- (expected: 0, got: 1) INNER JOIN "emails" ON "emails"."user_id" = "users"."id" WHERE "emails"."email" = 'user32@example.org' AND "emails"."confirmed_at" IS NOT NULL)) users

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #441204 (closed)

Edited by Patrick Bajao

Merge request reports