Observation: In this query plan we materialize 106 million rows by a product of lsif_references and the canonical package providers. What we actually want is a count for each of the 1,210 uploads. The way that the original query achieved this was by materializing the full join, sorting by id, then grouping. This took ~1.4GB to sort on disk each query.
Alternatively, we can use a correlated subquery/lateral join to do a single count per row. This is mechanically the same as the nested loop join that was previously used, except now we skip to the aggregate value instead of blowing up the intermediate result.
The following high-cost nodes are no longer in the query plan:
I don't know in practice if this will help us with our current low queue throughput, but it should advance the bad-SQL part of the queries for now. We should still look into reducing lock contention on the lsif_uploads table.
