krz/gitbay

store: the dashboard walks an index instead of sorting the table !230

merged merged by cmc on 2026-09-04 15:58 UTC · krz/gitbay:dashboard-indexes into main

4 files changed, +144 −47

Layout: unified · split

internal/store/dashboard.go +60 −47
@@ -45,33 +45,39 @@ func (s *Store) dashboardQuery(q string, userID int64) ([]DashboardItem, error)
45 return out, rows.Err() 45 return out, rows.Err()
46} 46}
47 47
48// The dashboard's four list queries are named so the plan test can assert
49// each still walks the 0035 index that supplies its ORDER BY.
50const dashboardMRsQuery = `
51 SELECT COALESCE(u.username, o.name) || '/' || r.name,
52 x.number, x.title, au.username, x.state, x.updated_at
53 FROM merge_requests x
54 JOIN repos r ON r.id = x.repo_id
55 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
56 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
57 JOIN users au ON au.id = x.author_id
58 WHERE x.state IN ('open', 'source_gone') AND ` + involvedCond + `
59 ORDER BY x.updated_at DESC LIMIT 50`
60
48// DashboardMRs returns open merge requests involving the user: on their 61// DashboardMRs returns open merge requests involving the user: on their
49// repositories (owned, granted, org) or authored by them anywhere. 62// repositories (owned, granted, org) or authored by them anywhere.
50func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) { 63func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) {
51 return s.dashboardQuery(` 64 return s.dashboardQuery(dashboardMRsQuery, userID)
52 SELECT COALESCE(u.username, o.name) || '/' || r.name,
53 x.number, x.title, au.username, x.state, x.updated_at
54 FROM merge_requests x
55 JOIN repos r ON r.id = x.repo_id
56 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
57 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
58 JOIN users au ON au.id = x.author_id
59 WHERE x.state IN ('open', 'source_gone') AND `+involvedCond+`
60 ORDER BY x.updated_at DESC LIMIT 50`, userID)
61} 65}
62 66
63// DashboardIssues is the issue counterpart of DashboardMRs. 67// DashboardIssues is the issue counterpart of DashboardMRs.
68const dashboardIssuesQuery = `
69 SELECT COALESCE(u.username, o.name) || '/' || r.name,
70 x.number, x.title, au.username, x.state, x.updated_at
71 FROM issues x
72 JOIN repos r ON r.id = x.repo_id
73 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
74 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
75 JOIN users au ON au.id = x.author_id
76 WHERE x.state = 'open' AND ` + involvedCond + `
77 ORDER BY x.updated_at DESC LIMIT 50`
78
64func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) { 79func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) {
65 return s.dashboardQuery(` 80 return s.dashboardQuery(dashboardIssuesQuery, userID)
66 SELECT COALESCE(u.username, o.name) || '/' || r.name,
67 x.number, x.title, au.username, x.state, x.updated_at
68 FROM issues x
69 JOIN repos r ON r.id = x.repo_id
70 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
71 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
72 JOIN users au ON au.id = x.author_id
73 WHERE x.state = 'open' AND `+involvedCond+`
74 ORDER BY x.updated_at DESC LIMIT 50`, userID)
75} 81}
76 82
77func (s *Store) PinRepo(userID, repoID int64) error { 83func (s *Store) PinRepo(userID, repoID int64) error {
@@ -124,22 +130,24 @@ func (s *Store) PinnedRepos(userID int64) ([]Repo, error) {
124// ReviewQueue returns open merge requests the user is involved in, has not 130// ReviewQueue returns open merge requests the user is involved in, has not
125// authored, and has not reviewed at the current head — what the rail shows 131// authored, and has not reviewed at the current head — what the rail shows
126// as waiting on them. Ordered most recently touched first. 132// as waiting on them. Ordered most recently touched first.
133const reviewQueueQuery = `
134 SELECT COALESCE(u.username, o.name) || '/' || r.name,
135 x.number, x.title, au.username, x.state, x.updated_at
136 FROM merge_requests x
137 JOIN repos r ON r.id = x.repo_id
138 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
139 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
140 JOIN users au ON au.id = x.author_id
141 WHERE x.state IN ('open', 'source_gone')
142 AND x.author_id <> ?1
143 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
144 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
145 AND rv.head_sha = x.head_sha)
146 AND ` + involvedCond + `
147 ORDER BY x.updated_at DESC LIMIT 8`
148
127func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) { 149func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
128 return s.dashboardQuery(` 150 return s.dashboardQuery(reviewQueueQuery, userID)
129 SELECT COALESCE(u.username, o.name) || '/' || r.name,
130 x.number, x.title, au.username, x.state, x.updated_at
131 FROM merge_requests x
132 JOIN repos r ON r.id = x.repo_id
133 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
134 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
135 JOIN users au ON au.id = x.author_id
136 WHERE x.state IN ('open', 'source_gone')
137 AND x.author_id <> ?1
138 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
139 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
140 AND rv.head_sha = x.head_sha)
141 AND `+involvedCond+`
142 ORDER BY x.updated_at DESC LIMIT 8`, userID)
143} 151}
144 152
145// OpenCounts returns the repo's open issue and open merge request counts, 153// OpenCounts returns the repo's open issue and open merge request counts,
@@ -155,19 +163,24 @@ func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
155// AssignedIssues returns open issues assigned to the user, wherever they 163// AssignedIssues returns open issues assigned to the user, wherever they
156// live. Assignment is a direct request for someone's attention, so it is 164// live. Assignment is a direct request for someone's attention, so it is
157// not narrowed by the involvement rule the other lists use. 165// not narrowed by the involvement rule the other lists use.
166//
167// It drives from issue_assignees rather than testing EXISTS against every
168// issue: the assignee rows for one user are a handful, the issues table
169// is the whole instance.
170const assignedIssuesQuery = `
171 SELECT COALESCE(u.username, o.name) || '/' || r.name,
172 x.number, x.title, au.username, x.state, x.updated_at
173 FROM issue_assignees ia
174 JOIN issues x ON x.id = ia.issue_id
175 JOIN repos r ON r.id = x.repo_id
176 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
177 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
178 JOIN users au ON au.id = x.author_id
179 WHERE ia.user_id = ?1 AND x.state = 'open'
180 ORDER BY x.updated_at DESC LIMIT 20`
181
158func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) { 182func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
159 return s.dashboardQuery(` 183 return s.dashboardQuery(assignedIssuesQuery, userID)
160 SELECT COALESCE(u.username, o.name) || '/' || r.name,
161 x.number, x.title, au.username, x.state, x.updated_at
162 FROM issues x
163 JOIN repos r ON r.id = x.repo_id
164 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
165 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
166 JOIN users au ON au.id = x.author_id
167 WHERE x.state = 'open'
168 AND EXISTS (SELECT 1 FROM issue_assignees ia
169 WHERE ia.issue_id = x.id AND ia.user_id = ?1)
170 ORDER BY x.updated_at DESC LIMIT 20`, userID)
171} 184}
172 185
173// DashboardBuild is one build row on the dashboard, with its repo resolved. 186// DashboardBuild is one build row on the dashboard, with its repo resolved.
internal/store/dashboardplan_test.go added +66
@@ -0,0 +1,66 @@
1package store
2
3import (
4 "strings"
5 "testing"
6)
7
8// queryPlan returns EXPLAIN QUERY PLAN for q as one string.
9func queryPlan(t *testing.T, s *Store, q string, args ...any) string {
10 t.Helper()
11 rows, err := s.DB.Query("EXPLAIN QUERY PLAN "+q, args...)
12 if err != nil {
13 t.Fatalf("explain: %v", err)
14 }
15 defer rows.Close()
16 var b strings.Builder
17 for rows.Next() {
18 var id, parent, notUsed int
19 var detail string
20 if err := rows.Scan(&id, &parent, &notUsed, &detail); err != nil {
21 t.Fatal(err)
22 }
23 b.WriteString(detail)
24 b.WriteString("\n")
25 }
26 return b.String()
27}
28
29// TestDashboardQueriesUseIndexes guards the plans the 0035 indexes exist
30// for. Reachability is correlated subqueries and cannot be indexed, so
31// what these queries buy from an index is the ORDER BY: the walk stops at
32// LIMIT rather than sorting the table. A rewrite that reintroduces a sort
33// puts the cost back — 0.8ms to 11.8ms on 20k issues — with no other
34// symptom, which is what this asserts against (#137).
35func TestDashboardQueriesUseIndexes(t *testing.T) {
36 s := open(t)
37 if err := s.MigrateUp(); err != nil {
38 t.Fatal(err)
39 }
40 // The planner picks against an empty table the same way it does
41 // against a full one here: these plans are driven by the ORDER BY and
42 // the index's presence, not by row counts.
43 cases := []struct {
44 name string
45 plan string
46 want string
47 // ordered marks the queries whose index supplies the ORDER BY, so
48 // a sort in the plan means the walk is back to reading every row.
49 // AssignedIssues is not one: it drives from one user's assignee
50 // rows, a handful, and sorting those is the cheap half.
51 ordered bool
52 }{
53 {"DashboardIssues", queryPlan(t, s, dashboardIssuesQuery, int64(1)), "issues_recent", true},
54 {"DashboardMRs", queryPlan(t, s, dashboardMRsQuery, int64(1)), "merge_requests_recent", true},
55 {"ReviewQueue", queryPlan(t, s, reviewQueueQuery, int64(1)), "merge_requests_recent", true},
56 {"AssignedIssues", queryPlan(t, s, assignedIssuesQuery, int64(1)), "issue_assignees_user", false},
57 }
58 for _, tc := range cases {
59 if !strings.Contains(tc.plan, tc.want) {
60 t.Errorf("%s does not use %s:\n%s", tc.name, tc.want, tc.plan)
61 }
62 if tc.ordered && strings.Contains(tc.plan, "USE TEMP B-TREE FOR ORDER BY") {
63 t.Errorf("%s sorts instead of walking an index:\n%s", tc.name, tc.plan)
64 }
65 }
66}
internal/store/migrations/0035_dashboard_indexes.down.sql added +3
@@ -0,0 +1,3 @@
1DROP INDEX issue_assignees_user;
2DROP INDEX merge_requests_recent;
3DROP INDEX issues_recent;
internal/store/migrations/0035_dashboard_indexes.up.sql added +15
@@ -0,0 +1,15 @@
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);