Perform SQL matching of Build&Runner tags to greatly speed-up job picking

2 files changed, 22 insertions, 3 deletions

diff --git a/app/models/ci/build.rb b/app/models/ci/build.rb
index 8738e094510..d2402b55184 100644
--- a/app/models/ci/build.rb
+++ b/app/models/ci/build.rb
@@ -47,6 +47,25 @@ module Ci
     scope :manual_actions, ->() { where(when: :manual, status: COMPLETED_STATUSES + [:manual]) }
     scope :ref_protected, -> { where(protected: true) }
 
+    scope :matches_tag_ids, -> (tag_ids) do
+      matcher = ::ActsAsTaggableOn::Tagging
+        .where(taggable_type: CommitStatus)
+        .where(context: 'tags')
+        .where('taggable_id = ci_builds.id')
+        .where.not(tag_id: tag_ids).select('1')
+
+      where("NOT EXISTS (?)", matcher)
+    end
+
+    scope :with_any_tags, -> do
+      matcher = ::ActsAsTaggableOn::Tagging
+        .where(taggable_type: CommitStatus)
+        .where(context: 'tags')
+        .where('taggable_id = ci_builds.id').select('1')
+
+      where("EXISTS (?)", matcher)
+    end
+
     mount_uploader :legacy_artifacts_file, LegacyArtifactUploader, mount_on: :artifacts_file
     mount_uploader :legacy_artifacts_metadata, LegacyArtifactUploader, mount_on: :artifacts_metadata
 
diff --git a/app/models/ci/runner.rb b/app/models/ci/runner.rb
index d39610a8995..dcbb397fb78 100644
--- a/app/models/ci/runner.rb
+++ b/app/models/ci/runner.rb
@@ -112,7 +112,7 @@ module Ci
     def can_pick?(build)
       return false if self.ref_protected? && !build.protected?
 
-      assignable_for?(build.project) && accepting_tags?(build)
+      assignable_for?(build.project_id) && accepting_tags?(build)
     end
 
     def only_for?(project)
@@ -171,8 +171,8 @@ module Ci
       end
     end
 
-    def assignable_for?(project)
-      is_shared? || projects.exists?(id: project.id)
+    def assignable_for?(project_id)
+      is_shared? || projects.exists?(id: project_id)
     end
 
     def accepting_tags?(build)