Add present on default branch to project_id_and_id_active_cis index (!90426) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Describe in detail what your merge request does and why.

This Merge Request adds the present_on_default_branch attribute to the project_id_and_id_active_cis index on the vulnerabilities table.

The present_on_default_branch field will be used on most queries, and the vulnerabilities indices should be rearranged.

More context can be found on this comment and this issue

This Merge Request is related to the issue #368323 (closed)

The original index index_vulnerabilities_on_project_id_and_id_active_cis was added on MR !80300 (merged) to speed up the batched resolution of active Cluster Image Scanning vulnerabilities:module.

Migration Up

main: == 20220616171355 UpdateVulnerabilitiesProjectIdIdActiveCisIndex: migrating ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:vulnerabilities, [:project_id, :id], {:where=>"report_type = 7 AND state = ANY(ARRAY[1, 4]) AND present_on_default_branch IS TRUE", :name=>"idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch", :algorithm=>:concurrently})
main:    -> 0.0125s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:vulnerabilities, [:project_id, :id], {:where=>"report_type = 7 AND state = ANY(ARRAY[1, 4]) AND present_on_default_branch IS TRUE", :name=>"idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch", :algorithm=>:concurrently})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerabilities)
main:    -> 0.0095s
main: -- remove_index(:vulnerabilities, {:algorithm=>:concurrently, :name=>"index_vulnerabilities_on_project_id_and_id_active_cis"})
main:    -> 0.0025s
main: == 20220616171355 UpdateVulnerabilitiesProjectIdIdActiveCisIndex: migrated (0.0361s)

Migration Down

rake db:migrate:down:main VERSION=20220616171355
main: == 20220616171355 UpdateVulnerabilitiesProjectIdIdActiveCisIndex: reverting ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- 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})
main:    -> 0.0131s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- 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})
main:    -> 0.0027s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerabilities)
main:    -> 0.0102s
main: -- remove_index(:vulnerabilities, {:algorithm=>:concurrently, :name=>"idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch"})
main:    -> 0.0025s
main: == 20220616171355 UpdateVulnerabilitiesProjectIdIdActiveCisIndex: reverted (0.0380s)

Queries Explanation

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')
    AND "vulnerabilities"."present_on_default_branch" IS TRUE
ORDER BY
    "vulnerabilities"."id" ASC
LIMIT 1;

