[Spike] - Optimize group dependency list query to be more performant at scale

Summary - Why is this spike needed?

In the group.sbom_occurrences query project_count, location_count and occurrence_count are expensive 🤑 and will cause timeouts for users. The results of before (including these aggregated fields in the query) and after (removing these fields) are in the description of the merge request Remove occurrence and project counts (!132671 - closed) • mo khan • 16.7.

Pictured below is a visual of the Group Dependency List from this group, with the impacted fields highlighted.

Removing project_count, location_count and occurrence_count will impact the user experience. For example, in the image above, @babel/core 7.23.0 is in 4 locations and has 3 projects. If we were to remove the automated grouping of component and version, this component would have 4 line numbers in the list:

Component	Packager	Location	License	Projects
@babel/core 7.23.0	npm	npm/package-lock.json _mono-repo_	MIT	mono-repo
@babel/core 7.23.0	yarn	yarn/yarn.lock _mono-repo_	MIT	mono-repo
@babel/core 7.23.0	npm	npm/package-lock.json _npm-project_	MIT	npm-project
@babel/core 7.23.0	yarn	yarn.lock _yarn-project_	MIT	Yarn Project

This Spike aims to explore ways to optimize the query without removing key fields.

Timebox Expectations

Expected Outcomes

• Possible options are explored and documented
• If feasible a technical proposal is added to a new issue under the Project and License filters (&10904 - closed) epic

Links/References

Remove occurrence and project counts (!132671 - closed) • mo khan • 16.7