Every `sec ask` full-scans and temp-B-tree-sorts `filing_document`: no index on `filing_date`, plus an unconditional COUNT nothing renders

[sroussey]

6ab497fa fixed the right thing — the limit now bounds work rather than embeddings (#351) — but
the selection it introduced is O(N) in converted filings, and sec ask runs it twice on
every question.

ask.ts:112-116 builds the index implicitly unless --no-index, so each question runs
selectDocumentsToIndex and countAlreadyIndexed. Both scan filing_document, and the first
also sorts it:

src/task/kb/selectDocumentsToIndex.ts:127-136

SELECT d.* FROM `filing_document` d
LEFT JOIN `kb_document` k ON k.`doc_id` = d.`accession_number` || ':' || d.`doc_file`
WHERE d.`section_count` > 0 AND k.`doc_id` IS NULL
ORDER BY d.`filing_date` DESC, d.`accession_number` DESC
LIMIT ?

filing_document carries exactly two indexes (storageRegistry.ts:239):
["form","converter_version","is_primary"] and ["converted_at"]. Neither covers
filing_date, so the LIMIT cannot terminate the scan.

Measured

Faithful copy of the real DDL and both real indexes, 300,000 rows, 99% already in kb_document
(the steady state ask runs in), LIMIT 26 (DEFAULT_ASK_INDEX_LIMIT + 1):

QP: SCAN d
QP: SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?) LEFT-JOIN
QP: USE TEMP B-TREE FOR ORDER BY
selectDocumentsToIndex LIMIT 26, no filing_date index: 415 ms / 416 ms

CREATE INDEX fd_filing_date ON filing_document (filing_date DESC, accession_number DESC);

QP: SCAN d USING INDEX fd_filing_date
QP: SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?) LEFT-JOIN
selectDocumentsToIndex LIMIT 26, WITH filing_date index:  21 ms / 12 ms

20–35× on an in-memory database, and the indexed plan is O(limit) rather than O(N) — the
gap widens with the corpus and widens again on disk with a cold page cache. This sits on top of
the chunk-scan cost in the sibling issue, on the same question.

The second scan

countAlreadyIndexed (:152-189) is a full filing_document ⋈ kb_document COUNT, run
unconditionally whenever force !== true, to populate skipped. The ask renderer never prints
skipped; only sec index does (ask.ts:71). So half the per-question cost is computed for a
field the caller discards.

Fix

1. Add ["filing_date", "accession_number"] to filing_document's indexes in
   src/config/storageRegistry.ts — one entry, and db setup / addMissingColumns already loop
   the registry.
2. Skip countAlreadyIndexed when the caller will not render it (an option on the task input, or
   compute it lazily), and consider whether ask's implicit index needs a selection at all when
   kb_chunk is non-empty and the scope is unchanged.

Smaller, same file

• countAlreadyIndexed omits the section_count > 0 clause the selection applies, so skipped
  can count documents the selection would never return.
• selectDocumentsToIndex's JSDoc says "newest first"; the repository arm selectByScan
  (:205-222) returns repository order, so under --limit N the two paths select different
  documents.

Found during the 2026-09-14 review. Snapshot: workglow-dev/prd → analysis/grades/2026-09-14/sec-detailed.md §4.6.

[sroussey]

Both fixes landed on claude/admiring-lovelace-59uare — 4d840aeb. No PR opened.

1. The index

src/config/storageRegistry.ts:239-249 — one more entry beside the two already there:

indexes: [
  ["form", "converter_version", "is_primary"],
  ["converted_at"],
  ["filing_date", "accession_number"],
],

ASC/ASC rather than the DESC/DESC of your probe: defineStorage's indexes cannot express per-column direction, and it does not need to — the ORDER BY reverses both columns, so SQLite walks the same index backwards and the temp B-tree goes away either way. That is the property the column order has to match, and it is what the test below asserts rather than assuming.

2. The count

countAlreadyIndexed is now asked for rather than assumed. IndexFilingSectionsTaskInput grows countSkipped, defaulting to counting so a caller has to have decided not to, and IndexFilingSectionsTaskOutput.skipped becomes number | undefined — absent when a run did not count, not zero, because zero is an answer (the index holds nothing in scope) and not what "nobody asked" means. src/cli/groups/ask.ts:115-121 passes countSkipped: false on the pre-index; the sec index renderer, which is the one caller that prints the number, is unchanged in behaviour.

Worth stating why the index does not help this one: it is an unbounded aggregate over the join, so it visits every matching row no matter what is indexed. Measured at 139.1 ms before the index and 140.2 ms after — the only way to make it cheaper is not to run it.

Measured

