Optimize projects_mirrored_with_pipelines_enabled query

What does this MR do?

Optimizes the projects_mirrored_with_pipelines_enabled counter query for batch counting in usage data

Part of issue #208923 (closed)

Query

SELECT COUNT(“projects”.“id”) FROM “projects” INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id” WHERE “projects”.“mirror” = true AND “projects”.“mirror_trigger_builds” = true AND “project_features”.“builds_access_level” = 20 AND “projects”.“id” BETWEEN 0 AND 9999;

Optimization

CREATE INDEX CONCURRENTLY index_projects_on_mirror_id ON public.projects USING btree (id) where mirror = true and mirror_trigger_builds = true;
-- The query has been executed. Duration: 1.460 min

Note: this optimization also depends on the index added here: !26802 (merged)

Before: Bad! https://explain.depesz.com/s/NGYO

After: Good! https://explain.depesz.com/s/mUvo

Timing

After the index for batch counting takes 2 seconds pessimistic

• ~10M projects,
• with 10_000 batch sizes
• 10M/10_000 = 1000 loops
• Time: < 2ms ( cold cache with no time constraint )

Migration output

VERBOSE=true bundle exec rake db:migrate:up VERSION=20200312160532
== 20200312160532 AddIndexOnMirrorAndIdToProjects: migrating ==================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, :id, {:where=>"mirror = true and mirror_trigger_builds = true", :name=>"index_projects_on_mirror_id", :algorithm=>:concurrently})
   -> 0.0121s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:projects, :id, {:where=>"mirror = true and mirror_trigger_builds = true", :name=>"index_projects_on_mirror_id", :algorithm=>:concurrently})
   -> 0.0036s
-- execute("RESET ALL")
   -> 0.0004s
== 20200312160532 AddIndexOnMirrorAndIdToProjects: migrated (0.0167s) =========

VERBOSE=true bundle exec rake db:migrate:down VERSION=20200312160532
== 20200312160532 AddIndexOnMirrorAndIdToProjects: reverting ==================
-- transaction_open?()
   -> 0.0000s
-- indexes(:projects)
   -> 0.0116s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_mirror_id"})
   -> 0.0023s
-- execute("RESET ALL")
   -> 0.0004s
== 20200312160532 AddIndexOnMirrorAndIdToProjects: reverted (0.0150s) =========