Optimize ci_pipeline counters

Create an index on user_id and created_at to ci_pipeline AMAU counters are calculated using batch counters #208885 (closed)

• One index is enough to ensure 10 (5x2) ci_pipelines AMAU counters are countable
• Each loop is below 500 milliseconds on gitlab.com database lab
• We save space now but, in the future we could add two indexes for source and config_source variations

Optimize

CREATE INDEX index_ci_pipelines_on_user_id_and_created_at ON public.ci_pipelines USING btree (user_id, created_at)
# The query has been executed. Duration: 14.886 min (edited) 

Query 1

SELECT MIN("ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 6 AND "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662'

Before: https://explain.depesz.com/s/rqWl 2.4 minutes

After: https://explain.depesz.com/s/5kvu 2 seconds

Query 2

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 6 AND "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662' AND "ci_pipelines"."user_id" BETWEEN 3000000 AND 3001250

Before: https://explain.depesz.com/s/NEkN

After: https://explain.depesz.com/s/irYX (400 milliseconds)

Query 3

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 7, 8, 10, 11, 12, 9) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662' AND "ci_pipelines"."user_id" BETWEEN 3000000 AND 3001250

Before: https://explain.depesz.com/s/3cx5

After: https://explain.depesz.com/s/9Y7x (450 milliseconds)

Query 4

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 2 AND "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662' AND "ci_pipelines"."user_id" BETWEEN 3000000 AND 3001250

Before: https://explain.depesz.com/s/HVM5

After: https://explain.depesz.com/s/Ygqw 400 milliseconds

Query 5

SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."config_source" = 1 AND "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662' AND "ci_pipelines"."user_id" BETWEEN 3000000 AND 3001250

Before: https://explain.depesz.com/s/asYl

After: https://explain.depesz.com/s/rLq24 400 milliseconds

Query 6

explain SELECT COUNT(DISTINCT "ci_pipelines"."user_id") FROM "ci_pipelines" WHERE "ci_pipelines"."created_at" BETWEEN '2020-02-11 14:07:44.935590' AND '2020-03-10 14:07:44.935662' AND "ci_pipelines"."user_id" BETWEEN 1000000 AND 1001250

Before: https://explain.depesz.com/s/6ht9

After: https://explain.depesz.com/s/mHTy 100 milliseconds

Migration Output

$ VERBOSE=true bundle exec rake db:migrate:up VERSION=20200306170321
== 20200306170321 AddIndexOnUserIdAndCreatedAtToCiPipelines: migrating ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0049s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_pipelines, [:user_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0243s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id], {:algorithm=>:concurrently})
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:ci_pipelines, {:algorithm=>:concurrently, :column=>[:user_id]})
   -> 0.0124s
-- execute("RESET ALL")
   -> 0.0004s
== 20200306170321 AddIndexOnUserIdAndCreatedAtToCiPipelines: migrated (0.0478s) 
$ VERBOSE=true bundle exec rake db:migrate:down VERSION=20200306170321
== 20200306170321 AddIndexOnUserIdAndCreatedAtToCiPipelines: reverting ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id], {:algorithm=>:concurrently})
   -> 0.0049s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- add_index(:ci_pipelines, [:user_id], {:algorithm=>:concurrently})
   -> 0.0220s
-- execute("RESET ALL")
   -> 0.0012s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:user_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0160s
-- execute("SET statement_timeout TO 0")
   -> 0.0012s
-- remove_index(:ci_pipelines, {:algorithm=>:concurrently, :column=>[:user_id, :created_at]})
   -> 0.0225s
-- execute("RESET ALL")
   -> 0.0011s
== 20200306170321 AddIndexOnUserIdAndCreatedAtToCiPipelines: reverted (0.0699s)