VulnerabilityReads require index or indicies for all activity filter fields ( HasIssues, HasResolutiions, HasRemediations and HasMergeRequests )
Following a lengthy conversation with @minac, it was determined that the new filter attributes for VulnerabilityReads, has_remediations
and has_merge_requests
require a new index(es?) to be introduced to facilitate their use, as filtering with these attributes without one will result in statement timeout failures on the Vulnerability Reports.
To add to the description above, we also realized this performance concern is also exists for other filer fields as well.
In this issue, we will try to tackle it
- HasIssues,
- HasResolutiions
- HasRemediations
- HasMergeRequests
Implementation plan:
- Create separate indexes for the 4 filters with
project_id
combination for the commonly used project vulnerability report page. Similar to !124083 (merged) - Create separate indexes for the 4 filters with
namespace_id
combination for the commonly used group vulnerability report page. Similar to !101788 (merged)
We will have to split the index creations into multiple MR's as creating all indexes in a single MR:
- could have a longer migration run time
- rollback plans will impact all indexes on the migration file.
- we may have to use
prepare_async_index
During development and review we should also understand the lock impacts these new indexes can have on the table. Related reading: https://gitlab.com/-/snippets/3611283