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

Click to expand

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