Optimize query for cherry picked merge requests

What does this MR do?

This MR fixes Query optimization for the cherry picked merge request search. Currently, the query takes some time on a project with a lot of notes, for example:

SELECT
    "merge_requests".*
FROM
    "merge_requests"
WHERE
    "merge_requests"."target_project_id" = 278964
    AND "merge_requests"."id" IN (
        SELECT
            "notes"."noteable_id"
        FROM
            "notes"
        WHERE
            "notes"."project_id" = 278964
            AND "notes"."noteable_type" = 'MergeRequest'
            AND "notes"."commit_id" IN (
                'c17848ad6b4cc5d5482bf085449ee9e8024a116e',
                'd967959a0ae7d69e6a3503fd25e8c41473a3b1a9',
                'e4339f5b10ab53d99c24597c3e18647d8714a7d5',
                'a4d05d06cbef35b62774eb08e60661b6b4e88d38',
                '44f055b8716a6443091887c4a5f60276c30b553c',
                'c2f25b2b250781a6517c246db71e87c7903e73d6',
                'fdce57e682a35a54d03cc5aa81ec3b5f2b4380d2',
                '35c2819f52cddd9c9f4a4a6832998255c66b883f',
                'ef5c42dd6e67d49f199ae7ae78e00edcf3b876a6',
                'ecdb5a383e6a73dee6fe62922c45160a647ef877',
                'ea44183c072fd087272c2df5713bf0c5a2a93bb1',
                'c77c0e915274fe2ba2baf9af25c8a8c8c35e223d',
                '4d4d28ec1379e8d79e293333d13622c387e822b7',
                '33d1ae4d27671df52f2611d1e4fa82f918c464fe',
                '1e74cc70e47c45616d1c60959441ade527bf4285',
                '74fce67aeacab475822ca43304a05fb88fa10a28',
                'dd1c00464ae96eb6d33f0d6db0708dd65c12dc8c',
                '9a7a66c356cee302a5e1fa78ebba0246089892c7',
                '4b950dc2f8b679e5dac08ba3cefa391855659a3f',
                'e68f6c96e808a77cde2b9a5832669deacbcfaf3a',
                '31cbf8dc37a03962667b9d9b9263ae401b6e6656',
                'ea08abd199a46b5f4f4a83f749640c130624b990',
                '0b90af665865e676fa5fdfc613c544e9411eff68',
                'ae271da6564a0a01fbea1c777859603be3a09262',
                '6abd28acf90c176903cabfd24fee965dff3d9148',
                '3c32f6d9d45b7022792f71ff9765bf3419004c6f',
                'db3ce6cd9e15ce2d37a349b25f8689fb928b9908',
                '38186b3c790e031f99ac1dad70996803a79f8f7d',
                'fd1ecb9d5929c834b5bd106f138f3fccf11a1ff8',
                'a2b544427866d4e59c55adea017c6ad5ed62fbb5',
                '9ef59ce58b6ae3835925b98f91033f11a2f37bac',
                '0bc379961f9634632d2a74a506604c2bdafeaa6d',
                '0e07d4db7d7f3557e82f777adbf948de8444e997',
                '4e6ccdb1477b9ed39ff774d476b286082628bcad',
                '7ee7ad9344c2d36c691773a480675904bd0f5748',
                '1030a56f2a4ed4d3b357a094c93cd643354993f1',
                '4eb4de9c0837d22839a55be7dc60bcad90d2211a',
                '523333da9a45159516f3b3a3d0c6bf7d97821ba4',
                '6af841bafd8c105f75b0eb3733174ea6c18d8983',
                '5c0d670b5ea5697c33a96c81459d430b82df3981',
                '23d95f17c24a3ec8113a65d5af6afd871b5036ba',
                '77b495f77200dede8e107049faf0618f4503a253',
                '87ff1bdd2fdb114ff193d7b062bc1662878b9f68',
                '95051adaf38fbbba4b8cdf062c66847a7f9b528d',
                '6abf99ae70285b2ff05a0dcce7a344439c225ec9',
                '47420c38ac1288c8741c27fd0a1cfdb7c7153e3a',
                '28471cf5e7d86e09d89afd3e1669ad3d294f0aad',
                '41303181ac2adc35b77f8e9cf56c1d992fc71d56',
                '6fde063f3a6df83ce582da170be86d5597d15506',
                '41e9595688f1531a36105f1a37d8a475e67b82ef',
                'dbd59feb10d5f6102f41b3d47ed3870a2f81b091',
                '3f5d0012ab463d240c944b6a395253c5645726c5',
                'e609f9594ca8940183a389dc3d94d3bbdbfa350b',
                'b58b53fccf77324ecec80283706738ffec07be77',
                '1a35f564e867a9746f3f0817b4568c4f38a8a8a8',
                '3a766b987aa6ec97c4a9566d921701dcbfb4b328',
                '4aa39fc5f1b48102bb3a24399b70f3ea91840ae6',
                'a6db63145b0acdb0306087de412c1eb00a213183',
                '2927d330cd0171cdc5c1ae791f65b8d22af688cc',
                'ecf38f38830fba5557bff858b91fa3d5a45b2c83',
                '6d72f39cc350b00c7a2ee7a804753f25241119ab',
                'a9b32f613c290ed4bbabda5f459023266833e8eb',
                '9c826440a9a161f0dbdeca18ec99036f87fee8e6',
                '499d436d7a8408d7cf1e0be7ab2f9cbb1703ae9d',
                'e9b0d74d9045a0a5e0806689bdb6b5b5d90ef654',
                'db3b102d41b463edd773916dcc07ccc36a5ce595',
                '473ccd7f5e159601b91ed0641e5139c2685fcdad',
                'bfeeecdf01cf2694b4a5af1a70b3bf45b9c9a609',
                'efd684241847291b1d2776f7aefef2035a67cfb9',
                '697459c14a4b61c1882a764b5bebd4d2e75cf1c3',
                'ae11d316c0f183f017d9df013dea15987aefb215',
                'c6137e874843fa4015c169091bb7ec855b918c58',
                'c899740a5eea05969ddd52614465df23ae237ba3',
                '96d02a33573233f9776a53e7487ebdb164e853db',
                'd290f0aa9f661cfc75fbcc00ee1230f95c861e09',
                'e2b12946c24ecd91358c4a155c5129b884d02ac6',
                'b5fd7238480df96e0ce1b5c307228843fec60f71',
                '08022572a2db1e4dde86fde623fe69777eeb2d2b',
                '7029cd7787ac107591417e7e21733d32fcf869fa',
                '563940baf3362ff835384a4979e7c61845c1d626',
                '2eb065b233646bacef9b58717742d4b02ee0300e',
                '42430d0619c11eeea7baeb033452acd0c479c7f8',
                '3e826e66186377759583e15b8fddce1b21dc3033',
                'e475734438a120375a4f487bfb46f4e05c02ab7c',
                '9c4c93ae5099eadb96e7a13ce18df361240e33a8',
                '9e50af23ae1b13c0b524322990e9fb17fb167112',
                '8f5e0fbaf89c4535ff84c39c2bff23b1e22d6b24',
                '556e642e5b3682e8a9e57b5dfb9b41ee0b79f79c',
                '939028e7634707dcde050560de02a56f3c3e4d06',
                '5bd754a6b396413ba86df2a4bcaf7b31dfdfa219',
                '32949cb92f007e0f68a8b796706fb4c492a69d1e',
                '602b3e44230d17359eba679da13608a40ff93f3f',
                '5d5cec6d7acc87cbc558b4be767d05d50d081a69',
                'dc3d354a9f917cb6f5b4913b1823d30324a98a43',
                '48edc67557e16f5f8447079ae87f4419fae20f55',
                '1b554254a8dbac0907df317a7606f445e7fe4ed9',
                'abae41e8e725915a4e07717a1c605798f75cbe40',
                'c8f244f1570f3f0b65f1762fb3a4eb30a5394686',
                '2fa64e76f60ff5a58ff85d0a977cf76729a4195d',
                '781df9fc8d0f1760b86fc6e088036fbf6f2cdb79',
                '1ff17bc2d1745fc8a4562303591e51f85d082b31',
                'd3f46313846e824fb48547ff99010a2dc0925df6',
                '9b9ba6b8a90a0e2fb8b7674fc6d7062a034eb456'
            )
    )

