Harden CI pipelines usage data queries

Failed usage pings from 2020-06-04

  "usage_activity_by_stage": {
    "verify": {
    451:      "ci_external_pipelines": -1,
    452:      "ci_internal_pipelines": -1,
    453:      "ci_pipeline_config_auto_devops": -1,
    454:      "ci_pipeline_config_repository": -1,

Ruby code

time_period = {}


ci_external_pipelines: Gitlab::UsageData.distinct_count(::Ci::Pipeline.external.where(time_period), :user_id),
SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 6
SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 6
SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 6 AND "ci_pipelines"."user_id" BETWEEN 0 AND 9999


ci_internal_pipelines: Gitlab::UsageData.distinct_count(::Ci::Pipeline.internal.where(time_period), :user_id)
SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12) OR "ci_pipelines"."source" IS NULL)
SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12) OR "ci_pipelines"."source" IS NULL)
SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."user_id" BETWEEN 1 AND 10000


ci_pipeline_config_auto_devops: Gitlab::UsageData.distinct_count(::Ci::Pipeline.auto_devops_source.where(time_period), :user_id)
SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2
SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2
SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2 AND "ci_pipelines"."user_id" BETWEEN 0 AND 9999


ci_pipeline_config_repository: Gitlab::UsageData.distinct_count(::Ci::Pipeline.repository_source.where(time_period), :user_id)
SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1
SELECT MAX("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1
SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1 AND "ci_pipelines"."user_id" BETWEEN 1 AND 10000

DROP INDEX index_ci_pipelines_on_user_id_and_created_at
CREATE INDEX index_ci_pipelines_on_user_id_and_created_at_and_source ON public.ci_pipelines USING btree (user_id, created_at, source)



gitlabhq_production=> \di+  index_ci_pipelines_on_user_id_and_created_at
                                               List of relations
 Schema |                     Name                     | Type  | Owner  |    Table     |  Size   | Description 
--------+----------------------------------------------+-------+--------+--------------+---------+-------------
 public | index_ci_pipelines_on_user_id_and_created_at | index | gitlab | ci_pipelines | 62 85 MB | 
(1 row)