Optimize template_repositories query
What does this MR do?
Optimizes the template_repositories
counter query for batch counting in usage data
Part of issue #208923 (closed)
Query
SELECT COUNT(“projects”.“id”) FROM “projects” WHERE “projects”.“namespace_id” = 1 AND “projects”.“id” BETWEEN 0 AND 9999;
Optimization
CREATE INDEX index_projects_on_namespace_id_and_id ON public.projects USING btree (namespace_id, id);
-- The query has been executed. Duration: 1.964 min
https://explain.depesz.com/s/8Xqs
Before: Bad!https://explain.depesz.com/s/EWIp
After: Good!Timing
After the index for batch counting takes 1.5 seconds pessimistic
- ~12M projects,
- with 10_000 batch sizes
- 12M/10_000 = 1200 loops
- Time: < 1ms ( cold cache with no time constraint )
Migration output
VERBOSE=true bundle exec rake db:migrate:up VERSION=20200316162648
== 20200316162648 AddIndexOnNamespaceIdAndIdToProjects: migrating =============
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, [:namespace_id, :id], {:algorithm=>:concurrently})
-> 0.0160s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:projects, [:namespace_id, :id], {:algorithm=>:concurrently})
-> 0.0055s
-- execute("RESET ALL")
-> 0.0001s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, :namespace_id, {:algorithm=>:concurrently})
-> 0.0161s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :column=>:namespace_id})
-> 0.0143s
-- execute("RESET ALL")
-> 0.0001s
== 20200316162648 AddIndexOnNamespaceIdAndIdToProjects: migrated (0.0528s) ====
VERBOSE=true bundle exec rake db:migrate:down VERSION=20200316162648
== 20200316162648 AddIndexOnNamespaceIdAndIdToProjects: reverting =============
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, :namespace_id, {:algorithm=>:concurrently})
-> 0.0124s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:projects, :namespace_id, {:algorithm=>:concurrently})
-> 0.0057s
-- execute("RESET ALL")
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, [:namespace_id, :id], {:algorithm=>:concurrently})
-> 0.0117s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :column=>[:namespace_id, :id]})
-> 0.0142s
-- execute("RESET ALL")
-> 0.0003s
== 20200316162648 AddIndexOnNamespaceIdAndIdToProjects: reverted (0.0451s) ====
Edited by Alex Buijs