Add index on sbom_occurrences table to match component_version_id

What does this MR do and why?

Add index on sbom_occurrences table on project_id, component_version_id and input_file_path.

EE: true Changelog: added

Related issue: Number of locations from the group query is dif... (#439048 - closed) • Zamir Martins • 16.9
Follow-up from: Use component_version_id instead component_id for (!142161 - merged) • Zamir Martins • 16.9

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.

DB

$ bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 117360, pg_backend_pid: 64587
main: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0135s
main: -- indexes(:sbom_occurrences)
main:    -> 0.0053s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:sbom_occurrences, {:algorithm=>:concurrently, :name=>"index_sbom_occurrences_for_input_file_path_search"})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- index_exists?(:sbom_occurrences, [:project_id, :component_version_id, :input_file_path], {:name=>"idx_sbom_occurr_on_project_component_version_input_file_path", :algorithm=>:concurrently})
main:    -> 0.0041s
main: -- add_index(:sbom_occurrences, [:project_id, :component_version_id, :input_file_path], {:name=>"idx_sbom_occurr_on_project_component_version_input_file_path", :algorithm=>:concurrently})
main:    -> 0.0077s
main: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: migrated (0.0523s)

main: == [advisory_lock_connection] object_id: 117360, pg_backend_pid: 64587
ci: == [advisory_lock_connection] object_id: 132100, pg_backend_pid: 64589
ci: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0005s
ci: -- indexes(:sbom_occurrences)
ci:    -> 0.0060s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:sbom_occurrences, {:algorithm=>:concurrently, :name=>"index_sbom_occurrences_for_input_file_path_search"})
ci:    -> 0.0052s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:sbom_occurrences, [:project_id, :component_version_id, :input_file_path], {:name=>"idx_sbom_occurr_on_project_component_version_input_file_path", :algorithm=>:concurrently})
ci:    -> 0.0043s
ci: -- add_index(:sbom_occurrences, [:project_id, :component_version_id, :input_file_path], {:name=>"idx_sbom_occurr_on_project_component_version_input_file_path", :algorithm=>:concurrently})
ci:    -> 0.0037s
ci: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: migrated (0.0360s)

ci: == [advisory_lock_connection] object_id: 132100, pg_backend_pid: 64589
$ bundle exec rails db:migrate:down:main VERSION=20240123120413
main: == [advisory_lock_connection] object_id: 117000, pg_backend_pid: 65031
main: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0124s
main: -- indexes(:sbom_occurrences)
main:    -> 0.0051s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:sbom_occurrences, {:algorithm=>:concurrently, :name=>"idx_sbom_occurr_on_project_component_version_input_file_path"})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:sbom_occurrences, [:project_id, :component_id, :input_file_path], {:name=>"index_sbom_occurrences_for_input_file_path_search", :algorithm=>:concurrently})
main:    -> 0.0056s
main: -- add_index(:sbom_occurrences, [:project_id, :component_id, :input_file_path], {:name=>"index_sbom_occurrences_for_input_file_path_search", :algorithm=>:concurrently})
main:    -> 0.0074s
main: == 20240123120413 IndexSbomOccurrencesOnProjectIdComponentVersionIdAndInputFilePath: reverted (0.0499s)

main: == [advisory_lock_connection] object_id: 117000, pg_backend_pid: 65031

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.