h-dv/code-index

Watch

Fork 0

symbol_edges has no index on to_id and symbols has none on ref_count, so reverse-edge queries full-scan #143

New issue

Closed

opened 2026-09-05 12:53:16 +02:00 by buildagent · 2 comments

buildagent commented

2026-09-05 12:53:16 +02:00

Member

Found by a production-readiness review using EXPLAIN QUERY PLAN against the live index (227,012 refs, 30,760 edges).

symbol_edges is WITHOUT ROWID with PRIMARY KEY (from_id, to_id) and no secondary index:

WHERE from_id = ? → SEARCH symbol_edges USING PRIMARY KEY ✅
WHERE to_id = ? → SCAN symbol_edges ❌

safe_delete's newly_orphaned (crates/daemon/src/refactor.rs:932) runs a correlated NOT EXISTS on to_id — one full scan per outgoing edge, O(out_degree × |edges|). At 30k edges this is tolerable; at the >1M edges #41 is meant to exercise, it is the thing that falls over.

Separately, there is no index on symbols.ref_count, and two queries ORDER BY ref_count DESC without one.

This is the #53 "three missing indexes" shape again — not algorithmic cost, just missing indexes. One CREATE INDEX each.

Care needed

These are new migrations on a chain that is already ~60 long, and index creation on a large symbols/symbol_edges costs time at upgrade. See the migration-time hazard filed alongside this — measure the cost before adding, and record it.
tests/corpus/cost-baseline.json ratchets SQLite work counters. Adding indexes should REDUCE vm_step on the affected repos. If it does not, the index is not being used and that is the real finding.

#53 (bound residual resolver superlinearity)
#41 (scale ceilings) — this is what a >1M-edge test should actually measure

🤖 Generated with Claude Code

https://claude.ai/code/session_01K1zj5VcFJvJt3pQxe9259K

Found by a production-readiness review using `EXPLAIN QUERY PLAN` against the live index (227,012 refs, 30,760 edges). `symbol_edges` is `WITHOUT ROWID` with `PRIMARY KEY (from_id, to_id)` and **no secondary index**: - `WHERE from_id = ?` → `SEARCH symbol_edges USING PRIMARY KEY` ✅ - `WHERE to_id = ?` → **`SCAN symbol_edges`** ❌ `safe_delete`'s `newly_orphaned` (`crates/daemon/src/refactor.rs:932`) runs a correlated `NOT EXISTS` on `to_id` — **one full scan per outgoing edge, O(out_degree × |edges|)**. At 30k edges this is tolerable; at the >1M edges #41 is meant to exercise, it is the thing that falls over. Separately, there is **no index on `symbols.ref_count`**, and two queries `ORDER BY ref_count DESC` without one. This is the #53 "three missing indexes" shape again — not algorithmic cost, just missing indexes. One `CREATE INDEX` each. ## Care needed - These are new migrations on a chain that is already ~60 long, and index creation on a large `symbols`/`symbol_edges` costs time at upgrade. See the migration-time hazard filed alongside this — measure the cost before adding, and record it. - `tests/corpus/cost-baseline.json` ratchets SQLite work counters. Adding indexes should REDUCE `vm_step` on the affected repos. If it does not, the index is not being used and that is the real finding. ## Related - #53 (bound residual resolver superlinearity) - #41 (scale ceilings) — this is what a >1M-edge test should actually measure 🤖 Generated with [Claude Code](https://claude.com/claude-code) https://claude.ai/code/session_01K1zj5VcFJvJt3pQxe9259K

buildagent commented

2026-09-05 14:13:23 +02:00

Author

Member

Measured at 1.1M edges — and the recommendation in this issue was wrong

#41's scale work reproduced this and measured it end to end. The defect is real and worse than filed; the fix I proposed is not.

The measurement

Fixture: a genuinely indexed project with 1.1M live edges grafted into the same tables and generation, queried through the real code-index-daemon binary over real RPC — not a bare Connection.

form	sqlite3 CLI (-O2)	through the real daemon
correlated `NOT EXISTS` (shipped until now)	56.0 s	147.8 s
uncorrelated `NOT IN`	0.155 s	0.460 s

