Considerably improve the query performance for MR discussions load
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'));
ruby-prof: discussions_improved.html.zip
Query plan: https://explain.depesz.com/s/Bzlx (discussion on this query is under https://gitlab.com/gitlab-org/gitlab-ce/issues/58296#note_161376388)
Issue: #27035 (closed)
Screenshots
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry for user-facing changes, or community contribution. Check the link for other scenarios. -
Documentation created/updated or follow-up review issue created -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Performance and Testing
Edited by Oswaldo Ferreira