Skip to content

Commit 6cb3e96

Browse files
t3dotggclaude
andcommitted
perf(server): record reads by id use the primary key, not the thread index
A read of one run, node, task or message by id walked every row of the thread in its thread index. On a thread with 34,195 nodes, one node read took 307,792 SQLite VM steps. It now takes 42. Co-Authored-By: Claude Fable 5.2 <[email protected]>
1 parent 82619ec commit 6cb3e96

2 files changed

Lines changed: 33 additions & 4 deletions

File tree

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

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -717,6 +717,30 @@ it.effect("idle compaction filters keep the latest run, live work and the newest
717717
.join("\n");
718718
assert.notInclude(runPlan, "orchestration_v2_projection_runs_thread_ordinal_idx");
719719
assert.match(runPlan, /SEARCH orchestration_v2_projection_runs USING INDEX \w*status\w*/);
720+
// An id filter reads by primary key, not every row of the thread.
721+
const byId = traceSqlStatements();
722+
const found = yield* store
723+
.getThreadRecords(threadId, ["runs", "nodes", "subagents", "messages"], {
724+
runIds: [RunId.make("run:idle-compaction:4")],
725+
nodeIds: [nodeId],
726+
subagentIds: [nodeId],
727+
messageIds: [MessageId.make("message:idle-compaction:4")],
728+
})
729+
.pipe(Effect.withTracer(byId.tracer));
730+
assert.deepEqual(
731+
found.runs.map((run) => run.id),
732+
["run:idle-compaction:4"],
733+
);
734+
for (const table of ["runs", "nodes", "subagents", "messages"]) {
735+
const query = byId.statements.find((statement) =>
736+
statement.includes(`FROM orchestration_v2_projection_${table}`),
737+
);
738+
assert.isDefined(query);
739+
const plan = (yield* sql.unsafe<{ readonly detail: string }>(`EXPLAIN QUERY PLAN ${query}`))
740+
.map((row) => row.detail)
741+
.join("\n");
742+
assert.include(plan, `USING INDEX sqlite_autoindex_orchestration_v2_projection_${table}_1`);
743+
}
720744
assert.deepEqual(
721745
records.providerTurns.map((turn) => turn.id),
722746
["turn:idle-newest"],

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

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3168,6 +3168,11 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
31683168
`;
31693169
});
31703170

3171+
// An id filter reads by primary key. `+` keeps SQLite off the thread index, which walks
3172+
// every row of the thread to find the few ids.
3173+
const threadColumn = (ids: ReadonlyArray<unknown> | undefined, column = "thread_id") =>
3174+
sql.literal(ids === undefined ? column : `+${column}`);
3175+
31713176
const readCanonicalProjection = Effect.fn("ProjectionStore.readCanonicalProjection")(
31723177
function* (
31733178
threadId: ThreadId,
@@ -3401,7 +3406,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
34013406
? sql<PayloadRow>`
34023407
SELECT payload_json
34033408
FROM orchestration_v2_projection_runs
3404-
WHERE thread_id = ${threadId}
3409+
WHERE ${threadColumn(filter?.runIds)} = ${threadId}
34053410
${filter?.runIds === undefined ? sql`` : sql`AND run_id IN (SELECT value FROM json_each(${encodeIdList(filter.runIds)}))`}
34063411
${
34073412
filter?.runStatuses === undefined
@@ -3470,7 +3475,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
34703475
? sql<PayloadRow>`
34713476
SELECT payload_json
34723477
FROM orchestration_v2_projection_nodes AS node
3473-
WHERE node.thread_id = ${threadId}
3478+
WHERE ${threadColumn(filter?.nodeIds, "node.thread_id")} = ${threadId}
34743479
${filter?.nodeIds === undefined ? sql`` : sql`AND node.node_id IN (SELECT value FROM json_each(${encodeIdList(filter.nodeIds)}))`}
34753480
${
34763481
filter?.omitSettledLeafNodes === true
@@ -3509,7 +3514,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
35093514
? sql<PayloadRow>`
35103515
SELECT payload_json
35113516
FROM orchestration_v2_projection_subagents AS subagent
3512-
WHERE thread_id = ${threadId}
3517+
WHERE ${threadColumn(filter?.subagentIds)} = ${threadId}
35133518
${filter?.subagentIds === undefined ? sql`` : sql`AND subagent_id IN (SELECT value FROM json_each(${encodeIdList(filter.subagentIds)}))`}
35143519
${filter?.subagentRunIds === undefined ? sql`` : sql`AND run_id IN (SELECT value FROM json_each(${encodeIdList(filter.subagentRunIds)}))`}
35153520
${
@@ -3644,7 +3649,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
36443649
: window === undefined
36453650
? sql<PayloadRow>`
36463651
SELECT payload_json FROM orchestration_v2_projection_messages
3647-
WHERE thread_id = ${threadId}
3652+
WHERE ${threadColumn(filter?.messageIds)} = ${threadId}
36483653
${filter?.messageIds === undefined ? sql`` : sql`AND message_id IN (SELECT value FROM json_each(${encodeIdList(filter.messageIds)}))`}
36493654
${filter?.messageRoles === undefined ? sql`` : sql`AND role IN (SELECT value FROM json_each(${encodeIdList(filter.messageRoles)}))`}
36503655
${filter?.monitorNotificationsOnly === true ? sql`AND json_extract(payload_json, '$.notification.source.kind') = 'monitor'` : sql``}

0 commit comments

Comments
 (0)