Skip to content

Add index for cluster_id in vulnerability_occurrences location

Sashi Kumar Kumaresan requested to merge sk/337926-add-cluster-id-index into master

What does this MR do and why?

Addresses #337926 (closed)

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 -> 'cluster_id' ?| array['153813']);
Before: 1.759 s 
 Nested Loop  (cost=1.12..1778.53 rows=1 width=1964) (actual time=1753.267..1753.269 rows=0 loops=1)
   Buffers: shared hit=3315 read=992 dirtied=239
   I/O Timings: read=1722.143 write=0.000
   ->  Index Scan using index_vulnerabilities_on_project_id_and_state_and_severity on public.vulnerabilities  (cost=0.56..530.19 rows=347 width=315) (actual time=5.652..1097.895 rows=736 loops=1)
         Index Cond: (vulnerabilities.project_id = 27958807)
         Buffers: shared hit=7 read=617 dirtied=239
         I/O Timings: read=1081.074 write=0.000
   ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences  (cost=0.56..3.59 rows=1 width=1649) (actual time=0.887..0.887 rows=0 loops=736)
         Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
         Filter: ((vulnerability_occurrences.report_type = 7) AND ((vulnerability_occurrences.location -> 'cluster_id'::text) ?| '{153813}'::text[]))
         Rows Removed by Filter: 1
         Buffers: shared hit=3308 read=375
         I/O Timings: read=641.069 write=0.000
After: 6.456 ms 
 Nested Loop  (cost=3.56..23.43 rows=1 width=1964) (actual time=0.187..0.188 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=3.00..9.08 rows=4 width=1649) (actual time=0.186..0.187 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_cluster_id  (cost=0.00..3.00 rows=4 width=0) (actual time=0.184..0.184 rows=0 loops=1)
               Index Cond: ((vulnerability_occurrences.location -> 'cluster_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.56..3.58 rows=1 width=315) (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

$ bundle exec rake db:migrate:up VERSION=20211022112202
== 20211022112202 AddClusterIdLocationIndexToVulnerabilityOccurrences: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_cluster_id", :algorithm=>:concurrently})
   -> 0.0043s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:vulnerability_occurrences, "(location -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_cluster_id", :algorithm=>:concurrently})
   -> 0.0465s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20211022112202 AddClusterIdLocationIndexToVulnerabilityOccurrences: migrated (0.0722s)

Migrate Down

$ bundle exec rake db:migrate:down VERSION=20211022112202
== 20211022112202 AddClusterIdLocationIndexToVulnerabilityOccurrences: reverting 
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0043s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_cluster_id"})
   -> 0.0045s
-- execute("RESET statement_timeout")
   -> 0.0005s
== 20211022112202 AddClusterIdLocationIndexToVulnerabilityOccurrences: reverted (0.0162s)

MR acceptance checklist

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

Merge request reports