Skip to content

Drop an unnecessary index on merge_requests

Sincheol (David) Kim requested to merge remove-index_merge_requests_on_target_project_id_and_iid_and_state_id into master

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.

Edited by Sincheol (David) Kim

Merge request reports