feat: Add resolved_on_default_branch index to vuln reads
What does this MR do and why?
Adds resolved_on_default_branch
index to vulnerability_reads
table in support of !95422 (merged)
Relates to #368284 (closed)
Migration Up
Details
main: == 20221003192827 AddIndexResolvedOnDefaultBranchToVulnerabilitiesRead: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:vulnerability_reads, [:project_id, :state, :id], {:where=>"resolved_on_default_branch IS TRUE", :name=>"index_vuln_reads_on_resolved_on_default_branch", :algorithm=>:concurrently})
main: -> 0.0066s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:vulnerability_reads, [:project_id, :state, :id], {:where=>"resolved_on_default_branch IS TRUE", :name=>"index_vuln_reads_on_resolved_on_default_branch", :algorithm=>:concurrently})
main: -> 0.0032s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: == 20221003192827 AddIndexResolvedOnDefaultBranchToVulnerabilitiesRead: migrated (0.0155s)
CREATE INDEX index_vuln_reads_on_resolved_on_default_branch ON vulnerability_reads USING btree (project_id, state, id) WHERE (resolved_on_default_branch IS TRUE);
The query has been executed. Duration: 3.584 min
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12451/commands/43985
Migration Down
Details
main: == 20221003192827 AddIndexResolvedOnDefaultBranchToVulnerabilitiesRead: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- indexes(:vulnerability_reads)
main: -> 0.0082s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vuln_reads_on_resolved_on_default_branch"})
main: -> 0.0027s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20221003192827 AddIndexResolvedOnDefaultBranchToVulnerabilitiesRead: reverted (0.0172s)
Example queries
SELECT
"vulnerability_reads"."id"
FROM
"vulnerability_reads"
WHERE
"vulnerability_reads"."project_id" = 278964
AND "vulnerability_reads"."id" >= 1
AND "resolved_on_default_branch" IS TRUE
ORDER BY
"vulnerability_reads"."id" ASC
LIMIT 1 OFFSET 1000
Before index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12451/commands/43984
After index (cold cache): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12451/commands/43986
After index (warm cache): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12451/commands/43987
Index
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 Lucas Charles