Sampled PRAGMA optimize (analysis_limit=1000) leaves stale stats → getDominantFile picks a quadratic plan, explore ~100s on large repos #2571

Summary

After a full codegraph index, runMaintenance() runs PRAGMA analysis_limit=1000 + PRAGMA optimize. On our repo the sampled statistics for idx_nodes_file_path come out as 280356 6 (≈6 nodes per file), while a full ANALYZE gives 280356 32. With the underestimate, SQLite plans the getDominantFile aggregation as "for every node, scan all nodes in the same file, then probe edges by (source,target)", which is O(Σ nodes_per_file²). A few large files (an Android R.java with 7,728 nodes, a .d.ts with 3,573) blow that up, so every generic explore costs ~100 s.

A plain full ANALYZE (0.77 s on this DB) fixes it: explore drops to ~1.5 s.

Related: #1864 (closed by #2086). That fix memoizes the result per process, which doesn't help here:

• CLI codegraph explore and codegraph prompt-hook spawn a fresh process per call, so they pay the full cost every time (prompt-hook ≈ 43 s per user prompt).
• In MCP mode the first explore still takes ~100 s, so concurrent calls during that window get "CodeGraph is busy … waited 45s in the queue".

Environment

• codegraph 1.6.2 (also reproduced with 1.6.0), Windows 11, node:sqlite backend
• Index: 9,128 files, 280,356 nodes, 671,472 edges, DB ≈ 1.05 GB (C# / JS / Java monorepo)

Evidence

CPU profile (node --cpu-prof on codegraph.js explore …): 44.2 s of a 44.8 s run is all in sqlite-adapter.js under getDominantFile (queries.js).

Same SQL, same DB, only the stats differ:

Toggling reproduces it deterministically:

db.exec('PRAGMA analysis_limit=1000'); db.exec('ANALYZE'); // stat → "280356 6", quadratic plan
db.exec('PRAGMA analysis_limit=0');    db.exec('ANALYZE'); // stat → "280356 32", linear plan

For comparison, another repo of ours (6,494 files, 408k edges, 1.6.2) gets an edges-first plan (SCAN e USING COVERING INDEX idx_edges_identity → PK lookups) and explores in ~1 s, so it depends on how the sample lands.

The largest files by node count in the slow repo:

7728  …/app/src/main/java/…/R.java
3989  …/R.java
3875  …/R.java
3573  …/DrawingWeb.d.ts
3092  …/OrganizationDataService.cs

Workaround

node -e "const {DatabaseSync}=require('node:sqlite');const db=new DatabaseSync('.codegraph/codegraph.db');db.exec('ANALYZE');db.close()"

It has to be repeated after every full re-index (manual codegraph index, or the automatic rebuild after an extraction-version bump on upgrade).

Suggested fixes (any one would do)

1. After indexAll, run a full ANALYZE (or at least ANALYZE nodes; ANALYZE edges;) instead of the sampled optimize. It took 0.77 s on a 1 GB DB.
2. Make the getDominantFile query plan-proof, e.g. force edges as the outer loop with CROSS JOIN so the planner can't reorder:

   SELECT n.file_path, COUNT(*) AS edge_count
   FROM edges e
   CROSS JOIN nodes n ON e.source = n.id
   CROSS JOIN nodes m ON e.target = m.id
   WHERE n.file_path = m.file_path
   GROUP BY n.file_path ORDER BY edge_count DESC LIMIT 20

   Tested on the same DB with the bad 280356 6 stats in place: plan becomes SCAN e USING COVERING INDEX idx_edges_identity → PK lookups on n and m, 2.7 s, same top result.
3. Persist the dominant-file result in project_metadata at index/sync time, so short-lived CLI / prompt-hook processes don't recompute it.