Fix N+1 in status checks API
The following discussion from !81005 (merged) should be addressed:
-
@euko started a discussion: question:
Right now hitting the status check API produces a N+1 problem. It is an existing one. Do you have an issue tracking this? preloads won't work here so we probably need to use BatchLoader.
I saw that one particular project had 24 external services registered. I think this particular project's been created as a testbed. If the project does represent the typical usage of the feature, we will have to address the N+1 issue sooner.
1.376ms SELECT DISTINCT "external_status_checks"."id" AS alias_0, "external_status_checks"."id" FROM "external_status_checks" LEFT OUTER JOIN "external_status_checks_protected_branches" ON "external_status_checks_protected_branches"."external_status_check_id" = "external_status_checks"."id" LEFT OUTER JOIN "protected_branches" ON "protected_branches"."id" = "external_status_checks_protected_branches"."protected_branch_id" WHERE "external_status_checks"."project_id" = 29 AND (protected_branches.id IS NULL OR protected_branches.name = 'main') ORDER BY "external_status_checks"."id" ASC LIMIT 20 OFFSET 0 /*application:web,correlation_id:01FWDMSTZGJSBCKYJEA6V2539Y,endpoint_id:GET /api/:version/projects/:id/merge_requests/:merge_request_iid/status_checks,db_config_name:main,line:/ee/lib/api/status_checks.rb:128:in `block (3 levels) in <class:StatusChecks>'*/ Role: Primary 1.277ms SELECT "external_status_checks"."id" AS t0_r0, "external_status_checks"."project_id" AS t0_r1, "external_status_checks"."created_at" AS t0_r2, "external_status_checks"."updated_at" AS t0_r3, "external_status_checks"."external_url" AS t0_r4, "external_status_checks"."name" AS t0_r5, "protected_branches"."id" AS t1_r0, "protected_branches"."project_id" AS t1_r1, "protected_branches"."name" AS t1_r2, "protected_branches"."created_at" AS t1_r3, "protected_branches"."updated_at" AS t1_r4, "protected_branches"."code_owner_approval_required" AS t1_r5, "protected_branches"."allow_force_push" AS t1_r6 FROM "external_status_checks" LEFT OUTER JOIN "external_status_checks_protected_branches" ON "external_status_checks_protected_branches"."external_status_check_id" = "external_status_checks"."id" LEFT OUTER JOIN "protected_branches" ON "protected_branches"."id" = "external_status_checks_protected_branches"."protected_branch_id" WHERE "external_status_checks"."project_id" = 29 AND (protected_branches.id IS NULL OR protected_branches.name = 'main') AND "external_status_checks"."id" IN (1, 2, 3) ORDER BY "external_status_checks"."id" ASC /*application:web,correlation_id:01FWDMSTZGJSBCKYJEA6V2539Y,endpoint_id:GET /api/:version/projects/:id/merge_requests/:merge_request_iid/status_checks,db_config_name:main,line:/ee/lib/api/status_checks.rb:128:in `block (3 levels) in <class:StatusChecks>'*/ Role: Primary 1.645ms SELECT "status_check_responses".* FROM "status_check_responses" WHERE "status_check_responses"."merge_request_id" = 78 AND "status_check_responses"."external_status_check_id" = 1 AND "status_check_responses"."sha" = '\xb8c63da39eee0bb110e34c278656a571b97ea76b' ORDER BY "status_check_responses"."id" DESC LIMIT 1 /*application:web,correlation_id:01FWDMSTZGJSBCKYJEA6V2539Y,endpoint_id:GET /api/:version/projects/:id/merge_requests/:merge_request_iid/status_checks,db_config_name:main,line:/ee/app/models/merge_requests/external_status_check.rb:31:in `status'*/ Role: Primary 1.188ms SELECT "status_check_responses".* FROM "status_check_responses" WHERE "status_check_responses"."merge_request_id" = 78 AND "status_check_responses"."external_status_check_id" = 2 AND "status_check_responses"."sha" = '\xb8c63da39eee0bb110e34c278656a571b97ea76b' ORDER BY "status_check_responses"."id" DESC LIMIT 1 /*application:web,correlation_id:01FWDMSTZGJSBCKYJEA6V2539Y,endpoint_id:GET /api/:version/projects/:id/merge_requests/:merge_request_iid/status_checks,db_config_name:main,line:/ee/app/models/merge_requests/external_status_check.rb:31:in `status'*/ Role: Primary 1.278ms SELECT "status_check_responses".* FROM "status_check_responses" WHERE "status_check_responses"."merge_request_id" = 78 AND "status_check_responses"."external_status_check_id" = 3 AND "status_check_responses"."sha" = '\xb8c63da39eee0bb110e34c278656a571b97ea76b' ORDER BY "status_check_responses"."id" DESC LIMIT 1 /*application:web,correlation_id:01FWDMSTZGJSBCKYJEA6V2539Y,endpoint_id:GET /api/:version/projects/:id/merge_requests/:merge_request_iid/status_checks,db_config_name:main,line:/ee/app/models/merge_requests/external_status_check.rb:31:in `status'*/