Remove unused index index_for_resource_group on ci_builds

What does this MR do and why?

A new index was recently added to cover this use case: #340381 (closed). The index_for_resource_group index is not being used anymore and was replaced by index_ci_builds_on_resource_group_and_status_and_commit_id.

Related to #341724 (closed)

Screenshots or screen recordings

Tested a couple of queries in Database Lab to verify that the new index is being used.

Test query 1:

SELECT
“ci_builds”.*
FROM
“ci_builds”
WHERE
“ci_builds”.“resource_group_id” = 110209 AND “ci_builds”.“status” = ‘waiting_for_resource’
LIMIT 1;

Explain:

 Limit  (cost=0.56..3.50 rows=1 width=1284) (actual time=6.300..6.302 rows=1 loops=1)
   Buffers: shared hit=3 read=8 dirtied=1
   I/O Timings: read=5.133 write=0.000
   ->  Index Scan using index_ci_builds_on_resource_group_and_status_and_commit_id on public.ci_builds  (cost=0.56..3.50 rows=1 width=1284) (actual time=6.297..6.298 rows=1 loops=1)
         Index Cond: ((ci_builds.resource_group_id = 110209) AND ((ci_builds.status)::text = 'waiting_for_resource'::text))
         Buffers: shared hit=3 read=8 dirtied=1
         I/O Timings: read=5.133 write=0.000

Test query 2:

SELECT
"ci_builds".*
FROM
"ci_builds"
WHERE
"ci_builds"."resource_group_id" = 110209 AND ("ci_builds"."status" IN ('created', 'scheduled', 'waiting_for_resource'))
ORDER BY
commit_id ASC,
CASE status WHEN 'waiting_for_resource' THEN 0 ELSE 1 END ASC
LIMIT 1;

Explain:

 Limit  (cost=215.02..215.03 rows=1 width=1288) (actual time=241.590..241.593 rows=1 loops=1)
   Buffers: shared hit=47 read=196 dirtied=3
   I/O Timings: read=237.061 write=0.000
   ->  Sort  (cost=215.02..215.38 rows=142 width=1288) (actual time=241.587..241.588 rows=1 loops=1)
         Sort Key: ci_builds.commit_id, (CASE ci_builds.status WHEN 'waiting_for_resource'::text THEN 0 ELSE 1 END)
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=47 read=196 dirtied=3
         I/O Timings: read=237.061 write=0.000
         ->  Index Scan using index_ci_builds_on_resource_group_and_status_and_commit_id on public.ci_builds  (cost=0.56..214.31 rows=142 width=1288) (actual time=40.993..240.715 rows=187 loops=1)
               Index Cond: ((ci_builds.resource_group_id = 110209) AND ((ci_builds.status)::text = ANY ('{created,scheduled,waiting_for_resource}'::text[])))
               Buffers: shared hit=44 read=196 dirtied=3
               I/O Timings: read=237.061 write=0.000

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.