perf(api): speed up face-suggestion lookups + regression guard #1252
Loading…
Reference in a new issue
No description provided.
Delete branch "fix/face-suggest-slow"
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?
Problem
The person-assignment dialog's suggestion dropdown (
GET /discovery/domains/{domainKey}/instance-suggestions→suggestSimilarInstances) fires on every ~300 ms-debounced keystroke and had become slow again. Three measured/argued cost drivers onmain(17d5147b):class_instance_occurrencesonoccurrence_keyALONE. The only index containingoccurrence_keyis the V074 unique constraint(domain_key, owner_id, occurrence_type, occurrence_key)— fourth column, unusable for a leading-occurrence_keypredicate → parallel seq scan of the whole occurrences table, twice per request, on a table that grows with one row per accepted/tentative face. (Sixteen otheroccurrence_keyprobes in the service carrydomain_key/owner_idbeside it and are served by the constraint prefix; the suggestion probes are the uncovered shapes.)(SELECT COUNT(*) FROM class_instance_occurrences cio2 WHERE cio2.class_instance_id = ci.id ...)— executed once per candidate embedding row, the exact ISSUE-140 class (PR #1144: thumbnail subquery → ~4,675 executions for ~350 persons → ~39 s). The PR#1146 fix batched the thumbnail probe but left the count probe per-row.suggestSimilarInstancesagainst a statement-count/probe regression, so the class kept recurring.Commits
test(api): pin instance-suggestion statement count(+ fix)suggestSimilarInstancesRunsConstantNumberOfStatementsRegardlessOfCandidateCount(mirrorspersonRankingRunsConstantNumberOfStatementsRegardlessOfFaceCount): identical statement budget at 1/3/50 candidate faces, plus an alias-independent textual pin of the candidate-rows statement (identified by its load-bearingAS instance_idalias) forbidding a scalar(SELECT COUNToccurrence probe — the per-row probe lives inside ONE statement and is invisible to a statement count. Mutation-checked: the test fails against the unfixed code and passes with the fix.LEFT JOINto a once-per-instance grouped aggregate over the same live-state rows scoped to the same candidate-instance predicates. Same values (the IN scope is implied by thecijoin), 1:1 join via GROUP BY so the row set is unchanged,COALESCE(..., 0)reproducesCOUNT(*) = 0for unmatched instances. No ranking/score/ordering/fallback changes — all 23 pre-existing suggestion tests pass unchanged.perf(api): index occurrence_key lookups for face suggestionsCREATE INDEXbtree onclass_instance_occurrences (occurrence_key)— Flyway runs migrations in transactions, soCONCURRENTLYis unavailable (V140 convention).occurrence_keydescends the new index directly, while the V074 constraint would need a full index scan (columns 1-3 unbound) which the planner rejects in favour of a seq scan. Measured on PG16, 455k occurrences, matched key:coe.occurrence_id): nested loop, two index scans, 0.061 msOccurrenceKeyIndexPostgresTest(Testcontainers, full Flyway chain) pins usability of the index for both exact statement shapes via the establishedenable_seqscan = offEXPLAIN pattern.Validation
rtk ./gradlew :apps:api:check— green (before the base rebase; discovery package + Postgres chain re-run green after).discoverypackage: 721 tests, 0 failures. Postgres-backed people suites (full Flyway chain incl. V167): green.Notes
q-filter changes, no frontend or thumbnail-selection changes.suggestSimilarInstances (the assignment dropdown) had no guard against the ISSUE-140 class of regression: a correlated per-row probe re-introduced inside the candidate-rows statement is invisible to a statement count. Pin both failure shapes at 1/3/50 candidate faces - a fixed statement budget (mirror of personRankingRunsConstantNumberOfStatementsRegardlessOfFaceCount) plus an alias-independent textual pin of the candidate-rows statement ("AS instance_id" filter) forbidding a scalar "(SELECT COUNT" occurrence probe. The new test failed against the then-current code, which still carried a per-row COUNT(*) (cio2) in the candidate statement. Fix, behaviour-preserving: replace the correlated scalar probe with a LEFT JOIN to a once-per-instance grouped aggregate over the same live-state rows scoped to the same candidate-instance predicates. Values are identical (same counted rows; the IN scope is implied by the ci join), the GROUP BY makes the join 1:1 so the row set is unchanged, and unmatched instances keep COALESCE(..., 0) = the old probe's COUNT(*) = 0. No ranking, score, ordering or fallback changes; all 23 existing suggestion tests pass unchanged.