Optimize AlertsService batch query

What does this MR do?

Optimizes the projects_enforcing_code_owner_approval counter query for batch counting in usage data

Part of issue #208923 (closed)

Query

SELECT COUNT(“projects”.“id”) FROM “projects” INNER JOIN “protected_branches” ON “protected_branches”.“project_id” = “projects”.“id” WHERE “projects”.“pending_delete” = false AND “projects”.“archived” = false AND “protected_branches”.“code_owner_approval_required” = true AND “projects”.“id” BETWEEN 0 AND 9999;

Optimization

exec CREATE INDEX index_projects_on_id_and_archived_and_pending_delete ON projects USING btree (id) WHERE archived = false AND pending_delete = false;
-- The query has been executed. Duration: 4.003 min

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

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

Timing

After the index for batch counting takes 2 seconds pessimistic

• ~13M projects
• with 10_000 batch sizes
• 13M/10_000 = 1300 loops
• Time: < 1ms ( cold cache with no time constraint )

Migration output

VERBOSE=true bundle exec rake db:migrate:up VERSION=20200311110649
== 20200318175008 AddIndexOnIdAndArchivedAndPendingDeleteToProjects: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, :id, {:where=>"archived = FALSE AND pending_delete = FALSE", :name=>"index_projects_on_id_and_archived_and_pending_delete", :algorithm=>:concurrently})
   -> 0.0138s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:projects, :id, {:where=>"archived = FALSE AND pending_delete = FALSE", :name=>"index_projects_on_id_and_archived_and_pending_delete", :algorithm=>:concurrently})
   -> 0.0088s
-- execute("RESET ALL")
   -> 0.0004s
== 20200318175008 AddIndexOnIdAndArchivedAndPendingDeleteToProjects: migrated (0.0235s)

VERBOSE=true bundle exec rake db:migrate:down VERSION=20200311110649
== 20200318175008 AddIndexOnIdAndArchivedAndPendingDeleteToProjects: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, "index_projects_on_id_and_archived_and_pending_delete", {:algorithm=>:concurrently})
   -> 0.0150s
== 20200318175008 AddIndexOnIdAndArchivedAndPendingDeleteToProjects: reverted (0.0152s)