Add index for agent_id in vulnerability_occurrences location

What does this MR do and why?

This MR adds new index used in !76342 (merged)

SQL Query

SELECT * FROM vulnerabilities INNER JOIN vulnerability_occurrences ON vulnerability_occurrences.vulnerability_id = vulnerabilities.id WHERE vulnerabilities.project_id = 27958807 AND vulnerability_occurrences.report_type = 7 AND (vulnerability_occurrences.location -> 'agent_id' ?| array['153813']);

Before: 2.497 s

 Nested Loop  (cost=1.00..2316.78 rows=1 width=1978) (actual time=2490.865..2490.868 rows=0 loops=1)
   Buffers: shared hit=2577 read=991 dirtied=147
   I/O Timings: read=2443.311 write=0.000
   ->  Index Scan using index_vulnerabilities_on_project_id_and_state_and_severity on public.vulnerabilities  (cost=0.56..703.56 rows=465 width=354) (actual time=6.515..1623.167 rows=736 loops=1)
         Index Cond: (vulnerabilities.project_id = 27958807)
         Buffers: shared hit=7 read=617 dirtied=147
         I/O Timings: read=1611.528 write=0.000
   ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences  (cost=0.44..3.46 rows=1 width=1624) (actual time=1.175..1.175 rows=0 loops=736)
         Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
         Filter: ((vulnerability_occurrences.report_type = 7) AND ((vulnerability_occurrences.location -> 'agent_id'::text) ?| '{153813}'::text[]))
         Rows Removed by Filter: 1
         Buffers: shared hit=2570 read=374
         I/O Timings: read=831.783 write=0.000

After: 7.013 ms

 Nested Loop  (cost=7.94..67.26 rows=1 width=1978) (actual time=0.155..0.157 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Heap Scan on public.vulnerability_occurrences  (cost=7.50..25.76 rows=12 width=1624) (actual time=0.155..0.155 rows=0 loops=1)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Index Scan using index_vulnerability_occurrences_on_location_agent_id  (cost=0.00..7.50 rows=12 width=0) (actual time=0.100..0.101 rows=0 loops=1)
               Index Cond: ((vulnerability_occurrences.location -> 'agent_id'::text) ?| '{153813}'::text[])
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
   ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.44..3.46 rows=1 width=354) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Filter: (vulnerabilities.project_id = 27958807)
         Rows Removed by Filter: 0
         I/O Timings: read=0.000 write=0.000

Database

Migrate Up

== 20211213064821 AddAgentIdLocationIndexToVulnerabilityOccurrences: reverting 
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0058s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_agent_id"})
   -> 0.0134s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20211213064821 AddAgentIdLocationIndexToVulnerabilityOccurrences: reverted (0.0260s) 

Migrate Down

== 20211213064821 AddAgentIdLocationIndexToVulnerabilityOccurrences: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_agent_id", :algorithm=>:concurrently})
   -> 0.0121s
-- execute("SET statement_timeout TO 0")
   -> 0.0016s
-- add_index(:vulnerability_occurrences, "(location -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_agent_id", :algorithm=>:concurrently})
   -> 0.0718s
-- execute("RESET statement_timeout")
   -> 0.0012s
== 20211213064821 AddAgentIdLocationIndexToVulnerabilityOccurrences: migrated (0.0969s) 

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #342662 (closed)