Improve db count query in snippet dashboard
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.