Artifact [d1744b7cba]

Artifact d1744b7cba1f38ad3bd9ce3c2052eaf6ac6674c7f7953abe9d45fad4734af88c:

• File internal/index/plan_test.go — part of check-in [4c973ff323] at 2026-08-17 21:00:08 on branch trunk — index: fold ticket summaries at write time

  The ticket list derived itself per request: GROUP BY over every change row (tk: 35,903), two correlated subqueries per ticket to find the newest title and status — 11,618, each with its own temp b-tree — and a HAVING that filtered after grouping, so paging saved nothing.

  tk 200ms -> 1ms sqlite 1ms fossil <1ms

  Written from refreshTicketDoc, which already folds exactly this state for the search doc, so the summary cannot disagree with what search sees. Backfilled at open for indexes written before the table existed.

  Second instance of the same bug the timeline had, so plan_test now covers this query too. (user: dmestas, size: 4144) [annotate] [blame] [check-ins using]

package index

import (
	"strings"
	"testing"
)

// The timeline once assembled itself at read time from a five-way UNION
// ALL and ended in USE TEMP B-TREE FOR ORDER BY — SQLite materializing
// and sorting every event in the repository to return fifty. It cost
// 536ms on tk where fossil serves the same page in 3-4ms.
//
// Nothing about that query was ill-typed; a code generator would have
// emitted it happily. The defect was only ever visible in the PLAN,
// which is why the plan is what this asserts.
func TestTimelineWalksAnIndex(t *testing.T) {
	ix := openIndex(t)

	// EXPLAIN QUERY PLAN reports the plan for the statement as written,
	// so this must be the same SQL Timeline issues. Kept beside it: if
	// the query changes shape and this is not updated, the test starts
	// describing a query that no longer exists — so assert on markers
	// general enough to survive cosmetic edits and specific enough to
	// catch a return to scanning.
	const q = `
		SELECT e.kind, e.hash, e.ref, e.date, e.user,
			CASE WHEN e.kind = 't'
				THEN 'Ticket [' || substr(e.ref, 1, 10) || '] ' || COALESCE(d.label, '')
				ELSE e.comment END,
			e.branch,
			CASE WHEN e.kind = 'ci'
				THEN NOT EXISTS (SELECT 1 FROM checkin_parent lp WHERE lp.parent = e.hash)
				ELSE 0 END
		FROM event e
		LEFT JOIN search_doc d ON e.kind = 't' AND d.hash = 'ticket:' || e.ref
		ORDER BY e.date DESC, e.hash DESC LIMIT ?`

	plan := queryPlan(t, ix, q, 50)

	// The sort is the expensive part and the one that scales with the
	// repository rather than the page.
	if strings.Contains(plan, "TEMP B-TREE") {
		t.Errorf("timeline sorts in a temp b-tree — it is materializing the whole repository:\n%s", plan)
	}
	// A plain SCAN of event means the ordering index was not used.
	if strings.Contains(plan, "SCAN event") && !strings.Contains(plan, "USING INDEX") {
		t.Errorf("timeline scans event instead of walking an index:\n%s", plan)
	}
	if !strings.Contains(plan, "event_by_date") {
		t.Errorf("timeline did not use event_by_date:\n%s", plan)
	}
}

// The branch-filtered timeline is a different plan and a different
// index; a fix that only covered the unfiltered page would leave the
// branch view scanning.
func TestBranchTimelineWalksAnIndex(t *testing.T) {
	ix := openIndex(t)
	const q = `
		SELECT e.kind, e.hash, e.ref, e.date, e.user, e.comment, e.branch, 0
		FROM event e
		WHERE e.branch = ? AND e.kind = ?
		ORDER BY e.date DESC, e.hash DESC LIMIT ?`

	plan := queryPlan(t, ix, q, "trunk", "ci", 50)
	if strings.Contains(plan, "TEMP B-TREE") {
		t.Errorf("branch timeline sorts in a temp b-tree:\n%s", plan)
	}
	if !strings.Contains(plan, "USING INDEX") {
		t.Errorf("branch timeline did not use an index:\n%s", plan)
	}
}

func queryPlan(t *testing.T, ix *Index, query string, args ...any) string {
	t.Helper()
	rows, err := ix.db.Query("EXPLAIN QUERY PLAN "+query, args...)
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	var b strings.Builder
	for rows.Next() {
		var id, parent, notused int
		var detail string
		if err := rows.Scan(&id, &parent, &notused, &detail); err != nil {
			t.Fatal(err)
		}
		b.WriteString(detail)
		b.WriteByte('\n')
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	return b.String()
}

// The ticket list had the timeline's disease: GROUP BY over every change
// row, two correlated subqueries per ticket — each with its own temp
// b-tree — and a HAVING that filtered after grouping, so paging saved
// nothing. 200ms on tk, with no zit call involved.
func TestTicketListWalksAnIndex(t *testing.T) {
	ix := openIndex(t)
	const q = `
		SELECT id, created, updated, title, status FROM ticket
		WHERE ?1 = '' OR updated < ?1 OR (updated = ?1 AND id > ?2)
		ORDER BY updated DESC, id
		LIMIT ?3`

	plan := queryPlan(t, ix, q, "", "", 50)
	if strings.Contains(plan, "TEMP B-TREE") {
		t.Errorf("ticket list sorts in a temp b-tree:\n%s", plan)
	}
	if strings.Contains(plan, "CORRELATED") {
		t.Errorf("ticket list runs a correlated subquery per row:\n%s", plan)
	}
	if !strings.Contains(plan, "ticket_by_updated") {
		t.Errorf("ticket list did not use ticket_by_updated:\n%s", plan)
	}
}