From 4f3fa519c627b7caf45273226ae438181dfbc392 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Mon, 7 Mar 2016 12:56:46 +0100 Subject: 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. --- app/models/merge_request.rb | 19 ++++++++++++++++++- spec/models/merge_request_spec.rb | 6 ++++++ 2 files changed, 24 insertions(+), 1 deletion(-) 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", /(?\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}" -- cgit v1.2.1