The chunks table has no scalar indexes, so every lookup scans every fragment

[octogonz]

Monodex never calls create_index. Not once, on any column, in any table. Every predicate is therefore evaluated by scanning every fragment: a row_id point lookup costs a full-table scan, and so does an array_contains check against active_label_ids. Fragment count multiplies that, and until #86 nothing ever reclaims fragments, so the scan gets more expensive over the life of a database.

This is the shared root under several things we have been treating separately. #85 is the re-crawl read path, where David Huang (@davidh233)'s team saw classify fail to finish in 19 minutes on a 48k-file repository against a freshly built database with 1,342 fragments. #83 is per-file completion marking, where each mark is a full-table predicate scan. Label cleanup (#88) scans on the read side and again per write. The fixes in those issues reduce how many scans happen; none of them make a scan cheaper.

I had this filed in my head as a research question, on the grounds that nobody had measured whether indexes help here. I think that was wrong. row_id is the primary key and every point lookup goes through it; active_label_ids is the filter on every search and every label operation. Those access patterns were known when the schema was designed, and a table that ships without indexes on them is missing ordinary hygiene rather than awaiting evidence. LanceDB has index types that match both shapes exactly, which is a decent sign we are outside the intended usage.

Proposal

Create two scalar indexes on the chunks table, at init_db alongside the tables in src/app/commands/init_db/run.rs:

table.create_index(&["row_id"], Index::BTree(Default::default())).execute().await?;
table.create_index(&["active_label_ids"], Index::LabelList(Default::default())).execute().await?;

Index::BTree is documented for scalar columns with mostly distinct values under highly selective equality queries, which is what row_id is. Index::LabelList exists specifically for List columns queried with array_contains. Our array_contains_str helper generates array_contains(active_label_ids, '<label>'), and Lance's LabelList handler notes that DataFusion normalizes array_contains into array_has and turns it into an index query, so the predicate we already emit is the one the index serves.

Correctness does not depend on any of this. LanceDB's own documentation for create_index says searches over a partially indexed table issue an indexed search over the covered data and a flat search over the rest, then combine. So an index is a performance structure that goes stale rather than wrong, and rows written after the last refresh are found by scan.

Refreshing. OptimizeAction::Index folds unindexed data into an existing index without rebuilding it. Two natural points to call it: after a successful crawl, and inside monodex gc if #86 lands. The second is convenient because gc is already the command that compacts and prunes, and index maintenance is the same category of work; it also gives gc a second job, which slightly strengthens the one-verb argument in that issue. Whether a crawl should refresh unconditionally or only past some threshold of unindexed rows is open. On a 39-hour initial build the answer barely matters; on a laptop watcher firing on every save it might.

Batch size interacts with this, and the interaction runs the wrong way. LanceDB #2085 reports a 1,000-element id IN (...) delete taking 3.9s against a BTree-indexed column on a 20M-row table, and explicitly notes that at batch size 100 the indexed deletes were faster than unindexed. So the cost is not "index bad", it is something that scales with IN-list length, and the linked PR title ("detect indexed delete compilation regressions") suggests predicate compilation rather than the scan. That issue is still open after 19 months, was closed and reopened this month, and its pending PR only adds tests. It was filed against 0.15.0 and we pin 0.27, so it may not reproduce. This matters to us because our UPSERT_BATCH_SIZE is 1000, exactly the size reported as pathological, and in #83 I recommended reusing 1000 over the 200 in David Huang (@davidh233)'s patch. That recommendation is right for today's unindexed table and may invert once a BTree exists. Whoever picks this up should sweep IN-list size against both indexes rather than testing one value, and treat the result as the input to a single batch-size decision across #83, #85, and #88.

What this does not cover

Vector indexing is a separate decision and stays out of scope. vector_search calls nearest_to on a table with no vector index, so every semantic query is a brute-force scan, and BL57 records that as a deliberate choice: acceptable at current scale, with IVF and HNSW available if it stops being. That one genuinely is data-driven, because ANN changes what retrieval returns rather than only how fast it returns it, and measured hybrid search on a 410k-chunk database is still 0.71 to 0.99s cold. Scalar indexes change no results at all, which is why I think they belong on a different footing.

The other thing left out is whether an index on active_label_ids weakens the case for ever moving label membership out of the row and into per-label manifests. It probably does, and that is a design conversation rather than part of this.