Add TargetProject And SourceBranch Index To MergeRequest (!57691) · Merge requests · GitLab.org / GitLab

What does this MR do?

One of the poor performing queries for the update merge request worker is:

SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."target_project_id" = $1 AND "merge_requests"."source_branch" = $2`

(#218410 (comment 535143110))

This merge request adds a index for this query.

➜  gitlab git:(test_transaction_pipeline) ✗ bundle exec rake db:migrate
== 20210329095548 AddTargetProjectAndSourceBranchIndexToMergeRequest: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:target_project_id, :source_branch], {:algorithm=>:concurrently})
   -> 0.0138s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- add_index(:merge_requests, [:target_project_id, :source_branch], {:algorithm=>:concurrently})
   -> 0.0465s
-- execute("RESET ALL")
   -> 0.0009s
== 20210329095548 AddTargetProjectAndSourceBranchIndexToMergeRequest: migrated (0.0630s)

➜  gitlab git:(test_transaction_pipeline) ✗ bundle exec rake db:rollback STEPS=1
== 20210329095548 AddTargetProjectAndSourceBranchIndexToMergeRequest: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:target_project_id, :source_branch], {:algorithm=>:concurrently})
   -> 0.0147s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :column=>[:target_project_id, :source_branch]})
   -> 0.0257s
-- execute("RESET ALL")
   -> 0.0007s
== 20210329095548 AddTargetProjectAndSourceBranchIndexToMergeRequest: reverted (0.0430s)

Database labs results:

Without index:

 Index Scan using index_on_merge_requests_for_latest_diffs on public.merge_requests  (cost=0.57..46810.23 rows=1336 width=764) (actual time=434.051..51656.030 rows=108 loops=1)
   Index Cond: (merge_requests.target_project_id = 278964)
   Filter: ((merge_requests.source_branch)::text = 'master'::text)
   Rows Removed by Filter: 53301
   Buffers: shared hit=1899 read=53216 dirtied=691
   I/O Timings: read=51050.954

With index:

 Index Scan using index_merge_requests_on_target_project_id_and_source_branch on public.merge_requests  (cost=0.57..1685.16 rows=1337 width=764) (actual time=2.884..211.482 rows=108 loops=1)
   Index Cond: ((merge_requests.target_project_id = 278964) AND ((merge_requests.source_branch)::text = 'master'::text))
   Buffers: shared read=112 dirtied=1
   I/O Timings: read=210.106

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Add TargetProject And SourceBranch Index To MergeRequest