Optimize usage_activity_by_stage.create.protected_branches

What does this MR do?

Ruby code

time_period = {}
Gitlab::UsageData.distinct_count(::Project.with_protected_branches.where(time_period), :creator_id)

time_period = { created_at: 28.days.ago..Time.current }
Gitlab::UsageData.distinct_count(::Project.with_protected_branches.where(time_period), :creator_id)

Queries

CREATE INDEX CONCURRENTLY index_projects_on_id_creator_id_and_created_at
ON projects(creator_id, created_at, id);

The query has been executed. Duration: 50.825 s

MIN no period

SELECT MIN("projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"

Before: Time: 10.824 min https://explain.depesz.com/s/UlTm

After ::User.minimum(:id)

MAX no period

SELECT MAX("projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"

Before Time: 6.530 min https://explain.depesz.com/s/lZIn

After ::User.maximum(:id)

COUNT no period

 SELECT COUNT(DISTINCT "projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"
WHERE "projects"."creator_id" BETWEEN 1 AND 1250

Before Time: 10.216 min https://explain.depesz.com/s/a3Ni

Filter: ((projects.creator_id >= 1) AND (projects.creator_id <= 10000))

After Time: 110.130 ms https://explain.depesz.com/s/wFsq

No Filter

MIN with period

SELECT MIN("projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"
WHERE "projects"."created_at" BETWEEN '2020-03-11 08:46:19.908535' AND '2020-04-08 08:46:19.908724'

Before: Time: 33.794 s https://explain.depesz.com/s/Cyqh

After ::User.minimum(:id)

MAX with period

SELECT MAX("projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"
WHERE "projects"."created_at" BETWEEN '2020-03-11 08:46:19.908535' AND '2020-04-08 08:46:19.908724'

BeforeTime: 22.810 s https://explain.depesz.com/s/bsad

After ::User.maximum(:id)

COUNT with period

SELECT COUNT(DISTINCT "projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"
WHERE "projects"."created_at" BETWEEN '2020-03-11 08:46:19.908535' AND '2020-04-08 08:46:19.908724'
  AND "projects"."creator_id" BETWEEN 0 AND 1250

Before: Time: 6.657 s https://explain.depesz.com/s/9BbB

Filter: ((projects.creator_id >= 0) AND (projects.creator_id <= 9999))

After: Time: 2.409 ms https://explain.depesz.com/s/hZxA

No filter

➜  gitlab git:(213800-optimize-usage_activity_by_stage-create-protected_branches) ✗ rake db:migrate:up VERSION=20200408175424
== 20200408175424 AddIndexOnCreatorIdCreatedAtIdToProjectsTable: migrating ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, [:creator_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0122s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:projects, [:creator_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0056s
-- execute("RESET ALL")
   -> 0.0001s
== 20200408175424 AddIndexOnCreatorIdCreatedAtIdToProjectsTable: migrated (0.0182s)

 gitlab git:(213800-optimize-usage_activity_by_stage-create-protected_branches) rake db:migrate:down VERSION=20200408175424
== 20200408175424 AddIndexOnCreatorIdCreatedAtIdToProjectsTable: reverting ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, [:creator_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0128s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- remove_index(:projects, {:algorithm=>:concurrently, :column=>[:creator_id, :created_at, :id]})
   -> 0.0117s
-- execute("RESET ALL")
   -> 0.0001s
== 20200408175424 AddIndexOnCreatorIdCreatedAtIdToProjectsTable: reverted (0.0249s)

Closes #213800 (closed)