Old - non mau

We add an index to optimize ci_jobs with a name.

WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'XXX'

Main issue #208884 (comment 310936393)

Optimization

CREATE INDEX aa_index_ci_builds_b2 ON public.ci_builds USING btree (name) WHERE ((type)::text = 'Ci::Build'::text)

# Timing 

Query 1

SELECT MIN("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning_jobs'

Before:

After: https://explain.depesz.com/s/7Lin

Query 2

SELECT MAX("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning_jobs'

Before:

After: https://explain.depesz.com/s/KuIi

Query 3

SELECT COUNT("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning_jobs' AND "ci_builds"."id" BETWEEN 0 AND 99999

Before: https://explain.depesz.com/s/LGWQ > 2 seconds

After: https://explain.depesz.com/s/n71i >10 ms

Count timing

• 5.5 million users / 1250 => 4400 loops with < 10 msecs => 44 seconds

Migration Output