Skip to content

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:

  1. Create separate indexes for the 4 filters with project_id combination for the commonly used project vulnerability report page. Similar to !124083 (merged)
  2. 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

Edited by Bala Kumar