Skip to content

Optimize service_desk_enabled_projects counter in usage_data

Alina Mihaila requested to merge 211802-optimize-service_desk_enabled_projects-counter-in-usage_data into master

What does this MR do?

Optimize service_desk_enabled_projects counter

Index added

CREATE INDEX CONCURRENTLY index_service_desk_enabled_projects_on_id_creator_id_and_created_at ON projects USING btree(id, creator_id, created_at)
WHERE "projects"."service_desk_enabled"

the query has been executed. Duration: 6.496 min

Optimization

time_period = {  }
project_creator_id_start = ::User.minimum(:id)
project_creator_id_finish = ::User.maximum(:id)

Gitlab::UsageData.distinct_count(::Project.service_desk_enabled.where(time_period), :creator_id, start: project_creator_id_start, finish: project_creator_id_finish)

time_period = { created_at: 28.days.ago..Time.current }

project_creator_id_start = ::User.minimum(:id)
project_creator_id_finish = ::User.maximum(:id)

Gitlab::UsageData.distinct_count(::Project.service_desk_enabled.where(time_period), :creator_id, start: project_creator_id_start, finish: project_creator_id_finish)

Queries

Query 1 MIN no period

SELECT MIN("users"."id")
FROM "users"

Before: Time: 7.839 ms https://explain.depesz.com/s/zR4

No filter

Query 2 MAX no period

SELECT MAX("users"."id")
FROM "users"

Before: Time: 10.718 ms https://explain.depesz.com/s/pdHK

no Filter

Query 3 COUNT no period

SELECT COUNT(DISTINCT "projects"."creator_id")
FROM "projects"
WHERE "projects"."service_desk_enabled" = TRUE
  AND "projects"."creator_id" BETWEEN 1 AND 10000

Before: Time: 2.027 min https://explain.depesz.com/s/I0Cj

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

After: Time: 484.426 ms https://explain.depesz.com/s/OMDo

No filter

Query 4 COUNT with time period

SELECT COUNT(DISTINCT "projects"."creator_id")
FROM "projects"
WHERE "projects"."service_desk_enabled" = TRUE
  AND "projects"."created_at" BETWEEN '2020-02-29 11:20:43.529892' AND '2020-03-28 11:20:43.530006'
  AND "projects"."creator_id" BETWEEN 0 AND 9999

Before: Time: 5.701 s https://explain.depesz.com/s/anig

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

After: Time: 573.991 ms https://explain.depesz.com/s/jOj2

Conformity

Closes #211802 (closed)

Edited by Alina Mihaila

Merge request reports