Skip to content

Index `vulnerability_reads` on `project_id` and `vulnerability_id`

Mehmet Emin INAC requested to merge minac_408764_create_specialized_index_on_vulnerability_reads_table into master

What does this MR do and why?

This index will be later utilized by a logic to iterate over all the records related to a project.

Related to Update the `namespace_id` of `vulnerability_rea... (!127649 - merged)

Database review

rake command outputs
rake db:migrate:up 
main: == [advisory_lock_connection] object_id: 708760, pg_backend_pid: 28655
main: == 20230727115635 IndexVulnerabilityReadsOnProjectIdAndVulnerabilityId: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_exists?(:vulnerability_reads, [:project_id, :vulnerability_id], {:name=>"index_vulnerability_reads_on_project_id_and_vulnerability_id", :algorithm=>:concurrently})
main:    -> 0.0084s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:vulnerability_reads, [:project_id, :vulnerability_id], {:name=>"index_vulnerability_reads_on_project_id_and_vulnerability_id", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20230727115635 IndexVulnerabilityReadsOnProjectIdAndVulnerabilityId: migrated (0.0197s)
rake db:migrate:down 
main: == [advisory_lock_connection] object_id: 219740, pg_backend_pid: 29300
main: == 20230727115635 IndexVulnerabilityReadsOnProjectIdAndVulnerabilityId: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0116s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0064s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vulnerability_reads_on_project_id_and_vulnerability_id"})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20230727115635 IndexVulnerabilityReadsOnProjectIdAndVulnerabilityId: reverted (0.0341s)

main: == [advisory_lock_connection] object_id: 219740, pg_backend_pid: 29300
An example query which will benefit from this index 
SELECT
    "vulnerability_reads"."vulnerability_id"
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."project_id" = 278964
    AND "vulnerability_reads"."vulnerability_id" >= 269214
ORDER BY
    "vulnerability_reads"."vulnerability_id" ASC
LIMIT 1 OFFSET 100

Execution plan before the index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20781/commands/68090

Execution plan after the index: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20781/commands/68096

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