Use a JOIN in IssuableFinder#by_project

When using IssuableFinder/IssuesFinder to find issues for multiple
projects it's more efficient to use a JOIN + a "WHERE project_id IN"
condition opposed to running a sub-query.

This change means that when finding issues without labels we're now
using the following SQL:

    SELECT issues.*
    FROM issues
    JOIN projects ON projects.id = issues.project_id

    LEFT JOIN label_links ON label_links.target_type = 'Issue'
                          AND label_links.target_id  = issues.id

    WHERE (
        projects.id IN (...)
        OR projects.visibility_level IN (20, 10)
    )
    AND issues.state IN ('opened','reopened')
    AND label_links.id IS NULL
    ORDER BY issues.id DESC;

instead of:

    SELECT issues.*
    FROM issues
    LEFT JOIN label_links ON label_links.target_type = 'Issue'
                          AND label_links.target_id  = issues.id

    WHERE issues.project_id IN (
        SELECT id
        FROM projects
        WHERE id IN (...)
        OR visibility_level IN (20,10)
    )
    AND issues.state IN ('opened','reopened')
    AND label_links.id IS NULL
    ORDER BY issues.id DESC;

The big benefit here is that in the last case PostgreSQL can't properly
use all available indexes. In particular it ends up performing a
sequence scan on the "label_links" table (processing around 290 000
rows). The new query is roughly 2x as fast as the old query.

2 files changed, 6 insertions, 0 deletions

diff --git a/app/models/concerns/issuable.rb b/app/models/concerns/issuable.rb
index 492a026add9..97d3cb18f4e 100644
--- a/app/models/concerns/issuable.rb
+++ b/app/models/concerns/issuable.rb
@@ -35,6 +35,9 @@ module Issuable
     scope :order_milestone_due_desc, -> { joins(:milestone).reorder('milestones.due_date DESC, milestones.id DESC') }
     scope :order_milestone_due_asc, -> { joins(:milestone).reorder('milestones.due_date ASC, milestones.id ASC') }
 
+    scope :join_project, -> { joins(:project) }
+    scope :references_project, -> { references(:project) }
+
     delegate :name,
              :email,
              to: :author,
diff --git a/app/models/merge_request.rb b/app/models/merge_request.rb
index 2eb03b8ba5b..1e8d9908f0a 100644
--- a/app/models/merge_request.rb
+++ b/app/models/merge_request.rb
@@ -134,6 +134,9 @@ class MergeRequest < ActiveRecord::Base
   scope :closed, -> { with_state(:closed) }
   scope :closed_and_merged, -> { with_states(:closed, :merged) }
 
+  scope :join_project, -> { joins(:target_project) }
+  scope :references_project, -> { references(:target_project) }
+
   def self.reference_prefix
     '!'
   end