Skip to content

Replace issues health status sorting indexes [ASYNC]

Mario Celi requested to merge 411673-replace-indexes into master

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

Before (using issue_type column)

After (using work_item_types table)

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.

Related to #411673 (closed)

Edited by Mario Celi

Merge request reports