Drop a partial index from merge_requests we no longer need
Drop an unnecessary index on merge_requests
idx_merge_requests_on_target_project_id_and_iid_opened
seems to be unnecessary since we have idx_mrs_on_target_id_and_created_at_and_state_id
which seems to cover the same use case. It currently uses ~200 MiB on production so it's not too big, but removing unnecessary index would help reducing WAL rate.
idx_merge_requests_on_target_project_id_and_iid_opened
seems to have been added in 709dd237 initially and idx_mrs_on_target_id_and_created_at_and_state_id
got created much later via !57267 (merged).
I couldn't find the original query that this index was targeting in 709dd237, but I've found some query that uses the same index. It doesn't seem iid
field is necessary so idx_mrs_on_target_id_and_created_at_and_state_id
seems sufficient.
Before: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27452/commands/85582
After: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27452/commands/85584
Migration (Up)
(drop-idx_merge_requests_on_target_project_id_and_iid_opened ✗) ➜ rake db:migrate [15:31 11/04]
main: == [advisory_lock_connection] object_id: 122620, pg_backend_pid: 15241
main: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: migrating
main: -- index_exists?(:merge_requests, [:target_project_id, :iid], {:name=>"idx_merge_requests_on_target_project_id_and_iid_opened"})
main: -> 0.0224s
main: -- quote_column_name("idx_merge_requests_on_target_project_id_and_iid_opened")
main: -> 0.0000s
main: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: migrated (0.0370s)
main: == [advisory_lock_connection] object_id: 122620, pg_backend_pid: 15241
ci: == [advisory_lock_connection] object_id: 123140, pg_backend_pid: 15243
ci: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: migrating
ci: -- index_exists?(:merge_requests, [:target_project_id, :iid], {:name=>"idx_merge_requests_on_target_project_id_and_iid_opened"})
ci: -> 0.0093s
ci: -- quote_column_name("idx_merge_requests_on_target_project_id_and_iid_opened")
ci: -> 0.0000s
ci: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: migrated (0.0194s)
ci: == [advisory_lock_connection] object_id: 123140, pg_backend_pid: 15243
Migration (Down)
(drop-idx_merge_requests_on_target_project_id_and_iid_opened ✗) ➜ rake db:rollback:main STEP=1 [15:30 11/04]
rake db:migratemain: == [advisory_lock_connection] object_id: 122260, pg_backend_pid: 14595
main: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: reverting
main: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: reverted (0.0259s)
main: == [advisory_lock_connection] object_id: 122260, pg_backend_pid: 14595
(drop-idx_merge_requests_on_target_project_id_and_iid_opened ✗) ➜ rake db:rollback:ci STEP=1 [15:30 11/04]
ci: == [advisory_lock_connection] object_id: 122260, pg_backend_pid: 13860
ci: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: reverting
ci: == 20240411052531 DropIdxMergeRequestsOnTargetProjectIdAndIidOpened: reverted (0.0312s)
ci: == [advisory_lock_connection] object_id: 122260, pg_backend_pid: 13860
Async Index removal verification
gitlabhq_development=# \di+ idx_merge_requests_on_target_project_id_and_iid_opened
Did not find any relation named "idx_merge_requests_on_target_project_id_and_iid_opened".
Related to https://gitlab.com/gitlab-org/gitlab/-/issues/448711#note_1831284182