Considerably improve the query performance for MR discussions load (!16635) · Merge requests · GitLab.org / GitLab

Oswaldo Ferreira requested to merge osw-improve-discussions-query into master Sep 13, 2019

What does this MR do?

It considerably improves the query for preloading MR discussion diffs for:

MR diff comments (made at Diffs tab; presented at the main MR page)
Comments on commits being listed at the MR

Additionally, it also prevents a second similar query that was unnecessarily being made.

Tests in production with this query has shown a drop from:

Planning time: 10.158 ms
Execution time: 3793.796 ms

To:

Planning time: 13.978 ms
Execution time: 5.843 ms

Query:

SELECT "note_diff_files".*
FROM "note_diff_files"
INNER JOIN "notes" ON "notes"."id" = "note_diff_files"."diff_note_id"
AND "notes"."type" IN ('DiffNote')
WHERE ("notes"."noteable_id" = 18527120
       AND "notes"."noteable_type" = 'MergeRequest'
       OR "notes"."system" = FALSE
       AND "notes"."project_id" IN (7764,
                                    7764)
       AND "notes"."noteable_type" = 'Commit'
       AND "notes"."commit_id" IN ('7d90dbc658817f5d0f0ea0e6f49c6a1cb0d285b7',
                                   'c2705610628c9cfec8ac364208252430cdb15e4d',
                                   '1eee2e4148be10ebd1b40a2d6b5fccbbbcd1044f',
                                   '6851b0505a99f445f762c265ba8c31b97e37d37a',
                                   '17706f8e4bd31ecf806c13e952942ab797678c80',
                                   'a90f1b13b705f3b4399ec8564ae0a0979a1c55d9',
                                   'c9f5a7b1155266df15fb4b4af9d2f7346230fb86',
                                   'c0a10cd230b0bae7e6199b28ac63c8c5b32775c6',
                                   '033321adb12e025d414aca299715f6c02046a8b8',
                                   '7165654a3c49b92f83735a52e45441a463208128',
                                   '60214ab0536c25e33fe7d9970c23dff3c5309edc',
                                   '46f088e8dddeaccd30d0147d8fde283f50c6496e',
                                   'f459b1108bbf1856befcac2753ff52e6597545ec',
                                   '9c71960a521f0d44e2bb06c2057ad3689687d72a',
                                   '1fe1561fcd2f2ac08863cb9ccc9b66b3af317df0',
                                   '0445aa6d0b879748ec68b0f9913d6037563dca30',
                                   '88eac68d39eb78a069ba468468d3c38eece00123',
                                   'c2e2fbe1333a49392a5e415e33287eaed4e1c31f',
                                   'da1c43e02fbca57be7d1b7109be56928a8f37c86',
                                   'd5c2f82b582857f5ad4cea037024fe39a1a853ef',
                                   'f6e4a7becc66dd88db4bf5e1001a06c8bd258933',
                                   'c1029ad958ff3302b577ab039b5226ec4ef0a257',
                                   'f22f68be490962ea2012309cc67cd571996f1631',
                                   'e3387993a498a923d0e83a17282cd54d0e98bd22',
                                   '1ebc184dbc475c410e075ae55abaf9b284cfb68c',
                                   'a5339d93156c5f46da707e34f42905cf1a8e1154',
                                   '42c81ee1509f57406a948f5d4f48725731dfad3d',
                                   'dfa87b22b18a06c826840d420d7205b0fb0e41d4',
                                   'd5b5b509556726f5686ead8eb127c4a4bcf4e4e8',
                                   '1a1bc45fb08b83506e2445e61b7263c34cb3aa56',
                                   '47b793efd6d911afdf37c7abc90824a3bc7d3bc0',
                                   '5b79da18a5576d5703e05b3bb546b002a14cc9d3',
                                   'bfc56e0e6fdb4fe4c4077aca0e2f49053064efd3',
                                   '6a815b7e69fe10a50e8c21389747b9c22bd283fe',
                                   '44ad653c368e80fe65da252cb93b8bc8fed5f732',
                                   '755759716060b5e1c13545b8f649d82413729695',
                                   '848c27ee3825acd583a3181330d31cdbfe0434cb',
                                   'ae80f54a0ecd8e13cfe3e243532a03adcad2debf',
                                   '1d59fd18697c7135964568e23014714b946786aa',
                                   '62a6773abe1885d4b8156b57677dffe1d98176ec',
                                   '32fcdd419b86e8111aa3ab6786085b82fd82ef0a',
                                   '8947928d653ed8d207f02e41928bbda29aa64058',
                                   '51def0947dc4fe75af0f3ea9a3df5b94fb36ff52',
                                   'acbbe5a076a7b31c0859103bcd446c70bcd47d1d',
                                   '53f4cd3e208366da5cca1b3595e26f059713f798',
                                   'e72887dd77ed381de68b82f12c56dd0646cf9b85',
                                   '91b93f4044d6e8a15bede332835cd7c2d6a97771',
                                   '13d275d89ceb795e058d5f7a27f0129e3261dea9',
                                   '57d36e63c5315f3a3df21d71e4463040ab1c5f43',
                                   'b21ab4ff63b68d6778403a48467373019083fb5a',
                                   'c642e0b90703d3ca34c216f049a1ebfc608b0e20',
                                   'cf54a82ed650531a85aa9f0d718776614e685fcd',
                                   '80e79e86fbcc43cd7002f8d2d910b3e180bae2f6',
                                   '9e9c8bf32318ee126e8cf4a13fde9fbbeace83a3',
                                   '523a1577bee6d45a7f2f93a4f85b5e510ab11448',
                                   '614f869c2d62e5a0905f7eef4588867c73ae09ce',
                                   '1176719499fada59d8e89fd30649d3c1644889ef',
                                   '420f72659d58e10a6673757de9f2d323d4390921',
                                   '0d2de838741e82daf711a80ba947da585a336b7d',
                                   '126ee91fe6c7018e1f67bbc3fda30f77bdeb2da8',
                                   'cce4cdc9411436f0f86e4ae68e6e03bbe9c5cca1',
                                   'cac483b6b16ff8321add282e097f12b1d4ac7cae',
                                   '7378c450a20b495cddc6bc71125e2b4f3ba45fad',
                                   'efa5bbae1ca60e8a0786f8b25adf8d0adba25e0a',
                                   '5cda61e40a3fab8b124788a1a35c2fa1c762a488',
                                   'e35eb4fd8eb0639c7a5d46e606ef3b8548a06be0',
                                   'b9b736f26ea7533473388fffe313190ec938d224',
                                   '67b1af3dc2a9f7a459d0981e24c6d35e1eac487b',
                                   '2aa4e8751e651f5183b5588847cd524c283d5c40',
                                   '4a649e9f3d0996cb630280808ef209ffa36c8ee9',
                                   'cb2c4db496f78caede7136301e0ae75ea209aad7',
                                   '018a8243dfaa3f575553372224e3d73e11b0404a',
                                   '4a7f1ab4909ae5bd6ebb498362e8c3fa19ed9514',
                                   '1d44494b51abcc7ae32c3f6b48a382fb5d608a34',
                                   '2746fcb4d5904dbe9542b8b0a5ddb3ced034ee8b',
                                   'b33601a044938ea7036d9a79f0eb1c6296b47779',
                                   '94b132615a91f08255d77ffcff1543734c46fe73',
                                   '7832e0d3fe93bdd6839213298d386f41aff43698',
                                   'eadfe7a399dc945653f0aa7cf32e542c6065ac96',
                                   'a5ca36000f0a0e559554d13ce100179273362013',
                                   '4c4613500032f7f04fc8e854cf61b1780e2ad1b8',
                                   '40cef3b83aeaca225f303f83d0a85b944916d771'));