Rerun from scratch rather than quoting the numbers above. Real SQLite file (WAL, temp_store = MEMORY), the schema built by SqliteTabularStorage from FilingDocumentSchema with the real index set, 300,000 rows, a tenth already in kb_document, LIMIT 26, ANALYZE run, mean of 5 executions after a warm-up. Node 24.20.0 / Bun 1.4.2.

BEFORE — indexes sqlite_autoindex_filing_document_1, filing_document_converted_at, filing_document_form_converter_version_is_primary:

EXPLAIN QUERY PLAN (selection):
  SCAN d
  SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?) LEFT-JOIN
  USE TEMP B-TREE FOR ORDER BY
  → 139.7 ms

EXPLAIN QUERY PLAN (countAlreadyIndexed):
  SCAN d USING COVERING INDEX sqlite_autoindex_filing_document_1
  SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?)
  → 139.1 ms

AFTER — the same, plus filing_document_filing_date_accession_number:

EXPLAIN QUERY PLAN (selection):
  SCAN d USING INDEX filing_document_filing_date_accession_number
  SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?) LEFT-JOIN
  → 0.2 ms

EXPLAIN QUERY PLAN (countAlreadyIndexed):
  SCAN d USING COVERING INDEX sqlite_autoindex_filing_document_1
  SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?)
  → 140.2 ms          ← not run at all on `sec ask` any more

The plans are exactly the ones in the report. My absolute numbers are lower (139.7 ms vs 415 ms for the unindexed selection) — different machine and a different row mix — but the shape is identical: SCAN d + USE TEMP B-TREE FOR ORDER BY becomes SCAN d USING INDEX, and O(N) becomes O(limit).

Per sec ask, on that corpus: ~279 ms → ~0.2 ms. The selection is ~700× cheaper and the second scan is gone.

What an operator with an existing database has to run

sec db setup

That is all — setupDatabase() emits every declared index as CREATE INDEX IF NOT EXISTS on every run, not only on a fresh database, so a newly declared index appears on the next setup and no rows move. (sec setup works too; it calls the same path.) This is the opposite of a new column, which CREATE TABLE IF NOT EXISTS cannot add and which needs the catch-up pass — so I verified the path rather than assuming it, against a database created at the old shape and carrying a row:

before `db setup`: [ filing_document_converted_at,
                     filing_document_form_converter_version_is_primary,
                     sqlite_autoindex_filing_document_1 ]
after  `db setup`: [ filing_document_converted_at,
                     filing_document_filing_date_accession_number,
                     filing_document_form_converter_version_is_primary,
                     sqlite_autoindex_filing_document_1 ]
row survived: 1

docs/fetch-and-storage.md §5 now says this, beside the column passes it is easily confused with. On a large filing_document the CREATE INDEX itself is the cost — it reads the table once under a write lock.

Tests

• src/task/kb/selectDocumentsToIndex.sqlite.test.ts — reads the newest filings from an index rather than sorting the table. It spies db.prepare, captures the statement and its bound params that selectDocumentsToIndex actually prepares (proxying .all), then runs EXPLAIN QUERY PLAN on that exact statement: asserts the plan names filing_document_filing_date_accession_number and that no step is USE TEMP B-TREE FOR ORDER BY. Planned from the real statement rather than looked up in sqlite_master on purpose — an index whose column order does not match the ORDER BY exists and removes nothing. Confirmed to fail without the registry entry, reproducing your "before" plan in a unit test:

  AssertionError: expected 'SCAN d\nSEARCH k USING COVERING INDEX…' to contain
                  'filing_document_filing_date_accession_number'
  + SCAN d
  + SEARCH k USING COVERING INDEX sqlite_autoindex_kb_document_1 (doc_id=?) LEFT-JOIN
  + USE TEMP B-TREE FOR ORDER BY
  

• src/task/kb/IndexFilingSectionsTask.test.ts — does not count what is already indexed for a caller that will not show it: three filings, all indexed, countSkipped: false; asserts no COUNT(*) statement is prepared and that skipped comes back undefined.

Not addressed

The two items under "Smaller, same file" are untouched and still stand — countAlreadyIndexed omitting the section_count > 0 clause the selection applies, and selectByScan returning repository order where the JSDoc promises newest-first. Both are correctness divergences rather than cost, and neither is in this change.

Also untouched: "consider whether ask's implicit index needs a selection at all when kb_chunk is non-empty and the scope is unchanged." With the index in place the selection is O(limit), so the remaining prize there is small.

Verification

command	exit	time
bun run format-check	0	—
bun run lint	0	—
bun run typecheck	0	1.7 s
bun run build	0	—
bunx vitest run src/task/kb src/config src/cli src/commands	0	—
bunx vitest run (full suite)	0	132 s — 1496 passed, 21 skipped, 151 files

No timeouts and no load-sensitive failures in the verify tier on this run.

---

Generated by Claude Code