Index vulnerability reads on filter columns for group-level queries
requested to merge 367300_add_index_on_vulnerability_reads_namespace_id_with_report_type into master
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.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mehmet Emin INAC