Add an index on vulnerability_occurrence_pipelines

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.