Optimize projects with repositories enabled

Optimize usage_activity_by_stage.projects_with_repositories_enabled

Issue #213799 (closed)

time_period = {}

projects_with_repositories_enabled: distinct_count(::Project.with_repositories_enabled.where(time_period), :creator_id)

# calculation 

Gitlab::UsageData.distinct_count(::Project.with_repositories_enabled.where(time_period), :creator_id)

SELECT MIN("projects"."creator_id") FROM "projects" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" WHERE "project_features"."repository_access_level" = 20
SELECT MAX("projects"."creator_id") FROM "projects" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" WHERE "project_features"."repository_access_level" = 20
SELECT COUNT(DISTINCT "projects"."creator_id") FROM "projects" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" WHERE "project_features"."repository_access_level" = 20 AND "projects"."creator_id" BETWEEN 1 AND 1250
#  rows returned 656

Optimization

CREATE INDEX CONCURRENTLY index_projects_on_creator_id_and_id ON public.projects USING btree (creator_id, id)
# The query has been executed. Duration: 1.361 min

Query plans

Query	Before	After
MIN	https://explain.depesz.com/s/3gnz	User.minimum(:id)
MAX	https://explain.depesz.com/s/zsX9	User.maximum(:id)
RANGE	https://explain.depesz.com/s/6F35	https://explain.depesz.com/s/D2cM

Migration output

$ VERBOSE=true bundle exec rake db:migrate:down VERSION=20200408153842
== 20200408153842 AddIndexOnCreatorIdAndIdOnProjects: reverting ===============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, [:creator_id, :id], {:algorithm=>:concurrently})
   -> 0.0114s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :column=>[:creator_id, :id]})
   -> 0.0258s
-- execute("RESET ALL")
   -> 0.0005s
== 20200408153842 AddIndexOnCreatorIdAndIdOnProjects: reverted (0.0381s) ======

$ VERBOSE=true bundle exec rake db:migrate:up VERSION=20200408153842
== 20200408153842 AddIndexOnCreatorIdAndIdOnProjects: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, [:creator_id, :id], {:algorithm=>:concurrently})
   -> 0.0121s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:projects, [:creator_id, :id], {:algorithm=>:concurrently})
   -> 0.0095s
-- execute("RESET ALL")
   -> 0.0002s
== 20200408153842 AddIndexOnCreatorIdAndIdOnProjects: migrated (0.0221s) ======