Optimize merge request query for large collections
What does this MR do and why?
WHERE ... IN (...
query is not performant for large collections. A
faster alternative is a INNER JOIN
over a VALUES
clause.
A similar fix was implemented here: !67315 (merged)
Before | After |
---|---|
Time: 467.616 ms | Time: 2.110 ms |
Database
Before
EXPLAIN SELECT
"merge_requests".*
FROM
"merge_requests"
WHERE
"merge_requests"."target_project_id" = 278964
AND "merge_requests"."iid" IN (59682, 59724, 59777, 59872, 59944, 60021, 60065, 60246, 60261, 60323, 60508, 60543, 60574, 60592, 60644, 60740, 60752, 60753, 60768, 60860, 60991, 61072, 61104, 61162, 61335, 61348, 61404, 61419, 61423, 61456, 61705, 61709, 61717, 61738, 61829, 61921, 62050, 62057, 60175, 55952, 56053, 56286, 57567, 57960, 58025, 58046, 58156, 58157, 58501, 58706, 58868, 58884, 59243, 59310, 59453, 59487, 59576, 59611, 59644, 59650, 59695, 59725, 59731, 59732, 59793, 59823, 59836, 59999, 60032, 60057, 60059, 60064, 60068, 60108, 60171, 60308, 60362, 60404, 60449, 60455, 60464, 60467, 60499, 60563, 60600, 60716, 60730, 60749, 60868, 60906, 60927, 60944, 60953, 61058, 61169, 61263, 61267, 61350, 61366, 61394, 61399, 61509, 61551, 61566)
Index Scan using index_merge_requests_on_target_project_id_and_iid_and_state_id on public.merge_requests (cost=0.57..340.25 rows=102 width=781) (actual time=0.053..459.414 rows=104 loops=1)
Index Cond: ((merge_requests.target_project_id = 278964) AND (merge_requests.iid = ANY ('{59682,59724,59777,59872,59944,60021,60065,60246,60261,60323,60508,60543,60574,60592,60644,60740,60752,60753,60768,60860,60991,61072,61104,61162,61335,61348,61404,61419,61423,61456,61705,61709,61717,61738,61829,61921,62050,62057,60175,55952,56053,56286,57567,57960,58025,58046,58156,58157,58501,58706,58868,58884,59243,59310,59453,59487,59576,59611,59644,59650,59695,59725,59731,59732,59793,59823,59836,59999,60032,60057,60059,60064,60068,60108,60171,60308,60362,60404,60449,60455,60464,60467,60499,60563,60600,60716,60730,60749,60868,60906,60927,60944,60953,61058,61169,61263,61267,61350,61366,61394,61399,61509,61551,61566}'::integer[])))
Buffers: shared hit=482 read=41
I/O Timings: read=457.333 write=0.000
https://explain-depesz.postgres.ai/s/Cp
After
EXPLAIN SELECT
"merge_requests".*
FROM
"merge_requests"
INNER JOIN
(VALUES
(59682), (59724), (59777), (59872), (59944), (60021), (60065), (60246), (60261), (60323), (60508), (60543), (60574), (60592), (60644),
(60740), (60752), (60753), (60768), (60860), (60991), (61072), (61104), (61162), (61335), (61348), (61404), (61419), (61423), (61456),
(61705), (61709), (61717), (61738), (61829), (61921), (62050), (62057), (60175), (55952), (56053), (56286), (57567), (57960), (58025),
(58046), (58156), (58157), (58501), (58706), (58868), (58884), (59243), (59310), (59453), (59487), (59576), (59611), (59644), (59650),
(59695), (59725), (59731), (59732), (59793), (59823), (59836), (59999), (60032), (60057), (60059), (60064), (60068), (60108), (60171),
(60308), (60362), (60404), (60449), (60455), (60464), (60467), (60499), (60563), (60600), (60716), (60730), (60749), (60868), (60906),
(60927), (60944), (60953), (61058), (61169), (61263), (61267), (61350), (61366), (61394), (61399), (61509), (61551), (61566)
) mr_ids (id) ON (mr_ids.id = merge_requests.iid)
WHERE
"merge_requests"."target_project_id" = 278964
Nested Loop (cost=0.57..219.57 rows=515 width=781) (actual time=0.066..0.958 rows=104 loops=1)
Buffers: shared hit=520
I/O Timings: read=0.000 write=0.000
-> Values Scan on "*VALUES*" (cost=0.00..1.30 rows=104 width=4) (actual time=0.002..0.051 rows=104 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.57..8.22 rows=5 width=781) (actual time=0.008..0.008 rows=1 loops=104)
Index Cond: ((merge_requests.target_project_id = 278964) AND (merge_requests.iid = "*VALUES*".column1))
Buffers: shared hit=520
I/O Timings: read=0.000 write=0.000
https://explain-depesz.postgres.ai/s/mR
How to set up and validate locally
You need a repository with at least 2 merge requests.
- Create markdown file
test.md
with following content
First reference !1
Second reference !2
- Open
test.md
file and wait till it loads - Verify that merge request links work
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Vasilii Iakliushin