Add partial index for active Cluster Image Scanning vulnerabilities

What does this MR do and why?

Introduces a partial index on the vulnerabilities table on project_id and id columns to speed up the batched resolution of active Cluster Image Scanning vulnerabilities:

"index_vulnerabilities_on_project_id_and_id_active_cis" btree (project_id, id) WHERE report_type = 7 AND (state = ANY (ARRAY[1, 4]))

Relevant links

• Dependent MR: !79406 (comment 836187351)
• #345905 (closed)

Database Review

Migration

rake db:migrate

== 20220209111007 AddPartialIndexForBatchingActiveClusterImageScanningVulnerabilities: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerabilities, [:project_id, :id], {:where=>"report_type = 7 AND state = ANY(ARRAY[1, 4])", :name=>"index_vulnerabilities_on_project_id_and_id_active_cis", :algorithm=>:concurrently})
   -> 0.0076s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:vulnerabilities, [:project_id, :id], {:where=>"report_type = 7 AND state = ANY(ARRAY[1, 4])", :name=>"index_vulnerabilities_on_project_id_and_id_active_cis", :algorithm=>:concurrently})
   -> 0.0043s
-- execute("RESET statement_timeout")
   -> 0.0005s
== 20220209111007 AddPartialIndexForBatchingActiveClusterImageScanningVulnerabilities: migrated (0.0148s)

rake db:rollback

== 20220209111007 AddPartialIndexForBatchingActiveClusterImageScanningVulnerabilities: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerabilities, [:project_id, :id], {:name=>"index_vulnerabilities_on_project_id_and_id_active_cis", :algorithm=>:concurrently})
   -> 0.0076s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:vulnerabilities, {:name=>"index_vulnerabilities_on_project_id_and_id_active_cis", :algorithm=>:concurrently, :column=>[:project_id, :id]})
   -> 0.0097s
-- execute("RESET statement_timeout")
   -> 0.0007s
== 20220209111007 AddPartialIndexForBatchingActiveClusterImageScanningVulnerabilities: reverted (0.0209s)

Query Plans

SELECT 
  DISTINCT "vulnerabilities"."id" AS alias_0, 
  "vulnerabilities"."id" 
FROM 
  "vulnerabilities" 
  LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id" 
WHERE 
  "vulnerabilities"."project_id" = 14485459 
  AND "vulnerabilities"."state" IN (1, 4) 
  AND "vulnerabilities"."report_type" = 7 
  AND "findings"."uuid" NOT IN (
    '14b2990e-74c1-5ba9-bc35-ef3235b5e683', 
    'cc0aef8c-aafb-5434-9ec2-7953190ed66a'
  ) 
ORDER BY 
  "vulnerabilities"."id" ASC 
LIMIT 
  1;

• Without index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8517/commands/30242
• With index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8517/commands/30245

---

UPDATE 
  "vulnerabilities" 
SET 
  "resolved_on_default_branch" = TRUE, 
  "state" = 3 
WHERE 
  "vulnerabilities"."id" IN (
    SELECT 
      "vulnerabilities"."id" 
    FROM 
      "vulnerabilities" 
      LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id" 
    WHERE 
      "vulnerabilities"."project_id" = 32325934 
      AND "vulnerabilities"."state" IN (1, 4) 
      AND "vulnerabilities"."report_type" = 7 
      AND "findings"."uuid" NOT IN (
        '14b2990e-74c1-5ba9-bc35-ef3235b5e683', 
        'cc0aef8c-aafb-5434-9ec2-7953190ed66a'
      ) 
      AND "vulnerabilities"."id" >= 27818106 
      and vulnerabilities.id < 27818420
  );

• Without index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8517/commands/30243
• With index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8517/commands/30247

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.

Related to #345905 (closed)