Skip to content

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.

Related to #425430 (closed)

Edited by Heinrich Lee Yu

Merge request reports