Query performance (in db-lab)

Query plan

https://explain.depesz.com/s/NTia

Timing

Time: 1.071 min
  - planning: 12.405 ms
  - execution: 1.071 min (estimated* for prod: 3.292...58.719 s)
    - I/O read: 1.020 min
    - I/O write: N/A

Shared buffers:
  - hits: 167 (~1.30 MiB) from the buffer pool
  - reads: 31241 (~244.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Given that the query searching the notes with index_notes_on_commit_id and index_notes_on_project_id_and_noteable_type but merging the results sequentially, it can be expensive if the project has too many notes and cherry-picked commits.

Approach

We should create an index on notes table for optimizing the query, something like:

exec CREATE INDEX test_index ON notes USING btree (project_id, commit_id) WHERE ((noteable_type)::text = 'MergeRequest'::text);

Query plan

https://explain.depesz.com/s/66Dy

Timing

Time: 67.225 ms
  - planning: 9.920 ms
  - execution: 57.305 ms
    - I/O read: 56.003 ms
    - I/O write: N/A

Shared buffers:
  - hits: 391 (~3.10 MiB) from the buffer pool
  - reads: 26 (~208.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Index details (in db-lab)

Size

List of relations
 Schema |    Name    | Type  |   Owner    | Table |  Size   | Description 
--------+------------+-------+------------+-------+---------+-------------
 public | test_index | index | joe_shinya | notes | 9036 MB | 
(1 row)

Timing

% time      seconds wait_event
------ ------------ -----------------------------
91.49  20253.170848 IO.DataFileRead
3.75     830.045994 Running
2.50     554.073752 IO.DataFileExtend
1.10     244.349545 IO.BufFileWrite
0.74     164.107568 LWLock.WALWriteLock
0.20      43.834928 IO.DataFileWrite
0.18      40.465748 IO.BufFileRead
0.01       2.997461 LWLock.buffer_mapping
0.01       2.918001 IO.SLRURead
0.00       0.809943 IO.WALWrite
0.00       0.336951 LWLock.WALBufMappingLock
0.00       0.011034 LWLock.clog
------ ------------ -----------------------------
100.00 22137.121773

Local migration log

shinya@shinya-B550-VISION-D:~/workspace/thin-gdk/services/rails/src$ tre bin/rails db:migrate:redo VERSION=20210409084242
INFO: This script is a predefined script in devkitkat.
== 20210409084242 CreateIndexOnNotesForCherryPickedMergeRequests: reverting ===
-- transaction_open?()
   -> 0.0000s
-- indexes(:notes)
   -> 0.0066s
-- remove_index(:notes, {:algorithm=>:concurrently, :name=>"index_notes_for_cherry_picked_merge_requests"})
   -> 0.0024s
== 20210409084242 CreateIndexOnNotesForCherryPickedMergeRequests: reverted (0.0112s) 

== 20210409084242 CreateIndexOnNotesForCherryPickedMergeRequests: migrating ===
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, [:project_id, :commit_id], {:where=>"((noteable_type)::text = 'MergeRequest'::text)", :name=>"index_notes_for_cherry_picked_merge_requests", :algorithm=>:concurrently})
   -> 0.0039s
-- add_index(:notes, [:project_id, :commit_id], {:where=>"((noteable_type)::text = 'MergeRequest'::text)", :name=>"index_notes_for_cherry_picked_merge_requests", :algorithm=>:concurrently})
   -> 0.0052s
== 20210409084242 CreateIndexOnNotesForCherryPickedMergeRequests: migrated (0.0097s) 

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