Skip to content

Commit e7b5c1d

Browse files
braulioboclaude
andcommitted
perf(server): reads of a long thread no longer touch every row
On a thread with tens of thousands of items, the shell item count looked up each run in the main runs table, the command read walked every item to find user messages and errors, and the provider-thread read looked each node up through a non-covering index. Add compact covering indexes for those lookups and hint them where the planner prefers the primary key or the ordinal index. Co-Authored-By: Claude Sonnet 5.5 <[email protected]>
1 parent 676afc4 commit e7b5c1d

5 files changed

Lines changed: 184 additions & 8 deletions

File tree

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

Lines changed: 141 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6605,8 +6605,10 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
66056605
readonly detail: string;
66066606
}>(`EXPLAIN QUERY PLAN ${statement}`);
66076607
// With no live run, a lookup that walks the ordinal index reads every run the thread has had.
6608-
const liveRunLookups = plan.filter((row) =>
6609-
row.detail.startsWith("SEARCH r USING INDEX orchestration_v2_projection_runs_"),
6608+
const liveRunLookups = plan.filter(
6609+
(row) =>
6610+
row.detail.startsWith("SEARCH r USING INDEX orchestration_v2_projection_runs_") &&
6611+
row.detail.includes("(thread_id=?"),
66106612
);
66116613
assert.lengthOf(liveRunLookups, 4);
66126614
assert.isFalse(plan.some((row) => /^SCAN (?:r|candidate)\b/.test(row.detail)));
@@ -6630,6 +6632,143 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
66306632
}),
66316633
);
66326634

