Owasp top 10 index for the group level vulnerability report page (!158219) · Merge requests · GitLab.org / GitLab

Bala Kumar requested to merge 458454-owasp-top-10-index-group-level-report into master Jul 03, 2024

What does this MR do and why?

Add index to support the group level vulnerability reports grouping by OWASP top 10.

Currently the page times out when visiting: https://gitlab.com/groups/gitlab-org/-/security/vulnerabilities/?groupBy=owasp_2017

Currently vulnerability_reads is under DB team radar for WAL updates as it has 25 indices and we discussed about this new index in #458454 (comment 1968516276). We also have prioritized to remove 3 unused indices in #470937 which should give us headroom as well.

Database

1. Migration up > bundle exec rake db:migrate:up:main VERSION=20240703142511

main: == [advisory_lock_connection] object_id: 130280, pg_backend_pid: 64522
main: == 20240703142511 AddIndexOwaspTop10ForGroupLevelReports: migrating ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0202s
main: -- index_exists?(:vulnerability_reads, [:owasp_top_10, :state, :report_type, :severity, :traversal_ids, :vulnerability_id, :resolved_on_default_branch], {:where=>"archived = false", :name=>"index_for_owasp_top_10_group_level_reports", :algorithm=>:concurrently})
main:    -> 0.0114s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:vulnerability_reads, [:owasp_top_10, :state, :report_type, :severity, :traversal_ids, :vulnerability_id, :resolved_on_default_branch], {:where=>"archived = false", :name=>"index_for_owasp_top_10_group_level_reports", :algorithm=>:concurrently})
main:    -> 0.0321s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20240703142511 AddIndexOwaspTop10ForGroupLevelReports: migrated (0.0856s) ==

main: == [advisory_lock_connection] object_id: 130280, pg_backend_pid: 64522

2. Migration down > bundle exec rake db:migrate:down:main VERSION=20240703142511

main: == [advisory_lock_connection] object_id: 128440, pg_backend_pid: 27369
main: == 20240703142511 AddIndexOwaspTop10ForGroupLevelReports: reverting ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0114s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0061s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_for_owasp_top_10_group_level_reports"})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20240703142511 AddIndexOwaspTop10ForGroupLevelReports: reverted (0.0289s) ==

main: == [advisory_lock_connection] object_id: 128440, pg_backend_pid: 27369

Query to be executed from UI to group.vulnerabilitySeveritiesCount GraphQL API:

SELECT
  COUNT(*) AS "count_all",
  "vulnerability_reads"."severity" AS "vulnerability_reads_severity"
FROM
  (
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 1
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 2
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 4
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 5
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 6
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."archived" = FALSE
        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
        AND "vulnerability_reads"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 7
      LIMIT
        1001
    )
  ) vulnerability_reads
GROUP BY
  "vulnerability_reads"."severity"
ORDER BY
  "vulnerability_reads"."severity" DESC

Query to be executed from UI to group.vulnerabilities GraphQL API:

SELECT
  "vulnerability_reads".*
FROM
  "vulnerability_reads"
WHERE
  "vulnerability_reads"."traversal_ids" >= '{9970}'
  AND "vulnerability_reads"."traversal_ids" < '{9971}'
  AND "vulnerability_reads"."archived" = FALSE
  AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
  AND "vulnerability_reads"."severity" IN (1, 2, 4, 5, 6, 7)
  AND "vulnerability_reads"."state" IN (1, 4)
  AND "vulnerability_reads"."resolved_on_default_branch" = FALSE
  AND "vulnerability_reads"."owasp_top_10" = 1
ORDER BY
  "vulnerability_reads"."severity" DESC,
  "vulnerability_reads"."traversal_ids" DESC,
  "vulnerability_reads"."vulnerability_id" DESC
LIMIT
  101

Without Index

