Skip to content

Add work_item_type_id closed_at index on issues table

Mario Celi requested to merge 411312-replace-index into master

What does this MR do and why?

In the effort to remove the issues.issue_type column, we need to replace existing issues that use the column with the equivalent that uses the work_item_type_id column.

In !82510 (merged) the index_on_issues_closed_incidents_by_project_id_and_closed_at index was introduced to support a DORA metric. The class has been refactored since then, bu we still produce the same query in https://gitlab.com/gitlab-org/gitlab/-/blob/d7268acafe79a45ecd7173ba37118ce182a9649c/ee/app/models/dora/time_to_restore_service_metric.rb#L20.

Right now Issue.with_issue_type(:incident) will generate the same query as before using the issue_type column. with_issue_type uses the work_item_type_id column behind the issue_type_uses_work_item_types_table feature flag. So, we need this index before we can enable the feature flag.

DB review

Old query with feature flag disabled (using issue_type column)

https://console.postgres.ai/shared/0f8988fe-f961-4e5c-891c-b3183e80f2a0

New query with feature flag enabled (project_id, work_item_type_id, closed_at)

https://console.postgres.ai/shared/90f08144-e509-45d6-87e8-4f189a0b5137

New query with feature flag enabled (work_item_type_id, project_id, closed_at) (old index order)

https://console.postgres.ai/shared/71b836bc-05c0-479b-895b-1fcf038ee210

Migration output

UP 
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 228380, pg_backend_pid: 4434
main: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1951s
main: -- index_exists?(:issues, [:project_id, :work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed", :algorithm=>:concurrently})
main:    -> 0.0293s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:issues, [:project_id, :work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed", :algorithm=>:concurrently})
main:    -> 0.0116s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: migrated (0.2660s)

main: == [advisory_lock_connection] object_id: 228380, pg_backend_pid: 4434
ci: == [advisory_lock_connection] object_id: 228600, pg_backend_pid: 4437
ci: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0012s
ci: -- index_exists?(:issues, [:project_id, :work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed", :algorithm=>:concurrently})
ci:    -> 0.0348s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0006s
ci: -- add_index(:issues, [:project_id, :work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed", :algorithm=>:concurrently})
ci:    -> 0.0092s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: migrated (0.0759s)

ci: == [advisory_lock_connection] object_id: 228600, pg_backend_pid: 4437
DOWN 
bin/rails db:rollback:main db:rollback:ci
main: == [advisory_lock_connection] object_id: 228120, pg_backend_pid: 4872
main: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1298s
main: -- indexes(:issues)
main:    -> 0.0444s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed"})
main:    -> 0.0040s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: reverted (0.2082s)

main: == [advisory_lock_connection] object_id: 228120, pg_backend_pid: 4872
ci: == [advisory_lock_connection] object_id: 263640, pg_backend_pid: 5117
ci: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- indexes(:issues)
ci:    -> 0.0458s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"idx_issues_on_project_work_item_type_closed_at_where_closed"})
ci:    -> 0.0023s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0010s
ci: == 20230614181637 AddIdxIssuesOnWorkItemTypeProjectClosedAtWhereClosed: reverted (0.0769s)

ci: == [advisory_lock_connection] object_id: 263640, pg_backend_pid: 5117

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 #411312 (closed)

Edited by Mario Celi

Merge request reports