6635+
it.effect("reads a long thread's records for a command without touching every row", () =>
6636+
Effect.gen(function* () {
6637+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
6638+
const sql = yield* SqlClient.SqlClient;
6639+
const suffix = "command-long-history";
6640+
const threadId = ThreadId.make(`thread:${suffix}`);
6641+
const now = yield* DateTime.now;
6642+
yield* projectionStore.apply({
6643+
id: EventId.make(`event:${suffix}:thread-created`),
6644+
type: "thread.created",
6645+
threadId,
6646+
occurredAt: now,
6647+
payload: {
6648+
createdBy: "user",
6649+
creationSource: "web",
6650+
id: threadId,
6651+
projectId: ProjectId.make(`project:${suffix}`),
6652+
title: "Long command history",
6653+
providerInstanceId,
6654+
modelSelection,
6655+
runtimeMode: "full-access",
6656+
interactionMode: "default",
6657+
branch: null,
6658+
worktreePath: null,
6659+
activeProviderThreadId: null,
6660+
lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: threadId },
6661+
forkedFrom: null,
6662+
createdAt: now,
6663+
updatedAt: now,
6664+
archivedAt: null,
6665+
settledOverride: null,
6666+
settledAt: null,
6667+
lastVisitedAt: null,
6668+
deletedAt: null,
6669+
},
6670+
});
6671+
for (const ordinal of [1, 2]) {
6672+
const messageId = MessageId.make(`message:${suffix}:${ordinal}`);
6673+
yield* projectionStore.apply({
6674+
id: EventId.make(`event:${suffix}:message-${ordinal}`),
6675+
type: "message.updated",
6676+
threadId,
6677+
occurredAt: now,
6678+
payload: {
6679+
id: messageId,
6680+
threadId,
6681+
runId: null,
6682+
nodeId: null,
6683+
role: "user",
6684+
text: `prompt ${ordinal}`,
6685+
attachments: [],
6686+
streaming: false,
6687+
createdBy: "user",
6688+
creationSource: "web",
6689+
createdAt: now,
6690+
updatedAt: now,
6691+
},
6692+
});
6693+
yield* projectionStore.apply({
6694+
id: EventId.make(`event:${suffix}:item-${ordinal}`),
6695+
type: "turn-item.updated",
6696+
threadId,
6697+
occurredAt: now,
6698+
payload: {
6699+
id: TurnItemId.make(`${suffix}:item:${ordinal}`),
6700+
threadId,
6701+
runId: null,
6702+
nodeId: null,
6703+
providerThreadId: null,
6704+
providerTurnId: null,
6705+
nativeItemRef: null,
6706+
parentItemId: null,
6707+
ordinal,
6708+
status: "completed",
6709+
title: "prompt",
6710+
startedAt: now,
6711+
completedAt: now,
6712+
updatedAt: now,
6713+
type: "user_message",
6714+
createdBy: "user",
6715+
creationSource: "web",
6716+
messageId,
6717+
inputIntent: "turn_start",
6718+
text: `prompt ${ordinal}`,
6719+
attachments: [],
6720+
},
6721+
});
6722+
}
6723+
6724+
const { statements, tracer } = traceSqlStatements();
6725+
yield* projectionStore
6726+
.getThreadRecords(threadId, ["runs", "nodes", "providerThreads", "messages", "turnItems"], {
6727+
turnItemTypes: ["user_message", "error"],
6728+
messageRoles: ["user"],
6729+
omitSettledLeafNodes: true,
6730+
omitSettledSubagents: true,
6731+
})
6732+
.pipe(Effect.withTracer(tracer));
6733+
yield* projectionStore.getThreadShell(threadId).pipe(Effect.withTracer(tracer));
6734+
const planOf = Effect.fnUntraced(function* (marker: string) {
6735+
const statement = statements.find((query) => query.includes(marker));
6736+
assert.isDefined(statement, marker);
6737+
return (yield* sql.unsafe<{ readonly detail: string }>(
6738+
`EXPLAIN QUERY PLAN ${statement}`,
6739+
)).map((row) => row.detail);
6740+
});
6741+
// Each read must reach only the rows it returns; a thread can hold tens of thousands.
6742+
const expectations = [
6743+
{
6744+
marker: "(type || '') IN (SELECT value FROM json_each",
6745+
uses: "turn_items_thread_type_idx (<expr>=? AND <expr>=?)",
6746+
avoids: "turn_items_thread_ordinal_idx",
6747+
},
6748+
{
6749+
marker: "MIN(ordinal) AS ordinal",
6750+
uses: "turn_items_thread_type_idx (<expr>=? AND <expr>=?)",
6751+
avoids: "turn_items_thread_run_idx",
6752+
},
6753+
{
6754+
marker: "owner_node_id IN (",
6755+
uses: "COVERING INDEX orchestration_v2_projection_nodes_thread_node_idx (<expr>=?)",
6756+
avoids: "nodes_thread_run_idx",
6757+
},
6758+
{
6759+
marker: "AS runless_item_count",
6760+
uses: "r USING COVERING INDEX orchestration_v2_projection_runs_id_status_idx (<expr>=?)",
6761+
avoids: "sqlite_autoindex_orchestration_v2_projection_runs_1",
6762+
},
6763+
] as const;
6764+
for (const { marker, uses, avoids } of expectations) {
6765+
const plan = (yield* planOf(marker)).join("\n");
6766+
assert.include(plan, uses, marker);
6767+
assert.notInclude(plan, avoids, marker);
6768+
}
6769+
}),
6770+
);
6771+
66336772
it.effect("selects the latest waiting secret only from active runs", () =>
66346773
Effect.gen(function* () {
66356774
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;

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

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3139,8 +3139,14 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
31393139
? yield* sql<PayloadRow>`
31403140
SELECT payload_json
31413141
FROM orchestration_v2_projection_turn_items
3142-
WHERE thread_id = ${threadId}
3143-
${filter?.turnItemTypes === undefined ? sql`` : sql`AND type IN (SELECT value FROM json_each(${encodeIdList(filter.turnItemTypes)}))`}
3142+
${
3143+
// The ordinal index would read every item of the thread to find these types.
3144+
filter?.turnItemTypes === undefined
3145+
? sql``
3146+
: sql`INDEXED BY orchestration_v2_projection_turn_items_thread_type_idx`
3147+
}
3148+
WHERE ${filter?.turnItemTypes === undefined ? sql`thread_id = ${threadId}` : sql`(thread_id || '') = ${threadId}`}
3149+
${filter?.turnItemTypes === undefined ? sql`` : sql`AND (type || '') IN (SELECT value FROM json_each(${encodeIdList(filter.turnItemTypes)}))`}
31443150
${filter?.turnItemStatuses === undefined ? sql`` : sql`AND status IN (SELECT value FROM json_each(${encodeIdList(filter.turnItemStatuses)}))`}
31453151
${filter?.turnItemRunId === undefined ? sql`` : sql`AND run_id = ${filter.turnItemRunId}`}
31463152
${filter?.turnItemNodeIds === undefined ? sql`` : sql`AND node_id IN (SELECT value FROM json_each(${encodeIdList(filter.turnItemNodeIds)}))`}
@@ -3493,9 +3499,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
34933499
FROM orchestration_v2_projection_provider_threads
34943500
WHERE (thread_id = ${threadId}
34953501
OR owner_node_id IN (
3496-
SELECT node_id
3502+
SELECT node_id || ''
34973503
FROM orchestration_v2_projection_nodes
3498-
WHERE thread_id = ${threadId}
3504+
INDEXED BY orchestration_v2_projection_nodes_thread_node_idx
3505+
WHERE (thread_id || '') = ${threadId}
34993506
)
35003507
OR provider_thread_id IN (
35013508
SELECT provider_thread_id
@@ -3732,7 +3739,8 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
37323739
const rows = yield* sql<{ message_id: string; ordinal: number }>`
37333740
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
37343741
FROM orchestration_v2_projection_turn_items
3735-
WHERE thread_id = ${threadId} AND type IN ('user_message', 'assistant_message')
3742+
INDEXED BY orchestration_v2_projection_turn_items_thread_type_idx
3743+
WHERE (thread_id || '') = ${threadId} AND (type || '') IN ('user_message', 'assistant_message')
37363744
${loadedMessageIds === undefined ? sql`` : sql`AND json_extract(payload_json, '$.messageId') IN (SELECT value FROM json_each(${encodeIdList(loadedMessageIds)}))`}
37373745
GROUP BY json_extract(payload_json, '$.messageId')
37383746
`;
@@ -6398,7 +6406,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
63986406
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
63996407
GROUP BY thread_id, run_id
64006408
) counts
6401-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_id
6409+
-- The covering index answers each run from a compact page set instead of the runs table.
6410+
LEFT JOIN orchestration_v2_projection_runs r
6411+
INDEXED BY orchestration_v2_projection_runs_id_status_idx
6412+
ON (r.run_id || '') = (counts.run_id || '')
64026413
GROUP BY counts.thread_id
64036414
`;
64046415

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,7 @@ import Migration0078 from "./Migrations/078_DropOrchestrationEventCorrelationInd
8484
import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
8585
import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
8686
import Migration0081 from "./Migrations/081_ProjectionRunsExecutedIndex.ts";
87+
import Migration0082 from "./Migrations/082_CommandReadIndexes.ts";
8788

8889
const migrationEntries = [
8990
[1, "OrchestrationEvents", Migration0001],
@@ -168,6 +169,7 @@ const migrationEntries = [
168169
[79, "DelegatedRecoveryIndexes", Migration0079],
169170
[80, "ScheduledTaskThreadMode", Migration0080],
170171
[81, "ProjectionRunsExecutedIndex", Migration0081],
172+
[82, "CommandReadIndexes", Migration0082],
171173
] as const;
172174

173175
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
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+
// Each lets a read of one long thread skip the rows it does not return. Every column a query
8+
// could match is wrapped in `|| ''`, so only a lookup that writes the same expression can use
9+
// them. Plain indexes were chosen for unrelated statements over their partial indexes.
10+
yield* sql`
11+
CREATE INDEX orchestration_v2_projection_turn_items_thread_type_idx
12+
ON orchestration_v2_projection_turn_items((thread_id || ''), (type || ''), ordinal, turn_item_id)
13+
`;
14+
yield* sql`
15+
CREATE INDEX orchestration_v2_projection_nodes_thread_node_idx
16+
ON orchestration_v2_projection_nodes((thread_id || ''), (node_id || ''))
17+
`;
18+
yield* sql`
19+
CREATE INDEX orchestration_v2_projection_runs_id_status_idx
20+
ON orchestration_v2_projection_runs((run_id || ''), status)
21+
`;
22+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@ describe("V2 preview upgrade", () => {
6161
[79, "DelegatedRecoveryIndexes"],
6262
[80, "ScheduledTaskThreadMode"],
6363
[81, "ProjectionRunsExecutedIndex"],
64+
[82, "CommandReadIndexes"],
6465
]);
6566
assert.deepStrictEqual(yield* runMigrations(), []);
6667
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -165,6 +166,7 @@ describe("V2 preview upgrade", () => {
165166
[79, "DelegatedRecoveryIndexes"],
166167
[80, "ScheduledTaskThreadMode"],
167168
[81, "ProjectionRunsExecutedIndex"],
169+
[82, "CommandReadIndexes"],
168170
]);
169171
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
170172
);

0 commit comments

Comments
 (0)