Skip to content

Added previously prepared indexes for issues to sort on closed_at

What does this MR do and why?

Related to #365463 (closed).

This MR is a follow-up from !89606 (merged), where I prepared the indexes to speed up the performance when sorting issues by their closed_at filed. With this MR those indexes are actually created.

Warning: before merging we have to make sure that the indexes preparation has been successfully completed in production.

Screenshots or screen recordings

Timings to apply the migration:

main: == 20220620151740 AddIndexesIssuesOnProjectIdAndClosedAt: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:issues, "project_id, closed_at DESC NULLS LAST, state_id, id", {:name=>"index_issues_on_project_id_closed_at_desc_state_id_and_id", :algorithm=>:concurrently})
main:    -> 0.0243s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:issues, "project_id, closed_at DESC NULLS LAST, state_id, id", {:name=>"index_issues_on_project_id_closed_at_desc_state_id_and_id", :algorithm=>:concurrently})
main:    -> 0.0021s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:issues, [:project_id, :closed_at, :state_id, :id], {:name=>"index_issues_on_project_id_closed_at_state_id_and_id", :algorithm=>:concurrently})
main:    -> 0.0243s
main: -- add_index(:issues, [:project_id, :closed_at, :state_id, :id], {:name=>"index_issues_on_project_id_closed_at_state_id_and_id", :algorithm=>:concurrently})
main:    -> 0.0015s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:issues)
main:    -> 0.0250s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_project_id_and_closed_at"})
main:    -> 0.0020s
main: == 20220620151740 AddIndexesIssuesOnProjectIdAndClosedAt: migrated (0.0960s) ==

Timings to revert the migration:

main: == 20220620151740 AddIndexesIssuesOnProjectIdAndClosedAt: reverting ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:issues, [:project_id, :closed_at], {:name=>"index_issues_on_project_id_and_closed_at", :algorithm=>:concurrently})
main:    -> 0.0221s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:issues, [:project_id, :closed_at], {:name=>"index_issues_on_project_id_and_closed_at", :algorithm=>:concurrently})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:issues)
main:    -> 0.0244s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:issues)
main:    -> 0.0255s
main: -- current_schema()
main:    -> 0.0003s
main: == 20220620151740 AddIndexesIssuesOnProjectIdAndClosedAt: reverted (0.0878s) ==

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Alexandru Croitor

Merge request reports