Skip to content

Commit 5f2bb93

Browse files
perf(server): user-only message reads use the user-message index for ordinals (pingdotgg#33009)
1 parent 05020d6 commit 5f2bb93

2 files changed

Lines changed: 22 additions & 2 deletions

File tree

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

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -811,6 +811,23 @@ it.effect("record reads filter notification sources without dropping matching me
811811
matching.messages.map((message) => [message.id, message.text]),
812812
[["monitor", "Complete body for monitor"]],
813813
);
814+
const { statements, tracer } = traceSqlStatements();
815+
const userMessages = yield* store
816+
.getThreadRecords(threadId, ["messages"], { messageRoles: ["user"] })
817+
.pipe(Effect.withTracer(tracer));
818+
assert.lengthOf(userMessages.messages, 3);
819+
const ordinalLookup = statements.find((statement) => statement.includes("MIN(ordinal)"));
820+
assert.isDefined(ordinalLookup);
821+
const sql = yield* SqlClient.SqlClient;
822+
const plan = (yield* sql.unsafe<{ readonly detail: string }>(
823+
`EXPLAIN QUERY PLAN ${ordinalLookup}`,
824+
[threadId, "[]"],
825+
)).map((row) => row.detail);
826+
// User-only reads must stay on the partial index instead of scanning every turn item.
827+
assert.isTrue(
828+
plan.some((detail) => detail.includes("turn_items_user_message_idx")),
829+
plan.join("\n"),
830+
);
814831
assert.lengthOf((yield* store.getThreadRecords(threadId, ["messages"])).messages, 4);
815832
assert.lengthOf(
816833
(yield* store.getThreadRecords(threadId, ["messages"], {
@@ -3119,7 +3136,7 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
31193136
parent_item_id, ordinal, type, status, updated_at, payload_json
31203137
) VALUES (
31213138
${`turn-item:bounded-sql-history:ordinal-${suffix}`}, ${itemThreadId}, NULL, NULL, NULL,
3122-
NULL, NULL, ${ordinal}, 'assistant_message', 'completed', ${nowIso},
3139+
NULL, NULL, ${ordinal}, 'user_message', 'completed', ${nowIso},
31233140
${encodeUnknownJsonString({ messageId })}
31243141
)
31253142
`;

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3834,10 +3834,13 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
38343834
(filter.messageRoles.includes("user") && filter.messageRoles.includes("assistant")))
38353835
? undefined
38363836
: messages.map((message) => message.id);
3837+
// The literal `type = 'user_message'` lets SQLite pick the partial user-message index.
3838+
const userOnly = messages.every((message) => message.role === "user");
38373839
const rows = yield* sql<{ message_id: string; ordinal: number }>`
38383840
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
38393841
FROM orchestration_v2_projection_turn_items
3840-
WHERE thread_id = ${threadId} AND type IN ('user_message', 'assistant_message')
3842+
WHERE thread_id = ${threadId}
3843+
AND ${userOnly ? sql`type = 'user_message'` : sql`type IN ('user_message', 'assistant_message')`}
38413844
${loadedMessageIds === undefined ? sql`` : sql`AND json_extract(payload_json, '$.messageId') IN (SELECT value FROM json_each(${encodeIdList(loadedMessageIds)}))`}
38423845
GROUP BY json_extract(payload_json, '$.messageId')
38433846
`;

0 commit comments

Comments
 (0)