Skip to content

Commit 2a45683

Browse files
committed
fix(server): collect SQLite planner statistics when the database opens
The server never ran ANALYZE or PRAGMA optimize, so sqlite_stat1 never existed and the query planner had to guess on every database, however large. Run PRAGMA optimize=0x10012 after migrations so tables with unanalyzed indexes or 10x row growth get bounded, approximate statistics on open. Fixes #17955
1 parent 397a461 commit 2a45683

2 files changed

Lines changed: 34 additions & 0 deletions

File tree

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,36 @@ it.effect("shrinks the WAL file back to the size limit after a large write", ()
8484
);
8585
});
8686

87+
it.effect("collects planner statistics for an existing database on open", () => {
88+
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-stats-"));
89+
const dbPath = NodePath.join(tempDir, "state.sqlite");
90+
const persistence = () =>
91+
SqlitePersistence.layerFromPath(dbPath).pipe(Layer.provide(NodeServices.layer));
92+
93+
return Effect.gen(function* () {
94+
yield* Effect.gen(function* () {
95+
const sql = yield* SqlClient.SqlClient;
96+
yield* sql`CREATE TABLE stats_probe(id INTEGER PRIMARY KEY, thread_id TEXT NOT NULL)`;
97+
yield* sql`CREATE INDEX stats_probe_thread_idx ON stats_probe(thread_id)`;
98+
yield* sql`
99+
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000)
100+
INSERT INTO stats_probe(thread_id) SELECT 'thread-' || (i % 10) FROM n
101+
`;
102+
}).pipe(Effect.provide(persistence()));
103+
104+
// A database written before statistics existed gets them on the next open.
105+
yield* Effect.gen(function* () {
106+
const sql = yield* SqlClient.SqlClient;
107+
const rows = yield* sql<{ readonly stat: string }>`
108+
SELECT stat FROM sqlite_stat1 WHERE idx = 'stats_probe_thread_idx'
109+
`;
110+
assert.deepEqual([...rows], [{ stat: "1000 100" }]);
111+
}).pipe(Effect.provide(persistence()));
112+
}).pipe(
113+
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
114+
);
115+
});
116+
87117
it.effect("applies busy_timeout in the shared persistence setup", () =>
88118
Effect.gen(function* () {
89119
const sql = yield* SqlClient.SqlClient;

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,10 @@ const layerSetup = Layer.effectDiscard(
2323
// largest size until the last connection closes.
2424
yield* sql.unsafe(`PRAGMA journal_size_limit = ${WAL_SIZE_LIMIT_BYTES};`);
2525
yield* runMigrations();
26+
// Without sqlite_stat1 the planner guesses at index selectivity. 0x10012
27+
// analyzes tables whose indexes lack stats or whose row count changed 10x,
28+
// under a temporary analysis_limit. Otherwise it only checks table sizes.
29+
yield* sql`PRAGMA optimize = 0x10012;`;
2630
}),
2731
);
2832

0 commit comments

Comments
 (0)