Merge remote-tracking branch 'upstream/master' into 14995-custom_wiki_sidebar

* upstream/master: (4180 commits)
  Enable frozen string literals for app/workers/*.rb
  Resolve "Search dropdown hides & shows when typing"
  Revert merge request widget button max height
  Update CHANGELOG.md for 11.0.2
  Update external link icon in header user dropdown
  Added Diff Viewer to new VUE based MR page
  Fixed eslint failure in IDE spec helpers
  Use refs instead of querySelector.
  Show file in tree on WebIDE open
  Resolve "Remove unused bootstrap component CSS"
  Resolve "Explain what Groups are in the New Group page"
  [QA] Make sure we wait for the deploy key list to load
  Update _scopes_form.html.haml to remove duplicate information
  Use the branch instead of the tag to install
  port the EE changes
  Add index on deployable_type/id for deployments
  Add a helper to rename a column using a background migration
  Fix performance bottleneck when rendering large wiki pages
  Port Namespace#root_ancestor to CE
  Remove duplicate spec
  ...

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