GET /api/v1/documents reads every upload and every sent document, texts included, to answer one page #551

New issue

Closed

opened 2026-10-01 10:47:35 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:47:35 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading the documents router and timing one page at two sizes.

Checked again on 2026-10-01 against main at 0a5cad587: still present.

list_documents builds a Python list of every UploadedDocument and every RenderedDocument the owner has, sorts it in Python and hands it to the paginator. The rendered rows are loaded whole, including source_text and plain_text, the full text of every CV and letter ever sent, although document_out uses none of it. So the cost of one page grows with every document sent, and walking the list with offset does quadratic work.

Where: api/routers/documents.py:106-143 (list_documents: rows += [... for d in changed_since(owned(request, RenderedDocument.objects), updated_since)], then rows.sort(...) and return rows); api/schemas.py:843-857 (document_out reads only ids, kind, title, language, application, timestamps)
What happens: with 10 KB of text per rendered document, the same limit=20 page took 7 ms with 30 sent documents and 62 ms with 600; walking all 600 in pages of 100 read the whole table six times.
Why it matters: this is the list agents and extensions page through. The docstring says only the page's rows are shaped, "which is where the cost was"; the rows themselves are still all read, with their texts.
Fix: page the union in SQL: uploads.values("pk", "created_at", "updated_at").annotate(source=Value("upload")).union(renders.values(...).annotate(source=Value("rendered"))) ordered by -created_at, -pk (or updated_at, pk with updated_since), slice it, then fetch that page's rows by id from each table. At the least, .only(...) the fields document_out reads, or .defer("source_text", "plain_text") on the rendered rows.

Related to #230 (closed), whose item 1 moved the other lists to shaping only the page and left this one reading every row.

Done when

A test in tests/test_api_lists.py asks GET /api/v1/documents?limit=20 for an owner with 5 and with 50 rendered documents and asserts that no query selects source_text and that the rows fetched are bounded by the page size, not the total.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading the documents router and timing one page at two sizes. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. `list_documents` builds a Python list of every `UploadedDocument` and every `RenderedDocument` the owner has, sorts it in Python and hands it to the paginator. The rendered rows are loaded whole, including `source_text` and `plain_text`, the full text of every CV and letter ever sent, although `document_out` uses none of it. So the cost of one page grows with every document sent, and walking the list with `offset` does quadratic work. - **Where:** `api/routers/documents.py:106-143` (`list_documents`: `rows += [... for d in changed_since(owned(request, RenderedDocument.objects), updated_since)]`, then `rows.sort(...)` and `return rows`); `api/schemas.py:843-857` (`document_out` reads only ids, kind, title, language, application, timestamps) - **What happens:** with 10 KB of text per rendered document, the same `limit=20` page took 7 ms with 30 sent documents and 62 ms with 600; walking all 600 in pages of 100 read the whole table six times. - **Why it matters:** this is the list agents and extensions page through. The docstring says only the page's rows are shaped, "which is where the cost was"; the rows themselves are still all read, with their texts. - **Fix:** page the union in SQL: `uploads.values("pk", "created_at", "updated_at").annotate(source=Value("upload")).union(renders.values(...).annotate(source=Value("rendered")))` ordered by `-created_at, -pk` (or `updated_at, pk` with `updated_since`), slice it, then fetch that page's rows by id from each table. At the least, `.only(...)` the fields `document_out` reads, or `.defer("source_text", "plain_text")` on the rendered rows. Related to #230 (closed), whose item 1 moved the other lists to shaping only the page and left this one reading every row. ## Done when - A test in `tests/test_api_lists.py` asks `GET /api/v1/documents?limit=20` for an owner with 5 and with 50 rendered documents and asserts that no query selects `source_text` and that the rows fetched are bounded by the page size, not the total.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:47:35 +00:00

tiagoagueda added the

tier

enhancement

labels

2026-10-01 10:47:35 +00:00

tiagoagueda referenced this issue from a commit

2026-10-05 23:05:04 +00:00

Page the documents list in SQL and never read a render's text

tiagoagueda commented

2026-10-06 09:32:02 +00:00