Slow SQL query for the NPM metadata endpoint
🐢 Problem
Follow up #338483 (closed).
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.
🚀 Solution
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.
Results
As a result, we significantly reduced the number of requests longer than 3 seconds.