Skip to content

Add index for owasp_top_10 project level GraphQL queries

Bala Kumar requested to merge 432618-include-owasp-graphql-db-index into master

What does this MR do and why?

Adds DB index to support the queries being executed by the GraphQL API changes for owasp_top_10 grouping feature being implemented in !140205 (merged) for Include OWASP filter to vulnerabilitySeverities... (#432618 - closed)

Index including namespace_id to support group level report queries is tracked in #437253 (closed)

Database

1. Migration up > bundle exec rake db:migrate:up VERSION=20240104223119 
main: == [advisory_lock_connection] object_id: 182520, pg_backend_pid: 7577
main: == 20240104223119 AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.5316s
main: -- index_exists?(:vulnerability_reads, [:project_id, :owasp_top_10], {:name=>"index_vuln_reads_on_project_id_owasp_top_10", :algorithm=>:concurrently})
main:    -> 0.0077s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:vulnerability_reads, [:project_id, :owasp_top_10], {:name=>"index_vuln_reads_on_project_id_owasp_top_10", :algorithm=>:concurrently})
main:    -> 0.0087s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240104223119 AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads: migrated (0.5657s)

main: == [advisory_lock_connection] object_id: 182520, pg_backend_pid: 7577
2. Migration down > bundle exec rake db:migrate:down:main VERSION=20240104223119 
main: == [advisory_lock_connection] object_id: 182520, pg_backend_pid: 8176
main: == 20240104223119 AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0668s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0069s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vuln_reads_on_project_id_owasp_top_10"})
main:    -> 0.0013s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20240104223119 AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads: reverted (0.0861s)

main: == [advisory_lock_connection] object_id: 182520, pg_backend_pid: 8176
  1. Query to be executed from UI to project.vulnerabilitySeveritiesCount GraphQL API:
SELECT COUNT(*) AS "count_all", "vulnerability_reads"."severity" AS "vulnerability_reads_severity" FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 278964 AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) AND "vulnerability_reads"."owasp_top_10" = 4 AND "vulnerability_reads"."resolved_on_default_branch" = FALSE GROUP BY "vulnerability_reads"."severity" ORDER BY "vulnerability_reads"."severity" DESC
  1. Query to be executed from UI to project.vulnerabilities GraphQL API:
SELECT "vulnerability_reads".* FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 278964 AND "vulnerability_reads"."owasp_top_10" = 4 AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) AND "vulnerability_reads"."state" IN (1, 4) AND "vulnerability_reads"."resolved_on_default_branch" = FALSE ORDER BY "vulnerability_reads"."severity" DESC, "vulnerability_reads"."vulnerability_id" DESC LIMIT 21

Without Index

1st query: 
Aggregate  (cost=167539.93..167540.06 rows=1 width=10) (actual time=6180.939..6192.608 rows=0 loops=1)
   Group Key: vulnerability_reads.severity
   Buffers: shared hit=20966 read=21600 dirtied=1792
   I/O Timings: read=17989.220 write=0.000
   ->  Gather Merge  (cost=167539.93..167540.05 rows=1 width=2) (actual time=6180.937..6192.603 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=20966 read=21600 dirtied=1792
         I/O Timings: read=17989.220 write=0.000
         ->  Sort  (cost=166539.90..166539.91 rows=1 width=2) (actual time=6136.550..6136.552 rows=0 loops=3)
               Sort Key: vulnerability_reads.severity DESC
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=20966 read=21600 dirtied=1792
               I/O Timings: read=17989.220 write=0.000
               ->  Parallel Index Scan using index_vulnerability_reads_on_project_id_and_vulnerability_id on public.vulnerability_reads  (cost=0.57..166539.89 rows=1 width=2) (actual time=6136.507..6136.507 rows=0 loops=3)
                     Index Cond: (vulnerability_reads.project_id = 278964)
                     Filter: ((NOT vulnerability_reads.resolved_on_default_branch) AND (vulnerability_reads.owasp_top_10 = 4) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])))
                     Rows Removed by Filter: 40255
                     Buffers: shared hit=20948 read=21600 dirtied=1792
                     I/O Timings: read=17989.220 write=0.000
  Time: 6.196 s  
  - planning: 3.448 ms  
  - execution: 6.193 s  
    - I/O read: 17.989 s  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 20966 (~163.80 MiB) from the buffer pool  
  - reads: 21600 (~168.80 MiB) from the OS file cache, including disk I/O  
  - dirtied: 1792 (~14.00 MiB)  
  - writes: 0