361× / 321×, out-degree 1000, 20 orphan candidates.

EXPLAIN QUERY PLAN confirms the mechanism filed here: SCAN e2 under CORRELATED SCALAR SUBQUERY 1, and the outer ORDER BY f.path denies early exit.

Why it hid, which is the part worth keeping

EXISTS short-circuits on a dense graph — and stops short-circuiting exactly where the answer is true, i.e. on the rows newly_orphaned exists to return. The first fixture built measured 145 ms and looked fine; that was the cheap branch. A benchmark that never produces orphans cannot see this.

Alongside it: GRAPH_EDGE_CAP guards graph.rs and never reaches refactor.rs. On the same index the graph tools declined in microseconds while safe_delete held a read-pool connection for 147 s with nothing disclosed.

Correcting this issue's ask

This is the #53 "three missing indexes" shape again — not algorithmic cost, just missing indexes. One CREATE INDEX each.

That was wrong for the to_id half. The shipped fix is a one-line rewrite to an uncorrelated IN list — no schema change, no migration. Adding symbol_edges(to_id) would have grown every database in the wild, at upgrade cost on a chain already ~60 long (see #144), for a case the rewrite handles for free. I filed the index because I reasoned from the query plan and stopped there; the lane measured both and the measurement decides.

Equivalence is graded row-for-row against the historical form (605 victims, 201 with orphans, 303 rows, 0 differences), and the test reads the shipped text from refactor::NEWLY_ORPHANED_SQL so it cannot grade a transcription of itself. The mutation restoring the correlated form is RUN and RED at 147.8 s against a 60 s hang ceiling.

The symbols.ref_count half of this issue is untouched and still open.

A second uncapped reader, now bounded

local_index::repo_map loads the whole symbol_edges table with no predicate: 1.9–2.2 s for 20 rows at 1.1M edges. Bounded rather than a defect, and now gated.

Follow-up worth doing (not done)

bounding_site_registry's GRAPH_EDGE_CAP entry says the graph tools "decline to answer … refusing to answer is the disclosure." True of explain_dependency/change_impact, and silently untrue of safe_delete and repo_map, which read the same table under no cap at all. Both are now measured, but the registry sentence still reads wider than the guarantee. Scoping it is cheap.

🤖 Generated with Claude Code

https://claude.ai/code/session_01K1zj5VcFJvJt3pQxe9259K

## Measured at 1.1M edges — and the recommendation in this issue was wrong #41's scale work reproduced this and measured it end to end. The defect is real and worse than filed; **the fix I proposed is not.** ### The measurement Fixture: a genuinely indexed project with 1.1M live edges grafted into the same tables and generation, queried through the **real `code-index-daemon` binary over real RPC** — not a bare `Connection`. | form | sqlite3 CLI (-O2) | through the real daemon | |---|---|---| | correlated `NOT EXISTS` (shipped until now) | 56.0 s | **147.8 s** | | uncorrelated `NOT IN` | 0.155 s | **0.460 s** | **361× / 321×**, out-degree 1000, 20 orphan candidates. `EXPLAIN QUERY PLAN` confirms the mechanism filed here: `SCAN e2` under `CORRELATED SCALAR SUBQUERY 1`, and the outer `ORDER BY f.path` denies early exit. ### Why it hid, which is the part worth keeping `EXISTS` short-circuits on a dense graph — and **stops short-circuiting exactly where the answer is `true`**, i.e. on the rows `newly_orphaned` exists to return. The first fixture built measured 145 ms and looked fine; that was the cheap branch. A benchmark that never produces orphans cannot see this. Alongside it: `GRAPH_EDGE_CAP` guards `graph.rs` and never reaches `refactor.rs`. On the same index the graph tools declined in microseconds while `safe_delete` held a read-pool connection for 147 s **with nothing disclosed**. ### Correcting this issue's ask > This is the #53 "three missing indexes" shape again — not algorithmic cost, just missing indexes. One `CREATE INDEX` each. **That was wrong for the `to_id` half.** The shipped fix is a one-line rewrite to an uncorrelated `IN` list — no schema change, no migration. Adding `symbol_edges(to_id)` would have grown every database in the wild, at upgrade cost on a chain already ~60 long (see #144), for a case the rewrite handles for free. I filed the index because I reasoned from the query plan and stopped there; the lane measured both and the measurement decides. Equivalence is graded row-for-row against the historical form (605 victims, 201 with orphans, 303 rows, **0 differences**), and the test reads the shipped text from `refactor::NEWLY_ORPHANED_SQL` so it cannot grade a transcription of itself. The mutation restoring the correlated form is RUN and RED at 147.8 s against a 60 s hang ceiling. **The `symbols.ref_count` half of this issue is untouched and still open.** ### A second uncapped reader, now bounded `local_index::repo_map` loads the whole `symbol_edges` table with no predicate: **1.9–2.2 s for 20 rows at 1.1M edges.** Bounded rather than a defect, and now gated. ### Follow-up worth doing (not done) `bounding_site_registry`'s `GRAPH_EDGE_CAP` entry says the graph tools *"decline to answer … refusing to answer is the disclosure."* True of `explain_dependency`/`change_impact`, and **silently untrue of `safe_delete` and `repo_map`**, which read the same table under no cap at all. Both are now measured, but the registry sentence still reads wider than the guarantee. Scoping it is cheap. 🤖 Generated with [Claude Code](https://claude.com/claude-code) https://claude.ai/code/session_01K1zj5VcFJvJt3pQxe9259K

dhoyer referenced this issue from a commit

2026-09-05 16:05:10 +02:00

perf: safe_delete stops scanning symbol_edges once per outgoing edge

buildagent referenced this issue

2026-09-05 17:44:41 +02:00

Two gates are blind to the population they exist for: the argument registry misses a prelude closure (FIXED), and no gate sees a read-path EXECUTION-COST regression #158

dhoyer referenced this issue from a commit

2026-09-05 18:43:40 +02:00

feat: ask for resolved callers, and stop a slow query looking like a dead daemon

buildagent referenced this issue

2026-09-05 21:50:24 +02:00

perf: project_overview needs generation-scoped O(1) aggregates, not repeated refs scans #73

dhoyer referenced this issue from a commit

2026-09-06 00:09:43 +02:00

perf: file_health reads a rollup instead of every ref (#73, m0061)

buildagent referenced this issue

2026-09-06 00:23:46 +02:00

Two gates are blind to the population they exist for: the argument registry misses a prelude closure (FIXED), and no gate sees a read-path EXECUTION-COST regression #158

buildagent commented

2026-09-06 09:29:44 +02:00

Author

Member

Triage 2026-09-06 at `f6a878a`: CLOSING. Both halves are resolved — one by a query rewrite that beats the index, one by a refusal with numbers recorded in the tree.

Verified against master, not against a lane report.

Half 1 — `symbol_edges(to_id)`: the index was refused, and the underlying defect was fixed better

a21b224 says it outright: "FIXED BY A QUERY REWRITE, NOT AN INDEX. I had filed the index (#143) and was wrong" — an index would have grown every database in the wild, at upgrade cost on a migration chain already ~60 long, for a case one uncorrelated IN handles for free.

The reasoning now lives at the code, not in this issue: crates/daemon/src/refactor.rs:66, the doc on NEWLY_ORPHANED_SQL. It states the exact fact this issue was filed on — "symbol_edges is WITHOUT ROWID with PRIMARY KEY (from_id, to_id) and carries NO index on to_id (m0013), so e2.to_id = ? is a full SCAN" — and then measures both forms at 1,100,001 edges, out-degree 1000:

form	sqlite3 CLI (-O2)	through the real daemon (debug)
correlated `NOT EXISTS`	56.0 s	147.8 s
the shipped uncorrelated form	0.155 s	0.460 s

361× and 321×. O(out_degree × |symbol_edges|) → O(|symbol_edges|).

It is graded, and graded against the historical form rather than against a transcription of the new one — the test reads the shipped text out of refactor::NEWLY_ORPHANED_SQL:

$ export CARGO_INCREMENTAL=0
$ cargo test -p code-index-daemon --test graph_cap_scale_e2e \
    orphan_evidence_survives_the_one_scan_rewrite -- --nocapture
#41 orphan-equivalence: 605 victims compared, 201 with orphan candidates, 303 rows
test orphan_evidence_survives_the_one_scan_rewrite ... ok
test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 5 filtered out; finished in 2.03s
EXIT=0

Not a vacuous pass: the census line proves 201 of the 605 compared victims actually had orphan candidates, which is the branch the correlated form was fast on and wrong about.

Half 2 — `symbols(ref_count)`: REFUSED ON MEASUREMENT, and the measurement is in the tree

crates/daemon/src/local_index.rs:4462, on top_referenced_symbols, opens with "#143: ref_count HAS NO INDEX, AND THAT IS A MEASURED DECISION, NOT AN OVERSIGHT. It was filed as one, so here is what an index on symbols(ref_count DESC, id) actually costs and buys" — measured on this repository's own 155 MB index (14,851 symbols / 231,368 refs, isolated runs, no ANALYZE):

READ — this statement: SCAN + TEMP B-TREE → SEARCH. 106,700 → 111 vm_step, 14,851 → 0 fullscan_step, 1 → 0 sorter runs, 6.27 ms → 0.021 ms.
WRITE — recompute_ref_counts: 2,359,234 → 2,521,503 vm_step (+6.9%), 196 → 227 ms min over 15 interleaved fresh-copy runs (+15%).

The read runs once per project_overview and is ~2.4% of that tool. The write runs on every resolve pass — the scoped path, i.e. every incremental re-index the watcher fires on a file save — and rebuilds the whole column regardless of scope. Net negative on exactly the axis cost-baseline.json's #73 bless names as its own disqualifier: "what would make this trade wrong is a hot repeated write path."

The methodology note is why I believe the direction: the first version of that measurement was wrong and says so. Re-running both variants against the same two files reported the indexed write 36% faster — WAL accumulation across iterations. Fresh copy per run, alternating, inverted it, and the corrected direction agrees with the opcode count while the contaminated one did not.

The refusal is cross-referenced so it cannot be filed a third time: crates/indexer/src/migrations/m0061_file_refs_rollup.rs:68 contrasts its own per-row-inserted cost against this one explicitly, and tests/corpus/cost-baseline.json:52,66 plus crates/indexer/tests/corpus_cost.rs:78,1195 carry it into the ratchet.

Verdict

A refusal with numbers is a resolution. Neither index will be added; the read cost it would have bought is real but is paid once per session against a write cost paid on every save, and the reverse-edge full-scan this issue was actually about is gone by a cheaper route. Nothing is left to do here.

Residual, stated

None that belongs to this issue. The one adjacent thing worth knowing: local_index.rs:4462 ends by noting the corpus ratchet cannot see this trade either way — corpus_cost measures a cold index and issues no daemon read — which is filed as #158 and stays open there, not here.

🤖 Triage lane, 2026-09-06, master f6a878a

## Triage 2026-09-06 at `f6a878a`: CLOSING. Both halves are resolved — one by a query rewrite that beats the index, one by a refusal with numbers recorded in the tree. Verified against master, not against a lane report. ### Half 1 — `symbol_edges(to_id)`: the index was refused, and the underlying defect was fixed *better* `a21b224` says it outright: **"FIXED BY A QUERY REWRITE, NOT AN INDEX. I had filed the index (#143) and was wrong"** — an index would have grown every database in the wild, at upgrade cost on a migration chain already ~60 long, for a case one uncorrelated `IN` handles for free. The reasoning now lives at the code, not in this issue: `crates/daemon/src/refactor.rs:66`, the doc on `NEWLY_ORPHANED_SQL`. It states the exact fact this issue was filed on — *"`symbol_edges` is `WITHOUT ROWID` with `PRIMARY KEY (from_id, to_id)` and carries NO index on `to_id` (m0013), so `e2.to_id = ?` is a full SCAN"* — and then measures both forms at 1,100,001 edges, out-degree 1000: | form | sqlite3 CLI (-O2) | through the real daemon (debug) | |---|---|---| | correlated `NOT EXISTS` | 56.0 s | 147.8 s | | the shipped uncorrelated form | 0.155 s | 0.460 s | 361× and 321×. `O(out_degree × |symbol_edges|)` → `O(|symbol_edges|)`. It is **graded**, and graded against the historical form rather than against a transcription of the new one — the test reads the shipped text out of `refactor::NEWLY_ORPHANED_SQL`: ``` $ export CARGO_INCREMENTAL=0 $ cargo test -p code-index-daemon --test graph_cap_scale_e2e \ orphan_evidence_survives_the_one_scan_rewrite -- --nocapture #41 orphan-equivalence: 605 victims compared, 201 with orphan candidates, 303 rows test orphan_evidence_survives_the_one_scan_rewrite ... ok test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 5 filtered out; finished in 2.03s EXIT=0 ``` Not a vacuous pass: the census line proves 201 of the 605 compared victims actually had orphan candidates, which is the branch the correlated form was fast on and wrong about. ### Half 2 — `symbols(ref_count)`: REFUSED ON MEASUREMENT, and the measurement is in the tree `crates/daemon/src/local_index.rs:4462`, on `top_referenced_symbols`, opens with **"#143: `ref_count` HAS NO INDEX, AND THAT IS A MEASURED DECISION, NOT AN OVERSIGHT. It was filed as one, so here is what an index on `symbols(ref_count DESC, id)` actually costs and buys"** — measured on this repository's own 155 MB index (14,851 symbols / 231,368 refs, isolated runs, no ANALYZE): - **READ** — this statement: `SCAN + TEMP B-TREE` → `SEARCH`. 106,700 → 111 vm_step, 14,851 → 0 fullscan_step, 1 → 0 sorter runs, **6.27 ms → 0.021 ms**. - **WRITE** — `recompute_ref_counts`: 2,359,234 → 2,521,503 vm_step (**+6.9%**), 196 → 227 ms min over 15 interleaved fresh-copy runs (**+15%**). The read runs **once** per `project_overview` and is ~2.4% of that tool. The write runs on **every** resolve pass — the scoped path, i.e. every incremental re-index the watcher fires on a file save — and rebuilds the whole column regardless of scope. Net negative on exactly the axis `cost-baseline.json`'s #73 bless names as its own disqualifier: *"what would make this trade wrong is a hot repeated write path."* The methodology note is why I believe the direction: the **first** version of that measurement was wrong and says so. Re-running both variants against the same two files reported the indexed write **36% faster** — WAL accumulation across iterations. Fresh copy per run, alternating, inverted it, and the corrected direction agrees with the opcode count while the contaminated one did not. The refusal is cross-referenced so it cannot be filed a third time: `crates/indexer/src/migrations/m0061_file_refs_rollup.rs:68` contrasts its own per-row-inserted cost against this one explicitly, and `tests/corpus/cost-baseline.json:52,66` plus `crates/indexer/tests/corpus_cost.rs:78,1195` carry it into the ratchet. ### Verdict **A refusal with numbers is a resolution.** Neither index will be added; the read cost it would have bought is real but is paid once per session against a write cost paid on every save, and the reverse-edge full-scan this issue was actually about is gone by a cheaper route. Nothing is left to do here. ### Residual, stated None that belongs to this issue. The one adjacent thing worth knowing: `local_index.rs:4462` ends by noting **the corpus ratchet cannot see this trade either way** — `corpus_cost` measures a cold index and issues no daemon read — which is filed as **#158** and stays open there, not here. 🤖 Triage lane, 2026-09-06, master `f6a878a`

buildagent closed this issue

2026-09-06 09:29:47 +02:00

buildagent referenced this issue

2026-09-06 09:44:46 +02:00

Two gates are blind to the population they exist for: the argument registry misses a prelude closure (FIXED), and no gate sees a read-path EXECUTION-COST regression #158

buildagent referenced this issue

2026-09-06 09:48:26 +02:00

honesty: no PROJECT-scoped asserted-name census (project_overview / resolution_gaps) and find_callees has no aggregate at any scope — the per-row marker and page count shipped in d0279c0 #93

buildagent referenced this issue

2026-09-06 09:53:02 +02:00

The index discloses its root but never its commit, so an agent pinned to one worktree was served another's content and could not tell #182

buildagent referenced this issue