Skip to content

Commit 2d8a843

Browse files
perf(server): shell reads of a worktree or family no longer parse every thread payload (#36146)
1 parent 4aec277 commit 2d8a843

5 files changed

Lines changed: 64 additions & 11 deletions

File tree

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

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2807,11 +2807,25 @@ it.effect("SQL shells read a subagent family and a worktree's threads", () =>
28072807
assert.deepEqual([...(yield* family(id("missing")))], []);
28082808

28092809
// Includes another root's idle child on the same path, which a shell may leave out.
2810-
const onWorktree = yield* store.getShellSnapshot({ location: "active", worktreePath });
2810+
const { statements, tracer } = traceSqlStatements();
2811+
const onWorktree = yield* store
2812+
.getShellSnapshot({ location: "active", worktreePath })
2813+
.pipe(Effect.withTracer(tracer));
28112814
assert.sameMembers(
28122815
onWorktree.threads.map((thread) => thread.id),
28132816
[id("root"), id("child"), id("other-child")],
28142817
);
2818+
// A path names a few threads; scanning every thread parses every payload.
2819+
const sql = yield* SqlClient.SqlClient;
2820+
const threadQuery = statements.find((statement) =>
2821+
statement.includes("AS latest_run_stop_requested"),
2822+
);
2823+
assert.isDefined(threadQuery);
2824+
const threadPlan = (yield* sql.unsafe<{ readonly detail: string }>(
2825+
`EXPLAIN QUERY PLAN ${threadQuery}`,
2826+
[],
2827+
)).map((row) => row.detail);
2828+
assert.notInclude(threadPlan, "SCAN t", threadPlan.join("\n"));
28152829
// A fork keeps its deleted source's root, so the deleted root still names the live tree.
28162830
yield* store.apply({
28172831
id: EventId.make("event:family:root-deleted"),
@@ -6629,6 +6643,10 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
66296643
assert.isNotEmpty(countQueries);
66306644
for (const countQuery of countQueries) {
66316645
for (const detail of yield* planOf(countQuery)) {
6646+
// A thread's counts read only each run's status, never its row and payload.
6647+
if (/^SEARCH (r|run) /.test(detail)) {
6648+
assert.include(detail, "COVERING INDEX", detail);
6649+
}
66326650
if (detail.includes("orchestration_v2_projection_turn_items")) {
66336651
// Only held input is read from the sparse index; transcript history stays unread.
66346652
assert.include(

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

Lines changed: 15 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -7666,12 +7666,11 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
76667666
(
76677667
SELECT item.payload_json
76687668
FROM orchestration_v2_projection_turn_items item
7669-
INNER JOIN orchestration_v2_projection_runs r ON r.run_id = item.run_id
7670-
WHERE r.run_id = presented.run_id
7671-
AND r.status = 'failed'
7669+
WHERE presented.status = 'failed'
76727670
AND item.thread_id = t.thread_id
7671+
AND item.run_id = presented.run_id
76737672
AND item.type = 'error' AND item.status = 'failed'
7674-
AND item.node_id IS json_extract(r.payload_json, '$.rootNodeId')
7673+
AND item.node_id IS json_extract(presented.payload_json, '$.rootNodeId')
76757674
ORDER BY item.updated_at DESC, item.ordinal DESC, item.turn_item_id DESC
76767675
LIMIT 1
76777676
) AS terminal_failure_payload_json,
@@ -7789,9 +7788,9 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
77897788
projectId === undefined ? sql`` : sql` AND t.project_id = ${projectId}`
77907789
}${
77917790
location === "active"
7792-
? sql` AND json_extract(t.payload_json, '$.archivedAt') IS NULL`
7791+
? sql` AND t.archived_at IS NULL`
77937792
: location === "archive"
7794-
? sql` AND json_extract(t.payload_json, '$.archivedAt') IS NOT NULL`
7793+
? sql` AND t.archived_at IS NOT NULL`
77957794
: sql``
77967795
}${
77977796
unsettledOnly
@@ -7800,15 +7799,17 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78007799
}${
78017800
familyOf === undefined
78027801
? sql``
7803-
: sql` AND json_extract(t.payload_json, '$.lineage.rootThreadId') = (
7802+
: sql` AND CASE WHEN json_valid(t.payload_json)
7803+
THEN json_extract(t.payload_json, '$.lineage.rootThreadId') END = (
78047804
SELECT json_extract(member.payload_json, '$.lineage.rootThreadId')
78057805
FROM orchestration_v2_projection_threads member
78067806
WHERE member.thread_id = ${familyOf}
78077807
)`
78087808
}${
78097809
worktreePath === undefined
78107810
? sql``
7811-
: sql` AND json_extract(t.payload_json, '$.worktreePath') = ${worktreePath}`
7811+
: sql` AND CASE WHEN json_valid(t.payload_json)
7812+
THEN json_extract(t.payload_json, '$.worktreePath') END = ${worktreePath}`
78127813
}${threadIds === undefined ? sql`` : sql` AND t.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))`}
78137814
ORDER BY t.updated_at ASC, t.thread_id ASC
78147815
`;
@@ -7836,6 +7837,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78367837
WHEN EXISTS (
78377838
SELECT 1
78387839
FROM orchestration_v2_projection_runs AS run
7840+
INDEXED BY orchestration_v2_projection_runs_run_status_idx
78397841
WHERE run.run_id = per_run.run_key
78407842
AND (
78417843
run.status IN ('queued', 'cancelled')
@@ -7882,7 +7884,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78827884
SUM(CASE WHEN counts.run_key = '' THEN counts.item_count ELSE 0 END)
78837885
AS runless_item_count
78847886
FROM orchestration_v2_projection_turn_item_counts counts
7885-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_key
7887+
-- The primary key would load each run's whole row just for its status.
7888+
LEFT JOIN orchestration_v2_projection_runs r
7889+
INDEXED BY orchestration_v2_projection_runs_run_status_idx
7890+
ON r.run_id = counts.run_key
78867891
WHERE counts.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
78877892
GROUP BY counts.thread_id
78887893
`;
@@ -8742,7 +8747,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
87428747
) AS has_remote_tasks
87438748
FROM orchestration_v2_projection_threads t
87448749
WHERE t.deleted_at IS NULL
8745-
AND json_extract(t.payload_json, '$.archivedAt') IS NULL
8750+
AND t.archived_at IS NULL
87468751
ORDER BY t.updated_at ASC, t.thread_id ASC
87478752
`;
87488753
return rows.map((row): ActiveShellIndexEntry => ({

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,7 @@ import Migration0085 from "./Migrations/085_TurnItemCounts.ts";
9696
import Migration0086 from "./Migrations/086_QueuedTurnInputIndex.ts";
9797
import Migration0087 from "./Migrations/087_ProjectionProjectsCustomTitle.ts";
9898
import Migration0088 from "./Migrations/088_TurnItemTypeStatusIndex.ts";
99+
import Migration0089 from "./Migrations/089_ShellThreadReadIndexes.ts";
99100

100101
const migrationEntries = [
101102
[1, "OrchestrationEvents", Migration0001],
@@ -187,6 +188,7 @@ const migrationEntries = [
187188
[86, "QueuedTurnInputIndex", Migration0086],
188189
[87, "ProjectionProjectsCustomTitle", Migration0087],
189190
[88, "TurnItemTypeStatusIndex", Migration0088],
191+
[89, "ShellThreadReadIndexes", Migration0089],
190192
] as const;
191193

192194
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
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+
// Keep these expressions identical to ProjectionStore's shell worktree and family filters.
8+
// json_valid keeps a malformed payload writable, as the other payload indexes do.
9+
yield* sql`
10+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_threads_worktree_path_idx
11+
ON orchestration_v2_projection_threads(
12+
CASE WHEN json_valid(payload_json) THEN json_extract(payload_json, '$.worktreePath') END
13+
)
14+
`;
15+
yield* sql`
16+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_threads_root_thread_idx
17+
ON orchestration_v2_projection_threads(
18+
CASE WHEN json_valid(payload_json) THEN json_extract(payload_json, '$.lineage.rootThreadId') END
19+
)
20+
`;
21+
// Shell item counts read only a run's status; the primary key would load the whole row.
22+
yield* sql`
23+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_run_status_idx
24+
ON orchestration_v2_projection_runs(run_id, status)
25+
`;
26+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@ describe("V2 preview upgrade", () => {
6868
[86, "QueuedTurnInputIndex"],
6969
[87, "ProjectionProjectsCustomTitle"],
7070
[88, "TurnItemTypeStatusIndex"],
71+
[89, "ShellThreadReadIndexes"],
7172
]);
7273
assert.deepStrictEqual(yield* runMigrations(), []);
7374
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -179,6 +180,7 @@ describe("V2 preview upgrade", () => {
179180
[86, "QueuedTurnInputIndex"],
180181
[87, "ProjectionProjectsCustomTitle"],
181182
[88, "TurnItemTypeStatusIndex"],
183+
[89, "ShellThreadReadIndexes"],
182184
]);
183185
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
184186
);

0 commit comments

Comments
 (0)