use union instead of multiple joins

the unions performs much better than the joins, and the execution time
is constant with a growing number of records.

1 files changed, 15 insertions, 14 deletions

diff --git a/app/models/ci/runner.rb b/app/models/ci/runner.rb
index 8f8dfbda412..cfed4a2eeea 100644
--- a/app/models/ci/runner.rb
+++ b/app/models/ci/runner.rb
@@ -29,33 +29,34 @@ module Ci
     scope :ordered, ->() { order(id: :desc) }
 
     scope :owned_or_shared, ->(project_id) do
-      joins(
+      project_runners = joins(
         %{
-          -- project runners
           LEFT JOIN ci_runner_projects ON ci_runner_projects.runner_id = ci_runners.id
+        }
+      ).where(
+        %{
+          ci_runner_projects.project_id = :project_id
+        },
+        project_id: project_id
+      )
 
-          -- group runners
+      group_runners = joins(
+        %{
           LEFT JOIN ci_runner_groups ON ci_runner_groups.runner_id = ci_runners.id
           LEFT JOIN namespaces ON namespaces.id = ci_runner_groups.group_id
           LEFT JOIN projects group_projects ON group_projects.namespace_id = namespaces.id
         }
       ).where(
         %{
-          -- project runners
-          ci_runner_projects.project_id = :project_id
-
-            OR
-
-          -- group runners
           group_projects.id = :project_id
-
-            OR
-
-          -- shared runners
-          ci_runners.is_shared = true
         },
         project_id: project_id
       )
+
+      shared_runners = where(is_shared: true)
+
+      union = Gitlab::SQL::Union.new([project_runners, group_runners, shared_runners])
+      from("(#{union.to_sql}) ci_runners")
     end
 
     scope :assignable_for, ->(project) do