Add index on document_receivers(person_id) for per-person document counts #681

New Issue

marcel · 2026-05-27T14:22:41+02:00

marcel commented

2026-05-27 14:22:41 +02:00

Context

Surfaced in the review of PR #679 (Phase 5 persons directory, #667) — Markus (architect).

The persons directory computes a per-person documentCount via correlated COUNT subqueries over documents (as sender) and document_receivers (as receiver). documents(sender_id) is indexed (V62), but document_receivers(person_id) is effectively unindexed: the join table's composite primary key leads on document_id, so it cannot serve a predicate on the trailing person_id column. The receiver-side count therefore degrades as the corpus grows.

Suggested approach

Add a Flyway migration creating an index on document_receivers(person_id).
(Optional) re-check the EXPLAIN plan of countByFilter/findByFilter's receiver subquery after adding it.

Out of scope / notes

Not added in #679 — that PR is presentation-only and deliberately ships no migration. This is a small follow-up migration.
Correctness is unaffected; this is purely query performance at scale.

## Context Surfaced in the review of PR #679 (Phase 5 persons directory, #667) — Markus (architect). The persons directory computes a per-person `documentCount` via correlated COUNT subqueries over `documents` (as sender) and `document_receivers` (as receiver). `documents(sender_id)` is indexed (V62), but **`document_receivers(person_id)` is effectively unindexed**: the join table's composite primary key leads on `document_id`, so it cannot serve a predicate on the trailing `person_id` column. The receiver-side count therefore degrades as the corpus grows. ## Suggested approach - Add a Flyway migration creating an index on `document_receivers(person_id)`. - (Optional) re-check the EXPLAIN plan of `countByFilter`/`findByFilter`'s receiver subquery after adding it. ## Out of scope / notes - Not added in #679 — that PR is presentation-only and deliberately ships no migration. This is a small follow-up migration. - Correctness is unaffected; this is purely query performance at scale.

marcel referenced this issue

2026-05-27 14:24:48 +02:00

feat(persons): clean filterable directory + triage UI (Phase 5, #667) #679

marcel referenced this issue

2026-05-27 14:26:16 +02:00

feat(persons): clean filterable directory + triage UI (Phase 5, #667) #679

marcel referenced this issue

2026-05-27 19:48:25 +02:00

feat(documents): honest handling of undated documents in browse & search (Phase 6, #668) #682

Sign in to join this conversation.