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"
https://explain.depesz.com/s/UlTm
Before: Time: 10.824 min
::User.minimum(:id)
After MAX no period
SELECT MAX("projects"."creator_id")
FROM "projects"
INNER JOIN "protected_branches" ON "protected_branches"."project_id" = "projects"."id"
https://explain.depesz.com/s/lZIn
Before Time: 6.530 min
::User.maximum(:id)
After 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
https://explain.depesz.com/s/a3Ni
Before Time: 10.216 minFilter: ((projects.creator_id >= 1) AND (projects.creator_id <= 10000))
https://explain.depesz.com/s/wFsq
After Time: 110.130 msNo 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'
https://explain.depesz.com/s/Cyqh
Before: Time: 33.794 s
::User.minimum(:id)
After 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'
https://explain.depesz.com/s/bsad
BeforeTime: 22.810 s
::User.maximum(:id)
After 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
https://explain.depesz.com/s/9BbB
Before: Time: 6.657 sFilter: ((projects.creator_id >= 0) AND (projects.creator_id <= 9999))
https://explain.depesz.com/s/hZxA
After: Time: 2.409 msNo 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)
Edited by Alina Mihaila