SQL query used for getting assigned merge requests is overly complex
Much like the query for assigned issues (https://gitlab.com/gitlab-org/gitlab-ce/issues/37125) the query used for getting assigned MRs is overly complex. This is the current query we run:
SELECT COUNT(*)
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 merge_requests.deleted_at IS NULL
AND merge_requests.state IN ('opened')
AND merge_requests.assignee_id = 1
AND (
EXISTS (
SELECT 1
FROM project_authorizations
WHERE project_authorizations.user_id = 1
AND project_authorizations.project_id = projects.id
)
OR projects.visibility_level IN (0,10,20)
)
AND projects.id IN (
SELECT merge_requests.target_project_id
FROM merge_requests
WHERE merge_requests.deleted_at IS NULL
AND merge_requests.state IN ('opened')
AND merge_requests.assignee_id = 1
)
AND (
project_features.merge_requests_access_level IN (10, 20)
OR project_features.merge_requests_access_level IS NULL
);This can just be the following:
SELECT COUNT(*)
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 merge_requests.deleted_at IS NULL
AND merge_requests.state IN ('opened')
AND merge_requests.assignee_id = 1
AND (
EXISTS (
SELECT 1
FROM project_authorizations
WHERE project_authorizations.user_id = 1
AND project_authorizations.project_id = projects.id
)
OR project_features.merge_requests_access_level IN (NULL, 10, 20)
);Query Plans
Current:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4773.77..4773.78 rows=1 width=8) (actual time=77.335..77.336 rows=1 loops=1)
-> Nested Loop Left Join (cost=65.21..4773.77 rows=1 width=0) (actual time=34.408..77.327 rows=11 loops=1)
Filter: ((project_features.merge_requests_access_level = ANY ('{10,20}'::integer[])) OR (project_features.merge_requests_access_level IS NULL))
-> Nested Loop Semi Join (cost=64.78..4773.28 rows=1 width=4) (actual time=34.382..77.203 rows=11 loops=1)
-> Nested Loop (cost=64.34..4303.12 rows=87 width=8) (actual time=15.514..17.950 rows=11 loops=1)
-> Bitmap Heap Scan on merge_requests (cost=63.91..3527.62 rows=87 width=4) (actual time=15.410..17.698 rows=11 loops=1)
Recheck Cond: (assignee_id = 1)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
Rows Removed by Filter: 1078
Heap Blocks: exact=1035
-> Bitmap Index Scan on index_merge_requests_on_assignee_id (cost=0.00..63.89 rows=1778 width=0) (actual time=14.640..14.640 rows=1090 loops=1)
Index Cond: (assignee_id = 1)
-> Index Scan using projects_pkey on projects (cost=0.43..8.90 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=11)
Index Cond: (id = merge_requests.target_project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..4.45 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=11)
Index Cond: ((user_id = 1) AND (project_id = projects.id))
Heap Fetches: 0
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..7.59 rows=74 width=4) (never executed)
Index Cond: (user_id = 1)
Heap Fetches: 0
-> Index Scan using index_merge_requests_on_target_project_id_and_iid on merge_requests merge_requests_1 (cost=0.43..5.35 rows=1 width=4) (actual time=5.384..5.384 rows=1 loops=11)
Index Cond: (target_project_id = projects.id)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text) AND (assignee_id = 1))
Rows Removed by Filter: 4921
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.48 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=11)
Index Cond: (projects.id = project_id)
Planning time: 3.842 ms
Execution time: 77.481 msUsing the query I propose above:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4229.60..4229.61 rows=1 width=8) (actual time=2.965..2.965 rows=1 loops=1)
-> Nested Loop Left Join (cost=64.95..4229.38 rows=87 width=0) (actual time=1.123..2.957 rows=11 loops=1)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (project_features.merge_requests_access_level = ANY ('{NULL,10,20}'::integer[])))
-> Nested Loop (cost=64.52..3794.19 rows=88 width=4) (actual time=0.597..2.398 rows=11 loops=1)
-> Bitmap Heap Scan on merge_requests (cost=64.09..3571.93 rows=88 width=4) (actual time=0.558..2.316 rows=11 loops=1)
Recheck Cond: (assignee_id = 1)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
Rows Removed by Filter: 1078
Heap Blocks: exact=1035
-> Bitmap Index Scan on index_merge_requests_on_assignee_id (cost=0.00..64.06 rows=1801 width=0) (actual time=0.262..0.262 rows=1090 loops=1)
Index Cond: (assignee_id = 1)
-> Index Only Scan using projects_pkey on projects (cost=0.43..2.52 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=11)
Index Cond: (id = merge_requests.target_project_id)
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11)
Index Cond: (projects.id = project_id)
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..4.45 rows=1 width=0) (never executed)
Index Cond: ((user_id = 1) AND (project_id = projects.id))
Heap Fetches: 0
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..7.59 rows=74 width=4) (actual time=0.021..0.351 rows=309 loops=1)
Index Cond: (user_id = 1)
Heap Fetches: 8
Planning time: 1.041 ms
Execution time: 3.031 msEdited by Yorick Peterse