perf(db): indexes for person-filter search + library stats paths (measured) #842
Loading…
Reference in a new issue
No description provided.
Delete branch "fix/search-stats-indexes"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Measured first, then fixed (prod EXPLAIN ANALYZE, read-only; after-numbers on a restored copy)
Root cause
The person-filter join
CAST(dr.id AS VARCHAR) = cio.occurrence_keywas not indexable → nested-loop rescanned an index 22.9 ms per occurrence (425 probes = 17 s for ONE token). Fix: expression indexidx_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.domainCountsrewrite moves lifecycle/stash/accepted filters into the LEFT JOIN conditions → planner uses the EXISTINGidx_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.SearchStatsIndexesMigrationTestpins the index DDL expression ↔ join form so the index can't silently go unused.