perf(db): indexes for person-filter search + library stats paths (measured) #842

Merged
chris merged 1 commit from fix/search-stats-indexes into main 2026-09-24 08:12:12 +02:00
Owner

Measured first, then fixed (prod EXPLAIN ANALYZE, read-only; after-numbers on a restored copy)

Query Before After
person:"Rikke" restriction 17,011 ms 6.4 ms
two person tokens (person-only) 9,873 ms 14.1 ms
per-occurrence discovery_results probe 22.9 ms 0.007 ms
stats domainCounts 197.3 ms cold 6.7 ms
stats visiblePhotoCount / person resolution 96.8 / 3.6 ms unchanged — no index justified (candidates measured, skipped)

Root cause

The person-filter join CAST(dr.id AS VARCHAR) = cio.occurrence_key was not indexable → nested-loop rescanned an index 22.9 ms per occurrence (425 probes = 17 s for ONE token). Fix: expression index idx_discovery_results_id_text ON discovery_results ((CAST(id AS VARCHAR))) — probes drop to 0.007 ms. The reverse cast (occurrence_key::uuid) was evaluated and REJECTED: 8 non-UUID generic_evidence keys make it unsafe at plan time (guard test pins the safe direction).

Also

  • LibraryStatsService.domainCounts rewrite moves lifecycle/stash/accepted filters into the LEFT JOIN conditions → planner uses the EXISTING idx_class_instances_owner_domain_state: 197 → 6.7 ms with zero new indexes (equivalence verified on prod data + tests). Your hypothesized occurrence-chain covering index measured no meaningful win (within noise) and was dropped per measure-first discipline.
  • New SearchStatsIndexesMigrationTest pins the index DDL expression ↔ join form so the index can't silently go unused.
  • All after-numbers + DDL documented in V137 migration comments. Scoped suites green (search 20, stats 3, migration pin 2).
## Measured first, then fixed (prod EXPLAIN ANALYZE, read-only; after-numbers on a restored copy) | Query | Before | After | |---|---|---| | person:"Rikke" restriction | **17,011 ms** | **6.4 ms** | | two person tokens (person-only) | 9,873 ms | 14.1 ms | | per-occurrence discovery_results probe | 22.9 ms | 0.007 ms | | stats domainCounts | 197.3 ms cold | 6.7 ms | | stats visiblePhotoCount / person resolution | 96.8 / 3.6 ms | unchanged — no index justified (candidates measured, skipped) | ## Root cause The person-filter join `CAST(dr.id AS VARCHAR) = cio.occurrence_key` was not indexable → nested-loop rescanned an index 22.9 ms per occurrence (425 probes = 17 s for ONE token). Fix: expression index `idx_discovery_results_id_text ON discovery_results ((CAST(id AS VARCHAR)))` — probes drop to 0.007 ms. The reverse cast (`occurrence_key::uuid`) was evaluated and REJECTED: 8 non-UUID generic_evidence keys make it unsafe at plan time (guard test pins the safe direction). ## Also - `LibraryStatsService.domainCounts` rewrite moves lifecycle/stash/accepted filters into the LEFT JOIN conditions → planner uses the EXISTING `idx_class_instances_owner_domain_state`: 197 → 6.7 ms with **zero new indexes** (equivalence verified on prod data + tests). Your hypothesized occurrence-chain covering index measured no meaningful win (within noise) and was dropped per measure-first discipline. - New `SearchStatsIndexesMigrationTest` pins the index DDL expression ↔ join form so the index can't silently go unused. - All after-numbers + DDL documented in V137 migration comments. Scoped suites green (search 20, stats 3, migration pin 2).
perf(db): indexes for person-filter search + library stats paths (measured)
All checks were successful
Build and Deploy / Detect changed services and test scopes (push) Successful in 3s
Build and Deploy / Smoke API and media worker (push) Has been skipped
Build and Deploy / Smoke web (push) Has been skipped
Build and Deploy / Smoke ML worker (push) Has been skipped
Build and Deploy / Smoke people worker (push) Has been skipped
Build and Deploy / Build postgres image (push) Has been skipped
Build and Deploy / Build plants worker image (push) Has been skipped
Build and Deploy / Build converter worker image (push) Has been skipped
Build and Deploy / Deploy postgres (push) Has been skipped
Build and Deploy / Build API image (push) Has been skipped
Build and Deploy / Build media worker image (push) Has been skipped
Build and Deploy / Build ML worker image (push) Has been skipped
Build and Deploy / Build people worker image (push) Has been skipped
Build and Deploy / Build web image (push) Has been skipped
Build and Deploy / Deploy API (push) Has been skipped
Build and Deploy / Deploy web (push) Has been skipped
Build and Deploy / Deploy media worker (push) Has been skipped
Build and Deploy / Deploy ML worker (push) Has been skipped
Build and Deploy / Deploy plants worker (push) Has been skipped
Build and Deploy / Deploy people worker (push) Has been skipped
Build and Deploy / Deploy converter worker (push) Has been skipped
Build and Deploy / Clean Docker build artifacts (push) Has been skipped
Build and Deploy / Run scoped PR tests (push) Successful in 1m50s
73c2fd39d1
chris merged commit b2d1bd7fb3 into main 2026-09-24 08:12:12 +02:00
Sign in to join this conversation.
No reviewers
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
chris/memories!842
No description provided.