Harden ci pipelines auto_devops & config_repository usage data

Harden ci pipelines auto_devops & config_repository usage data

1] pry(main)> time_period = {}
=> {}
[2] pry(main)> Gitlab::UsageData.distinct_count(::Ci::Pipeline.auto_devops_source.where(time_period), :user_id)
   (1.5ms)  SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2
   (0.7ms)  SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2
   (0.8ms)  SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2 AND "ci_pipelines"."user_id" BETWEEN 0 AND 1250
=> 0
[3] pry(main)> Gitlab::UsageData.distinct_count(::Ci::Pipeline.repository_source.where(time_period), :user_id)
   (0.8ms)  SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1
   (0.6ms)  SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1
   (0.6ms)  SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1 AND "ci_pipelines"."user_id" BETWEEN 0 AND 1250

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1 AND "ci_pipelines"."user_id" BETWEEN 0 AND 1250

before: https://explain.depesz.com/s/QfOI 12.5 seconds after: https://explain.depesz.com/s/tbGY 2 to 3.3 seconds

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2 AND "ci_pipelines"."user_id" BETWEEN 0 AND 1250

before: https://explain.depesz.com/s/TGz1 43.9 seconds after: https://explain.depesz.com/s/yJ4s 130 milliseconds

Index creation

exec CREATE INDEX index_ci_pipelines_on_user_id_and_created_at_and_config_source ON public.ci_pipelines USING btree (user_id, created_at, config_source)
The query has been executed. Duration: 10.744 min

index_ci_pipelines_on_user_id_and_created_at_and_config_source
\di+ index_ci_pipelines_on_user_id_and_created_at_and_config_source
List of relations
 Schema |                              Name                              | Type  | Owner  |    Table     |  Size   | Description 
--------+----------------------------------------------------------------+-------+--------+--------------+---------+-------------
 public | index_ci_pipelines_on_user_id_and_created_at_and_config_source | index | gitlab | ci_pipelines | 5871 MB | 
(1 row)

Migration output

$ rails db:migrate:up VERSION=20200704143633
== 20200704143633 AddIndexOnUserIdAndCreatedAtWhereSourceToCiPipelines: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id, :created_at, :config_source], {:algorithm=>:concurrently})
   -> 0.0060s
-- add_index(:ci_pipelines, [:user_id, :created_at, :config_source], {:algorithm=>:concurrently})
   -> 0.0137s
== 20200704143633 AddIndexOnUserIdAndCreatedAtWhereSourceToCiPipelines: migrated (0.0200s) 

$ rails db:migrate:down VERSION=20200704143633
== 20200704143633 AddIndexOnUserIdAndCreatedAtWhereSourceToCiPipelines: reverting 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id, :created_at, :config_source], {:algorithm=>:concurrently})
   -> 0.0067s
-- remove_index(:ci_pipelines, {:algorithm=>:concurrently, :column=>[:user_id, :created_at, :config_source]})
   -> 0.0194s
== 20200704143633 AddIndexOnUserIdAndCreatedAtWhereSourceToCiPipelines: reverted (0.0264s)