Optimize accessibility queries for reviews and review requests.
Review Request #11388 — Created Jan. 19, 2021 and submitted
Reviews and review request queries attempted to pre-fetch a list of IDs
of review groups and repositories they had access to, in order to
optimize the main queries. This didn't work quite right, as the return
type provided by Django wasn't a straight list but rather a type of
queryset, which it was turning into a JOIN when passed into the main
queries. That created a pretty deeply-nested query, which was far from
This wasn't helped by one of subqueries for review requests calling the
wrong function, pulling in the actual subquery and not the ID list.
The queries now use the functions for ID lists in all cases, and those
explicitly return IDs to avoid the issue.
The result is that the ID queries are separated out, turning this one
giant query into 3. The ID queries are small enough and simple enough to
be cached on the database end, speeding those up.
There's still room for improvement here, which is being explored. Some
caching and pre-fetching of state like the existence of Local Sites,
private repositories, or invite-only groups will let us further simplify
the queries for all users.
Unit tests pass.