Defer the byline_documents join past LIMIT in the list-view path #40

Context

The 2026-07-21 storage sweep found that admin list-view cold queries regressed ≈2× since the April baseline (page-list at 100k: 128 → 276 ms; filter+sort: 352 → 530 ms). Single-doc reads, batch fetch, and populate are unchanged — the regression is confined to the list path.

Root cause (confirmed in the 100k EXPLAIN plan): the document-grain / version split moved order_key and source_locale out of the version stream into byline_documents, so the byline_current_documents view now joins byline_documents to reunite the two grains. Because the outer list query does SELECT d.*, that join is evaluated across every current version before ORDER BY … LIMIT 20:

Nested Loop  (rows=100000 loops=1)
  -> Subquery Scan on sq (windowed current versions)   ~99k buffers
  -> Index Scan using byline_documents_pkey  loops=100000  Buffers: shared hit=400000

400k of the query's 498k shared-buffer hits (~80%) are this per-current-version lookup. The getDocumentsByDocumentIds plan carries the same join but stays flat because it joins only 50 rows — proving the cost is specifically join-applied-pre-pagination-across-the-whole-collection, not the join existing.

Decision — deferred, not urgent

Accepted as-is for the scale Byline targets. A typical deployment is well under 10k documents, where page-list is ~32 ms and imperceptible; the public cache-miss paths (single-doc reconstruct, populate) are untouched. This is tracked, not scheduled.

Named trigger to pick this up: a real large-collection deployment (50k+ documents in one collection) reporting sluggish admin list pagination.

The experiment

The ORDER BY created_at DESC, id DESC keys both live on the version row (sq), not on byline_documents — so the sort + LIMIT 20 can complete entirely within the windowed version rows, joining byline_documents only to the ≤20 survivors. The view can't express this today because it projects order_key/source_locale through sq. Options to try in the findDocuments list-view path:

• Resolve the current-version page (window + ORDER BY + LIMIT) without byline_documents, then LEFT JOIN LATERAL byline_documents on the paginated set only.
• Or select order_key/source_locale only for the surviving rows (deferred projection) rather than through the view.
• Re-run the 100k sweep and compare page-list / filter+sort medians against this summary; confirm the window-aggregate half (~80 ms) is now the floor.

Sub-item: filter+sort has a second, independent bottleneck

The filter+sort query's plan shows a Parallel Seq Scan on byline_store_text for title ILIKE '%storage%' (~120 ms, ~134k rows removed per worker) — a leading-wildcard substring match, unindexable without trigrams. This is separate from the view-join regression and the deferred-join fix won't touch it.

• If admin list-search on large collections matters, evaluate a pg_trgm GIN index on byline_store_text.value to cover the ILIKE '%…%' half. Measure the write-path and index-size cost against the read gain before committing.

References

• Sweep summary: benchmarks/storage/results/2026-07-21-storage-cold-summary.md (findings fix: update README for starting projects. #2, fix(field): Fix some UI detail issues. #3)
• 100k plans: benchmarks/storage/results/2026-07-21-storage-cold-100k.md
• April baseline: benchmarks/storage/results/2026-04-18-storage-cold-summary.md
• Storage reference: docs/03-architecture/01-document-storage.md