Skip to content

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