Skip to content

Add index on to speed up cascading deletes

What does this MR do and why?

This MR adds index_vulnerability_occurrence_pipelines_on_occurrence_id index to speed up cascading deletes that happen when dropping Vulnerabilities::Finding objects. Unfortunately, despite halving the batch size in half the timeout DID happen – https://log.gprd.gitlab.net/goto/09d0c226bd81e7765eaa2fd4c4c3752c

Related to #341917

Database review

Query before and after

Query plans were generated on a thin clone.

Look at Triggers timing on the top

Before: https://explain.dalibo.com/plan/VmQ

After: https://explain.dalibo.com/plan/l1O

db:migrate and db:rollback

➜ bundle exec rails db:migrate 
== 20211104074251 AddOccurrenceIdIndexToVulnerabilityOccurrencePipelines: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrence_pipelines, :occurrence_id, {:name=>"index_vulnerability_occurrence_pipelines_on_occurrence_id", :algorithm=>:concurrently})
   -> 0.0025s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:vulnerability_occurrence_pipelines, :occurrence_id, {:name=>"index_vulnerability_occurrence_pipelines_on_occurrence_id", :algorithm=>:concurrently})
   -> 0.0115s
-- execute("RESET statement_timeout")
   -> 0.0005s
== 20211104074251 AddOccurrenceIdIndexToVulnerabilityOccurrencePipelines: migrated (0.0165s) 


➜ bundle exec rails db:rollback
== 20211104074251 AddOccurrenceIdIndexToVulnerabilityOccurrencePipelines: reverting 
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrence_pipelines)
   -> 0.0021s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:vulnerability_occurrence_pipelines, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrence_pipelines_on_occurrence_id"})
   -> 0.0035s
-- execute("RESET statement_timeout")
   -> 0.0004s
== 20211104074251 AddOccurrenceIdIndexToVulnerabilityOccurrencePipelines: reverted (0.0078s)

Index size

gitlabhq_dblab=# \di+ vulnerability_occurrence_pipelines_occurrence_id_idx
                                                               List of relations
┌────────┬──────────────────────────────────────────────────────┬───────┬────────┬────────────────────────────────────┬─────────┬─────────────┐
│ Schema │                         Name                         │ Type  │ Owner  │               Table                │  Size   │ Description │
├────────┼──────────────────────────────────────────────────────┼───────┼────────┼────────────────────────────────────┼─────────┼─────────────┤
│ public │ vulnerability_occurrence_pipelines_occurrence_id_idx │ index │ gitlab │ vulnerability_occurrence_pipelines │ 8104 MB │             │
└────────┴──────────────────────────────────────────────────────┴───────┴────────┴────────────────────────────────────┴─────────┴─────────────┘

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

Loading