Evaluate limiting Group Level Vulnerability Report for groups below a certain size
We are using the vulnerabilitySeveritiesCount GraphQL API for the project and group level vulnerability report pages. For large groups (many projects) it will have performance issues and the query timeouts.
- Identify and apply a limit similar to the 600 limit we have applied for dependency list filter.
- When a group is limited, the user page should show message like: "This group has too many projects to display at once. Please reduce the number of projects below ##."
Problematic query:
SELECT
count(*) AS count_all,
vulnerability_reads.severity AS vulnerability_reads_severity
FROM
vulnerability_reads
JOIN projects ON projects.id = vulnerability_reads.project_id
WHERE
vulnerability_reads.namespace_id IN (
SELECT
namespaces.id
FROM
namespaces
WHERE
namespaces.type = 'Group' AND
traversal_ids @> '{?}'
) AND
projects.archived = false AND
vulnerability_reads.report_type = 7 AND
vulnerability_reads.state IN ( 1, 4 )
GROUP BY
vulnerability_reads.severity
ORDER BY
vulnerability_reads.severity DESC;
Implementation notes:
- Consider storing this limit in an application setting (versus a hardcoded value) so that we can easily change the threshold as we monitor performance.
Duplicated by #430814 (closed) which captures the timeouts faced by customers.
Edited by Alana Bellucci