Drop an unnecessary index on merge_requests
What does this MR do and why?
Drop an unnecessary index on merge_requests
index_merge_requests_on_target_project_id_and_iid_and_state_id
seems to
be unnecessary since we have idx_mrs_on_target_id_and_created_at_and_state_id
which covers the same use case.
It currently uses up ~9 GiB on production and removing unnecessary index would help reducing WAL rate.
index_merge_requests_on_target_project_id_and_iid_and_state_id
seems to have been added in !42481 (merged) and idx_mrs_on_target_id_and_created_at_and_state_id
got created subsequently via !57267 (merged)
Running the original query mentioned in !42481 (merged) resulted in a similar query plan as follows.
Before: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27345/commands/85082
After: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27345/commands/85088
Migration (Up)
(master ✗) ➜ rake db:migrate [18:19 03/04]
main: == [advisory_lock_connection] object_id: 121600, pg_backend_pid: 34233
main: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: migrating
main: -- index_exists?(:merge_requests, [:target_project_id, :iid, :state_id], {:name=>"index_merge_requests_on_target_project_id_and_iid_and_state_id"})
main: -> 0.0225s
main: -- quote_column_name("index_merge_requests_on_target_project_id_and_iid_and_state_id")
main: -> 0.0000s
main: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: migrated (0.0400s)
main: == [advisory_lock_connection] object_id: 121600, pg_backend_pid: 34233
ci: == [advisory_lock_connection] object_id: 121940, pg_backend_pid: 34235
ci: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: migrating
ci: -- index_exists?(:merge_requests, [:target_project_id, :iid, :state_id], {:name=>"index_merge_requests_on_target_project_id_and_iid_and_state_id"})
ci: -> 0.0163s
ci: -- quote_column_name("index_merge_requests_on_target_project_id_and_iid_and_state_id")
ci: -> 0.0000s
ci: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: migrated (0.0287s)
ci: == [advisory_lock_connection] object_id: 121940, pg_backend_pid: 34235
Migration (Down)
(master ✗) ➜ rake db:rollback:main STEP=1 [18:21 03/04]
main: == [advisory_lock_connection] object_id: 121240, pg_backend_pid: 36245
main: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: reverting
main: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: reverted (0.0268s)
(master ✗) ➜ rake db:rollback:ci STEP=1 [18:21 03/04]
ci: == [advisory_lock_connection] object_id: 121240, pg_backend_pid: 35599
ci: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: reverting
ci: == 20240403070604 RemoveIndexMergeRequestsOnTargetProjectIdAndIidAndStateId: reverted (0.0257s)
Async Index removal verification
gitlabhq_development=# \di+ index_merge_requests_on_target_project_id_and_iid_and_state_id
Did not find any relation named "index_merge_requests_on_target_project_id_and_iid_and_state_id".
Related to https://gitlab.com/gitlab-org/gitlab/-/issues/448711#note_1831175065
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.