Add tmp issues.correct_work_item_type_id indexes

What does this MR do and why?

We are temporarily going to use this column in order to cleanup the original one. In the mean time we still need these indexes to exist as otherwise large instances might face performance issues when fetching issue records.

Index created async for .com in !170009 (merged)

As noted in !170009 (comment 2175111703) it should be fine to create these indexes temporarily in the issues table and that's why the cop was disabled.

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.

Migration output

UP

main: == [advisory_lock_connection] object_id: 129460, pg_backend_pid: 56063
main: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0132s
main: -- index_exists?(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
main:    -> 0.0176s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
main:    -> 0.0069s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: migrated (0.0525s)

main: == [advisory_lock_connection] object_id: 129460, pg_backend_pid: 56063
ci: == [advisory_lock_connection] object_id: 129720, pg_backend_pid: 56065
ci: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
ci:    -> 0.0089s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
ci:    -> 0.0054s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: migrated (0.0269s)

ci: == [advisory_lock_connection] object_id: 129720, pg_backend_pid: 56065
main: == [advisory_lock_connection] object_id: 131540, pg_backend_pid: 56069
main: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
main:    -> 0.0099s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
main:    -> 0.0113s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: migrated (0.0318s)

main: == [advisory_lock_connection] object_id: 131540, pg_backend_pid: 56069
ci: == [advisory_lock_connection] object_id: 164220, pg_backend_pid: 56071
ci: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
ci:    -> 0.0094s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
ci:    -> 0.0028s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: migrated (0.0244s)

ci: == [advisory_lock_connection] object_id: 164220, pg_backend_pid: 56071
main: == [advisory_lock_connection] object_id: 225580, pg_backend_pid: 56074
main: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
main:    -> 0.0092s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: migrated (0.0215s)

main: == [advisory_lock_connection] object_id: 225580, pg_backend_pid: 56074
ci: == [advisory_lock_connection] object_id: 228660, pg_backend_pid: 56076
ci: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
ci:    -> 0.0102s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
ci:    -> 0.0026s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: migrated (0.0251s)

ci: == [advisory_lock_connection] object_id: 228660, pg_backend_pid: 56076
main: == [advisory_lock_connection] object_id: 261220, pg_backend_pid: 56079
main: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
main:    -> 0.0088s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: migrated (0.0204s)

main: == [advisory_lock_connection] object_id: 261220, pg_backend_pid: 56079
ci: == [advisory_lock_connection] object_id: 294580, pg_backend_pid: 56081
ci: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
ci:    -> 0.0100s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
ci:    -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: migrated (0.0241s)

ci: == [advisory_lock_connection] object_id: 294580, pg_backend_pid: 56081

DOWN

main: == [advisory_lock_connection] object_id: 129060, pg_backend_pid: 56529
main: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0135s
main: -- indexes(:issues)
main:    -> 0.0089s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_correct_type_project_created_at_state"})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: reverted (0.0379s)

main: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- indexes(:issues)
main:    -> 0.0079s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type"})
main:    -> 0.0010s
main: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: reverted (0.0155s)

main: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- indexes(:issues)
main:    -> 0.0074s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type"})
main:    -> 0.0009s
main: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: reverted (0.0148s)

main: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- indexes(:issues)
main:    -> 0.0071s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed"})
main:    -> 0.0008s
main: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: reverted (0.0144s)

main: == [advisory_lock_connection] object_id: 129060, pg_backend_pid: 56529
ci: == [advisory_lock_connection] object_id: 260480, pg_backend_pid: 56777
ci: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:issues)
ci:    -> 0.0103s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_correct_type_project_created_at_state"})
ci:    -> 0.0020s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20241106220155 CreateTmpIndexOnIssuesCorrectTypeProjectCreatedAtState: reverted (0.0264s)

ci: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0005s
ci: -- indexes(:issues)
ci:    -> 0.0098s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type"})
ci:    -> 0.0011s
ci: == 20241106215907 CreateTmpIndexOnIssuesProjectHealthIdAscStateCorrectType: reverted (0.0248s)

ci: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- indexes(:issues)
ci:    -> 0.0090s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type"})
ci:    -> 0.0011s
ci: == 20241106215409 CreateTmpIndexOnIssuesProjectHealthIdDescStateCorrectType: reverted (0.0220s)

ci: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- indexes(:issues)
ci:    -> 0.0087s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed"})
ci:    -> 0.0009s
ci: == 20241106213841 CreateTmpIndexOnIssuesByProjectCorrectTypeWhereClosed: reverted (0.0211s)

ci: == [advisory_lock_connection] object_id: 260480, pg_backend_pid: 56777

Related to #498271 (closed)