Skip to content

Compare results for scan_finding approval rules from multiple pipelines

Sashi Kumar Kumaresan requested to merge sk/379108-add-multi-pipeline-compare into master

What does this MR do and why?

It might be possible that the pipeline for target branch sha or source branch sha contains detached pipelines that do not include security jobs. Because of this we might incorrectly require approvals while they are not needed or not require approvals while they needed. With this change we want to change this behavior to get reports from all pipelines related to given source/target commit.

Let's take this scenario where MR is created between a source and target branch:

gitGraph
  commit id: "SHA 11"
  commit id: "SHA 12"
  branch "source branch"
  commit id: "SHA 21"
  commit id: "SHA 22"
  commit id: "SHA 23"
  checkout main
  commit id: "SHA 13"
  commit id: "SHA 14"

And if this is the pipelines view for each commits:

Untitled.drawio.svg

The comparison logic is triggered following the sequence:

  1. After any pipeline is complete we find the MR that has this pipeline as the MRs head pipeline. In this case Pipeline 23a
  2. For the selected MR, we take the target branch's latest completed pipeline. In this case its Pipeline 13c, note that we are not picking Pipeline 14 as it is still running.
  3. For selecting target branch's related pipelines, we take the SHA of the selected target pipeline(SHA 13) and select the latest completed pipelines per source that got executed for that particular SHA. In this case the pipelines would be Pipeline 13b and Pipeline 13c
  4. For selecting the source branch's related pipelines, we take the SHA of the the MR's head pipeline(Pipeline 23c) (if the pipeline is merged_result_pipeline we take the source_sha to make sure we consider both the merged result pipeline and the pipelines with matching SHA) and select completed pipelines per source for the particular SHA (SHA 23). In this case it would be Pipeline 23a, Pipeline 23b and Pipeline 23c
  5. Query the security_findings for those selected pipelines and compare based on the rules configured in scan result policies

Database Query Plan

Query 1

SELECT
    "security_findings".* 
FROM
    "security_findings" 
INNER JOIN
    "security_scans" 
        ON "security_scans"."id" = "security_findings"."scan_id" 
WHERE
    "security_scans"."status" = 1 
    AND "security_scans"."project_id" = 45447096 
    AND "security_scans"."pipeline_id" IN (
        875841179, 847069768
    )

Plan

