immich

mirror of https://github.com/immich-app/immich.git synced 2026-10-18 14:36:41 +08:00

Files

T

History

bo0tzz 9ef6f16655 perf(server): evaluate V3 ids.all filters as an uncorrelated IN

albumIds/personIds/tagIds `all` filters used a correlated
EXISTS (... GROUP BY assetId HAVING count(distinct id) = n). An EXISTS with
an aggregate can't be turned into a semi-join, so it ran as a per-row SubPlan
for every candidate asset walked in fileCreatedAt order; with rare or no
matches that is the user's whole library.

Use asset.id IN (... GROUP BY assetId HAVING ...) instead: same per-asset
condition, aggregated once over the matching membership rows, and a normal
semi-join the planner can cost. Results are identical.

| user0 (920k set), take 100        | before  | after  |
| --------------------------------- | ------- | ------ |
| albumIds.all (150k + 10k album)   | 1200 ms | 140 ms |
| personIds.all (no common assets)  | 2600 ms | 5 ms   |
| tagIds.all (parent+child, dense)  | 37 ms   | 80 ms  |

The dense-tag case is slower because the whole tag membership is grouped
instead of stopping at the first 100 hits; the cost is now bounded by the
size of the filtered albums/people/tags rather than the library.

2026-10-13 14:46:12 +02:00