Catalog costs on interactive paths: rating and label histograms, keyword lists, face matching #75

New Issue

2026-09-26T14:43:23Z

dtourolle commented

2026-09-26 14:43:23 +00:00

Smaller catalog costs on interactive paths, measured on the reference catalog on 2026-09-25 and left for later. None of them alone is worth a release, but several are paid on every keystroke or scroll.

rating_histogram plus local_original_count: ~8 + 1.7 ms on every rating keystroke. Could be updated incrementally.
label_histogram: 7 ms, because the versions_judgement index does not carry label.
keywords::list: 7 ms per call inside for_images, because keywords_term is not covering.
The grid's count on every scroll reload: ~4 ms, or 7 ms with a rating filter.
merge::match_faces scans both faces tables, and the remaining per-face loop (13k faces) could be one statement. It is most of the 279 ms left in a sync merge.

Acceptance

Each change measured with catalog_bench on a .backup copy, before and after, with the EXPLAIN QUERY PLAN change recorded in the commit
Results unchanged (table checksums, as in the 2026-09-25 pass)

**Smaller catalog costs on interactive paths, measured on the reference catalog on 2026-09-25 and left for later.** None of them alone is worth a release, but several are paid on every keystroke or scroll. - `rating_histogram` plus `local_original_count`: ~8 + 1.7 ms on every rating keystroke. Could be updated incrementally. - `label_histogram`: 7 ms, because the `versions_judgement` index does not carry `label`. - `keywords::list`: 7 ms per call inside `for_images`, because `keywords_term` is not covering. - The grid's count on every scroll reload: ~4 ms, or 7 ms with a rating filter. - `merge::match_faces` scans both `faces` tables, and the remaining per-face loop (13k faces) could be one statement. It is most of the 279 ms left in a sync merge. ## Acceptance - [ ] Each change measured with `catalog_bench` on a `.backup` copy, before and after, with the `EXPLAIN QUERY PLAN` change recorded in the commit - [ ] Results unchanged (table checksums, as in the 2026-09-25 pass)

dtourolle added the catalog size:M performance labels 2026-09-26 14:43:23 +00:00

dtourolle referenced this issue from a commit

2026-09-26 20:19:10 +00:00

Measure what the library screen reads on each keystroke and scroll

dtourolle referenced this issue from a commit

2026-09-26 20:19:10 +00:00

Record the open's backfill stamp and the first-use indexes in catalog.md

dtourolle commented

2026-09-26 20:20:20 +00:00

Done in 408f189, 8111872, 73059f2, fe6e523, d537e4a, 87badb6, 981022a, ae0281f and ce6705b, released in v0.17.0. On the reference catalog: rating chips 6.3–7.7 → 1.2–1.4 ms, label chips 7.2–8.4 → 1.7–2.0 ms, local-originals count 1.3 → 0.05 ms, keyword lists ~3 → ~1.4 ms, unfiltered grid count 1.4 → 0.3 ms, and a whole sync merge 230 → 135 ms (tombstones read once per merge, faces matched from the covering faces_box index, assignments read once per pass). The two new indexes are created on first use with IF NOT EXISTS, so there is no schema bump. Not done: the grid count with a rating filter (~4 ms), whose cost is RatingFilter's correlated subquery shared by every grid query; it needs its own change.

Done in 408f189, 8111872, 73059f2, fe6e523, d537e4a, 87badb6, 981022a, ae0281f and ce6705b, released in v0.17.0. On the reference catalog: rating chips 6.3–7.7 → 1.2–1.4 ms, label chips 7.2–8.4 → 1.7–2.0 ms, local-originals count 1.3 → 0.05 ms, keyword lists ~3 → ~1.4 ms, unfiltered grid count 1.4 → 0.3 ms, and a whole sync merge 230 → 135 ms (tombstones read once per merge, faces matched from the covering `faces_box` index, assignments read once per pass). The two new indexes are created on first use with IF NOT EXISTS, so there is no schema bump. Not done: the grid count with a rating filter (~4 ms), whose cost is `RatingFilter`'s correlated subquery shared by every grid query; it needs its own change.

dtourolle closed this issue

2026-09-26 20:20:21 +00:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: dtourolle/DarkRoom#75