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)