Fix scoping vulns on group dashboard
What does this MR do?
Fix the Group Security Dashboard by ensuring we only filter pipeline that effectively have vulnerabilities.
Here are the new queries:
Vulnerabilities list:
EXPLAIN ANALYZE
SELECT "vulnerability_occurrences".*
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id"
WHERE "vulnerability_occurrence_pipelines"."pipeline_id" IN
(SELECT max(ci_pipelines.id) AS id
FROM "ci_pipelines"
WHERE "ci_pipelines"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (rs.path LIKE 'olivier/%'))
AND (EXISTS
(SELECT 1
FROM "vulnerability_occurrence_pipelines"
WHERE (ci_pipelines.id=vulnerability_occurrence_pipelines.pipeline_id)))
AND "ci_pipelines"."status" = 'success'
GROUP BY "ci_pipelines"."project_id")
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=15.74..10336.93 rows=12991 width=662) (actual time=0.331..0.560 rows=72 loops=1)
-> Nested Loop (cost=15.32..3275.64 rows=12991 width=8) (actual time=0.323..0.364 rows=72 loops=1)
-> HashAggregate (cost=14.90..14.91 rows=1 width=4) (actual time=0.319..0.319 rows=3 loops=1)
Group Key: max(ci_pipelines.id)
-> GroupAggregate (cost=14.86..14.88 rows=1 width=8) (actual time=0.311..0.314 rows=3 loops=1)
Group Key: ci_pipelines.project_id
-> Sort (cost=14.86..14.87 rows=1 width=8) (actual time=0.307..0.309 rows=11 loops=1)
Sort Key: ci_pipelines.project_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop Semi Join (cost=7.13..14.85 rows=1 width=8) (actual time=0.193..0.278 rows=11 loops=1)
-> Nested Loop (cost=6.71..8.20 rows=15 width=8) (actual time=0.119..0.163 rows=31 loops=1)
Join Filter: (ci_pipelines.project_id = rs.source_id)
-> Unique (cost=6.44..6.45 rows=2 width=8) (actual time=0.109..0.111 rows=5 loops=1)
-> Sort (cost=6.44..6.44 rows=2 width=8) (actual time=0.108..0.108 rows=5 loops=1)
Sort Key: projects.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.29..6.43 rows=2 width=8) (actual time=0.040..0.059 rows=5 loops=1)
-> Index Scan using index_routes_on_path on routes rs (cost=0.14..3.09 rows=2 width=4) (actual time=0.019..0.026 rows=5 loops=1)
Index Cond: (((path)::text >= 'olivier/'::text) AND ((path)::text < 'olivier0'::text))
Filter: (((path)::text ~~ 'olivier/%'::text) AND ((source_type)::text = 'Project'::text))
Rows Removed by Filter: 2
-> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..1.66 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=5)
Index Cond: (id = rs.source_id)
Heap Fetches: 4
-> Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on ci_pipelines (cost=0.27..0.76 rows=9 width=8) (actual time=0.004..0.007 rows=6 loops=5)
Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
-> Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.42..242.32 rows=12991 width=4) (actual time=0.002..0.002 rows=0 loops=31)
Index Cond: (pipeline_id = ci_pipelines.id)
Heap Fetches: 11
-> Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.42..3130.83 rows=12991 width=12) (actual time=0.003..0.010 rows=24 loops=3)
Index Cond: (pipeline_id = (max(ci_pipelines.id)))
-> Index Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.42..0.53 rows=1 width=662) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
Planning time: 8.017 ms
Execution time: 0.995 ms
(35 rows)summary counters:
EXPLAIN ANALYZE
SELECT COUNT(*) AS count_all,
"vulnerability_occurrences"."report_type" AS vulnerability_occurrences_report_type,
"vulnerability_occurrences"."severity" AS vulnerability_occurrences_severity
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id"
WHERE "vulnerability_occurrence_pipelines"."pipeline_id" IN
(SELECT max(id) AS id
FROM "ci_pipelines"
WHERE "ci_pipelines"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (rs.path LIKE 'olivier/%'))
AND (EXISTS
(SELECT 1
FROM "vulnerability_occurrence_pipelines"
WHERE (ci_pipelines.id=vulnerability_occurrence_pipelines.pipeline_id)))
AND "ci_pipelines"."status" = 'success'
GROUP BY "ci_pipelines"."project_id")
GROUP BY "vulnerability_occurrences"."report_type",
"vulnerability_occurrences"."severity";
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10434.36..10434.44 rows=8 width=12) (actual time=0.311..0.312 rows=6 loops=1)
Group Key: vulnerability_occurrences.report_type, vulnerability_occurrences.severity
-> Nested Loop (cost=15.74..10336.93 rows=12991 width=4) (actual time=0.152..0.297 rows=72 loops=1)
-> Nested Loop (cost=15.32..3275.64 rows=12991 width=8) (actual time=0.149..0.170 rows=72 loops=1)
-> HashAggregate (cost=14.90..14.91 rows=1 width=4) (actual time=0.147..0.147 rows=3 loops=1)
Group Key: max(ci_pipelines.id)
-> GroupAggregate (cost=14.86..14.88 rows=1 width=8) (actual time=0.142..0.145 rows=3 loops=1)
Group Key: ci_pipelines.project_id
-> Sort (cost=14.86..14.87 rows=1 width=8) (actual time=0.139..0.140 rows=11 loops=1)
Sort Key: ci_pipelines.project_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop Semi Join (cost=7.13..14.85 rows=1 width=8) (actual time=0.079..0.132 rows=11 loops=1)
-> Nested Loop (cost=6.71..8.20 rows=15 width=8) (actual time=0.044..0.075 rows=31 loops=1)
Join Filter: (ci_pipelines.project_id = rs.source_id)
-> Unique (cost=6.44..6.45 rows=2 width=8) (actual time=0.038..0.041 rows=5 loops=1)
-> Sort (cost=6.44..6.44 rows=2 width=8) (actual time=0.038..0.040 rows=5 loops=1)
Sort Key: projects.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.29..6.43 rows=2 width=8) (actual time=0.015..0.027 rows=5 loops=1)
-> Index Scan using index_routes_on_path on routes rs (cost=0.14..3.09 rows=2 width=4) (actual time=0.008..0.013 rows=5 loops=1)
Index Cond: (((path)::text >= 'olivier/'::text) AND ((path)::text < 'olivier0'::text))
Filter: (((path)::text ~~ 'olivier/%'::text) AND ((source_type)::text = 'Project'::text))
Rows Removed by Filter: 2
-> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..1.66 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5)
Index Cond: (id = rs.source_id)
Heap Fetches: 4
-> Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on ci_pipelines (cost=0.27..0.76 rows=9 width=8) (actual time=0.003..0.005 rows=6 loops=5)
Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
-> Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.42..242.32 rows=12991 width=4) (actual time=0.002..0.002 rows=0 loops=31)
Index Cond: (pipeline_id = ci_pipelines.id)
Heap Fetches: 11
-> Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.42..3130.83 rows=12991 width=12) (actual time=0.002..0.004 rows=24 loops=3)
Index Cond: (pipeline_id = (max(ci_pipelines.id)))
-> Index Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.42..0.53 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=72)
Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
Planning time: 1.272 ms
Execution time: 0.528 ms
(37 rows)What are the relevant issue numbers?
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides -
Link to e2e tests MR added if this MR has Requires e2e tests label. See the Test Planning Process. -
EE specific content should be in the top level /eefolder -
For a paid feature, have we considered GitLab.com plans, how it works for groups, and is there a design for promoting it to users who aren't on the correct plan?
Edited by Kamil Trzciński