Skip to content

Commit 134b2e6

Browse files
shaan sisodiashaan sisodia
authored andcommitted
perf(server): index slow projection lookups
1 parent 67c0fc0 commit 134b2e6

5 files changed

Lines changed: 155 additions & 1 deletion

File tree

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,7 @@ import Migration0063 from "./Migrations/063_DropUnusedV1Projections.ts";
6969
import Migration0064 from "./Migrations/064_CanonicalizeThreadPullRequestLinks.ts";
7070
import Migration0065 from "./Migrations/065_CodexUsageNativeTurnIndex.ts";
7171
import Migration0066 from "./Migrations/066_RepairClientSettlementTimestamps.ts";
72+
import Migration0067 from "./Migrations/067_OrchestrationSlowQueryIndexes.ts";
7273

7374
export const migrationEntries = [
7475
[1, "OrchestrationEvents", Migration0001],
@@ -138,6 +139,7 @@ export const migrationEntries = [
138139
[64, "CanonicalizeThreadPullRequestLinks", Migration0064],
139140
[65, "CodexUsageNativeTurnIndex", Migration0065],
140141
[66, "RepairClientSettlementTimestamps", Migration0066],
142+
[67, "OrchestrationSlowQueryIndexes", Migration0067],
141143
] as const;
142144

143145
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);

