Summary
DbReader.LoadColumns and LoadIndexes execute PRAGMA table_info / PRAGMA index_list on every reader open. For short-running queries (callers, definition, excerpt) these schema-introspection round-trips dominate startup latency, and the results are essentially immutable for the lifetime of the database file (the schema changes only on writer-side migrations).
Where
src/CodeIndex/Database/DbReader.cs:242-245 (LoadColumns called per reader)src/CodeIndex/Database/DbReader.cs:329-343 (LoadIndexes called per reader)src/CodeIndex/Database/DbContext.cs (no shared schema cache keyed by db path + schema generation)
Suggested approach
(1) Add a process-level ConcurrentDictionary<string, SchemaSnapshot> keyed by the absolute DB path. (2) Invalidate the snapshot when the writer increments a schema_generation counter (new metadata row written under a migration). (3) On reader open, reuse the snapshot if the generation matches — only run PRAGMA table_info/index_list on miss. (4) Add a benchmark that measures wall time for 100 cold cdidx callers invocations against a non-trivial DB; expect a measurable improvement. (5) Cover edge cases: external sqlite3 shell mutations bypass the writer counter — document that and recommend cdidx validate after manual schema edits.
Summary
DbReader.LoadColumnsandLoadIndexesexecutePRAGMA table_info/PRAGMA index_liston every reader open. For short-running queries (callers,definition,excerpt) these schema-introspection round-trips dominate startup latency, and the results are essentially immutable for the lifetime of the database file (the schema changes only on writer-side migrations).Where
src/CodeIndex/Database/DbReader.cs:242-245(LoadColumns called per reader)src/CodeIndex/Database/DbReader.cs:329-343(LoadIndexes called per reader)src/CodeIndex/Database/DbContext.cs(no shared schema cache keyed by db path + schema generation)Suggested approach
(1) Add a process-level
ConcurrentDictionary<string, SchemaSnapshot>keyed by the absolute DB path. (2) Invalidate the snapshot when the writer increments aschema_generationcounter (new metadata row written under a migration). (3) On reader open, reuse the snapshot if the generation matches — only runPRAGMA table_info/index_liston miss. (4) Add a benchmark that measures wall time for 100 coldcdidx callersinvocations against a non-trivial DB; expect a measurable improvement. (5) Cover edge cases: externalsqlite3shell mutations bypass the writer counter — document that and recommendcdidx validateafter manual schema edits.