internal/store/dashboardplan_test.go

 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}