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.