Added index for member_approval on member_namespace_id and status
What does this MR do and why?
- Adds partial index on member_approval table for member_namespace_id and status column.
- This index will be relevant when querying the list of pending members for a namespace. It was raised as a MR feedback here
ref: #433173 (closed)
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.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
Database migration
UP
main: == [advisory_lock_connection] object_id: 122320, pg_backend_pid: 27511
main: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0168s
main: -- index_exists?(:member_approvals, [:member_namespace_id, :status], {:where=>"status = 0", :name=>"index_member_approvals_on_member_namespace_id_status", :algorithm=>:concurrently})
main: -> 0.0031s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:member_approvals, [:member_namespace_id, :status], {:where=>"status = 0", :name=>"index_member_approvals_on_member_namespace_id_status", :algorithm=>:concurrently})
main: -> 0.0029s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: migrated (0.0343s)
main: == [advisory_lock_connection] object_id: 122320, pg_backend_pid: 27511
ci: == [advisory_lock_connection] object_id: 122740, pg_backend_pid: 27513
ci: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0005s
ci: -- index_exists?(:member_approvals, [:member_namespace_id, :status], {:where=>"status = 0", :name=>"index_member_approvals_on_member_namespace_id_status", :algorithm=>:concurrently})
ci: -> 0.0028s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0001s
ci: -- add_index(:member_approvals, [:member_namespace_id, :status], {:where=>"status = 0", :name=>"index_member_approvals_on_member_namespace_id_status", :algorithm=>:concurrently})
ci: -> 0.0023s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0001s
ci: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: migrated (0.0175s)
Down
main: == [advisory_lock_connection] object_id: 121980, pg_backend_pid: 28162
main: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: reverting
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0189s
main: -- indexes(:member_approvals)
main: -> 0.0039s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:member_approvals, {:algorithm=>:concurrently, :name=>"index_member_approvals_on_member_namespace_id_status"})
main: -> 0.0020s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
main: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: reverted (0.0378s)
main: == [advisory_lock_connection] object_id: 121980, pg_backend_pid: 28162
ci: == [advisory_lock_connection] object_id: 121980, pg_backend_pid: 28584
ci: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: reverting
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0192s
ci: -- indexes(:member_approvals)
ci: -> 0.0040s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- remove_index(:member_approvals, {:algorithm=>:concurrently, :name=>"index_member_approvals_on_member_namespace_id_status"})
ci: -> 0.0023s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0004s
ci: == 20240410061546 AddIndexForMemberApprovalsMemberNamespaceIdStatus: reverted (0.0443s)
ci: == [advisory_lock_connection] object_id: 121980, pg_backend_pid: 28584