Schedule health_status on projects index removal

Nicolas Dularrequested to merge
nd/remove-health-status-project-index into master

What does this MR do and why?

Schedule health_status on projects index removal

The index initially got introduced for filtering and sorting on health_status, but it no longer gets used by the optimizer. We can therefore drop it to reduce the amount of indexes on the issues table.

This index got introduced with !104094 (merged). However, the usage is low: https://dashboards.gitlab.net/goto/DfAwhCwHR?orgId=1https://dashboards.gitlab.net/goto/DfAwhCwHR?orgId=1.

And for the original query it got introduced, the query optimizer no longer uses the index, see:

As part of our guidelines on adding indexes: https://docs.gitlab.com/development/database/query_performance/#slow-list-views-and-apis we should reduce the amount of indexes, and not try to fit all possible combinations of sorting/filtering. This is a good use-case as the index is also barely used.

References

Screenshots or screen recordings

Before After

How to set up and validate locally

Migration

 bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 17141
main: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: migrating ===========
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:name=>"index_issues_on_project_health_status_asc_work_item_type"})
main:    -> 0.0281s
main: -- quote_column_name("index_issues_on_project_health_status_asc_work_item_type")
main:    -> 0.0000s
main: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: migrated (0.0612s) ==

main: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 17141
ci: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 17142
ci: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: migrating ===========
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:name=>"index_issues_on_project_health_status_asc_work_item_type"})
ci:    -> 0.0123s
ci: -- quote_column_name("index_issues_on_project_health_status_asc_work_item_type")
ci:    -> 0.0000s
ci: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: migrated (0.0321s) ==

ci: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 17142

Checking scheduled indexes

gitlabhq_development=# select * from postgres_async_indexes order by id desc limit 1;
 id |          created_at           |          updated_at           |                           name                           |                                     definition                                     | table_name | attempts | last_error
----+-------------------------------+-------------------------------+----------------------------------------------------------+------------------------------------------------------------------------------------+------------+----------+------------
 40 | 2025-08-04 14:17:09.189892+00 | 2025-08-04 14:17:09.189892+00 | index_issues_on_project_health_status_asc_work_item_type | DROP INDEX CONCURRENTLY "index_issues_on_project_health_status_asc_work_item_type" | issues     |        0 |
(1 row)

Reindexing

bundle exec rails gitlab:db:reindex

\d index_issues_on_project_health_status_asc_work_item_type
Did not find any relation named "index_issues_on_project_health_status_asc_work_item_type".

Rollback

rails db:migrate:down:main VERSION=20250804140514
main: == [advisory_lock_connection] object_id: 144880, pg_backend_pid: 17533
main: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: reverting ===========
main: == 20250804140514 RemoveIndexIssuesOnProjectHealthStatus: reverted (0.0289s) ==

main: == [advisory_lock_connection] object_id: 144880, pg_backend_pid: 17533

MR acceptance checklist

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 Nicolas Dular

Merge request reports