Create index on user_add_on_assignments
What does this MR do and why?
The code changes add a new index to the subscription_user_add_on_assignments
table in the database. This index will help speed up queries that search for user add-on assignments based on the add-on purchase ID and the assignment ID. The index name is idx_user_add_on_assignments_on_add_on_purchase_id_and_id
.
Reference of suggestion comment: !149892 (comment 1871528242)
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.
Database
database migration
Click to expand
bin/rails db:migrate:main
main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 99374
main: == 20240423082718 AddIndexToUserAddOnAssignmentsAddOnPurchaseIdAndId: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0191s
main: -- index_exists?(:subscription_user_add_on_assignments, [:add_on_purchase_id, :id], {:name=>"idx_user_add_on_assignments_on_add_on_purchase_id_and_id", :algorithm=>:concurrently})
main: -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:subscription_user_add_on_assignments, [:add_on_purchase_id, :id], {:name=>"idx_user_add_on_assignments_on_add_on_purchase_id_and_id", :algorithm=>:concurrently})
main: -> 0.0029s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20240423082718 AddIndexToUserAddOnAssignmentsAddOnPurchaseIdAndId: migrated (0.0407s)
main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 99374
database rollback
Click to expand
bin/rails db:rollback:main
main: == [advisory_lock_connection] object_id: 123600, pg_backend_pid: 98981
main: == 20240423082718 AddIndexToUserAddOnAssignmentsAddOnPurchaseIdAndId: reverting
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0119s
main: -- indexes(:subscription_user_add_on_assignments)
main: -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:subscription_user_add_on_assignments, {:algorithm=>:concurrently, :name=>"idx_user_add_on_assignments_on_add_on_purchase_id_and_id"})
main: -> 0.0024s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: == 20240423082718 AddIndexToUserAddOnAssignmentsAddOnPurchaseIdAndId: reverted (0.0286s)
main: == [advisory_lock_connection] object_id: 123600, pg_backend_pid: 9898
creating index
query plan
SELECT
"subscription_user_add_on_assignments"."user_id"
FROM
"subscription_user_add_on_assignments"
WHERE
"subscription_user_add_on_assignments"."add_on_purchase_id" = 64
ORDER BY
"subscription_user_add_on_assignments"."id" DESC
PostgresAI: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27731/commands/86446
Related to #456823
Edited by Bishwa Hang Rai