Skip to content

Added index for member_approval on member_namespace_id and status

Suraj Tripathi requested to merge 433173_add_idx_on_member_approval_memb_namespace_id_and_status into master

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

Merge request reports