Skip to content

Commit 600751b

Browse files
joei4cmclaude
andcommitted
perf(server): the shell read no longer sorts every run of every thread
The thread-shell list finds each thread's last executed run with a correlated subquery ordered by completion time. No index carried that order, so SQLite sorted the thread's whole run history for every thread in the list. On a 200-thread fixture with one million runs the shell query took 214 ms; with the index it takes 2 ms and returns the same rows. Migration 83 adds a partial index on the runs table in the order that lookup reads, with the lookup's own predicate, so the shell query walks the index instead of sorting. The lookup keeps its semantics: same runs, same order. Refs #23000 Refs #9661 Co-Authored-By: Claude Code <[email protected]>
1 parent 3a30b01 commit 600751b

4 files changed

Lines changed: 48 additions & 0 deletions

File tree

‎apps/server/src/orchestration-v2/ProjectionStore.test.ts‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5206,6 +5206,32 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
52065206
}),
52075207
);
52085208

5209+
it.effect("reads a thread's last executed run through the last executed run index", () =>
5210+
Effect.gen(function* () {
5211+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
5212+
const sql = yield* SqlClient.SqlClient;
5213+
const { projectId } = yield* assertProjectScopedShells("sql-shell-last-executed-index");
5214+
const { statements, tracer } = traceSqlStatements();
5215+
yield* projectionStore
5216+
.getShellSnapshot({ projectId, location: "active" })
5217+
.pipe(Effect.withTracer(tracer));
5218+
5219+
const threadQuery = statements.find((statement) =>
5220+
statement.includes("AS latest_run_stop_requested"),
5221+
);
5222+
assert.isDefined(threadQuery);
5223+
const plan = (yield* sql.unsafe<{ readonly detail: string }>(
5224+
`EXPLAIN QUERY PLAN ${threadQuery}`,
5225+
)).map((row) => row.detail);
5226+
const lastExecutedIndex = plan.findIndex((detail) =>
5227+
detail.includes("orchestration_v2_projection_runs_last_executed_idx"),
5228+
);
5229+
assert.notStrictEqual(lastExecutedIndex, -1);
5230+
// The lookup that follows the search is the sort it no longer needs.
5231+
assert.notInclude(plan[lastExecutedIndex + 1] ?? "", "USE TEMP B-TREE");
5232+
}),
5233+
);
5234+
52095235
it.effect("reads a run's turn-start items by thread and run, not by walking thread history", () =>
52105236
Effect.gen(function* () {
52115237
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;

‎apps/server/src/persistence/Migrations.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,7 @@ import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
8585
import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
8686
import Migration0081 from "./Migrations/081_ProjectionMessageSearchText.ts";
8787
import Migration0082 from "./Migrations/082_TurnItemConversationIndex.ts";
88+
import Migration0083 from "./Migrations/083_LastExecutedRunIndex.ts";
8889

8990
const migrationEntries = [
9091
[1, "OrchestrationEvents", Migration0001],
@@ -170,6 +171,7 @@ const migrationEntries = [
170171
[80, "ScheduledTaskThreadMode", Migration0080],
171172
[81, "ProjectionMessageSearchText", Migration0081],
172173
[82, "TurnItemConversationIndex", Migration0082],
174+
[83, "LastExecutedRunIndex", Migration0083],
173175
] as const;
174176

175177
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
export default Effect.gen(function* () {
5+
const sql = yield* SqlClient.SqlClient;
6+
7+
// The shell's last executed run lookup sorts every run of the thread. This index carries
8+
// that order and the same predicate the lookup filters on. A database that already has
9+
// the index keeps it, so a hand-applied one does not fail the migration.
10+
yield* sql`
11+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_last_executed_idx
12+
ON orchestration_v2_projection_runs(
13+
thread_id, (completed_at IS NULL) DESC, completed_at DESC, ordinal DESC, run_id DESC
14+
)
15+
WHERE status <> 'queued'
16+
AND NOT (status = 'cancelled' AND json_extract(payload_json, '$.startedAt') IS NULL)
17+
`;
18+
});

‎apps/server/src/persistence/reconcileV2PreviewMigration.test.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@ describe("V2 preview upgrade", () => {
6262
[80, "ScheduledTaskThreadMode"],
6363
[81, "ProjectionMessageSearchText"],
6464
[82, "TurnItemConversationIndex"],
65+
[83, "LastExecutedRunIndex"],
6566
]);
6667
assert.deepStrictEqual(yield* runMigrations(), []);
6768
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -167,6 +168,7 @@ describe("V2 preview upgrade", () => {
167168
[80, "ScheduledTaskThreadMode"],
168169
[81, "ProjectionMessageSearchText"],
169170
[82, "TurnItemConversationIndex"],
171+
[83, "LastExecutedRunIndex"],
170172
]);
171173
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
172174
);

0 commit comments

Comments
 (0)