internal/store/migrations/0035_dashboard_indexes.up.sql

 1-- The dashboard's lists are "the newest N rows I can reach". Reachability
 2-- is a set of correlated subqueries, so no index can satisfy the filter;
 3-- what an index can do is supply the order, so the walk stops at LIMIT
 4-- instead of sorting every row in the table.
 5--
 6-- Deliberately not (state, updated_at): the merge request lists match
 7-- state with IN, which turns one ordered walk into two that must be
 8-- merged, and measured slower than no index at all — 12.1ms to 19.6ms on
 9-- 20k rows. Ordering alone is what these queries want.
10CREATE INDEX issues_recent ON issues(updated_at DESC);
11CREATE INDEX merge_requests_recent ON merge_requests(updated_at DESC);
12
13-- issue_assignees' primary key leads with issue_id, so AssignedIssues had
14-- no way in by user and tested EXISTS against every issue instead.
15CREATE INDEX issue_assignees_user ON issue_assignees(user_id);