Improve issues filtering performance

4 files changed, 35 insertions, 14 deletions

diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb
index f49301e2631..2fca012252e 100644
--- a/app/finders/issuable_finder.rb
+++ b/app/finders/issuable_finder.rb
@@ -33,15 +33,17 @@ class IssuableFinder
     items = by_scope(items)
     items = by_state(items)
     items = by_group(items)
-    items = by_project(items)
     items = by_search(items)
-    items = by_milestone(items)
     items = by_assignee(items)
     items = by_author(items)
-    items = by_label(items)
     items = by_due_date(items)
     items = by_non_archived(items)
     items = by_iids(items)
+    items = by_milestone(items)
+    items = by_label(items)
+
+    # Filtering by project HAS TO be the last because we use the project IDs yielded by the issuable query thus far
+    items = by_project(items)
     sort(items)
   end
 
@@ -107,8 +109,7 @@ class IssuableFinder
     @project = project
   end
 
-  def projects
-    return @projects if defined?(@projects)
+  def projects(items = nil)
     return @projects = project if project?
 
     projects =
@@ -117,7 +118,7 @@ class IssuableFinder
       elsif group
         GroupProjectsFinder.new(group).execute(current_user)
       else
-        ProjectsFinder.new.execute(current_user)
+        projects_finder.execute(current_user, item_project_ids(items))
       end
 
     @projects = projects.with_feature_available_for_user(klass, current_user).reorder(nil)
@@ -257,9 +258,9 @@ class IssuableFinder
   def by_project(items)
     items =
       if project?
-        items.of_projects(projects).references_project
-      elsif projects
-        items.merge(projects.reorder(nil)).join_project
+        items.of_projects(projects(items)).references_project
+      elsif projects(items)
+        items.merge(projects(items).reorder(nil)).join_project
       else
         items.none
       end
@@ -314,13 +315,14 @@ class IssuableFinder
       if filter_by_no_milestone?
         items = items.left_joins_milestones.where(milestone_id: [-1, nil])
       elsif filter_by_upcoming_milestone?
-        upcoming_ids = Milestone.upcoming_ids_by_projects(projects)
+        upcoming_ids = Milestone.upcoming_ids_by_projects(projects(items))
         items = items.left_joins_milestones.where(milestone_id: upcoming_ids)
       else
         items = items.with_milestone(params[:milestone_title])
+        items_projects = projects(items)
 
-        if projects
-          items = items.where(milestones: { project_id: projects })
+        if items_projects
+          items = items.where(milestones: { project_id: items_projects })
         end
       end
     end
@@ -334,9 +336,10 @@ class IssuableFinder
         items = items.without_label
       else
         items = items.with_label(label_names, params[:sort])
+        items_projects = projects(items)
 
-        if projects
-          label_ids = LabelsFinder.new(current_user, project_ids: projects).execute(skip_authorization: true).select(:id)
+        if items_projects
+          label_ids = LabelsFinder.new(current_user, project_ids: items_projects).execute(skip_authorization: true).select(:id)
           items = items.where(labels: { id: label_ids })
         end
       end
@@ -396,4 +399,8 @@ class IssuableFinder
   def current_user_related?
     params[:scope] == 'created-by-me' || params[:scope] == 'authored' || params[:scope] == 'assigned-to-me'
   end
+
+  def projects_finder
+    @projects_finder ||= ProjectsFinder.new
+  end
 end
diff --git a/app/finders/issues_finder.rb b/app/finders/issues_finder.rb
index f542f72a386..08713272947 100644
--- a/app/finders/issues_finder.rb
+++ b/app/finders/issues_finder.rb
@@ -41,4 +41,8 @@ class IssuesFinder < IssuableFinder
       user_id: user.id,
       project_ids: user.authorized_projects(Gitlab::Access::REPORTER).select(:id))
   end
+
+  def item_project_ids(items)
+    items&.reorder(nil)&.select(:project_id)
+  end
 end
diff --git a/app/finders/merge_requests_finder.rb b/app/finders/merge_requests_finder.rb
index b76ca389f38..1eec45d9cb5 100644
--- a/app/finders/merge_requests_finder.rb
+++ b/app/finders/merge_requests_finder.rb
@@ -20,4 +20,10 @@ class MergeRequestsFinder < IssuableFinder
   def klass
     MergeRequest
   end
+
+  private
+
+  def item_project_ids(items)
+    items&.reorder(nil)&.select(:target_project_id)
+  end
 end
diff --git a/changelogs/unreleased/25503_issues_finder_performance.yml b/changelogs/unreleased/25503_issues_finder_performance.yml
new file mode 100644
index 00000000000..87964269c6d
--- /dev/null
+++ b/changelogs/unreleased/25503_issues_finder_performance.yml
@@ -0,0 +1,4 @@
+---
+title: Filter by projects in the end of search
+merge_request: 9030
+author: