From 44fe828ed82d49aa35651a8ff676188955ef7474 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Matija=20=C4=8Cupi=C4=87?= <matteeyah@gmail.com>
Date: Mon, 2 Aug 2021 11:32:34 +0200
Subject: [PATCH] Optimize #latest_successful_for_refs method

Optimizes Ci::Pipeline#latest_successful_for_refs to load just the
needed branches in two queries. Instead of loading all branches in one
query then filtering the needed branches in memory.

Changelog: fixed
---
 app/models/ci/pipeline.rb       | 12 +++++++----
 spec/models/ci/pipeline_spec.rb | 36 +++++++++++++++++++++++++--------
 2 files changed, 36 insertions(+), 12 deletions(-)

diff --git a/app/models/ci/pipeline.rb b/app/models/ci/pipeline.rb
index 53003152791878..f9c7bb45018aaf 100644
--- a/app/models/ci/pipeline.rb
+++ b/app/models/ci/pipeline.rb
@@ -377,11 +377,15 @@ def self.latest_successful_for_sha(sha)
     end
 
     def self.latest_successful_for_refs(refs)
-      relation = newest_first(ref: refs).success
+      return Ci::Pipeline.none if refs.empty?
 
-      relation.each_with_object({}) do |pipeline, hash|
-        hash[pipeline.ref] ||= pipeline
-      end
+      refs_values = refs.map { |ref| "(#{connection.quote(ref)})" }.join(",")
+      join_query = success.where("refs_values.ref = ci_pipelines.ref").order(id: :desc).limit(1)
+
+      Ci::Pipeline
+        .from("(VALUES #{refs_values}) refs_values (ref)")
+        .joins("INNER JOIN LATERAL (#{join_query.to_sql}) #{Ci::Pipeline.table_name} ON TRUE")
+        .index_by(&:ref)
     end
 
     def self.latest_running_for_ref(ref)
diff --git a/spec/models/ci/pipeline_spec.rb b/spec/models/ci/pipeline_spec.rb
index 74a476a642219e..8ab94301f7e397 100644
--- a/spec/models/ci/pipeline_spec.rb
+++ b/spec/models/ci/pipeline_spec.rb
@@ -2263,18 +2263,38 @@ def create_pipeline(status, ref, sha)
     end
 
     describe '.latest_successful_for_refs' do
-      let!(:latest_successful_pipeline1) do
-        create_pipeline(:success, 'ref1', 'D')
-      end
+      subject(:latest_successful_for_refs) { described_class.latest_successful_for_refs(refs) }
+
+      context 'when refs are specified' do
+        let(:refs) { %w(first_ref second_ref third_ref) }
+
+        before do
+          create(:ci_empty_pipeline, id: 1001, status: :success, ref: 'first_ref', sha: 'sha')
+          create(:ci_empty_pipeline, id: 1002, status: :success, ref: 'second_ref', sha: 'sha')
+        end
 
-      let!(:latest_successful_pipeline2) do
-        create_pipeline(:success, 'ref2', 'D')
+        let!(:latest_successful_pipeline_for_first_ref) do
+          create(:ci_empty_pipeline, id: 2001, status: :success, ref: 'first_ref', sha: 'sha')
+        end
+
+        let!(:latest_successful_pipeline_for_second_ref) do
+          create(:ci_empty_pipeline, id: 2002, status: :success, ref: 'second_ref', sha: 'sha')
+        end
+
+        it 'returns the latest successful pipeline for both refs' do
+          expect(latest_successful_for_refs).to eq({
+            'first_ref' => latest_successful_pipeline_for_first_ref,
+            'second_ref' => latest_successful_pipeline_for_second_ref
+          })
+        end
       end
 
-      it 'returns the latest successful pipeline for both refs' do
-        refs = %w(ref1 ref2 ref3)
+      context 'when no refs are specified' do
+        let(:refs) { [] }
 
-        expect(described_class.latest_successful_for_refs(refs)).to eq({ 'ref1' => latest_successful_pipeline1, 'ref2' => latest_successful_pipeline2 })
+        it 'returns an empty relation whenno refs are specified' do
+          expect(latest_successful_for_refs).to be_empty
+        end
       end
     end
   end
-- 
GitLab