Skip to content

Optimize the pending todos check query

Adam Hegyi requested to merge optimize-frequently-called-todos-query-v2 into master

What does this MR do and why?

This MR adds a new index to the todos table. The async index was created last week and the index is already on PRD: !97025 (merged)

DB

Up:

main: == 20220912085047 AddIndexToTodosPendingQuery: migrating ======================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:todos, [:user_id, :project_id, :target_type, :target_id, :id], {:name=>"index_on_todos_user_project_target_and_state", :where=>"state = 'pending'", :algorithm=>:concurrently})
main:    -> 0.0113s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:todos, [:user_id, :project_id, :target_type, :target_id, :id], {:name=>"index_on_todos_user_project_target_and_state", :where=>"state = 'pending'", :algorithm=>:concurrently})
main:    -> 0.0137s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220912085047 AddIndexToTodosPendingQuery: migrated (0.0394s) =============

ci: == 20220912085047 AddIndexToTodosPendingQuery: migrating ======================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:todos, [:user_id, :project_id, :target_type, :target_id, :id], {:name=>"index_on_todos_user_project_target_and_state", :where=>"state = 'pending'", :algorithm=>:concurrently})
ci:    -> 0.0067s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:todos, [:user_id, :project_id, :target_type, :target_id, :id], {:name=>"index_on_todos_user_project_target_and_state", :where=>"state = 'pending'", :algorithm=>:concurrently})
ci:    -> 0.0052s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20220912085047 AddIndexToTodosPendingQuery: migrated (0.0174s) =============

Down:

ci: == 20220912085047 AddIndexToTodosPendingQuery: reverting ======================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:todos)
ci:    -> 0.0081s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:todos, {:algorithm=>:concurrently, :name=>"index_on_todos_user_project_target_and_state"})
ci:    -> 0.0029s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220912085047 AddIndexToTodosPendingQuery: reverted (0.0167s) =============

main: == 20220912085047 AddIndexToTodosPendingQuery: reverting ======================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:todos)
main:    -> 0.0082s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:todos, {:algorithm=>:concurrently, :name=>"index_on_todos_user_project_target_and_state"})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220912085047 AddIndexToTodosPendingQuery: reverted (0.0154s) =============

MR acceptance checklist

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

Merge request reports