Full Plan 
 Nested Loop  (cost=1.13..23430.38 rows=21 width=1148) (actual time=91.303..112.324 rows=1575 loops=1)
   Buffers: shared hit=126 read=407
   I/O Timings: read=109.981 write=0.000
   ->  Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans  (cost=0.56..14.16 rows=1 width=8) (actual time=74.023..74.107 rows=2 loops=1)
         Index Cond: (security_scans.pipeline_id = ANY ('{875841179,847069768}'::bigint[]))
         Filter: ((security_scans.status = 1) AND (security_scans.project_id = 45447096))
         Rows Removed by Filter: 6
         Buffers: shared hit=4 read=15
         I/O Timings: read=73.887 write=0.000
   ->  Append  (cost=0.57..23191.39 rows=22483 width=1148) (actual time=8.607..18.887 rows=788 loops=2)
         Buffers: shared hit=122 read=392
         I/O Timings: read=36.094 write=0.000
         ->  Index Scan using security_findings_50_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_50  (cost=0.57..1501.13 rows=1481 width=1131) (actual time=0.259..0.260 rows=0 loops=2)
               Index Cond: (security_findings_50.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.481 write=0.000
         ->  Index Scan using security_findings_51_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_51  (cost=0.57..1478.73 rows=1458 width=1119) (actual time=0.258..0.259 rows=0 loops=2)
               Index Cond: (security_findings_51.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.481 write=0.000
         ->  Index Scan using security_findings_52_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_52  (cost=0.57..1619.95 rows=1597 width=1113) (actual time=1.825..1.825 rows=0 loops=2)
               Index Cond: (security_findings_52.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=3.612 write=0.000
         ->  Index Scan using security_findings_53_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_53  (cost=0.57..1845.76 rows=1782 width=1168) (actual time=0.254..0.254 rows=0 loops=2)
               Index Cond: (security_findings_53.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.465 write=0.000
         ->  Index Scan using security_findings_54_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_54  (cost=0.57..1736.17 rows=1672 width=1200) (actual time=0.220..0.220 rows=0 loops=2)
               Index Cond: (security_findings_54.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.408 write=0.000
         ->  Index Scan using security_findings_55_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_55  (cost=0.57..1922.04 rows=1828 width=1226) (actual time=0.214..0.214 rows=0 loops=2)
               Index Cond: (security_findings_55.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.400 write=0.000
         ->  Index Scan using security_findings_56_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_56  (cost=0.57..2141.22 rows=2043 width=1215) (actual time=0.208..0.208 rows=0 loops=2)
               Index Cond: (security_findings_56.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.387 write=0.000
         ->  Index Scan using security_findings_57_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_57  (cost=0.57..1969.00 rows=1883 width=1221) (actual time=0.195..0.195 rows=0 loops=2)
               Index Cond: (security_findings_57.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.364 write=0.000
         ->  Index Scan using security_findings_58_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_58  (cost=0.57..1547.90 rows=1496 width=1147) (actual time=0.162..0.162 rows=0 loops=2)
               Index Cond: (security_findings_58.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.296 write=0.000
         ->  Index Scan using security_findings_59_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_59  (cost=0.57..1416.13 rows=1401 width=1107) (actual time=0.218..0.218 rows=0 loops=2)
               Index Cond: (security_findings_59.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.405 write=0.000
         ->  Index Scan using security_findings_60_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_60  (cost=0.57..1491.69 rows=1469 width=1118) (actual time=0.232..0.232 rows=0 loops=2)
               Index Cond: (security_findings_60.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.431 write=0.000
         ->  Index Scan using security_findings_61_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_61  (cost=0.57..1503.59 rows=1483 width=1126) (actual time=4.360..14.551 rows=788 loops=2)
               Index Cond: (security_findings_61.scan_id = security_scans.id)
               Buffers: shared hit=70 read=340
               I/O Timings: read=28.046 write=0.000
         ->  Index Scan using security_findings_62_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_62  (cost=0.57..1440.00 rows=1424 width=1110) (actual time=0.098..0.098 rows=0 loops=2)
               Index Cond: (security_findings_62.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.158 write=0.000
         ->  Index Scan using security_findings_63_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_63  (cost=0.57..1465.66 rows=1466 width=1072) (actual time=0.092..0.092 rows=0 loops=2)
               Index Cond: (security_findings_63.scan_id = security_scans.id)
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.161 write=0.000

Time: 145.506 ms
  - planning: 32.811 ms
  - execution: 112.695 ms
    - I/O read: 109.981 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 126 (~1008.00 KiB) from the buffer pool
  - reads: 407 (~3.20 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Query 2 (with dismissed state)

SELECT
    "security_findings".* 
FROM
    "security_findings" 
INNER JOIN
    "security_scans" 
        ON "security_scans"."id" = "security_findings"."scan_id" 
WHERE
    "security_scans"."status" = 1 
    AND "security_scans"."project_id" = 45447096 
    AND "security_scans"."pipeline_id" IN (
        875841179, 847069768
    ) 
    AND "security_scans"."scan_type" IN (
        1, 2, 3, 4, 5, 6, 7
    )
    AND "security_findings"."severity" IN (1, 2, 4, 5, 6, 7)
    AND (
        NOT EXISTS (
            SELECT
                1 
            FROM
                "vulnerabilities" 
            INNER JOIN
                "vulnerability_occurrences" 
                    ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id" 
            WHERE
                "vulnerabilities"."state" = 2 
                AND (
                    vulnerability_occurrences.uuid = security_findings.uuid::text
                )
        )
    )

Plan

Full Plan 
 Nested Loop Anti Join  (cost=2.27..25203.58 rows=18 width=1141) (actual time=559.890..3444.500 rows=1575 loops=1)
   Buffers: shared hit=10357 read=5934 dirtied=217
   I/O Timings: read=3216.328 write=0.000
   ->  Nested Loop  (cost=1.13..25100.64 rows=19 width=1141) (actual time=529.149..606.248 rows=1575 loops=1)
         Buffers: shared hit=130 read=411
         I/O Timings: read=582.477 write=0.000
         ->  Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans  (cost=0.56..15.01 rows=1 width=8) (actual time=77.495..83.622 rows=2 loops=1)
               Index Cond: (security_scans.pipeline_id = ANY ('{875841179,847069768}'::bigint[]))
               Filter: ((security_scans.status = 1) AND (security_scans.project_id = 45447096) AND (security_scans.scan_type = ANY ('{1,2,3,4,5,6,7}'::integer[])))
               Rows Removed by Filter: 6
               Buffers: shared hit=4 read=15
               I/O Timings: read=80.214 write=0.000
         ->  Append  (cost=0.57..24878.78 rows=20686 width=1141) (actual time=224.636..260.693 rows=788 loops=2)
               Buffers: shared hit=126 read=396
               I/O Timings: read=502.263 write=0.000
               ->  Index Scan using security_findings_51_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_51  (cost=0.57..1488.98 rows=1457 width=1119) (actual time=23.896..23.896 rows=0 loops=2)
                     Index Cond: (security_findings_51.scan_id = security_scans.id)
                     Filter: (security_findings_51.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=47.678 write=0.000
               ->  Index Scan using security_findings_52_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_52  (cost=0.57..1631.93 rows=1597 width=1113) (actual time=16.593..16.593 rows=0 loops=2)
                     Index Cond: (security_findings_52.scan_id = security_scans.id)
                     Filter: (security_findings_52.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=33.075 write=0.000
               ->  Index Scan using security_findings_53_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_53  (cost=0.57..1858.43 rows=1267 width=1168) (actual time=13.873..13.873 rows=0 loops=2)
                     Index Cond: (security_findings_53.scan_id = security_scans.id)
                     Filter: (security_findings_53.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=27.620 write=0.000
               ->  Index Scan using security_findings_54_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_54  (cost=0.57..1748.01 rows=1212 width=1200) (actual time=20.928..20.928 rows=0 loops=2)
                     Index Cond: (security_findings_54.scan_id = security_scans.id)
                     Filter: (security_findings_54.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=41.754 write=0.000
               ->  Index Scan using security_findings_55_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_55  (cost=0.57..1935.04 rows=1827 width=1226) (actual time=22.713..22.713 rows=0 loops=2)
                     Index Cond: (security_findings_55.scan_id = security_scans.id)
                     Filter: (security_findings_55.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=36.652 write=0.000
               ->  Index Scan using security_findings_56_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_56  (cost=0.57..2156.54 rows=2043 width=1215) (actual time=11.297..11.297 rows=0 loops=2)
                     Index Cond: (security_findings_56.scan_id = security_scans.id)
                     Filter: (security_findings_56.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=22.488 write=0.000
               ->  Index Scan using security_findings_57_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_57  (cost=0.57..1982.43 rows=1391 width=1221) (actual time=13.368..13.368 rows=0 loops=2)
                     Index Cond: (security_findings_57.scan_id = security_scans.id)
                     Filter: (security_findings_57.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=26.587 write=0.000
               ->  Index Scan using security_findings_58_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_58  (cost=0.57..1559.12 rows=1035 width=1147) (actual time=11.649..11.649 rows=0 loops=2)
                     Index Cond: (security_findings_58.scan_id = security_scans.id)
                     Filter: (security_findings_58.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=23.191 write=0.000
               ->  Index Scan using security_findings_59_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_59  (cost=0.57..1426.64 rows=1401 width=1107) (actual time=16.399..16.399 rows=0 loops=2)
                     Index Cond: (security_findings_59.scan_id = security_scans.id)
                     Filter: (security_findings_59.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=32.674 write=0.000
               ->  Index Scan using security_findings_60_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_60  (cost=0.57..1502.70 rows=1469 width=1118) (actual time=14.155..14.155 rows=0 loops=2)
                     Index Cond: (security_findings_60.scan_id = security_scans.id)
                     Filter: (security_findings_60.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=28.191 write=0.000
               ->  Index Scan using security_findings_61_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_61  (cost=0.57..1514.02 rows=1026 width=1126) (actual time=21.937..57.697 rows=788 loops=2)
                     Index Cond: (security_findings_61.scan_id = security_scans.id)
                     Filter: (security_findings_61.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=70 read=340
                     I/O Timings: read=107.266 write=0.000
               ->  Index Scan using security_findings_62_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_62  (cost=0.57..1450.68 rows=984 width=1110) (actual time=5.142..5.142 rows=0 loops=2)
                     Index Cond: (security_findings_62.scan_id = security_scans.id)
                     Filter: (security_findings_62.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=10.145 write=0.000
               ->  Index Scan using security_findings_63_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_63  (cost=0.57..1610.20 rows=1096 width=1071) (actual time=12.379..12.379 rows=0 loops=2)
                     Index Cond: (security_findings_63.scan_id = security_scans.id)
                     Filter: (security_findings_63.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=24.636 write=0.000
               ->  Index Scan using security_findings_64_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_64  (cost=0.57..1740.15 rows=1733 width=1081) (actual time=10.112..10.112 rows=0 loops=2)
                     Index Cond: (security_findings_64.scan_id = security_scans.id)
                     Filter: (security_findings_64.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=20.110 write=0.000
               ->  Index Scan using security_findings_65_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_65  (cost=0.56..1170.48 rows=1148 width=1111) (actual time=10.153..10.153 rows=0 loops=2)
                     Index Cond: (security_findings_65.scan_id = security_scans.id)
                     Filter: (security_findings_65.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4 read=4
                     I/O Timings: read=20.196 write=0.000
   ->  Nested Loop  (cost=1.14..5.41 rows=1 width=37) (actual time=1.800..1.800 rows=0 loops=1575)
         Buffers: shared hit=10227 read=5523 dirtied=217
         I/O Timings: read=2633.851 write=0.000
         ->  Index Scan using index_vulnerability_occurrences_on_uuid on public.vulnerability_occurrences  (cost=0.57..2.71 rows=1 width=45) (actual time=1.509..1.509 rows=1 loops=1575)
               Index Cond: ((vulnerability_occurrences.uuid)::text = (security_findings_51.uuid)::text)
               Buffers: shared hit=3575 read=4300 dirtied=10
               I/O Timings: read=2282.152 write=0.000
         ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.57..2.70 rows=1 width=8) (actual time=0.285..0.285 rows=0 loops=1575)
               Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
               Filter: (vulnerabilities.state = 2)
               Rows Removed by Filter: 1
               Buffers: shared hit=6652 read=1223 dirtied=207
               I/O Timings: read=351.700 write=0.000

Time: 3.496 s
  - planning: 48.191 ms
  - execution: 3.448 s
    - I/O read: 3.216 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 10357 (~80.90 MiB) from the buffer pool
  - reads: 5934 (~46.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 217 (~1.70 MiB)
  - writes: 0

Query 3 (without dismissed state)

EXPLAIN SELECT
    "security_findings".* 
FROM
    "security_findings" 
INNER JOIN
    "security_scans" 
        ON "security_scans"."id" = "security_findings"."scan_id" 
WHERE
    "security_scans"."status" = 1 
    AND "security_scans"."project_id" = 45447096 
    AND "security_scans"."pipeline_id" IN (
        875841179, 847069768
    ) 
    AND "security_scans"."scan_type" IN (
        1, 2, 3, 4, 5, 6, 7
    )
    AND "security_findings"."severity" IN (1, 2, 4, 5, 6, 7)

Plan

Full Plan 
 Nested Loop  (cost=1.13..25100.64 rows=19 width=1141) (actual time=154.030..204.057 rows=1575 loops=1)
   Buffers: shared hit=130 read=411
   I/O Timings: read=199.375 write=0.000
   ->  Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans  (cost=0.56..15.01 rows=1 width=8) (actual time=4.512..4.622 rows=2 loops=1)
         Index Cond: (security_scans.pipeline_id = ANY ('{875841179,847069768}'::bigint[]))
         Filter: ((security_scans.status = 1) AND (security_scans.project_id = 45447096) AND (security_scans.scan_type = ANY ('{1,2,3,4,5,6,7}'::integer[])))
         Rows Removed by Filter: 6
         Buffers: shared hit=4 read=15
         I/O Timings: read=4.428 write=0.000
   ->  Append  (cost=0.57..24878.78 rows=20686 width=1141) (actual time=74.716..99.493 rows=788 loops=2)
         Buffers: shared hit=126 read=396
         I/O Timings: read=194.947 write=0.000
         ->  Index Scan using security_findings_51_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_51  (cost=0.57..1488.98 rows=1457 width=1119) (actual time=7.376..7.377 rows=0 loops=2)
               Index Cond: (security_findings_51.scan_id = security_scans.id)
               Filter: (security_findings_51.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=14.665 write=0.000
         ->  Index Scan using security_findings_52_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_52  (cost=0.57..1631.93 rows=1597 width=1113) (actual time=13.854..13.855 rows=0 loops=2)
               Index Cond: (security_findings_52.scan_id = security_scans.id)
               Filter: (security_findings_52.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=27.587 write=0.000
         ->  Index Scan using security_findings_53_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_53  (cost=0.57..1858.43 rows=1267 width=1168) (actual time=4.482..4.482 rows=0 loops=2)
               Index Cond: (security_findings_53.scan_id = security_scans.id)
               Filter: (security_findings_53.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=8.876 write=0.000
         ->  Index Scan using security_findings_54_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_54  (cost=0.57..1748.01 rows=1212 width=1200) (actual time=2.969..2.969 rows=0 loops=2)
               Index Cond: (security_findings_54.scan_id = security_scans.id)
               Filter: (security_findings_54.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=5.854 write=0.000
         ->  Index Scan using security_findings_55_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_55  (cost=0.57..1935.04 rows=1827 width=1226) (actual time=9.094..9.094 rows=0 loops=2)
               Index Cond: (security_findings_55.scan_id = security_scans.id)
               Filter: (security_findings_55.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=18.039 write=0.000
         ->  Index Scan using security_findings_56_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_56  (cost=0.57..2156.54 rows=2043 width=1215) (actual time=5.010..5.011 rows=0 loops=2)
               Index Cond: (security_findings_56.scan_id = security_scans.id)
               Filter: (security_findings_56.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=9.930 write=0.000
         ->  Index Scan using security_findings_57_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_57  (cost=0.57..1982.43 rows=1391 width=1221) (actual time=4.816..4.816 rows=0 loops=2)
               Index Cond: (security_findings_57.scan_id = security_scans.id)
               Filter: (security_findings_57.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=9.556 write=0.000
         ->  Index Scan using security_findings_58_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_58  (cost=0.57..1559.12 rows=1035 width=1147) (actual time=2.261..2.261 rows=0 loops=2)
               Index Cond: (security_findings_58.scan_id = security_scans.id)
               Filter: (security_findings_58.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=4.436 write=0.000
         ->  Index Scan using security_findings_59_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_59  (cost=0.57..1426.64 rows=1401 width=1107) (actual time=6.097..6.097 rows=0 loops=2)
               Index Cond: (security_findings_59.scan_id = security_scans.id)
               Filter: (security_findings_59.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=12.124 write=0.000
         ->  Index Scan using security_findings_60_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_60  (cost=0.57..1502.70 rows=1469 width=1118) (actual time=2.922..2.922 rows=0 loops=2)
               Index Cond: (security_findings_60.scan_id = security_scans.id)
               Filter: (security_findings_60.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=5.764 write=0.000
         ->  Index Scan using security_findings_61_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_61  (cost=0.57..1514.02 rows=1026 width=1126) (actual time=9.073..33.732 rows=788 loops=2)
               Index Cond: (security_findings_61.scan_id = security_scans.id)
               Filter: (security_findings_61.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=70 read=340
               I/O Timings: read=64.968 write=0.000
         ->  Index Scan using security_findings_62_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_62  (cost=0.57..1450.68 rows=984 width=1110) (actual time=1.442..1.442 rows=0 loops=2)
               Index Cond: (security_findings_62.scan_id = security_scans.id)
               Filter: (security_findings_62.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=2.807 write=0.000
         ->  Index Scan using security_findings_63_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_63  (cost=0.57..1610.20 rows=1096 width=1071) (actual time=3.550..3.550 rows=0 loops=2)
               Index Cond: (security_findings_63.scan_id = security_scans.id)
               Filter: (security_findings_63.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=7.031 write=0.000
         ->  Index Scan using security_findings_64_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_64  (cost=0.57..1740.15 rows=1733 width=1081) (actual time=1.609..1.609 rows=0 loops=2)
               Index Cond: (security_findings_64.scan_id = security_scans.id)
               Filter: (security_findings_64.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=3.142 write=0.000
         ->  Index Scan using security_findings_65_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_65  (cost=0.56..1170.48 rows=1148 width=1111) (actual time=0.103..0.104 rows=0 loops=2)
               Index Cond: (security_findings_65.scan_id = security_scans.id)
               Filter: (security_findings_65.severity = ANY ('{1,2,4,5,6,7}'::integer[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=4
               I/O Timings: read=0.167 write=0.000

Time: 248.489 ms
  - planning: 44.090 ms
  - execution: 204.399 ms
    - I/O read: 199.375 ms
    - I/O write: 0.000 ms

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

Query 4

SELECT
    DISTINCT "security_scans"."scan_type" 
FROM
    "security_scans" 
WHERE
    "security_scans"."pipeline_id" IN (
        875841179, 847069768
    )

Plan

Full Plan 
 HashAggregate  (cost=5.86..5.92 rows=6 width=2) (actual time=37.352..37.354 rows=4 loops=1)
   Group Key: security_scans.scan_type
   Buffers: shared hit=5 read=10
   I/O Timings: read=36.913 write=0.000
   ->  Index Only Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans  (cost=0.56..5.83 rows=11 width=2) (actual time=24.007..37.324 rows=8 loops=1)
         Index Cond: (security_scans.pipeline_id = ANY ('{875841179,847069768}'::bigint[]))
         Heap Fetches: 0
         Buffers: shared hit=5 read=10
         I/O Timings: read=36.913 write=0.000

Time: 39.142 ms
  - planning: 1.675 ms
  - execution: 37.467 ms
    - I/O read: 36.913 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 10 (~80.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

📹 Demo

link

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Addresses #379108 (closed)

Edited by Sashi Kumar Kumaresan

Merge request reports