Optimize ListCorpusWorkerVersions
The endpoint currently has 502 errors and 500 errors due to the database running out of disk space and due to the queries being rather complex.
The endpoint currently retrieves worker versions along with workers, repos on the worker, revisions and repos from the revisions (two joins for the same repos!) in a single query, ordering by revision creation date and grouping by 5 columns, along with a count of elements linked to those worker versions for each corpus.
This endpoint is used by the frontend to power the worker version filter on element lists, so it is called pretty often even if it might not be very visible to users. Errors from this endpoint are also not reported to users at all, so when an error occurs, the filter is disabled like when no worker version is available on this corpus.
We can try to:
- Use cursor pagination (-20%), since the frontend does not care about the count
- Order by version ID (or add a
created
field on the worker version itself), which enables prefetching instead of selecting (at least -20%) - Make the element count optional (
?with_count
), or remove it (-10%)