Update index in vulnerability_reads to include report_type
The following discussion from !76220 (merged) should be addressed:
-
@ahegyi started a discussion: (+7 comments)
I have some concerns regarding the queries. The query plans from the description suggest that we had cached execution where most of the data were already in the buffer. This will not be the case on PRD:
Project queries: 700ms / 1s Group queries: 10s / statement timeout
Pain points:
ORDER BY clause is not covered with an index: severity, id
Some columns are not covered with an index: report_type
IN queries + ORDER BY is slow. This affect both project-level and group-level queries. It's worse for group-level queries. More info here
COUNT queries will be eventually very slow for projects/groups with many records. More info here.
The first step would be adding extra indexes and checking if the execution time improves:
vulnerability_reads: project_id, state, report_id, severity, vulnerability_id
vulnerabilities: project_id, state, report_id, severity, id
Since the finder can have different filter combinations, we might need to add more indexes. Unfortunately, it's often impractical to optimize all possible scenarios for our finders. We'll have to find the most used filter combinations and optimize them.