DB lab: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/25121/commands/79769

2nd query: 
Limit  (cost=119115.54..119115.54 rows=1 width=169) (actual time=609.114..609.116 rows=0 loops=1)
   Buffers: shared hit=84 read=390 dirtied=34
   I/O Timings: read=594.097 write=0.000
   ->  Sort  (cost=119115.54..119115.54 rows=1 width=169) (actual time=609.112..609.113 rows=0 loops=1)
         Sort Key: vulnerability_reads.severity DESC, vulnerability_reads.vulnerability_id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=84 read=390 dirtied=34
         I/O Timings: read=594.097 write=0.000
         ->  Index Scan using index_project_vulnerability_reads_common_finder_query_desc on public.vulnerability_reads  (cost=0.57..119115.53 rows=1 width=169) (actual time=609.087..609.088 rows=0 loops=1)
               Index Cond: ((vulnerability_reads.project_id = 278964) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])))
               Filter: ((NOT vulnerability_reads.resolved_on_default_branch) AND (vulnerability_reads.owasp_top_10 = 4))
               Rows Removed by Filter: 469
               Buffers: shared hit=78 read=390 dirtied=34
               I/O Timings: read=594.097 write=0.000
Time: 613.489 ms  
  - planning: 4.282 ms  
  - execution: 609.207 ms  
    - I/O read: 594.097 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 84 (~672.00 KiB) from the buffer pool  
  - reads: 390 (~3.00 MiB) from the OS file cache, including disk I/O  
  - dirtied: 34 (~272.00 KiB)  
  - writes: 0

DB lab: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/25121/commands/79775

With Index

CREATE INDEX index_vuln_reads_on_project_id_owasp_top_10 ON vulnerability_reads USING btree (project_id, owasp_top_10);
1st query: 
 Aggregate  (cost=5.05..5.07 rows=1 width=10) (actual time=0.318..0.319 rows=0 loops=1)
   Group Key: vulnerability_reads.severity
   Buffers: shared hit=9 read=4
   I/O Timings: read=0.219 write=0.000
   ->  Sort  (cost=5.05..5.05 rows=1 width=2) (actual time=0.317..0.318 rows=0 loops=1)
         Sort Key: vulnerability_reads.severity DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=9 read=4
         I/O Timings: read=0.219 write=0.000
         ->  Index Scan using index_vuln_reads_on_project_id_owasp_top_10 on public.vulnerability_reads  (cost=0.57..5.04 rows=1 width=2) (actual time=0.258..0.258 rows=0 loops=1)
               Index Cond: ((vulnerability_reads.project_id = 278964) AND (vulnerability_reads.owasp_top_10 = 4))
               Filter: ((NOT vulnerability_reads.resolved_on_default_branch) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])))
               Rows Removed by Filter: 0
               Buffers: shared hit=6 read=4
               I/O Timings: read=0.219 write=0.000

Time: 4.261 ms  
  - planning: 3.830 ms  
  - execution: 0.431 ms  
    - I/O read: 0.219 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 9 (~72.00 KiB) from the buffer pool  
  - reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

DB Lab: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/25121/commands/79772

2nd query: 
Limit  (cost=5.05..5.06 rows=1 width=169) (actual time=0.059..0.061 rows=0 loops=1)
   Buffers: shared hit=16
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=5.05..5.06 rows=1 width=169) (actual time=0.058..0.058 rows=0 loops=1)
         Sort Key: vulnerability_reads.severity DESC, vulnerability_reads.vulnerability_id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=16
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_vuln_reads_on_project_id_owasp_top_10 on public.vulnerability_reads  (cost=0.57..5.04 rows=1 width=169) (actual time=0.034..0.034 rows=0 loops=1)
               Index Cond: ((vulnerability_reads.project_id = 278964) AND (vulnerability_reads.owasp_top_10 = 4))
               Filter: ((NOT vulnerability_reads.resolved_on_default_branch) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])))
               Rows Removed by Filter: 0
               Buffers: shared hit=10
               I/O Timings: read=0.000 write=0.000
Time: 4.290 ms  
  - planning: 4.160 ms  
  - execution: 0.130 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 16 (~128.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

DB Lab: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/25121/commands/79773

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #432618 (closed)

Edited by Bala Kumar

Merge request reports