Replace issues health status sorting indexes [ASYNC]
What does this MR do and why?
Replace issues health status sorting indexes
Before health status sorting indexes would contain
issue_type
as the last column of the index. This
one needs to be replaced with the equivalent for each,
and use the work_item_type_id
column. Existing indexes are:
- index_on_issues_health_status_asc_order
- index_on_issues_health_status_desc_order
Original indexes were introduced in !104094 (merged). In this MR you can find context on where the indexes are used as well as database plans for the queries before and after the index was introduced. We are swapping these indexes in the effort to remove the issue_type
column from the issues table, in favor of the FK work_item_type_id
.
Using the new work_item_types table for filtering is currently behind the issue_type_uses_work_item_types_table
feature flag.
Creating these indexes ASYNC in .com over the weekend since the issues table is quite large.
DB review
Query plans
issue_type
column)
Before (using - DESC Query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13279/commands/46604
- ASC Query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13279/commands/46604
work_item_types
table)
After (using - ASC Query: https://console.postgres.ai/shared/c2fc5aef-c46a-424c-95fd-3b17e2cc7fb7
- DESC Query: https://console.postgres.ai/shared/8af556e1-3d04-4a23-be9a-a14d5babf2d5
Migration output
UP
main: == [advisory_lock_connection] object_id: 228380, pg_backend_pid: 27539
main: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: migrating
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_asc_work_item_type", :algorithm=>:concurrently})
main: -> 0.0323s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_asc_work_item_type", :algorithm=>:concurrently})
main: -> 0.0010s
main: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: migrated (0.2551s)
main: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: migrating
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_desc_work_item_type", :algorithm=>:concurrently})
main: -> 0.0293s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_desc_work_item_type", :algorithm=>:concurrently})
main: -> 0.0000s
main: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: migrated (0.0423s)
main: == [advisory_lock_connection] object_id: 228380, pg_backend_pid: 27539
ci: == [advisory_lock_connection] object_id: 228760, pg_backend_pid: 27541
ci: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: migrating
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_asc_work_item_type", :algorithm=>:concurrently})
ci: -> 0.0386s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_asc_work_item_type", :algorithm=>:concurrently})
ci: -> 0.0007s
ci: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: migrated (0.0696s)
ci: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: migrating
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_desc_work_item_type", :algorithm=>:concurrently})
ci: -> 0.0348s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_issues_on_project_health_status_desc_work_item_type", :algorithm=>:concurrently})
ci: -> 0.0000s
ci: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: migrated (0.0586s)
ci: == [advisory_lock_connection] object_id: 228760, pg_backend_pid: 27541
DOWN
bin/rails db:rollback:main db:rollback:ci STEP=2
main: == [advisory_lock_connection] object_id: 229140, pg_backend_pid: 27972
main: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: reverting
main: -- index_name(:issues, "index_issues_on_project_health_status_desc_work_item_type")
main: -> 0.0001s
main: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: reverted (0.2204s)
main: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: reverting
main: -- index_name(:issues, "index_issues_on_project_health_status_asc_work_item_type")
main: -> 0.0000s
main: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: reverted (0.0109s)
main: == [advisory_lock_connection] object_id: 229140, pg_backend_pid: 27972
ci: == [advisory_lock_connection] object_id: 237020, pg_backend_pid: 28263
ci: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: reverting
ci: -- index_name(:issues, "index_issues_on_project_health_status_desc_work_item_type")
ci: -> 0.0001s
ci: == 20230615202511 PrepareIndexIssuesOnProjectHealthStatusDescWorkItemType: reverted (0.0344s)
ci: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: reverting
ci: -- index_name(:issues, "index_issues_on_project_health_status_asc_work_item_type")
ci: -> 0.0000s
ci: == 20230615202125 PrepareIndexIssuesOnProjectHealthStatusAscWorkItemType: reverted (0.0222s)
ci: == [advisory_lock_connection] object_id: 237020, pg_backend_pid: 28263
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 #411673 (closed)