A pushed document's signatures are found by scanning every evidence row in the database

Open Beginner friendly
  • Is something wrong?
#1,471 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
62/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
postgresql, rust
Domain
backend, databases

Research direction

Start with signature_keys_of_document in crates/utopia-store/src/phrase_bindings.rs, where the EXISTS subquery joins fact_evidence to chunks to reach the document. Confirm that fact_evidence.document_id matches the chunk's document, then check the schema for the fact_evidence_document_idx index. Replace the join with a direct fe.document_id = $2 filter and use EXPLAIN ANALYZE to confirm an index scan. Done when results are unchanged and the query runs in well under a second on a large dataset.

Written by the indexing model from the issue text.

Description

A pushed document's signatures are found by scanning every evidence row in the database

What we see

Since #1242 every extracted document of a pushed source asks signature_keys_of_document (crates/utopia-store/src/phrase_bindings.rs) which signatures its live open statements fall on. On our instance that query is now the single largest consumer of the database:

  • 12 s per document on a base of 4.5 M facts, 8.3 M evidence rows (EXPLAIN ANALYZE below).
  • The workers run it for every document they finish, so 28 to 30 copies run at once and hold utopia-db at its CPU limit (3.4 of 4 cores) for as long as a backfill lasts. Every search on the instance waits behind them: our context assembly's p50 went from 4 s to 26 s during the week, and half of its calls hit the 30 s timeout.

Why

The query reaches the document through the chunk:

AND EXISTS (SELECT 1 FROM fact_evidence fe JOIN chunks c ON c.id = fe.chunk_id
             WHERE fe.fact_id = f.id AND c.document_id = $2)

fact_evidence has no index on chunk_id, so the planner hashes the document's chunks and seq-scans all of fact_evidence (2.76 M rows per parallel worker, 5.6 s) to find the ~300 rows that cite them:

->  Parallel Seq Scan on fact_evidence fe  (cost=0.00..158514.36 rows=3440536 width=32) (actual time=0.030..5643.970 rows=2763178 loops=3)
Execution Time: 12014.875 ms

But fact_evidence already carries document_id, indexed by fact_evidence_document_idx, and on our data it always equals the chunk's document (0 of 8,289,533 rows differ, none null).

Proposed change

Read the document column directly:

AND EXISTS (SELECT 1 FROM fact_evidence fe
             WHERE fe.fact_id = f.id AND fe.document_id = $2)

Same rows, 0.16 s instead of 12 s on the same document:

->  Index Scan using fact_evidence_document_idx on fact_evidence fe (actual time=0.098..34.880 rows=315 loops=1)
Execution Time: 162.319 ms

The same chunk-join shape appears in documents.rs (the delete path, lines ~1166, 1263, 1295, 1387) and time_resolution.rs:789; those run once per delete or per resolution rather than per extraction, so they matter less, but the same one-line change applies if you want them consistent.

Happy to send the PR if you agree with the shape.

Dominant language
Rust
Stars
8.1k
Forks
1.1k
Avg merge
23h 41m
Merged PRs (30d)
224

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from deeplethe/utopia

All issues in deeplethe/utopia

Similar issues

More Rust issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.