Remove source_project_id from basic search for merge requests
What does this MR do?
Related to #327106
This change brings the Basic Search (backed by PostgreSQL) for Merge Requests in line with how Advanced Search (backed by Elasticsearch) works. Basic Search for merge requests currently searches both target_project_id and source_project_id while Advanced Search only searches target_project_id. This will improve performance for Global and Group searches, Project searches had this removed in another MR (!67921 (merged))
Database
Notes:
- all SQL uses my user id and gitlab-org group id
- global searches for merge requests are not returning in any reasonable amount of time (>100 minutes) so i won't include them for timings
Group Search - Merge Requests
Before
Explain plan: https://console.postgres.ai/shared/2b1e8fab-6f15-41c2-96e4-b989e4ae0172 (need to delete my database-lab instance so this link may not work)
explain plan
Limit (cost=54721.27..54721.28 rows=1 width=2551) (actual time=33651.406..33651.433 rows=21 loops=1)
Buffers: shared hit=2740723 read=391242
I/O Timings: read=9346.880 write=0.000
-> Sort (cost=54721.27..54721.28 rows=1 width=2551) (actual time=33651.404..33651.427 rows=21 loops=1)
Sort Key: merge_requests.created_at DESC
Sort Method: top-N heapsort Memory: 71kB
Buffers: shared hit=2740723 read=391242
I/O Timings: read=9346.880 write=0.000
-> Nested Loop Left Join (cost=50145.66..54721.26 rows=1 width=2551) (actual time=32686.200..33582.842 rows=76606 loops=1)
Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 73
Buffers: shared hit=2740720 read=391242
I/O Timings: read=9346.880 write=0.000
-> Hash Semi Join (cost=50145.22..54717.15 rows=1 width=2555) (actual time=32686.161..33311.746 rows=76679 loops=1)
Hash Cond: (projects.namespace_id = namespaces.id)
Buffers: shared hit=2417824 read=390822
I/O Timings: read=9314.934 write=0.000
-> Nested Loop (cost=48551.21..53122.18 rows=367 width=2559) (actual time=32511.080..33092.968 rows=76687 loops=1)
Buffers: shared hit=2416932 read=390402
I/O Timings: read=9145.630 write=0.000
-> HashAggregate (cost=48550.77..48557.24 rows=647 width=2551) (actual time=32511.039..32614.389 rows=76687 loops=1)
Group Key: merge_requests.id, merge_requests.target_branch, merge_requests.source_branch, merge_requests.source_project_id, merge_requests.author_id, merge_requests.assignee_id, merge_requests.title, merge_requests.created_at, merge_requests.updated_at, merge_requests.milestone_id, merge_requests.merge_status, merge_requests.target_project_id, merge_requests.iid, merge_requests.description, merge_requests.updated_by_id, merge_requests.merge_error, merge_requests.merge_params, merge_requests.merge_when_pipeline_succeeds, merge_requests.merge_user_id, merge_requests.merge_commit_sha, merge_requests.approvals_before_merge, merge_requests.rebase_commit_sha, merge_requests.in_progress_merge_commit_sha, merge_requests.lock_version, merge_requests.title_html, merge_requests.description_html, merge_requests.time_estimate, merge_requests.squash, merge_requests.cached_markdown_version, merge_requests.last_edited_at, merge_requests.last_edited_by_id, merge_requests.head_pipeline_id, merge_requests.merge_jid, merge_requests.discussion_locked, merge_requests.latest_merge_request_diff_id, merge_requests.allow_maintainer_to_push, merge_requests.state_id, merge_requests.rebase_jid, merge_requests.squash_commit_sha, merge_requests.sprint_id, merge_requests.merge_ref_sha, merge_requests.draft
Buffers: shared hit=1801033 read=390101
I/O Timings: read=9095.817 write=0.000
-> Append (cost=14085.52..48482.83 rows=647 width=2551) (actual time=384.426..21134.684 rows=143387 loops=1)
Buffers: shared hit=352607 read=317940
I/O Timings: read=7373.980 write=0.000
-> Nested Loop (cost=14085.52..24190.10 rows=323 width=769) (actual time=384.425..11240.840 rows=66708 loops=1)
Buffers: shared hit=162724 read=156572
I/O Timings: read=4628.286 write=0.000
-> HashAggregate (cost=14084.95..14095.59 rows=1064 width=8) (actual time=382.396..383.558 rows=1422 loops=1)
Group Key: projects_1.id
Buffers: shared hit=11134 read=3356
I/O Timings: read=345.345 write=0.000
-> Nested Loop (cost=1.00..14082.29 rows=1064 width=8) (actual time=2.612..381.022 rows=1422 loops=1)
Buffers: shared hit=11134 read=3356
I/O Timings: read=345.345 write=0.000
-> Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..3.59 rows=2004 width=4) (actual time=2.473..181.137 rows=1526 loops=1)
Index Cond: (((rs.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs.path)::text ~<~ 'gitlab-org0'::text))
Filter: (((rs.path)::text ~~ 'gitlab-org/%'::text) AND ((rs.source_type)::text = 'Project'::text))
Rows Removed by Filter: 261
Buffers: shared hit=223 read=1510
I/O Timings: read=173.802 write=0.000
-> Index Scan using projects_pkey on public.projects projects_1 (cost=0.44..7.03 rows=1 width=4) (actual time=0.130..0.130 rows=1 loops=1526)
Index Cond: (projects_1.id = rs.source_id)
Filter: ((NOT projects_1.archived) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (projects_1.visibility_level = ANY ('{10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=10911 read=1846
I/O Timings: read=171.543 write=0.000
SubPlan 5
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_4 (cost=0.57..3.59 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1422)
Index Cond: ((project_authorizations_4.user_id = 5708766) AND (project_authorizations_4.project_id = projects_1.id))
Heap Fetches: 85
Buffers: shared hit=6653
I/O Timings: read=0.000 write=0.000
SubPlan 6
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_5 (cost=0.57..144.95 rows=5350 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_5.user_id = 5708766)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_merge_requests_on_source_project_id_and_source_branch on public.merge_requests (cost=0.57..9.19 rows=24 width=769) (actual time=0.190..7.604 rows=47 loops=1422)
Index Cond: (merge_requests.source_project_id = projects_1.id)
Filter: (((merge_requests.title)::text ~~* '%test%'::text) OR (merge_requests.description ~~* '%test%'::text))
Rows Removed by Filter: 64
Buffers: shared hit=151590 read=153216
I/O Timings: read=4282.941 write=0.000
-> Nested Loop (cost=14085.52..24283.03 rows=324 width=769) (actual time=416.275..9861.681 rows=76679 loops=1)
Buffers: shared hit=189883 read=161368
I/O Timings: read=2745.694 write=0.000
-> HashAggregate (cost=14084.95..14095.59 rows=1064 width=8) (actual time=414.454..415.436 rows=1422 loops=1)
Group Key: projects_2.id
Buffers: shared hit=10916 read=3574
I/O Timings: read=387.508 write=0.000
-> Nested Loop (cost=1.00..14082.29 rows=1064 width=8) (actual time=0.188..413.180 rows=1422 loops=1)
Buffers: shared hit=10916 read=3574
I/O Timings: read=387.508 write=0.000
-> Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs_1 (cost=0.56..3.59 rows=2004 width=4) (actual time=0.151..134.480 rows=1526 loops=1)
Index Cond: (((rs_1.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs_1.path)::text ~<~ 'gitlab-org0'::text))
Filter: (((rs_1.path)::text ~~ 'gitlab-org/%'::text) AND ((rs_1.source_type)::text = 'Project'::text))
Rows Removed by Filter: 261
Buffers: shared hit=150 read=1583
I/O Timings: read=129.128 write=0.000
-> Index Scan using projects_pkey on public.projects projects_2 (cost=0.44..7.03 rows=1 width=4) (actual time=0.181..0.181 rows=1 loops=1526)
Index Cond: (projects_2.id = rs_1.source_id)
Filter: ((NOT projects_2.archived) AND ((alternatives: SubPlan 7 or hashed SubPlan 8) OR (projects_2.visibility_level = ANY ('{10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=10766 read=1991
I/O Timings: read=258.380 write=0.000
SubPlan 7
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_6 (cost=0.57..3.59 rows=1 width=0) (actual time=0.112..0.112 rows=1 loops=1422)
Index Cond: ((project_authorizations_6.user_id = 5708766) AND (project_authorizations_6.project_id = projects_2.id))
Heap Fetches: 85
Buffers: shared hit=6587 read=66
I/O Timings: read=152.702 write=0.000
SubPlan 8
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_7 (cost=0.57..144.95 rows=5350 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_7.user_id = 5708766)
Heap Fetches: 0
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 merge_requests_1 (cost=0.57..9.27 rows=24 width=769) (actual time=0.122..6.607 rows=54 loops=1422)
Index Cond: (merge_requests_1.target_project_id = projects_2.id)
Filter: (((merge_requests_1.title)::text ~~* '%test%'::text) OR (merge_requests_1.description ~~* '%test%'::text))
Rows Removed by Filter: 71
Buffers: shared hit=178967 read=157794
I/O Timings: read=2358.186 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..7.05 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=76687)
Index Cond: (projects.id = merge_requests.target_project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=615899 read=301
I/O Timings: read=49.813 write=0.000
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=76687)
Index Cond: ((project_authorizations.user_id = 5708766) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 81
Buffers: shared hit=309439 read=13
I/O Timings: read=20.203 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..145.58 rows=4344 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 5708766) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Hash (cost=1591.50..1591.50 rows=201 width=4) (actual time=175.057..175.061 rows=262 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
Buffers: shared hit=892 read=420
I/O Timings: read=169.304 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=1585.47..1589.49 rows=201 width=4) (actual time=0.316..174.900 rows=262 loops=1)
Buffers: shared hit=892 read=420
I/O Timings: read=169.304 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1585.47 rows=201 width=356) (actual time=0.312..174.346 rows=262 loops=1)
Buffers: shared hit=892 read=420
I/O Timings: read=169.304 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=356) (actual time=0.298..0.299 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared read=4
I/O Timings: read=0.256 write=0.000
-> Nested Loop (cost=0.56..157.80 rows=20 width=356) (actual time=0.882..28.813 rows=44 loops=6)
Buffers: shared hit=892 read=416
I/O Timings: read=169.048 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.017 rows=44 loops=6)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.74 rows=2 width=356) (actual time=0.401..0.658 rows=1 loops=262)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=892 read=416
I/O Timings: read=169.048 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=76679)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=306296 read=420
I/O Timings: read=31.946 write=0.000
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=4138)
Index Cond: ((project_authorizations_2.user_id = 5708766) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 20))
Heap Fetches: 3
Buffers: shared hit=16600
I/O Timings: read=0.000 write=0.000
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..145.58 rows=4344 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_3.user_id = 5708766) AND (project_authorizations_3.access_level >= 20))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000SQL
SELECT
"merge_requests".*
FROM ((
SELECT
"merge_requests".*
FROM
"merge_requests"
WHERE
"merge_requests"."source_project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (10, 20))
AND "projects"."archived" = FALSE
AND (rs.path LIKE 'gitlab-org/%')))
UNION (
SELECT
"merge_requests".*
FROM
"merge_requests"
WHERE
"merge_requests"."target_project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (10, 20))
AND "projects"."archived" = FALSE
AND (rs.path LIKE 'gitlab-org/%')))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces")
AND (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10, 20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20, 30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND ("merge_requests"."title" ILIKE '%test%'
OR "merge_requests"."description" ILIKE '%test%')
ORDER BY
created_at DESC
LIMIT 21 OFFSET 0cold cache
Time: 5.57087668 min
- planning: 29.544 ms
- execution: 5.57087668 min
- I/O read: 5.404 min
- I/O write: 0.164 ms
Shared buffers:
- hits: 2609162 (~19.90 GiB) from the buffer pool
- reads: 523939 (~4.00 GiB) from the OS file cache, including disk I/O
- dirtied: 4568 (~35.70 MiB)
- writes: 3 (~24.00 KiB)warm cache
Time: 33.680 s
- planning: 21.318 ms
- execution: 33.659 s
- I/O read: 9.347 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 2740723 (~20.90 GiB) from the buffer pool
- reads: 391242 (~3.00 GiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0After
Explain plan: explain.depesz.com keeps giving me an error so the plan is below
plan
Limit (cost=20147.41..20147.42 rows=1 width=769) (actual time=15768.352..15768.506 rows=21 loops=1)
Buffers: shared hit=453401 read=235008
I/O Timings: read=7323.376 write=0.000
-> Sort (cost=20147.41..20147.42 rows=1 width=769) (actual time=15768.350..15768.499 rows=21 loops=1)
Sort Key: merge_requests.created_at DESC
Sort Method: top-N heapsort Memory: 83kB
Buffers: shared hit=453401 read=235008
I/O Timings: read=7323.376 write=0.000
-> Nested Loop Left Join (cost=15680.40..20147.40 rows=1 width=769) (actual time=286.914..15635.389 rows=76606 loops=1)
Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 73
Buffers: shared hit=453398 read=235008
I/O Timings: read=7323.376 write=0.000
-> Nested Loop (cost=15679.96..20143.29 rows=1 width=773) (actual time=286.579..15220.576 rows=76679 loops=1)
Buffers: shared hit=131010 read=234113
I/O Timings: read=7278.383 write=0.000
-> Hash Semi Join (cost=15679.40..20133.79 rows=1 width=12) (actual time=282.464..321.221 rows=1422 loops=1)
Hash Cond: (projects.namespace_id = namespaces.id)
Buffers: shared hit=22857 read=5486
I/O Timings: read=248.606 write=0.000
-> Nested Loop (cost=14085.39..18538.27 rows=577 width=16) (actual time=255.094..291.728 rows=1422 loops=1)
Buffers: shared hit=21999 read=5032
I/O Timings: read=225.968 write=0.000
-> HashAggregate (cost=14084.95..14095.59 rows=1064 width=8) (actual time=255.040..257.119 rows=1422 loops=1)
Group Key: projects_1.id
Buffers: shared hit=9785 read=4705
I/O Timings: read=220.935 write=0.000
-> Nested Loop (cost=1.00..14082.29 rows=1064 width=8) (actual time=1.305..253.614 rows=1422 loops=1)
Buffers: shared hit=9785 read=4705
I/O Timings: read=220.935 write=0.000
-> Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..3.59 rows=2004 width=4) (actual time=0.805..84.198 rows=1526 loops=1)
Index Cond: (((rs.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs.path)::text ~<~ 'gitlab-org0'::text))
Filter: (((rs.path)::text ~~ 'gitlab-org/%'::text) AND ((rs.source_type)::text = 'Project'::text))
Rows Removed by Filter: 261
Buffers: shared hit=97 read=1636
I/O Timings: read=77.930 write=0.000
-> Index Scan using projects_pkey on public.projects projects_1 (cost=0.44..7.03 rows=1 width=4) (actual time=0.110..0.110 rows=1 loops=1526)
Index Cond: (projects_1.id = rs.source_id)
Filter: ((NOT projects_1.archived) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (projects_1.visibility_level = ANY ('{10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=9688 read=3069
I/O Timings: read=143.004 write=0.000
SubPlan 5
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_4 (cost=0.57..3.59 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1422)
Index Cond: ((project_authorizations_4.user_id = 5708766) AND (project_authorizations_4.project_id = projects_1.id))
Heap Fetches: 85
Buffers: shared hit=6510 read=143
I/O Timings: read=10.767 write=0.000
SubPlan 6
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_5 (cost=0.57..144.95 rows=5350 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_5.user_id = 5708766)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..4.16 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=1422)
Index Cond: (projects.id = projects_1.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=12214 read=327
I/O Timings: read=5.033 write=0.000
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1422)
Index Cond: ((project_authorizations.user_id = 5708766) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 85
Buffers: shared hit=6840 read=13
I/O Timings: read=0.225 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..145.58 rows=4344 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 5708766) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Hash (cost=1591.50..1591.50 rows=201 width=4) (actual time=27.347..27.430 rows=262 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
Buffers: shared hit=858 read=454
I/O Timings: read=22.638 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=1585.47..1589.49 rows=201 width=4) (actual time=0.366..27.298 rows=262 loops=1)
Buffers: shared hit=858 read=454
I/O Timings: read=22.638 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1585.47 rows=201 width=356) (actual time=0.359..26.727 rows=262 loops=1)
Buffers: shared hit=858 read=454
I/O Timings: read=22.638 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=356) (actual time=0.348..0.357 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared read=4
I/O Timings: read=0.296 write=0.000
-> Nested Loop (cost=0.56..157.80 rows=20 width=356) (actual time=0.195..4.228 rows=44 loops=6)
Buffers: shared hit=858 read=450
I/O Timings: read=22.342 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.013 rows=44 loops=6)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.74 rows=2 width=356) (actual time=0.058..0.095 rows=1 loops=262)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=858 read=450
I/O Timings: read=22.342 write=0.000
-> Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.57..9.26 rows=24 width=769) (actual time=0.224..10.445 rows=54 loops=1422)
Index Cond: (merge_requests.target_project_id = projects.id)
Filter: (((merge_requests.title)::text ~~* '%test%'::text) OR (merge_requests.description ~~* '%test%'::text))
Rows Removed by Filter: 71
Buffers: shared hit=108153 read=228627
I/O Timings: read=7029.777 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.50 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=76679)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=305821 read=895
I/O Timings: read=44.993 write=0.000
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=4138)
Index Cond: ((project_authorizations_2.user_id = 5708766) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 20))
Heap Fetches: 3
Buffers: shared hit=16567
I/O Timings: read=0.000 write=0.000
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..145.58 rows=4344 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_3.user_id = 5708766) AND (project_authorizations_3.access_level >= 20))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000SQL
SELECT
"merge_requests".*
FROM
"merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces")
AND (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10, 20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20, 30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND ("merge_requests"."title" ILIKE '%test%'
OR "merge_requests"."description" ILIKE '%test%')
AND "merge_requests"."target_project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5708766
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (10, 20))
AND "projects"."archived" = FALSE
AND (rs.path LIKE 'gitlab-org/%'))
ORDER BY
created_at DESC
LIMIT 21 OFFSET 0cold cache
Time: 5.168 min
- planning: 31.035 ms
- execution: 5.168 min
- I/O read: 4.921 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 443568 (~3.40 GiB) from the buffer pool
- reads: 245158 (~1.90 GiB) from the OS file cache, including disk I/O
- dirtied: 1437 (~11.20 MiB)
- writes: 0warm cache
Time: 15.785 s
- planning: 14.834 ms
- execution: 15.770 s
- I/O read: 7.323 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 453401 (~3.50 GiB) from the buffer pool
- reads: 235008 (~1.80 GiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Screenshots or Screencasts (strongly suggested)
How to setup and validate locally (strongly suggested)
note: if you have Advanced Search enabled, you will need to disable it via the Admin UI - Advanced Search settings, OR add &basic_search=true to the search URL
- Navigate to the search UI
- Perform searches for merge requests (global, group, project)
- verify that
source_project_idis not included in the SQL
Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) - [-] I have tested this MR in all supported browsers, or it's not needed.
- [-] I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Security
Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.
- [-] 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
Edited by Mayra Cabrera