Remove unused index idx_merge_requests_on_state_id_and_merge_status
In doing some investigation of slow queries we found that updating merge_requests.merge_status
in MergeRequestMergeabilityCheckWorker
is often in the top 5 most time consuming queries (by total time) in our primary database.
/application:sidekiq,correlation_id:01GK30KWZA94SFNKMAJEEXB604,jid:eb6b47fea2c86d18df78b384,endpoint_id:MergeRequestMergeabilityCheckWorker,db_config_name:main/ UPDATE "merge_requests" SET "merge_status" = $1 WHERE "merge_requests"."id" = $2
From what I can tell this merge_status
column is only used in the idx_merge_requests_on_state_id_and_merge_status
index and according to Prometheus metrics this query is unused. If we removed this index then these updates would become eligible for HOT updates in Postgres which makes them considerably cheaper especially on a table like merge_requests
with many indexes.
So we should remove this index if we don't think we need it.
Edited by Dylan Griffith