[Bug]: Cached-thumbnail hydrate on directory open is O(n^2) via unindexed OFFSET pagination #190

New issue

Open

opened 2026-08-28 21:42:52 +00:00 by Claude · 0 comments

Claude commented

2026-08-28 21:42:52 +00:00

Member

Affected area

Directory-open cache hydrate (CachedThumbnailLoadWorker / WorkingDB.iter_thumbnail_blob_dicts()), separate from the live metadata/thumbnail scan (scan_metadata_ordered_and_upsert, issue #184).

Report (from live testing on #184's follow-up branch)

On a ~125,000-image network workspace (Windows 11, remote share, thumbnail thread count 16) that had been scanned/tested repeatedly before (so a large fraction of images already have cached thumbnail_blobs from prior runs — ~75,000 of 125,000 in the reproduction), reopening the directory shows the first ~276 alphabetically-sorted placeholder rows on screen with no thumbnails populated, for far longer than the live scan's ~30 img/s rate would predict (the live scan is correctly only processing the ~50,000 genuinely-pending images, confirmed via its own progress counter). User's own diagnosis: the process that displays already-cached thumbnails appears to be "buried" and should run before/independently of new generation — which is exactly right.

Root cause

WorkingDB.iter_thumbnail_blob_dicts() (db/working_db.py) pages through cached rows with:

SELECT relative_path, thumbnail_blob FROM images
WHERE thumbnail_blob IS NOT NULL
ORDER BY filename, relative_path
LIMIT ? OFFSET ?

images has no index covering filename (only relative_path, exact_hash, perceptual_hash). Every chunk call therefore does a full-table scan + temp-b-tree sort of the whole images table, then discards OFFSET rows before returning chunk_size (default 200). Cost per chunk grows with OFFSET, making a full drain O(n^2) in image count — the same anti-pattern class as issue #184, but in the cache-hydrate path (CachedThumbnailLoadWorker, started from open_directory() off the main thread) rather than the live enrichment queue.

Measured locally (125k rows, 75k pre-cached, chunk_size=200, same shape as the report):

First ~30 chunks (out of 375 total): ~9.8s cumulative, already ~326ms/chunk average and rising.
A single chunk near the end of the cached range (~chunk #370): 4.9 seconds by itself.
Full drain extrapolates to many minutes, i.e. all cached thumbnails for a directory this size may not finish appearing within a normal work session, even though every one of them requires zero decode work — they're already in the DB.

This is a different bug from #184: #184's O(n^2) was in the pending-enrichment dispatch loop and is already fixed; this one is in the separate already-cached-thumbnail hydrate path that #184 did not touch.

Suggested fix direction

Add an index covering (filename, relative_path) on images (bump WORKING_DB_VERSION, additive migration).
Replace iter_thumbnail_blob_dicts()'s OFFSET pagination with keyset/seek pagination (WHERE (filename, relative_path) > (?, ?)), so each chunk is an index seek instead of a rescan from the start.

Acceptance criteria

On a workspace with a large already-cached thumbnail set, cached thumbnails for all rows (including ones far into the alphabetically-sorted set) appear within a few seconds of directory open, not minutes.
iter_thumbnail_blob_dicts() chunk cost stays roughly flat (no growth with offset) as the cached set grows.
Existing chunking/coverage test (test_iter_thumbnail_blob_dicts_chunks) still passes.
Schema migration is additive only; existing working DBs pick up the new index on next open without data loss.
No change to live-scan (#184) behavior or ordering.

## Affected area Directory-open cache hydrate (`CachedThumbnailLoadWorker` / `WorkingDB.iter_thumbnail_blob_dicts()`), separate from the live metadata/thumbnail scan (`scan_metadata_ordered_and_upsert`, issue #184). ## Report (from live testing on #184's follow-up branch) On a ~125,000-image network workspace (Windows 11, remote share, thumbnail thread count 16) that had been scanned/tested repeatedly before (so a large fraction of images already have cached `thumbnail_blob`s from prior runs — ~75,000 of 125,000 in the reproduction), reopening the directory shows the first ~276 alphabetically-sorted placeholder rows on screen with **no thumbnails populated**, for far longer than the live scan's ~30 img/s rate would predict (the live scan is correctly only processing the ~50,000 genuinely-pending images, confirmed via its own progress counter). User's own diagnosis: the process that displays already-cached thumbnails appears to be "buried" and should run before/independently of new generation — which is exactly right. ## Root cause `WorkingDB.iter_thumbnail_blob_dicts()` (`db/working_db.py`) pages through cached rows with: ```sql SELECT relative_path, thumbnail_blob FROM images WHERE thumbnail_blob IS NOT NULL ORDER BY filename, relative_path LIMIT ? OFFSET ? ``` `images` has no index covering `filename` (only `relative_path`, `exact_hash`, `perceptual_hash`). Every chunk call therefore does a full-table scan + temp-b-tree sort of the whole `images` table, then discards `OFFSET` rows before returning `chunk_size` (default 200). Cost per chunk grows with `OFFSET`, making a full drain O(n^2) in image count — the same anti-pattern class as issue #184, but in the cache-hydrate path (`CachedThumbnailLoadWorker`, started from `open_directory()` off the main thread) rather than the live enrichment queue. Measured locally (125k rows, 75k pre-cached, chunk_size=200, same shape as the report): - First ~30 chunks (out of 375 total): ~9.8s cumulative, already ~326ms/chunk average and rising. - A single chunk near the end of the cached range (~chunk #370): **4.9 seconds** by itself. - Full drain extrapolates to many minutes, i.e. all cached thumbnails for a directory this size may not finish appearing within a normal work session, even though every one of them requires zero decode work — they're already in the DB. This is a different bug from #184: #184's O(n^2) was in the pending-enrichment dispatch loop and is already fixed; this one is in the separate already-cached-thumbnail hydrate path that #184 did not touch. ## Suggested fix direction - Add an index covering `(filename, relative_path)` on `images` (bump `WORKING_DB_VERSION`, additive migration). - Replace `iter_thumbnail_blob_dicts()`'s OFFSET pagination with keyset/seek pagination (`WHERE (filename, relative_path) > (?, ?)`), so each chunk is an index seek instead of a rescan from the start. ## Acceptance criteria - [ ] On a workspace with a large already-cached thumbnail set, cached thumbnails for all rows (including ones far into the alphabetically-sorted set) appear within a few seconds of directory open, not minutes. - [ ] `iter_thumbnail_blob_dicts()` chunk cost stays roughly flat (no growth with offset) as the cached set grows. - [ ] Existing chunking/coverage test (`test_iter_thumbnail_blob_dicts_chunks`) still passes. - [ ] Schema migration is additive only; existing working DBs pick up the new index on next open without data loss. - [ ] No change to live-scan (#184) behavior or ordering.

Claude added the

Kind/Bug

Priority/High

Reviewed/Confirmed

labels

2026-08-28 21:42:59 +00:00

Claude referenced this issue from a commit

2026-08-28 21:51:15 +00:00

fix(db): use seek pagination for cached-thumbnail hydrate (issue #190)

Claude referenced this issue

2026-08-29 00:18:40 +00:00

[Bug]: SQLite variable limit silently disables grid preload, cached-thumbnail hydrate, and prune at ~33k+ images #191