Add an index on vulnerability_occurrence_pipelines
requested to merge 344084-create-index-on-vulnerability_occurrence_pipelines-to-speed-up-vulnerability-report into master
What does this MR do and why?
This MR creates (occurrence_id, id DESC)
index on vulnerability_occurrence_pipelines
table to speed up a query executed when going to Vulnerability Report
Related to #344084 (closed)
Related to gitlab-com/gl-infra/production#5744 (closed)
Database review
Query before: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6955/commands/24642
Query after: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7076/commands/25048
migrate and rollback
➜ bundle exec rails db:migrate
== 20211028085926 AddOccurrenceIdIdDescIndexToVulnerabilityOccurrencePipelines: migrating
-- transaction_open?()
-> 0.0000s
-- index_exists?(:vulnerability_occurrence_pipelines, [:occurrence_id, :id], {:order=>{:id=>:desc}, :name=>"index_vulnerability_occurrence_pipelines_occurrence_id_and_id", :algorithm=>:concurrently})
-> 0.0026s
-- execute("SET statement_timeout TO 0")
-> 0.0006s
-- add_index(:vulnerability_occurrence_pipelines, [:occurrence_id, :id], {:order=>{:id=>:desc}, :name=>"index_vulnerability_occurrence_pipelines_occurrence_id_and_id", :algorithm=>:concurrently})
-> 0.0917s
-- execute("RESET statement_timeout")
-> 0.0019s
== 20211028085926 AddOccurrenceIdIdDescIndexToVulnerabilityOccurrencePipelines: migrated (0.1005s)
➜ bundle exec rails db:rollback
== 20211028085926 AddOccurrenceIdIdDescIndexToVulnerabilityOccurrencePipelines: reverting
-- transaction_open?()
-> 0.0000s
-- indexes(:vulnerability_occurrence_pipelines)
-> 0.0022s
-- execute("SET statement_timeout TO 0")
-> 0.0006s
-- remove_index(:vulnerability_occurrence_pipelines, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrence_pipelines_occurrence_id_and_id"})
-> 0.0796s
-- execute("RESET statement_timeout")
-> 0.0019s
== 20211028085926 AddOccurrenceIdIdDescIndexToVulnerabilityOccurrencePipelines: reverted (0.0885s)
Size of the table
gitlabhq_production=> SELECT relpages, reltuples FROM pg_class WHERE relname = 'vulnerability_occurrence_pipelines';
relpages | reltuples
----------+---------------
3099465 | 3.7166906e+08 (371_669_060)
(1 row)
Timings
https://gitlab.slack.com/archives/CLJMDRD8C/p1635412322340800
exec CREATE INDEX CONCURRENTLY index_vulnerability_occurrence_pipelines_occurrence_id_and_id ON vulnerability_occurrence_pipelines(occurrence_id, id DESC);
The query has been executed. Duration: 26.723 min
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 Michał Zając