Improve query for merge request collaboration check
What does this MR do and why?
We only query projects for forks since these are the only ones that matter regarding the allow_collaboration check.
I found out in #425430 (closed) that we are iterating over a lot of MRs unnecessarily. This is a problem because in 21797c5e the DB field is defaulted to true
.
Previously, users can only set this to true
on these forked project MRs.
Old query
SELECT * FROM merge_requests WHERE source_project_id = 278964 AND state_id = 1 AND allow_maintainer_to_push = TRUE;
gitlabhq_dblab=# EXPLAIN ANALYZE SELECT * FROM merge_requests WHERE source_project_id = 278964 AND state_id = 1 AND allow_maintainer_to_push = TRUE;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_merge_requests_on_source_project_and_branch_state_opened on merge_requests (cost=0.56..3786.70 rows=1853 width=892) (actual time=0.030..2.484 rows=1261 loops=1)
Index Cond: (source_project_id = 278964)
Filter: allow_maintainer_to_push
Rows Removed by Filter: 46
Planning Time: 0.362 ms
Execution Time: 2.597 ms
New query
SELECT * FROM merge_requests WHERE source_project_id = 278964 AND state_id = 1 AND allow_maintainer_to_push = TRUE AND source_project_id <> target_project_id;
gitlabhq_dblab=# EXPLAIN ANALYZE SELECT * FROM merge_requests WHERE source_project_id = 278964 AND state_id = 1 AND allow_maintainer_to_push = TRUE AND source_project_id <> target_project_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_merge_requests_on_source_project_and_branch_state_opened on merge_requests (cost=0.56..3793.81 rows=1844 width=892) (actual time=2.180..2.181 rows=0 loops=1)
Index Cond: (source_project_id = 278964)
Filter: (allow_maintainer_to_push AND (source_project_id <> target_project_id))
Rows Removed by Filter: 1307
Planning Time: 0.379 ms
Execution Time: 2.246 ms
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.
Related to #425430 (closed)
Edited by Heinrich Lee Yu