Resolve "Performance issues on vulnerability_findings endpoint"
What does this MR do and why?
This MR adds a feature flag to modify the Security::FindingsFinder to use a more efficient LATERAL join between security_scans and security_findings.
This MR will be followed by additional MRs to add indexes to further improve the query, and a modification of the endpoint to use keyset pagination.
For more details and rationale see the parent issue #411666 (closed)
Query before
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/22744/commands/73308
SELECT
"security_findings".*
FROM
"security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
WHERE
"security_scans"."pipeline_id" = 967613589
AND "security_findings"."deduplicated" = TRUE
AND "security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
AND "security_findings"."confidence" IN (1, 2, 3, 4, 5, 6, 7)
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::uuid = security_findings.uuid::uuid)))
ORDER BY
"security_findings"."severity" DESC,
"security_findings"."id" ASC
LIMIT 20 OFFSET 0Limit (cost=9543.26..9543.29 rows=11 width=1139) (actual time=65388.124..65388.167 rows=20 loops=1)
Buffers: shared hit=171804 read=36527 dirtied=609
I/O Timings: read=63591.116 write=0.000
-> Sort (cost=9543.26..9543.29 rows=11 width=1139) (actual time=65388.122..65388.162 rows=20 loops=1)
Sort Key: security_findings.severity DESC, security_findings.id
Sort Method: top-N heapsort Memory: 58kB
Buffers: shared hit=171804 read=36527 dirtied=609
I/O Timings: read=63591.116 write=0.000
-> Nested Loop Anti Join (cost=2.84..9543.07 rows=11 width=1139) (actual time=154.130..65266.001 rows=21846 loops=1)
Buffers: shared hit=171798 read=36527 dirtied=609
I/O Timings: read=63591.116 write=0.000
-> Nested Loop (cost=1.70..9492.93 rows=12 width=1139) (actual time=134.176..1033.944 rows=21846 loops=1)
Buffers: shared hit=5 read=2653
I/O Timings: read=870.061 write=0.000
-> Nested Loop (cost=1.14..11.24 rows=1 width=16) (actual time=32.550..32.587 rows=1 loops=1)
Buffers: shared hit=5 read=8
I/O Timings: read=32.396 write=0.000
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans (cost=0.57..7.65 rows=1 width=8) (actual time=26.553..26.574 rows=1 loops=1)
Index Cond: (security_scans.pipeline_id = 967613589)
Filter: (security_scans.latest AND (security_scans.status = 1) AND (security_scans.scan_type = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=3 read=5
I/O Timings: read=26.459 write=0.000
-> Index Only Scan using security_scans_pkey on public.security_scans scans_security_findings (cost=0.57..3.58 rows=1 width=8) (actual time=5.988..5.990 rows=1 loops=1)
Index Cond: (scans_security_findings.id = security_scans.id)
Heap Fetches: 0
Buffers: shared hit=2 read=3
I/O Timings: read=5.937 write=0.000
-> Append (cost=0.57..9310.98 rows=13657 width=1139) (actual time=101.613..951.192 rows=21846 loops=1)
Buffers: shared read=2645
I/O Timings: read=837.665 write=0.000
-> Index Scan using security_findings_66_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_66 security_findings_1 (cost=0.57..852.25 rows=1058 width=1109) (actual time=13.238..13.238 rows=0 loops=1)
Index Cond: ((security_findings_1.scan_id = scans_security_findings.id) AND (security_findings_1.deduplicated = true))
Filter: ((security_findings_1.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_1.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=13.174 write=0.000
-> Index Scan using security_findings_67_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_67 security_findings_2 (cost=0.57..886.62 rows=1564 width=1113) (actual time=16.848..16.850 rows=0 loops=1)
Index Cond: ((security_findings_2.scan_id = scans_security_findings.id) AND (security_findings_2.deduplicated = true))
Filter: ((security_findings_2.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_2.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=16.769 write=0.000
-> Index Scan using security_findings_68_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_68 security_findings_3 (cost=0.57..767.00 rows=936 width=1129) (actual time=16.182..16.183 rows=0 loops=1)
Index Cond: ((security_findings_3.scan_id = scans_security_findings.id) AND (security_findings_3.deduplicated = true))
Filter: ((security_findings_3.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_3.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=16.109 write=0.000
-> Index Scan using security_findings_69_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_69 security_findings_4 (cost=0.57..732.18 rows=1287 width=1144) (actual time=15.271..15.274 rows=0 loops=1)
Index Cond: ((security_findings_4.scan_id = scans_security_findings.id) AND (security_findings_4.deduplicated = true))
Filter: ((security_findings_4.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_4.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=15.201 write=0.000
-> Index Scan using security_findings_70_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_70 security_findings_5 (cost=0.57..745.56 rows=1314 width=1116) (actual time=14.518..14.519 rows=0 loops=1)
Index Cond: ((security_findings_5.scan_id = scans_security_findings.id) AND (security_findings_5.deduplicated = true))
Filter: ((security_findings_5.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_5.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=14.455 write=0.000
-> Index Scan using security_findings_71_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_71 security_findings_6 (cost=0.57..680.83 rows=1200 width=1106) (actual time=9.070..9.072 rows=0 loops=1)
Index Cond: ((security_findings_6.scan_id = scans_security_findings.id) AND (security_findings_6.deduplicated = true))
Filter: ((security_findings_6.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_6.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=9.003 write=0.000
-> Index Scan using security_findings_72_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_72 security_findings_7 (cost=0.57..668.32 rows=812 width=1115) (actual time=16.436..765.790 rows=21846 loops=1)
Index Cond: ((security_findings_7.scan_id = scans_security_findings.id) AND (security_findings_7.deduplicated = true))
Filter: ((security_findings_7.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_7.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=2597
I/O Timings: read=675.387 write=0.000
-> Index Scan using security_findings_73_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_73 security_findings_8 (cost=0.57..628.60 rows=1109 width=1127) (actual time=13.933..13.933 rows=0 loops=1)
Index Cond: ((security_findings_8.scan_id = scans_security_findings.id) AND (security_findings_8.deduplicated = true))
Filter: ((security_findings_8.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_8.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=13.831 write=0.000
-> Index Scan using security_findings_74_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_74 security_findings_9 (cost=0.57..679.26 rows=826 width=1154) (actual time=14.831..14.831 rows=0 loops=1)
Index Cond: ((security_findings_9.scan_id = scans_security_findings.id) AND (security_findings_9.deduplicated = true))
Filter: ((security_findings_9.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_9.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=14.759 write=0.000
-> Index Scan using security_findings_75_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_75 security_findings_10 (cost=0.57..669.34 rows=816 width=1175) (actual time=11.730..11.730 rows=0 loops=1)
Index Cond: ((security_findings_10.scan_id = scans_security_findings.id) AND (security_findings_10.deduplicated = true))
Filter: ((security_findings_10.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_10.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=11.614 write=0.000
-> Index Scan using security_findings_76_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_76 security_findings_11 (cost=0.57..695.77 rows=854 width=1174) (actual time=10.403..10.403 rows=0 loops=1)
Index Cond: ((security_findings_11.scan_id = scans_security_findings.id) AND (security_findings_11.deduplicated = true))
Filter: ((security_findings_11.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_11.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=10.315 write=0.000
-> Index Scan using security_findings_77_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_77 security_findings_12 (cost=0.57..662.75 rows=1174 width=1190) (actual time=14.693..14.693 rows=0 loops=1)
Index Cond: ((security_findings_12.scan_id = scans_security_findings.id) AND (security_findings_12.deduplicated = true))
Filter: ((security_findings_12.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_12.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=14.583 write=0.000
-> Index Scan using security_findings_78_scan_id_deduplicated_idx on gitlab_partitions_dynamic.security_findings_78 security_findings_13 (cost=0.56..574.21 rows=707 width=1199) (actual time=12.550..12.550 rows=0 loops=1)
Index Cond: ((security_findings_13.scan_id = scans_security_findings.id) AND (security_findings_13.deduplicated = true))
Filter: ((security_findings_13.severity = ANY ('{1,2,4,5,6,7}'::integer[])) AND (security_findings_13.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=12.464 write=0.000
-> Nested Loop (cost=1.14..4.17 rows=1 width=16) (actual time=2.937..2.937 rows=0 loops=21846)
Buffers: shared hit=171793 read=33874 dirtied=609
I/O Timings: read=62721.055 write=0.000
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.60 rows=1 width=24) (actual time=2.072..2.074 rows=1 loops=21846)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 889
Buffers: shared hit=72558 read=23879 dirtied=220
I/O Timings: read=44581.232 write=0.000
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.57 rows=1 width=8) (actual time=0.857..0.857 rows=0 loops=21846)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=99235 read=9995 dirtied=389
I/O Timings: read=18139.823 write=0.000Time: 1.090 min
- planning: 40.549 ms
- execution: 1.090 min
- I/O read: 1.060 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 171804 (~1.30 GiB) from the buffer pool
- reads: 36527 (~285.40 MiB) from the OS file cache, including disk I/O
- dirtied: 609 (~4.80 MiB)
- writes: 0 Query after
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/22950/commands/73996
SELECT
"security_findings".*
FROM
"security_scans",
unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities" ("severity"),
LATERAL (
SELECT
"security_findings".*
FROM
"security_findings"
WHERE ("security_findings"."scan_id" = "security_scans"."id")
AND ("security_findings"."severity" = "severities"."severity")
AND "security_findings"."deduplicated" = TRUE
AND "security_findings"."confidence" IN (1, 2, 3, 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::uuid = security_findings.uuid::uuid)))
ORDER BY
"security_findings"."severity" DESC,
"security_findings"."id" ASC
LIMIT 20) AS "security_findings"
WHERE
"security_scans"."pipeline_id" = 967613589
AND "security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
AND "security_scans"."scan_type" IN (1, 2, 3, 4, 5, 6, 7)
ORDER BY
"security_findings"."severity" DESC,
"security_findings"."id" ASC
LIMIT 20 OFFSET 0Limit (cost=1490.00..1490.05 rows=20 width=1153) (actual time=25.368..25.377 rows=20 loops=1)
Buffers: shared hit=9163
I/O Timings: read=0.000 write=0.000
-> Sort (cost=1490.00..1490.30 rows=120 width=1153) (actual time=25.366..25.373 rows=20 loops=1)
Sort Key: security_findings.severity DESC, security_findings.id
Sort Method: top-N heapsort Memory: 66kB
Buffers: shared hit=9163
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=9.19..1486.81 rows=120 width=1153) (actual time=21.017..25.233 rows=80 loops=1)
Buffers: shared hit=9160
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans (cost=0.57..7.64 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=1)
Index Cond: (security_scans.pipeline_id = 967613589)
Filter: (security_scans.latest AND (security_scans.status = 1) AND (security_scans.scan_type = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=8.62..1477.97 rows=120 width=1153) (actual time=20.989..25.182 rows=80 loops=1)
Buffers: shared hit=9152
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest severities (cost=0.00..0.06 rows=6 width=2) (actual time=0.009..0.016 rows=6 loops=1)
I/O Timings: read=0.000 write=0.000
-> Limit (cost=8.62..245.92 rows=20 width=1153) (actual time=4.019..4.188 rows=13 loops=6)
Buffers: shared hit=9152
I/O Timings: read=0.000 write=0.000
-> Nested Loop Anti Join (cost=8.62..27072.44 rows=2281 width=1153) (actual time=4.018..4.186 rows=13 loops=6)
Buffers: shared hit=9152
I/O Timings: read=0.000 write=0.000
-> Merge Append (cost=7.49..15684.56 rows=2459 width=1153) (actual time=3.994..4.002 rows=13 loops=6)
Sort Key: security_findings.id
Buffers: shared hit=8393
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_68_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_68 security_findings_1 (cost=0.57..1405.98 rows=223 width=1129) (actual time=0.008..0.008 rows=0 loops=6)
Index Cond: (security_findings_1.scan_id = security_scans.id)
Filter: (security_findings_1.deduplicated AND (security_findings_1.severity = severities.severity) AND (security_findings_1.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_69_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_69 security_findings_2 (cost=0.57..1350.13 rows=213 width=1144) (actual time=0.007..0.007 rows=0 loops=6)
Index Cond: (security_findings_2.scan_id = security_scans.id)
Filter: (security_findings_2.deduplicated AND (security_findings_2.severity = severities.severity) AND (security_findings_2.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_70_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_70 security_findings_3 (cost=0.57..1370.97 rows=218 width=1116) (actual time=0.009..0.009 rows=0 loops=6)
Index Cond: (security_findings_3.scan_id = security_scans.id)
Filter: (security_findings_3.deduplicated AND (security_findings_3.severity = severities.severity) AND (security_findings_3.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_71_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_71 security_findings_4 (cost=0.57..1251.17 rows=199 width=1106) (actual time=0.007..0.007 rows=0 loops=6)
Index Cond: (security_findings_4.scan_id = security_scans.id)
Filter: (security_findings_4.deduplicated AND (security_findings_4.severity = severities.severity) AND (security_findings_4.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_72_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_72 security_findings_5 (cost=0.57..1226.21 rows=194 width=1115) (actual time=3.898..3.904 rows=13 loops=6)
Index Cond: (security_findings_5.scan_id = security_scans.id)
Filter: (security_findings_5.deduplicated AND (security_findings_5.severity = severities.severity) AND (security_findings_5.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 9756
Buffers: shared hit=8111
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_73_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_73 security_findings_6 (cost=0.57..1161.06 rows=184 width=1127) (actual time=0.009..0.009 rows=0 loops=6)
Index Cond: (security_findings_6.scan_id = security_scans.id)
Filter: (security_findings_6.deduplicated AND (security_findings_6.severity = severities.severity) AND (security_findings_6.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_74_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_74 security_findings_7 (cost=0.57..1258.09 rows=197 width=1154) (actual time=0.005..0.005 rows=0 loops=6)
Index Cond: (security_findings_7.scan_id = security_scans.id)
Filter: (security_findings_7.deduplicated AND (security_findings_7.severity = severities.severity) AND (security_findings_7.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_75_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_75 security_findings_8 (cost=0.57..1243.84 rows=194 width=1175) (actual time=0.008..0.008 rows=0 loops=6)
Index Cond: (security_findings_8.scan_id = security_scans.id)
Filter: (security_findings_8.deduplicated AND (security_findings_8.severity = severities.severity) AND (security_findings_8.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_76_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_76 security_findings_9 (cost=0.57..1302.52 rows=204 width=1174) (actual time=0.006..0.006 rows=0 loops=6)
Index Cond: (security_findings_9.scan_id = security_scans.id)
Filter: (security_findings_9.deduplicated AND (security_findings_9.severity = severities.severity) AND (security_findings_9.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_77_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_77 security_findings_10 (cost=0.57..1242.09 rows=194 width=1190) (actual time=0.006..0.006 rows=0 loops=6)
Index Cond: (security_findings_10.scan_id = security_scans.id)
Filter: (security_findings_10.deduplicated AND (security_findings_10.severity = severities.severity) AND (security_findings_10.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_78_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_78 security_findings_11 (cost=0.57..1259.11 rows=196 width=1199) (actual time=0.006..0.006 rows=0 loops=6)
Index Cond: (security_findings_11.scan_id = security_scans.id)
Filter: (security_findings_11.deduplicated AND (security_findings_11.severity = severities.severity) AND (security_findings_11.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_79_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_79 security_findings_12 (cost=0.57..1162.32 rows=181 width=1209) (actual time=0.007..0.007 rows=0 loops=6)
Index Cond: (security_findings_12.scan_id = security_scans.id)
Filter: (security_findings_12.deduplicated AND (security_findings_12.severity = severities.severity) AND (security_findings_12.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=24
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_80_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_80 security_findings_13 (cost=0.43..393.05 rows=62 width=1164) (actual time=0.005..0.005 rows=0 loops=6)
Index Cond: (security_findings_13.scan_id = security_scans.id)
Filter: (security_findings_13.deduplicated AND (security_findings_13.severity = severities.severity) AND (security_findings_13.confidence = ANY ('{1,2,3,4,5,6,7}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=18
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.14..4.62 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=80)
Buffers: shared hit=759
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..2.11 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=80)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 11
Buffers: shared hit=359
I/O Timings: read=0.000 write=0.000
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.51 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=80)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=400
I/O Timings: read=0.000 write=0.000Time: 58.859 ms
- planning: 33.002 ms
- execution: 25.857 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 9163 (~71.60 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Related to #411666 (closed)
Edited by Malcolm Locke