Summary
The group-detail forensic tabs read each projection table with WHERE group_id = X ORDER BY <time column>, … (the model's Meta.ordering). None of these tables carries a composite index leading with the group FK and continuing with the ordering column, so Postgres can only use the implicit FK index on group_id to select rows and must then sort the entire matched set in memory (a Sort node, spilling to external merge Disk for large groups) on every tab load.
Affected tables (ordering vs indexes)
DeliveryArtifact — Meta.ordering = ["first_seen_ms","artifact_id"] (models.py:399); indexes (:406-410) = (group, artifact_kind), (artifact_id), (first_seen_ms) — no (group, first_seen_ms).NetworkObservation — Meta.ordering = ["wall_time_ms","engine_id","id"] (models.py:527); indexes (:528-534) = (group, phase), (message_id), (engine_id, wall_time_ms), (nostr_event_id), (relay_url) — no (group, wall_time_ms).ConvergenceRun — Meta.ordering = ["started_at_ms","engine_id","run_id"] (models.py:558); indexes (:565-568) = (group, phase), (engine_id, started_at_ms) — no (group, started_at_ms).EpochStateTransition — Meta.ordering = ["wall_time_ms","engine_id","id"] (models.py:675); indexes (:676-680) = (group, epoch), (engine_id, wall_time_ms), (new_state) — no (group, wall_time_ms).
Read paths applying the group filter + default ordering: forensics/views.py:494-512, :581, :596, :608 (each .filter(group=group) then ordered by the time column).
Concrete failure scenario
Rendering the Delivery / Network / Convergence / State tab for a large, contended group runs SELECT … WHERE group_id = X ORDER BY first_seen_ms/wall_time_ms/started_at_ms, …. The (group, phase) and (engine_id, wall_time_ms) indexes do not satisfy a group-scoped order-by on the time column, so the planner scans the group_id FK index and sorts every projection row of the group in memory on each request. These are the largest derived tables in the schema, so each tab load is a per-request full filesort proportional to the group's total projected volume.
Why not a duplicate
#287 is specifically AuditEvent's group+time composite being keyed on the group_ref TextField rather than the group FK (the raw-event read path). #176 is AuditEvent's admin default ordering forcing a filesort. #158/#201/#259 concern admin list_select_related / heavy-column loading. None of the 146 open issues address the derived projection tables (NetworkObservation, ConvergenceRun, EpochStateTransition, DeliveryArtifact) whose group-scoped detail reads filesort for lack of a (group, <ordering column>) composite index.
Suggested fix
Add composite indexes (group, first_seen_ms) on DeliveryArtifact, (group, wall_time_ms) on NetworkObservation and EpochStateTransition, and (group, started_at_ms) on ConvergenceRun (extending with the secondary ordering columns where cheap), turning each group-scoped tab read into an index-ordered range scan.
Severity: MEDIUM — performance regression on the hot group-detail read path (consistent with #287).
Summary
The group-detail forensic tabs read each projection table with
WHERE group_id = X ORDER BY <time column>, …(the model'sMeta.ordering). None of these tables carries a composite index leading with the group FK and continuing with the ordering column, so Postgres can only use the implicit FK index ongroup_idto select rows and must then sort the entire matched set in memory (aSortnode, spilling toexternal merge Diskfor large groups) on every tab load.Affected tables (ordering vs indexes)
DeliveryArtifact—Meta.ordering = ["first_seen_ms","artifact_id"](models.py:399); indexes (:406-410) =(group, artifact_kind),(artifact_id),(first_seen_ms)— no(group, first_seen_ms).NetworkObservation—Meta.ordering = ["wall_time_ms","engine_id","id"](models.py:527); indexes (:528-534) =(group, phase),(message_id),(engine_id, wall_time_ms),(nostr_event_id),(relay_url)— no(group, wall_time_ms).ConvergenceRun—Meta.ordering = ["started_at_ms","engine_id","run_id"](models.py:558); indexes (:565-568) =(group, phase),(engine_id, started_at_ms)— no(group, started_at_ms).EpochStateTransition—Meta.ordering = ["wall_time_ms","engine_id","id"](models.py:675); indexes (:676-680) =(group, epoch),(engine_id, wall_time_ms),(new_state)— no(group, wall_time_ms).Read paths applying the group filter + default ordering:
forensics/views.py:494-512,:581,:596,:608(each.filter(group=group)then ordered by the time column).Concrete failure scenario
Rendering the Delivery / Network / Convergence / State tab for a large, contended group runs
SELECT … WHERE group_id = X ORDER BY first_seen_ms/wall_time_ms/started_at_ms, …. The(group, phase)and(engine_id, wall_time_ms)indexes do not satisfy a group-scoped order-by on the time column, so the planner scans thegroup_idFK index and sorts every projection row of the group in memory on each request. These are the largest derived tables in the schema, so each tab load is a per-request full filesort proportional to the group's total projected volume.Why not a duplicate
#287 is specifically
AuditEvent's group+time composite being keyed on thegroup_refTextField rather than the group FK (the raw-event read path). #176 isAuditEvent's admin default ordering forcing a filesort. #158/#201/#259 concern adminlist_select_related/ heavy-column loading. None of the 146 open issues address the derived projection tables (NetworkObservation,ConvergenceRun,EpochStateTransition,DeliveryArtifact) whose group-scoped detail reads filesort for lack of a(group, <ordering column>)composite index.Suggested fix
Add composite indexes
(group, first_seen_ms)onDeliveryArtifact,(group, wall_time_ms)onNetworkObservationandEpochStateTransition, and(group, started_at_ms)onConvergenceRun(extending with the secondary ordering columns where cheap), turning each group-scoped tab read into an index-ordered range scan.Severity: MEDIUM — performance regression on the hot group-detail read path (consistent with #287).