Improve db count query in snippet dashboard
When we render the snippets dashboard the view _snippets_scope_menu.html.haml performs different queries to retrieve the count
of snippets based on the visibility_level
.
But this feels like it can be improved, besides depending on some conditions, we might perform more queries than we should.
At the moment, there is an index in the visibility_level
and these count
are performed on a scoped relation, therefore they aren't too critical (unless whether the user is an admin which will get more results).
My proposal is to add something like:
diff --git a/app/models/snippet.rb b/app/models/snippet.rb
index fd1e3b5240c..e04839c869e 100644
--- a/app/models/snippet.rb
+++ b/app/models/snippet.rb
@@ -81,6 +81,16 @@ class Snippet < ApplicationRecord
end
end
+ def self.visibility_counters
+ select('
+ count(case when visibility_level=20 and secret is FALSE then 1 else null end) as are_public,
+ count(case when visibility_level=20 and secret is TRUE then 1 else null end) as are_secret,
+ count(case when visibility_level=10 then 1 else null end) as are_internal,
+ count(case when visibility_level=0 then 1 else null end) as are_private,
+ count(case when visibility_level=20 OR visibility_level=10 then 1 else null end) as are_public_or_internal
+ ').first.slice(:are_public, :are_secret, :are_private, :are_public_or_internal)
+ end
+
def self.only_personal_snippets
where(project_id: nil)
end
This way we can retrieve all the counters at the same time. I think it is more performant than the former approach because we avoid the overhead of performing a PostgreSQL query, but I'm not sure how much will be the improvement.