On 20k issues and 20k merge requests:
| before | after | |
|---|---|---|
| DashboardIssues | 11.8ms | 0.8ms |
| DashboardMRs | 12.1ms | 0.8ms |
| ReviewQueue | 15.5ms | 0.3ms |
| AssignedIssues | 10.6ms | 0.04ms |
Reachability is correlated subqueries and cannot be indexed. The index supplies the ORDER BY instead, so the walk stops at LIMIT.
Worth flagging: the indexes #137 asked for — (author_id, updated_at) on
both tables — are not the ones here, because nothing can use them. The
filter is author_id = ? OR reachable, and an OR across a column and a
correlated subquery scans either way; rewriting it as a UNION so the
author branch could use one measured slower (11.9ms → 15.3ms).
(state, updated_at) is worse still on the merge request lists, which
match state with IN: 12.1ms → 19.6ms, worse than no index.
Closes #137