Skip to content

Commit 9bf8c50

Browse files
perf(server): migrate-dev-db legacy guard uses the V2 created-threads index
Without a hint SQLite answers the correlated NOT EXISTS with a per-thread stream index scan. On 3000 imported threads with 30000 events that took 150-270ms here and 20.7s in review; with the partial index the importer already names, it takes about 1.5ms. The index ships in the same migration as the V2 tables the guard checks for. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 4324451 commit 9bf8c50

1 file changed

Lines changed: 4 additions & 2 deletions

File tree

‎apps/server/scripts/migrate-dev-db.ts‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -251,12 +251,14 @@ const ensureNoPendingLegacyThreads = Effect.fn("ensureDevDbNoPendingLegacyThread
251251
AND name IN ('projection_threads', 'orchestration_v2_projection_threads')`;
252252
const names = new Set(tables.map((table) => table.name));
253253
if (!names.has("projection_threads")) return;
254-
// The V2 tables and application_event_version arrive in the same migration.
254+
// The V2 tables, application_event_version, and the partial index arrive in
255+
// the same migration. Without the hint SQLite scans each thread's stream.
255256
const [pending] = names.has("orchestration_v2_projection_threads")
256257
? yield* sql<{ count: number }>`
257258
SELECT COUNT(*) AS count FROM projection_threads AS thread
258259
WHERE NOT EXISTS (
259-
SELECT 1 FROM orchestration_events AS event
260+
SELECT 1
261+
FROM orchestration_events AS event INDEXED BY orchestration_events_v2_created_threads_idx
260262
WHERE event.application_event_version = 2
261263
AND event.aggregate_kind = 'thread'
262264
AND event.stream_id = thread.thread_id

0 commit comments

Comments
 (0)