Slow SQL query for the NPM metadata endpoint

After removing an n+1 situation on the npm metadata endpoint, we noticed that the response time was reduced but was still pretty high. See https://dashboards.gitlab.net/d/stage-groups-package/stage-groups-group-dashboard-package-package?viewPanel=54&orgId=1.

Looking at this kibana search, we noticed that this call triggers an SQL query that is rather long to execute.

Run a database analysis on that SQL query and see if there are ways to improve it.

Other considerations:

Be aware of this bug: #339460 (closed). The solution of that bug will certainly modify or remove that call.

Having said that, aim for the simplest solution here to improve the query and keep heavy refactorings for when #339460 (closed) is tackled.

As a result, we significantly reduced the number of requests longer than 3 seconds.

Edited Sep 17, 2021 by Tim Rizzi