1 files changed, 39 insertions, 14 deletions

diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb
index b2d4f9938ff..5d5f72c4d86 100644
--- a/app/finders/issuable_finder.rb
+++ b/app/finders/issuable_finder.rb
@@ -6,7 +6,7 @@
 #   klass - actual class like Issue or MergeRequest
 #   current_user - which user use
 #   params:
-#     scope: 'created-by-me' or 'assigned-to-me' or 'all'
+#     scope: 'created_by_me' or 'assigned_to_me' or 'all'
 #     state: 'opened' or 'closed' or 'all'
 #     group_id: integer
 #     project_id: integer
@@ -23,6 +23,7 @@
 #     created_before: datetime
 #     updated_after: datetime
 #     updated_before: datetime
+#     use_cte_for_search: boolean
 #
 class IssuableFinder
   prepend FinderWithCrossProjectAccess
@@ -54,6 +55,7 @@ class IssuableFinder
       sort
       state
       include_subgroups
+      use_cte_for_search
     ]
   end
 
@@ -74,19 +76,21 @@ class IssuableFinder
     items = init_collection
     items = filter_items(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)
+    # This has to be last as we may use a CTE as an optimization fence by
+    # passing the use_cte_for_search param
+    # https://www.postgresql.org/docs/current/static/queries-with.html
+    items = by_search(items)
 
     sort(items)
   end
 
   def filter_items(items)
+    items = by_project(items)
     items = by_scope(items)
     items = by_created_at(items)
     items = by_updated_at(items)
     items = by_state(items)
     items = by_group(items)
-    items = by_search(items)
     items = by_assignee(items)
     items = by_author(items)
     items = by_non_archived(items)
@@ -107,7 +111,6 @@ class IssuableFinder
   #
   def count_by_state
     count_params = params.merge(state: nil, sort: nil)
-    labels_count = label_names.any? ? label_names.count : 1
     finder = self.class.new(current_user, count_params)
     counts = Hash.new(0)
 
@@ -116,6 +119,11 @@ class IssuableFinder
     # per issuable, so we have to count those in Ruby - which is bad, but still
     # better than performing multiple queries.
     #
+    # This does not apply when we are using a CTE for the search, as the labels
+    # GROUP BY is inside the subquery in that case, so we set labels_count to 1.
+    labels_count = label_names.any? ? label_names.count : 1
+    labels_count = 1 if use_cte_for_search?
+
     finder.execute.reorder(nil).group(:state).count.each do |key, value|
       counts[Array(key).last.to_sym] += value / labels_count
     end
@@ -159,7 +167,7 @@ class IssuableFinder
         finder_options = { include_subgroups: params[:include_subgroups], only_owned: true }
         GroupProjectsFinder.new(group: group, current_user: current_user, options: finder_options).execute
       else
-        ProjectsFinder.new(current_user: current_user, project_ids_relation: item_project_ids(items)).execute
+        ProjectsFinder.new(current_user: current_user).execute
       end
 
     @projects = projects.with_feature_available_for_user(klass, current_user).reorder(nil)
@@ -279,9 +287,9 @@ class IssuableFinder
     return items.none if current_user_related? && !current_user
 
     case params[:scope]
-    when 'created-by-me', 'authored'
+    when 'created_by_me', 'authored'
       items.where(author_id: current_user.id)
-    when 'assigned-to-me'
+    when 'assigned_to_me'
       items.assigned_to(current_user)
     else
       items
@@ -316,9 +324,9 @@ class IssuableFinder
   def by_project(items)
     items =
       if project?
-        items.of_projects(projects(items)).references_project
-      elsif projects(items)
-        items.merge(projects(items).reorder(nil)).join_project
+        items.of_projects(projects).references_project
+      elsif projects
+        items.merge(projects.reorder(nil)).join_project
       else
         items.none
       end
@@ -326,8 +334,24 @@ class IssuableFinder
     items
   end
 
+  def use_cte_for_search?
+    return false unless search
+    return false unless Gitlab::Database.postgresql?
+
+    params[:use_cte_for_search]
+  end
+
   def by_search(items)
-    search ? items.full_search(search) : items
+    return items unless search
+
+    if use_cte_for_search?
+      cte = Gitlab::SQL::RecursiveCTE.new(klass.table_name)
+      cte << items
+
+      items = klass.with(cte.to_arel).from(klass.table_name)
+    end
+
+    items.full_search(search)
   end
 
   def by_iids(items)
@@ -337,7 +361,7 @@ class IssuableFinder
   def sort(items)
     # Ensure we always have an explicit sort order (instead of inheriting
     # multiple orders when combining ActiveRecord::Relation objects).
-    params[:sort] ? items.sort(params[:sort], excluded_labels: label_names) : items.reorder(id: :desc)
+    params[:sort] ? items.sort_by_attribute(params[:sort], excluded_labels: label_names) : items.reorder(id: :desc)
   end
 
   def by_assignee(items)
@@ -423,6 +447,7 @@ class IssuableFinder
   end
 
   def current_user_related?
-    params[:scope] == 'created-by-me' || params[:scope] == 'authored' || params[:scope] == 'assigned-to-me'
+    scope = params[:scope]
+    scope == 'created_by_me' || scope == 'authored' || scope == 'assigned_to_me'
   end
 end