Skip to content

Fix N+1 issue for CompareController

What does this MR do and why?

Contributes to #360578 (closed)

Problem

  1. We trigger N+1 queries for each commit to fetch pipeline status assigned to it.
  2. We trigger N+1 queries for each commit to load markdown cache.

Solution

Preload pipeline statuses and markdown caches to avoid unnecessary DB and Redis queries.

Database

Before (N queries for each commit)

SELECT DISTINCT ON (sha) * FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 1 AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."sha" = '2d1db523e11e777e49377cfb22d368deec3f0793' AND "ci_pipelines"."ref" = 'master' ORDER BY "ci_pipelines"."sha" ASC, "ci_pipelines"."id" DESC /*application:web,correlation_id:01G3XR8BFZ743XDVT4RVSCW5WG,endpoint_id:Projects::CompareController#show,db_config_name:ci,line:/app/models/ci/pipeline.rb:429:in `latest_pipeline_per_commit'*/

SELECT DISTINCT ON (sha) * FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 1 AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."sha" = 'ddd0f15ae83993f5cb66a927a28673882e99100b' AND "ci_pipelines"."ref" = 'master' ORDER BY "ci_pipelines"."sha" ASC, "ci_pipelines"."id" DESC /*application:web,correlation_id:01G3XR8BFZ743XDVT4RVSCW5WG,endpoint_id:Projects::CompareController#show,db_config_name:ci,line:/app/models/ci/pipeline.rb:429:in `latest_pipeline_per_commit'*/ 

After (a single query)

SELECT DISTINCT ON (sha) * FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 1 AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."sha" IN ('2d1db523e11e777e49377cfb22d368deec3f0793', 'ddd0f15ae83993f5cb66a927a28673882e99100b') AND "ci_pipelines"."ref" = 'master' ORDER BY "ci_pipelines"."sha" ASC, "ci_pipelines"."id" DESC /*application:web,correlation_id:01G3XR71VYRFBB6XWVMTXPGPZN,endpoint_id:Projects::CompareController#show,db_config_name:ci,line:/app/models/ci/pipeline.rb:429:in `latest_pipeline_per_commit'*/

Screenshots or screen recordings

Before After
Screenshot_2022-05-23_at_19.15.43 Screenshot_2022-05-23_at_22.25.06

How to set up and validate locally

Visit http://127.0.0.1:3000/gitlab-org/gitlab-test/-/compare/1a0b36b3cdad1d2ee32457c102a8c0b7056fa863...master?from_project_id=1

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Vasilii Iakliushin

Merge request reports

Loading