‎apps/server/src/persistence/Migrations/055_OrchestrationV2.test.ts‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ layer("055_OrchestrationV2", (it) => {
1515
Effect.sync(() => {
1616
assert.deepStrictEqual(
1717
migrationEntries.map(([id]) => id),
18-
Array.from({ length: 66 }, (_, index) => index + 1),
18+
Array.from({ length: 67 }, (_, index) => index + 1),
1919
);
2020
}),
2121
);
@@ -40,6 +40,7 @@ layer("055_OrchestrationV2", (it) => {
4040
[64, "CanonicalizeThreadPullRequestLinks"],
4141
[65, "CodexUsageNativeTurnIndex"],
4242
[66, "RepairClientSettlementTimestamps"],
43+
[67, "OrchestrationSlowQueryIndexes"],
4344
]);
4445
assert.deepStrictEqual(yield* runMigrations(), []);
4546

@@ -72,6 +73,7 @@ layer("055_OrchestrationV2", (it) => {
7273
{ migration_id: 64, name: "CanonicalizeThreadPullRequestLinks" },
7374
{ migration_id: 65, name: "CodexUsageNativeTurnIndex" },
7475
{ migration_id: 66, name: "RepairClientSettlementTimestamps" },
76+
{ migration_id: 67, name: "OrchestrationSlowQueryIndexes" },
7577
]);
7678

7779
const tables = yield* sql<{ readonly name: string }>`
Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,109 @@
1+
import { assert, it } from "@effect/vitest";
2+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
3+
import * as Effect from "effect/Effect";
4+
import * as SqlClient from "effect/sql/SqlClient";
5+
6+
import { runMigrations } from "../Migrations.ts";
7+
8+
const layer = it.layer(NodeSqliteClient.layer({ filename: ":memory:" }));
9+
10+
layer("067_OrchestrationSlowQueryIndexes", (it) => {
11+
it.effect("creates the indexes and the planner uses them for the slow queries", () =>
12+
Effect.gen(function* () {
13+
const sql = yield* SqlClient.SqlClient;
14+
yield* runMigrations({ toMigrationInclusive: 66 });
15+
yield* sql`
16+
INSERT INTO orchestration_v2_projection_turn_items (
17+
turn_item_id, thread_id, run_id, ordinal, type, status, payload_json, updated_at
18+
) VALUES (
19+
'item-1', 'thread-1', NULL, 1, 'user_message', 'completed',
20+
'{"messageId":"message-1"}', '2026-01-01'
21+
)
22+
`;
23+
const beforeMessages = yield* sql`
24+
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
25+
FROM orchestration_v2_projection_turn_items
26+
WHERE thread_id = 'thread-1' AND type IN ('user_message', 'assistant_message')
27+
GROUP BY json_extract(payload_json, '$.messageId')
28+
`;
29+
const beforeRunning = yield* sql<{ readonly detail: string }>`
30+
EXPLAIN QUERY PLAN
31+
SELECT thread_id FROM orchestration_v2_projection_runs WHERE status = 'running'
32+
`;
33+
assert.isFalse(
34+
beforeRunning.some((row) => row.detail.includes("runs_recovery_idx")),
35+
beforeRunning.map((row) => row.detail).join("\n"),
36+
);
37+
yield* runMigrations({ toMigrationInclusive: 67 });
38+
assert.deepEqual(yield* runMigrations(), []);
39+
40+
const afterRunning = yield* sql<{ readonly detail: string }>`
41+
EXPLAIN QUERY PLAN
42+
SELECT thread_id FROM orchestration_v2_projection_runs WHERE status = 'running'
43+
`;
44+
assert.isTrue(
45+
afterRunning.some((row) => row.detail.includes("runs_running_idx")),
46+
afterRunning.map((row) => row.detail).join("\n"),
47+
);
48+
const recoveryPlan = yield* sql<{ readonly detail: string }>`
49+
EXPLAIN QUERY PLAN
50+
SELECT thread_id FROM orchestration_v2_projection_runs
51+
WHERE thread_id = 'thread-1'
52+
AND status IN ('queued', 'preparing', 'starting', 'running', 'waiting')
53+
`;
54+
assert.isTrue(
55+
recoveryPlan.some((row) => row.detail.includes("runs_recovery_idx")),
56+
recoveryPlan.map((row) => row.detail).join("\n"),
57+
);
58+
59+
const rows = yield* sql<{ readonly name: string }>`
60+
SELECT name FROM sqlite_master WHERE type = 'index'
61+
`;
62+
const indexes = new Set(rows.map((row) => row.name));
63+
for (const name of [
64+
"orchestration_v2_projection_turn_items_thread_type_idx",
65+
"orchestration_v2_projection_turn_items_thread_message_idx",
66+
"orchestration_v2_projection_turn_items_run_status_idx",
67+
"orchestration_v2_projection_runs_running_idx",
68+
"orchestration_v2_projection_runs_user_message_idx",
69+
"orchestration_v2_projection_provider_turns_thread_completed_idx",
70+
"idx_orchestration_events_aggregate_version_sequence",
71+
]) {
72+
assert.isTrue(indexes.has(name), name);
73+
}
74+
75+
const afterMessages = yield* sql`
76+
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
77+
FROM orchestration_v2_projection_turn_items
78+
WHERE thread_id = 'thread-1' AND type IN ('user_message', 'assistant_message')
79+
GROUP BY json_extract(payload_json, '$.messageId')
80+
`;
81+
assert.deepEqual(afterMessages, beforeMessages);
82+
const messagePlan = yield* sql<{ readonly detail: string }>`
83+
EXPLAIN QUERY PLAN
84+
SELECT json_extract(payload_json, '$.messageId'), MIN(ordinal)
85+
FROM orchestration_v2_projection_turn_items
86+
WHERE thread_id = 'thread-1' AND type IN ('user_message', 'assistant_message')
87+
GROUP BY json_extract(payload_json, '$.messageId')
88+
`;
89+
assert.isTrue(
90+
messagePlan.some((row) =>
91+
row.detail.includes("orchestration_v2_projection_turn_items_thread_message_idx"),
92+
),
93+
messagePlan.map((row) => row.detail).join("\n"),
94+
);
95+
96+
const runPlan = yield* sql<{ readonly detail: string }>`
97+
EXPLAIN QUERY PLAN
98+
SELECT thread_id FROM orchestration_v2_projection_runs
99+
WHERE json_extract(payload_json, '$.userMessageId') = 'message-1'
100+
`;
101+
assert.isTrue(
102+
runPlan.some((row) =>
103+
row.detail.includes("orchestration_v2_projection_runs_user_message_idx"),
104+
),
105+
runPlan.map((row) => row.detail).join("\n"),
106+
);
107+
}),
108+
);
109+
});
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
// Each expression index repeats the json_extract text of the query it serves.
5+
// SQLite only uses an expression index when the query expression matches it.
6+
export default Effect.gen(function* () {
7+
const sql = yield* SqlClient.SqlClient;
8+
9+
yield* sql`
10+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_thread_type_idx
11+
ON orchestration_v2_projection_turn_items(thread_id, type)
12+
`;
13+
yield* sql`
14+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_thread_message_idx
15+
ON orchestration_v2_projection_turn_items(thread_id, json_extract(payload_json, '$.messageId'))
16+
WHERE type IN ('user_message', 'assistant_message')
17+
`;
18+
yield* sql`
19+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_run_status_idx
20+
ON orchestration_v2_projection_turn_items(run_id, status)
21+
`;
22+
yield* sql`
23+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_running_idx
24+
ON orchestration_v2_projection_runs(thread_id)
25+
WHERE status = 'running'
26+
`;
27+
yield* sql`
28+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_user_message_idx
29+
ON orchestration_v2_projection_runs(json_extract(payload_json, '$.userMessageId'))
30+
`;
31+
yield* sql`
32+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_provider_turns_thread_completed_idx
33+
ON orchestration_v2_projection_provider_turns(thread_id, completed_at DESC)
34+
`;
35+
yield* sql`
36+
CREATE INDEX IF NOT EXISTS idx_orchestration_events_aggregate_version_sequence
37+
ON orchestration_events(aggregate_kind, application_event_version, sequence)
38+
`;
39+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ describe("V2 preview upgrade", () => {
4646
[64, "CanonicalizeThreadPullRequestLinks"],
4747
[65, "CodexUsageNativeTurnIndex"],
4848
[66, "RepairClientSettlementTimestamps"],
49+
[67, "OrchestrationSlowQueryIndexes"],
4950
]);
5051
assert.deepStrictEqual(yield* runMigrations(), []);
5152
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -135,6 +136,7 @@ describe("V2 preview upgrade", () => {
135136
[64, "CanonicalizeThreadPullRequestLinks"],
136137
[65, "CodexUsageNativeTurnIndex"],
137138
[66, "RepairClientSettlementTimestamps"],
139+
[67, "OrchestrationSlowQueryIndexes"],
138140
]);
139141
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
140142
);

0 commit comments

Comments
 (0)