chadtmiller.com/knotless

Switch repository

Loading…

What changes

When the index is asked for a pull's checks, it now reads them through a new index, check_pull_at (json_extract(value, '$.pull'), started_at, uri), newest first. Before, it walked the repository's runs newest first and kept the few that were that pull's. Any other filter is read as before. The answer is the same.

Why

On prod today the checks statement ran 91 times, with a mean of 321ms and a max of 747ms. getMergeStatus, getQueue, a pull's page and the checks tool all ask for one pull's checks. A check_pull index on the pull was already there, but (?9 IS NULL OR json_extract(value, '$.pull') = ?9) keeps SQLite from using it, so it walked check_repo. chadtmiller.com/knotless gets about 1,400 runs a day, so that walk grows with every run. The pull's form now names its index, and the new index keeps the order the statement sorts by, so nothing is sorted.

How it was checked

The serve tests that read a pull's checks (merge status, the queue, checks by pull) cover both forms. The plans gate in test plans both at prod's sizes. Running it is left to CI. After deploy, the check is the statement's mean in stats, against 321ms.

Shortcomings

A filter by workflow alone, as the runs page asks for, still walks the repository's runs. That page is seldom read.

🤖 Generated with Claude Code

This pull adds a check_pull_at index on (json_extract(value,'$.pull'), started_at, uri). When a pull filter is given, the checks statement now names that index with INDEXED BY. Other filters still use the old repository-ordered form. I read the diff and found nothing that must change.

• Behaviour: Both branches keep the same WHERE clauses and ORDER BY started_at DESC, uri DESC. The ?9 IS NULL condition moves into the None form, so it stays valid there.
• Planner: The +repo_did mark and the expression written exactly as in the index follow AGENTS.md's Queries guidance.
• INDEXED BY: It errors if the index is missing, but the index is created with IF NOT EXISTS alongside the others, so existing databases get it too.
• Checks: I did not read the CI results. The test plans gate is what proves the new plan avoids a scan or sort, so make sure it passes before merging.
• Optional: check_pull stays because another query uses it, so every run write now maintains two indexes on the pull. That's acceptable.