before index

 Limit  (cost=0.99..5.94 rows=1 width=16) (actual time=2.295..2.296 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.226 write=0.000
   ->  Unique  (cost=0.99..143886.36 rows=29038 width=16) (actual time=2.293..2.294 rows=0 loops=1)
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.226 write=0.000
         ->  Nested Loop  (cost=0.99..143812.02 rows=29739 width=16) (actual time=2.292..2.293 rows=0 loops=1)
               Buffers: shared hit=3 read=3
               I/O Timings: read=2.226 write=0.000
               ->  Index Scan using index_vulnerabilities_on_project_id_and_id_active_cis on public.vulnerabilities  (cost=0.42..42355.91 rows=29038 width=8) (actual time=2.290..2.291 rows=0 loops=1)
                     Index Cond: (vulnerabilities.project_id = 14485459)
                     Filter: (vulnerabilities.present_on_default_branch IS TRUE)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=3 read=3
                     I/O Timings: read=2.226 write=0.000
               ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences findings  (cost=0.56..3.48 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (findings.vulnerability_id = vulnerabilities.id)
                     Filter: ((findings.uuid)::text <> ALL ('{14b2990e-74c1-5ba9-bc35-ef3235b5e683,cc0aef8c-aafb-5434-9ec2-7953190ed66a}'::text[]))
                     Rows Removed by Filter: 0
                     I/O Timings: read=0.000 write=0.000

Time: 8.170 ms  
  - planning: 5.800 ms  
  - execution: 2.370 ms  
    - I/O read: 2.226 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 3 (~24.00 KiB) from the buffer pool  
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11592/commands/41182

exec CREATE INDEX idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch ON vulnerabilities USING btree (project_id, id) WHERE ((report_type = 7) AND (state = ANY (ARRAY[1, 4])) AND (present_on_default_branch IS TRUE));

exec DROP INDEX CONCURRENTLY "index_vulnerabilities_on_project_id_and_id_active_cis"

after index

 Limit  (cost=0.99..4.67 rows=1 width=16) (actual time=0.146..0.147 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=0.100 write=0.000
   ->  Unique  (cost=0.99..106873.74 rows=29034 width=16) (actual time=0.145..0.146 rows=0 loops=1)
         Buffers: shared hit=3 read=3
         I/O Timings: read=0.100 write=0.000
         ->  Nested Loop  (cost=0.99..106799.39 rows=29739 width=16) (actual time=0.144..0.145 rows=0 loops=1)
               Buffers: shared hit=3 read=3
               I/O Timings: read=0.100 write=0.000
               ->  Index Only Scan using idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch on public.vulnerabilities  (cost=0.42..5357.66 rows=29034 width=8) (actual time=0.143..0.143 rows=0 loops=1)
                     Index Cond: (vulnerabilities.project_id = 14485459)
                     Heap Fetches: 0
                     Buffers: shared hit=3 read=3
                     I/O Timings: read=0.100 write=0.000
               ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences findings  (cost=0.56..3.48 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (findings.vulnerability_id = vulnerabilities.id)
                     Filter: ((findings.uuid)::text <> ALL ('{14b2990e-74c1-5ba9-bc35-ef3235b5e683,cc0aef8c-aafb-5434-9ec2-7953190ed66a}'::text[]))
                     Rows Removed by Filter: 0
                     I/O Timings: read=0.000 write=0.000

Time: 4.899 ms  
  - planning: 4.705 ms  
  - execution: 0.194 ms  
    - I/O read: 0.100 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 3 (~24.00 KiB) from the buffer pool  
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11592/commands/41185

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
    AND "vulnerabilities"."present_on_default_branch" IS TRUE);

before index

ModifyTable on public.vulnerabilities  (cost=7.61..10.64 rows=1 width=390) (actual time=58.888..58.891 rows=0 loops=1)
   Buffers: shared hit=236 read=58 dirtied=15
   I/O Timings: read=56.383 write=0.000
   ->  Nested Loop  (cost=7.61..10.64 rows=1 width=390) (actual time=58.886..58.888 rows=0 loops=1)
         Buffers: shared hit=236 read=58 dirtied=15
         I/O Timings: read=56.383 write=0.000
         ->  HashAggregate  (cost=7.05..7.06 rows=1 width=28) (actual time=58.885..58.886 rows=0 loops=1)
               Group Key: vulnerabilities_1.id
               Buffers: shared hit=236 read=58 dirtied=15
               I/O Timings: read=56.383 write=0.000
               ->  Nested Loop  (cost=0.99..7.04 rows=1 width=28) (actual time=58.882..58.883 rows=0 loops=1)
                     Buffers: shared hit=236 read=58 dirtied=15
                     I/O Timings: read=56.383 write=0.000
                     ->  Index Scan using index_vulnerabilities_on_project_id_and_id_active_cis on public.vulnerabilities vulnerabilities_1  (cost=0.42..3.45 rows=1 width=14) (actual time=6.480..52.983 rows=58 loops=1)
                           Index Cond: ((vulnerabilities_1.project_id = 32325934) AND (vulnerabilities_1.id >= 27818106) AND (vulnerabilities_1.id < 27818420))
                           Filter: ((vulnerabilities_1.present_on_default_branch IS TRUE) AND (vulnerabilities_1.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities_1.report_type = 7))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=7 read=55 dirtied=15
                           I/O Timings: read=51.000 write=0.000
                     ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences findings  (cost=0.56..3.58 rows=1 width=14) (actual time=0.099..0.099 rows=0 loops=58)
                           Index Cond: (findings.vulnerability_id = vulnerabilities_1.id)
                           Filter: ((findings.uuid)::text <> ALL ('{14b2990e-74c1-5ba9-bc35-ef3235b5e683,cc0aef8c-aafb-5434-9ec2-7953190ed66a}'::text[]))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=229 read=3
                           I/O Timings: read=5.383 write=0.000
         ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.56..3.58 rows=1 width=367) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (vulnerabilities.id = findings.vulnerability_id)
               I/O Timings: read=0.000 write=0.000

Time: 61.555 ms  
  - planning: 2.469 ms  
  - execution: 59.086 ms  
    - I/O read: 56.383 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 236 (~1.80 MiB) from the buffer pool  
  - reads: 58 (~464.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 15 (~120.00 KiB)  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11592/commands/41183

after index

 ModifyTable on public.vulnerabilities  (cost=7.61..10.64 rows=1 width=390) (actual time=0.688..0.690 rows=0 loops=1)
   Buffers: shared hit=291 read=3
   I/O Timings: read=0.231 write=0.000
   ->  Nested Loop  (cost=7.61..10.64 rows=1 width=390) (actual time=0.686..0.687 rows=0 loops=1)
         Buffers: shared hit=291 read=3
         I/O Timings: read=0.231 write=0.000
         ->  HashAggregate  (cost=7.05..7.06 rows=1 width=28) (actual time=0.685..0.686 rows=0 loops=1)
               Group Key: vulnerabilities_1.id
               Buffers: shared hit=291 read=3
               I/O Timings: read=0.231 write=0.000
               ->  Nested Loop  (cost=0.99..7.04 rows=1 width=28) (actual time=0.683..0.683 rows=0 loops=1)
                     Buffers: shared hit=291 read=3
                     I/O Timings: read=0.231 write=0.000
                     ->  Index Scan using idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch on public.vulnerabilities vulnerabilities_1  (cost=0.42..3.45 rows=1 width=14) (actual time=0.301..0.508 rows=58 loops=1)
                           Index Cond: ((vulnerabilities_1.project_id = 32325934) AND (vulnerabilities_1.id >= 27818106) AND (vulnerabilities_1.id < 27818420))
                           Filter: ((vulnerabilities_1.present_on_default_branch IS TRUE) AND (vulnerabilities_1.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities_1.report_type = 7))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=59 read=3
                           I/O Timings: read=0.231 write=0.000
                     ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences findings  (cost=0.56..3.58 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=58)
                           Index Cond: (findings.vulnerability_id = vulnerabilities_1.id)
                           Filter: ((findings.uuid)::text <> ALL ('{14b2990e-74c1-5ba9-bc35-ef3235b5e683,cc0aef8c-aafb-5434-9ec2-7953190ed66a}'::text[]))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=232
                           I/O Timings: read=0.000 write=0.000
         ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.56..3.58 rows=1 width=367) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (vulnerabilities.id = findings.vulnerability_id)
               I/O Timings: read=0.000 write=0.000

Time: 3.465 ms  
  - planning: 2.606 ms  
  - execution: 0.859 ms  
    - I/O read: 0.231 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 291 (~2.30 MiB) from the buffer pool  
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11592/commands/41186

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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 Aug 16, 2022 by Marcos Rocha

Add present on default branch to project_id_and_id_active_cis index