Merge branch '43552-user-owned-projects-query-performance-improvement' into 'master'

Resolve "Slow SQL running in production queryid 3257994922"

Closes #43552

See merge request gitlab-org/gitlab-ce!17934

2 files changed, 15 insertions, 3 deletions

diff --git a/app/models/user.rb b/app/models/user.rb
index b8c55205ab8..fa54581d220 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -623,9 +623,7 @@ class User < ActiveRecord::Base
   end
 
   def owned_projects
-    @owned_projects ||=
-      Project.where('namespace_id IN (?) OR namespace_id = ?',
-                    owned_groups.select(:id), namespace.id).joins(:namespace)
+    @owned_projects ||= Project.from("(#{owned_projects_union.to_sql}) AS projects")
   end
 
   # Returns projects which user can admin issues on (for example to move an issue to that project).
@@ -1196,6 +1194,15 @@ class User < ActiveRecord::Base
 
   private
 
+  def owned_projects_union
+    Gitlab::SQL::Union.new([
+      Project.where(namespace: namespace),
+      Project.joins(:project_authorizations)
+        .where("projects.namespace_id <> ?", namespace.id)
+        .where(project_authorizations: { user_id: id, access_level: Gitlab::Access::OWNER })
+    ], remove_duplicates: false)
+  end
+
   def ci_projects_union
     scope  = { access_level: [Gitlab::Access::MASTER, Gitlab::Access::OWNER] }
     groups = groups_projects.where(members: scope)
diff --git a/changelogs/unreleased/43552-user-owned-projects-query-performance-improvement.yml b/changelogs/unreleased/43552-user-owned-projects-query-performance-improvement.yml
new file mode 100644
index 00000000000..39f92c281ad
--- /dev/null
+++ b/changelogs/unreleased/43552-user-owned-projects-query-performance-improvement.yml
@@ -0,0 +1,5 @@
+---
+title: Improves the performance of projects list page
+merge_request: 17934
+author:
+type: performance