Optimize todos counters in usage data
Optimize the todos queries in usage data
Console work to fetch the queries, # reset to master so no need for # Feature.enable(:usage_ping_batch_counter)
# 1st Query
time_period = {}
Gitlab::UsageData.distinct_count(::Todo.where(time_period), :author_id)
SELECT MIN("todos"."author_id") FROM "todos"
explain SELECT MAX("todos"."author_id") FROM "todos"
# Good! https://explain.depesz.com/s/xqz
# 2000 is the minimal batch
explain SELECT COUNT(DISTINCT "todos"."author_id") FROM "todos" WHERE "todos"."author_id" BETWEEN 910000 AND 912000
# Good! https://explain.depesz.com/s/lWWl
# 2nd query
time_period = { created_at: 28.days.ago..Time.current }
Gitlab::UsageData.distinct_count(::Todo.where(time_period), :author_id)distinct_count(::Todo.where(time_period), :author_id)
SELECT MIN("todos"."author_id") FROM "todos" WHERE "todos"."created_at" BETWEEN '2020-02-07 06:22:09.650617' AND '2020-03-06 06:22:09.650886'
explain SELECT MAX("todos"."author_id") FROM "todos" WHERE "todos"."created_at" BETWEEN '2020-02-07 06:22:09.650617' AND '2020-03-06 06:22:09.650886'
# Good https://explain.depesz.com/s/Mrfkg
explain SELECT COUNT(DISTINCT "todos"."author_id") FROM "todos" WHERE "todos"."created_at" BETWEEN '2020-02-07 06:22:09.650617' AND '2020-03-06 06:22:09.650886' AND "todos"."author_id" BETWEEN 610000 AND 612000
# Bad! https://explain.depesz.com/s/CCpc
Edited by Alper Akgun