Add index_purl_type_package_name_on_pm_affected_packages

What does this MR do and why?

Add index_purl_type_package_name_on_pm_affected_packages.

Related issue: #464575 (closed)

This index is going to be used by the query defined as part of Add for_occurrences and with_advisory scopes (!158791 - merged) • Zamir Martins • 17.3

DB Migration/Rollback

$ bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 129420, pg_backend_pid: 76831
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0158s
main: -- index_exists?(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_pm_affected_packages_on_purl_type_and_package_name", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_pm_affected_packages_on_purl_type_and_package_name", :algorithm=>:concurrently})
main:    -> 0.4288s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrated (0.4624s)

main: == [advisory_lock_connection] object_id: 129420, pg_backend_pid: 76831
ci: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 76834
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- index_exists?(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_pm_affected_packages_on_purl_type_and_package_name", :algorithm=>:concurrently})
ci:    -> 0.0028s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_pm_affected_packages_on_purl_type_and_package_name", :algorithm=>:concurrently})
ci:    -> 0.0056s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrated (0.0241s)

ci: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 76834

$ bundle exec rails db:migrate:down:main VERSION=20240628141027
main: == [advisory_lock_connection] object_id: 127740, pg_backend_pid: 93484
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0102s
main: -- indexes(:pm_affected_packages)
main:    -> 0.0030s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:pm_affected_packages, {:algorithm=>:concurrently, :name=>"index_purl_type_package_name_on_pm_affected_packages"})
main:    -> 0.0146s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverted (0.0411s)

$ bundle exec rails db:migrate:down:ci VERSION=20240628141027
ci: == [advisory_lock_connection] object_id: 127740, pg_backend_pid: 93970
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0244s
ci: -- indexes(:pm_affected_packages)
ci:    -> 0.0038s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:pm_affected_packages, {:algorithm=>:concurrently, :name=>"index_purl_type_package_name_on_pm_affected_packages"})
ci:    -> 0.0016s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverted (0.0512s)

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.