Skip to content

Remove unused index index_for_resource_group on ci_builds

Alishan Ladhani requested to merge ali/remove-old-index_for_resource_group into master

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.

Edited by Alishan Ladhani

Merge request reports

Loading