Remove index idx_merge_requests_on_state_id_and_merge_status async
What does this MR do and why?
This MR just schedules the index idx_merge_requests_on_state_id_and_merge_status to be removed.
Per #402490 (closed) we found that
this index is unused and merge_status is updated frequently. Also
since this is the only index involving merge_status we are hopeful
that removing this increases
HOT updates
for the merge_requests table.
Migrations
$ bin/rake db:migrate
main: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: migrating
main: -- index_exists?(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status"})
main: -> 0.0799s
main: -- quote_column_name("idx_merge_requests_on_state_id_and_merge_status")
main: -> 0.0000s
main: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: migrated (0.0930s)
ci: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: migrating
ci: -- index_exists?(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status"})
ci: -> 0.0107s
ci: -- quote_column_name("idx_merge_requests_on_state_id_and_merge_status")
ci: -> 0.0000s
ci: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: migrated (0.0199s)
$ bin/rake db:rollback:main
main: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: reverting
main: == 20230328020316 DeleteUnusedIndexOnMergeRequestsOnStateIdAndMergeStatus: reverted (0.0731s)I also validated locally following https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#verify-indexes-removed-asynchronously that the index was indeed removed:
gitlabhq_development=# \d+ idx_merge_requests_on_state_id_and_merge_status
Did not find any relation named "idx_merge_requests_on_state_id_and_merge_status".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.
Related to #402490 (closed)