1st query:

 Aggregate  (cost=329271.74..329289.77 rows=200 width=10) (actual time=40575.881..40575.945 rows=4 loops=1)
   Group Key: vulnerability_reads.severity
   Buffers: shared hit=75536 read=49389 dirtied=5404
   I/O Timings: read=32967.475 write=0.000
   ->  Sort  (cost=329271.74..329277.09 rows=2137 width=2) (actual time=40575.846..40575.891 rows=413 loops=1)
         Sort Key: vulnerability_reads.severity DESC
         Sort Method: quicksort  Memory: 44kB
         Buffers: shared hit=75536 read=49389 dirtied=5404
         I/O Timings: read=32967.475 write=0.000
         ->  Subquery Scan on vulnerability_reads  (cost=329110.81..329153.55 rows=2137 width=2) (actual time=40575.645..40575.772 rows=413 loops=1)
               Buffers: shared hit=75533 read=49389 dirtied=5404
               I/O Timings: read=32967.475 write=0.000
               ->  HashAggregate  (cost=329110.81..329132.18 rows=2137 width=10) (actual time=40575.639..40575.732 rows=413 loops=1)
                     Group Key: vulnerability_reads_1.id, vulnerability_reads_1.severity
                     Buffers: shared hit=75533 read=49389 dirtied=5404
                     I/O Timings: read=32967.475 write=0.000
                     ->  Append  (cost=0.69..329100.13 rows=2137 width=10) (actual time=129.388..40574.795 rows=413 loops=1)
                           Buffers: shared hit=75533 read=49389 dirtied=5404
                           I/O Timings: read=32967.475 write=0.000
                           ->  Limit  (cost=0.69..28126.97 rows=181 width=10) (actual time=129.387..2166.716 rows=38 loops=1)
                                 Buffers: shared hit=376 read=2378 dirtied=264
                                 I/O Timings: read=2123.666 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_1  (cost=0.69..28126.97 rows=181 width=10) (actual time=129.385..2166.692 rows=38 loops=1)
                                       Index Cond: ((vulnerability_reads_1.resolved_on_default_branch = false) AND (vulnerability_reads_1.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_1.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_1.severity = 1) AND (vulnerability_reads_1.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_1.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_1.owasp_top_10 = 1)
                                       Rows Removed by Filter: 4113
                                       Buffers: shared hit=376 read=2378 dirtied=264
                                       I/O Timings: read=2123.666 write=0.000
                           ->  Limit  (cost=0.69..12530.33 rows=80 width=10) (actual time=4094.043..4094.045 rows=0 loops=1)
                                 Buffers: shared hit=376 read=4713 dirtied=474
                                 I/O Timings: read=4018.962 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_2  (cost=0.69..12530.33 rows=80 width=10) (actual time=4094.040..4094.041 rows=0 loops=1)
                                       Index Cond: ((vulnerability_reads_2.resolved_on_default_branch = false) AND (vulnerability_reads_2.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_2.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_2.severity = 2) AND (vulnerability_reads_2.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_2.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_2.owasp_top_10 = 1)
                                       Rows Removed by Filter: 7703
                                       Buffers: shared hit=376 read=4713 dirtied=474
                                       I/O Timings: read=4018.962 write=0.000
                           ->  Limit  (cost=0.69..84798.47 rows=552 width=10) (actual time=6136.207..20732.006 rows=42 loops=1)
                                 Buffers: shared hit=18206 read=22848 dirtied=2637
                                 I/O Timings: read=14630.773 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_3  (cost=0.69..84798.47 rows=552 width=10) (actual time=6136.194..20731.939 rows=42 loops=1)
                                       Index Cond: ((vulnerability_reads_3.resolved_on_default_branch = false) AND (vulnerability_reads_3.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_3.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_3.severity = 4) AND (vulnerability_reads_3.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_3.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_3.owasp_top_10 = 1)
                                       Rows Removed by Filter: 98277
                                       Buffers: shared hit=18206 read=22848 dirtied=2637
                                       I/O Timings: read=14630.773 write=0.000
                           ->  Limit  (cost=0.69..108027.67 rows=706 width=10) (actual time=3.220..8431.843 rows=151 loops=1)
                                 Buffers: shared hit=22016 read=13077 dirtied=1761
                                 I/O Timings: read=8259.540 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_4  (cost=0.69..108027.67 rows=706 width=10) (actual time=3.210..8431.812 rows=151 loops=1)
                                       Index Cond: ((vulnerability_reads_4.resolved_on_default_branch = false) AND (vulnerability_reads_4.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_4.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_4.severity = 5) AND (vulnerability_reads_4.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_4.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_4.owasp_top_10 = 1)
                                       Rows Removed by Filter: 57226
                                       Buffers: shared hit=22016 read=13077 dirtied=1761
                                       I/O Timings: read=8259.540 write=0.000
                           ->  Limit  (cost=0.69..37656.29 rows=243 width=10) (actual time=4039.659..4039.659 rows=0 loops=1)
                                 Buffers: shared hit=21540 read=5380 dirtied=264
                                 I/O Timings: read=3329.646 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_5  (cost=0.69..37656.29 rows=243 width=10) (actual time=4039.637..4039.638 rows=0 loops=1)
                                       Index Cond: ((vulnerability_reads_5.resolved_on_default_branch = false) AND (vulnerability_reads_5.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_5.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_5.severity = 6) AND (vulnerability_reads_5.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_5.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_5.owasp_top_10 = 1)
                                       Rows Removed by Filter: 38895
                                       Buffers: shared hit=21540 read=5380 dirtied=264
                                       I/O Timings: read=3329.646 write=0.000
                           ->  Limit  (cost=0.69..57928.34 rows=375 width=10) (actual time=1.571..1110.309 rows=182 loops=1)
                                 Buffers: shared hit=13019 read=993 dirtied=4
                                 I/O Timings: read=604.888 write=0.000
                                 ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads vulnerability_reads_6  (cost=0.69..57928.34 rows=375 width=10) (actual time=1.553..1110.271 rows=182 loops=1)
                                       Index Cond: ((vulnerability_reads_6.resolved_on_default_branch = false) AND (vulnerability_reads_6.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_6.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_6.severity = 7) AND (vulnerability_reads_6.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_6.traversal_ids < '{9971}'::bigint[]))
                                       Filter: (vulnerability_reads_6.owasp_top_10 = 1)
                                       Rows Removed by Filter: 25924
                                       Buffers: shared hit=13019 read=993 dirtied=4
                                       I/O Timings: read=604.888 write=0.000

Time: 40.583 s
  - planning: 6.438 ms
  - execution: 40.576 s
    - I/O read: 32.967 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 75536 (~590.10 MiB) from the buffer pool
  - reads: 49389 (~385.90 MiB) from the OS file cache, including disk I/O
  - dirtied: 5404 (~42.20 MiB)
  - writes: 0

DB lab: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29680/commands/92131

2nd query:

 Limit  (cost=219044.82..219045.08 rows=101 width=232) (actual time=32442.054..32442.067 rows=101 loops=1)
   Buffers: shared hit=87667 read=42901 dirtied=16562
   I/O Timings: read=26246.902 write=0.000
   ->  Sort  (cost=219044.82..219048.46 rows=1454 width=232) (actual time=32442.051..32442.057 rows=101 loops=1)
         Sort Key: vulnerability_reads.severity DESC, vulnerability_reads.traversal_ids DESC, vulnerability_reads.vulnerability_id DESC
         Sort Method: top-N heapsort  Memory: 78kB
         Buffers: shared hit=87667 read=42901 dirtied=16562
         I/O Timings: read=26246.902 write=0.000
         ->  Index Scan using index_vulnerability_reads_common_attrs_and_detection_for_groups on public.vulnerability_reads  (cost=0.69..218989.15 rows=1454 width=232) (actual time=0.222..32439.010 rows=413 loops=1)
               Index Cond: ((vulnerability_reads.resolved_on_default_branch = false) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (vulnerability_reads.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads.traversal_ids < '{9971}'::bigint[]))
               Filter: (vulnerability_reads.owasp_top_10 = 1)
               Rows Removed by Filter: 232138
               Buffers: shared hit=87656 read=42901 dirtied=16562
               I/O Timings: read=26246.902 write=0.000

Time: 32.446 s
  - planning: 4.219 ms
  - execution: 32.442 s
    - I/O read: 26.247 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 87667 (~684.90 MiB) from the buffer pool
  - reads: 42901 (~335.20 MiB) from the OS file cache, including disk I/O
  - dirtied: 16562 (~129.40 MiB)
  - writes: 0

DB lab: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29680/commands/92132

With Index

CREATE INDEX index_for_owasp_top_10_group_level_reports ON vulnerability_reads USING btree (owasp_top_10, state, report_type, severity, traversal_ids, vulnerability_id, resolved_on_default_branch) WHERE (archived = false);

1st query:

  Sort  (cost=3268.20..3268.70 rows=200 width=10) (actual time=121.523..121.530 rows=3 loops=1)
   Sort Key: vulnerability_reads.severity DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=470 read=93
   I/O Timings: read=119.493 write=0.000
   ->  HashAggregate  (cost=3258.55..3260.55 rows=200 width=10) (actual time=121.491..121.497 rows=3 loops=1)
         Group Key: vulnerability_reads.severity
         Buffers: shared hit=467 read=93
         I/O Timings: read=119.493 write=0.000
         ->  HashAggregate  (cost=3204.95..3226.39 rows=2144 width=10) (actual time=121.446..121.478 rows=76 loops=1)
               Group Key: vulnerability_reads.id, vulnerability_reads.severity
               Buffers: shared hit=467 read=93
               I/O Timings: read=119.493 write=0.000
               ->  Append  (cost=0.69..3194.23 rows=2144 width=10) (actual time=17.883..121.197 rows=76 loops=1)
                     Buffers: shared hit=467 read=93
                     I/O Timings: read=119.493 write=0.000
                     ->  Limit  (cost=0.69..274.65 rows=177 width=10) (actual time=12.983..12.984 rows=0 loops=1)
                           Buffers: shared hit=74 read=13
                           I/O Timings: read=12.756 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads  (cost=0.69..274.65 rows=177 width=10) (actual time=12.981..12.981 rows=0 loops=1)
                                 Index Cond: ((vulnerability_reads.owasp_top_10 = 1) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads.severity = 1) AND (vulnerability_reads.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads.resolved_on_default_branch = false))
                                 Buffers: shared hit=74 read=13
                                 I/O Timings: read=12.756 write=0.000
                     ->  Limit  (cost=0.69..144.85 rows=81 width=10) (actual time=2.490..2.490 rows=0 loops=1)
                           Buffers: shared hit=78 read=2
                           I/O Timings: read=2.420 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads vulnerability_reads_1  (cost=0.69..144.85 rows=81 width=10) (actual time=2.487..2.487 rows=0 loops=1)
                                 Index Cond: ((vulnerability_reads_1.owasp_top_10 = 1) AND (vulnerability_reads_1.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_1.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_1.severity = 2) AND (vulnerability_reads_1.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_1.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads_1.resolved_on_default_branch = false))
                                 Buffers: shared hit=78 read=2
                                 I/O Timings: read=2.420 write=0.000
                     ->  Limit  (cost=0.69..808.15 rows=553 width=10) (actual time=2.405..43.567 rows=42 loops=1)
                           Buffers: shared hit=80 read=36
                           I/O Timings: read=43.122 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads vulnerability_reads_2  (cost=0.69..808.15 rows=553 width=10) (actual time=2.403..43.543 rows=42 loops=1)
                                 Index Cond: ((vulnerability_reads_2.owasp_top_10 = 1) AND (vulnerability_reads_2.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_2.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_2.severity = 4) AND (vulnerability_reads_2.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_2.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads_2.resolved_on_default_branch = false))
                                 Buffers: shared hit=80 read=36
                                 I/O Timings: read=43.122 write=0.000
                     ->  Limit  (cost=0.69..1022.11 rows=711 width=10) (actual time=4.129..53.398 rows=32 loops=1)
                           Buffers: shared hit=79 read=34
                           I/O Timings: read=52.771 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads vulnerability_reads_3  (cost=0.69..1022.11 rows=711 width=10) (actual time=4.127..53.374 rows=32 loops=1)
                                 Index Cond: ((vulnerability_reads_3.owasp_top_10 = 1) AND (vulnerability_reads_3.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_3.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_3.severity = 5) AND (vulnerability_reads_3.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_3.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads_3.resolved_on_default_branch = false))
                                 Buffers: shared hit=79 read=34
                                 I/O Timings: read=52.771 write=0.000
                     ->  Limit  (cost=0.69..372.32 rows=249 width=10) (actual time=2.967..2.968 rows=0 loops=1)
                           Buffers: shared hit=77 read=3
                           I/O Timings: read=2.844 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads vulnerability_reads_4  (cost=0.69..372.32 rows=249 width=10) (actual time=2.965..2.965 rows=0 loops=1)
                                 Index Cond: ((vulnerability_reads_4.owasp_top_10 = 1) AND (vulnerability_reads_4.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_4.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_4.severity = 6) AND (vulnerability_reads_4.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_4.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads_4.resolved_on_default_branch = false))
                                 Buffers: shared hit=77 read=3
                                 I/O Timings: read=2.844 write=0.000
                     ->  Limit  (cost=0.69..540.00 rows=373 width=10) (actual time=2.715..5.733 rows=2 loops=1)
                           Buffers: shared hit=79 read=5
                           I/O Timings: read=5.581 write=0.000
                           ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads vulnerability_reads_5  (cost=0.69..540.00 rows=373 width=10) (actual time=2.713..5.729 rows=2 loops=1)
                                 Index Cond: ((vulnerability_reads_5.owasp_top_10 = 1) AND (vulnerability_reads_5.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads_5.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads_5.severity = 7) AND (vulnerability_reads_5.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads_5.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads_5.resolved_on_default_branch = false))
                                 Buffers: shared hit=79 read=5
                                 I/O Timings: read=5.581 write=0.000

Time: 128.653 ms
  - planning: 6.792 ms
  - execution: 121.861 ms
    - I/O read: 119.493 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 470 (~3.70 MiB) from the buffer pool
  - reads: 93 (~744.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

DB Lab: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29960/commands/93085

2nd query: (this is with warm cache, have a DM with `@ghavenga` to populate this later for cold cache as my DB lab setup is persisted to work on another issue and performing `reset` clears the entire setup)

 Limit  (cost=2242.28..2242.54 rows=101 width=249) (actual time=0.973..0.982 rows=76 loops=1)
   Buffers: shared hit=571
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=2242.28..2245.93 rows=1459 width=249) (actual time=0.971..0.975 rows=76 loops=1)
         Sort Key: vulnerability_reads.severity DESC, vulnerability_reads.traversal_ids DESC, vulnerability_reads.vulnerability_id DESC
         Sort Method: quicksort  Memory: 45kB
         Buffers: shared hit=571
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_for_owasp_top_10_group_level_reports on public.vulnerability_reads  (cost=0.69..2186.42 rows=1459 width=249) (actual time=0.094..0.674 rows=76 loops=1)
               Index Cond: ((vulnerability_reads.owasp_top_10 = 1) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND (vulnerability_reads.report_type = ANY ('{6,2,5,3,1,0,4,99}'::integer[])) AND (vulnerability_reads.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (vulnerability_reads.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_reads.traversal_ids < '{9971}'::bigint[]) AND (vulnerability_reads.resolved_on_default_branch = false))
               Buffers: shared hit=560
               I/O Timings: read=0.000 write=0.000

Time: 5.216 ms
  - planning: 4.146 ms
  - execution: 1.070 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 571 (~4.50 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

DB Lab: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29960/commands/93086

Related to #458454

Edited Jul 17, 2024 by Bala Kumar

Owasp top 10 index for the group level vulnerability report page

What does this MR do and why?

Database

Without Index

With Index

Merge request reports