Use a UNION in MergeRequest.in_projects

The OR condition for source_project_id/target_project_id leads to a
query plan that performs rather poorly on PostgreSQL due to the use of
sub-queries. Because Rails offers no easy alternative for this
particular problem we're forced to using a UNION for both conditions.
The resulting query performs much faster than just using an OR.

2 files changed, 24 insertions, 1 deletions

diff --git a/app/models/merge_request.rb b/app/models/merge_request.rb
index c1e18bb3cc5..188325045e2 100644
--- a/app/models/merge_request.rb
+++ b/app/models/merge_request.rb
@@ -135,7 +135,6 @@ class MergeRequest < ActiveRecord::Base
   scope :by_branch, ->(branch_name) { where("(source_branch LIKE :branch) OR (target_branch LIKE :branch)", branch: branch_name) }
   scope :cared, ->(user) { where('assignee_id = :user OR author_id = :user', user: user.id) }
   scope :by_milestone, ->(milestone) { where(milestone_id: milestone) }
-  scope :in_projects, ->(project_ids) { where("source_project_id in (:project_ids) OR target_project_id in (:project_ids)", project_ids: project_ids) }
   scope :of_projects, ->(ids) { where(target_project_id: ids) }
   scope :merged, -> { with_state(:merged) }
   scope :closed_and_merged, -> { with_states(:closed, :merged) }
@@ -161,6 +160,24 @@ class MergeRequest < ActiveRecord::Base
     super("merge_requests", /(?<merge_request>\d+)/)
   end
 
+  # Returns all the merge requests from an ActiveRecord:Relation.
+  #
+  # This method uses a UNION as it usually operates on the result of
+  # ProjectsFinder#execute. PostgreSQL in particular doesn't always like queries
+  # using multiple sub-queries especially when combined with an OR statement.
+  # UNIONs on the other hand perform much better in these cases.
+  #
+  # relation - An ActiveRecord::Relation that returns a list of Projects.
+  #
+  # Returns an ActiveRecord::Relation.
+  def self.in_projects(relation)
+    source = where(source_project_id: relation).select(:id)
+    target = where(target_project_id: relation).select(:id)
+    union  = Gitlab::SQL::Union.new([source, target])
+
+    where("merge_requests.id IN (#{union.to_sql})")
+  end
+
   def to_reference(from_project = nil)
     reference = "#{self.class.reference_prefix}#{iid}"
 
diff --git a/spec/models/merge_request_spec.rb b/spec/models/merge_request_spec.rb
index 59c40922abb..8bf68013fd2 100644
--- a/spec/models/merge_request_spec.rb
+++ b/spec/models/merge_request_spec.rb
@@ -80,6 +80,12 @@ describe MergeRequest, models: true do
     it { is_expected.to respond_to(:merge_when_build_succeeds) }
   end
 
+  describe '.in_projects' do
+    it 'returns the merge requests for a set of projects' do
+      expect(described_class.in_projects(Project.all)).to eq([subject])
+    end
+  end
+
   describe '#to_reference' do
     it 'returns a String reference to the object' do
       expect(subject.to_reference).to eq "!#{subject.iid}"