Skip to content

Index vulnerability reads on filter columns for group-level queries

What does this MR do and why?

This MR introduces a new index for the vulnerability_reads table to speed up the group-level queries.

Related to [Proposal] Add `namespace_id` to `vulnerability... (#367300 - closed)

Database review

I didn't choose creating this index asynchronously because we've recently created a similar index with a post-deployment migration.

rake db:migrate:up
main: == 20220729052031 AddIndexOnVulnerabilityReadsNamespaceIdWithReportType: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:vulnerability_reads, [:namespace_id, :state, :report_type, :severity, :vulnerability_id], {:name=>"index_vulnerability_reads_common_finder_query_with_namespace_id", :order=>{:vulnerability_id=>:desc}, :algorithm=>:concurrently})
main:    -> 0.0141s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:vulnerability_reads, [:namespace_id, :state, :report_type, :severity, :vulnerability_id], {:name=>"index_vulnerability_reads_common_finder_query_with_namespace_id", :order=>{:vulnerability_id=>:desc}, :algorithm=>:concurrently})
main:    -> 0.0045s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20220729052031 AddIndexOnVulnerabilityReadsNamespaceIdWithReportType: migrated (0.0295s)
rake db:migrate:down
main: == 20220729052031 AddIndexOnVulnerabilityReadsNamespaceIdWithReportType: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0151s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vulnerability_reads_common_finder_query_with_namespace_id"})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20220729052031 AddIndexOnVulnerabilityReadsNamespaceIdWithReportType: reverted (0.0287s)

MR acceptance checklist

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

Edited by Mehmet Emin INAC

Merge request reports

Loading