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.
With withSharedAlbums, getMapMarkers filtered on
`ownerId IN (...) OR EXISTS (album_asset ... albumId IN (<all album ids>))`.
The OR can't use an index for either side, so Postgres walked every located
exif row in the instance and probed asset plus album_asset for each.
Query owned assets and album assets in separate UNION ALL branches; the album
branch excludes the owners so nothing is returned twice. Results are
identical (diffed with an id tiebreak). The default request (no shared
albums) produces the same plan as before.
| user assets (albums) | before | after |
| -------------------- | ------- | ------- |
| 600k (2034) | 3300 ms | 950 ms |
| 200k (434) | 4300 ms | 1100 ms |
| 10k (33) | 5000 ms | 170 ms |
Most of the remaining time at 600k is the owned branch itself (~500 ms for
338k markers without shared albums).
getAll filtered groups with json_array_length(assets) > 1 outside the CTE.
Postgres inlines the CTE and turns that into a HAVING on a second copy of the
json_agg(...) expression; the copies carry distinct tag SubPlans, so the
aggregate isn't deduplicated and every member's asset/exif/tags JSON was
built twice. count(*) counts the same joined rows, so it's an equivalent
filter that leaves a single json_agg. Output is identical.
| user assets (dup groups) | before | after |
| ------------------------ | ------ | ------ |
| 600k (3k) | 185 ms | 110 ms |
| 200k (1k) | 71 ms | 41 ms |
| 10k (50) | 4.7 ms | 3.5 ms |
Search queries with withExif selected to_json(asset_exif) next to the
ORDER BY ... LIMIT, so Postgres built the exif JSON (and carried the whole
exif row) for every candidate row before the top-N sort threw them away.
For the large-files page this also pushed the planner into a merge join that
scanned all of asset_exif once per parallel worker.
Select exif through a correlated subquery instead, which is evaluated only
for the rows that are returned. The inner/left joins stay, so filtering and
ordering are unchanged; results are identical.
| query (user set) | before | after |
| -------------------------------- | ------- | ------- |
| searchLargeAssets (920k) | 3050 ms | 210 ms |
| searchLargeAssets (870k) | 2900 ms | 210 ms |
| searchLargeAssets (10k) | 50 ms | 31 ms |
| searchRandom withExif (920k) | 7000 ms | 950 ms |
| searchRandom withExif (10k) | 75 ms | 31 ms |
| V3 order=fileSizeInBytes (920k) | 2000 ms | 800 ms |
searchMetadata with withExif (web search page) usually walks the
fileCreatedAt index and was already cheap; it is unchanged (±5 ms).
Single-row job queries using withExifInner are unaffected.
getAssetsByOriginalPath matched `%<path>/%`, so opening /a/b also listed
files from /x/a/b, and `_`/`%` in folder names acted as wildcards (a_b also
matched a-b). Match the directory as an escaped prefix instead. The mobile app
strips the leading slash from the folder path, so for relative input both the
path as-is and with a leading slash are matched.
Mostly a correctness fix: every owned row is still scanned, prefix LIKE is
only slightly cheaper than the leading-wildcard one (600k user, 5k-asset
folder: 155 ms -> 132 ms; mobile form ~170 ms either way). It does make the
predicate usable by a ("ownerId", "originalPath" text_pattern_ops) index
(~60 ms in testing), which is left as a separate decision.
getUniqueOriginalPaths evaluated a regex substring for every owned asset
before deduplicating into a few hundred directories. left/split_part yields
the same directory (everything up to and including the last '/') at a
fraction of the cost. Output is identical (diffed for 4 users).
| user assets | before | after |
| ---------------- | ------- | ------- |
| 600k (241 dirs) | 2950 ms | 2230 ms |
| 200k (7.5k dirs) | 1050 ms | 690 ms |
| 10k | 75 ms | 44 ms |
The rest is a sort+unique over every row: the planner can't estimate the
distinct count of the expression, so it never picks a hash aggregate. A
statistics object on this exact expression fixes the estimate (~450 ms at
600k); left as a separate decision since it needs a migration.
albumAsset.getUpdates and albumAssetExif.getUpdates join through
album_asset and album_user, so an asset that is in k of the user's albums
is sent k times. The rows are identical (all columns come from asset /
asset_exif) and ordered by the asset's updateId, so duplicates are
adjacent; skip them while streaming. The mobile client upserts by id, so
the end state is unchanged.
An EXISTS semi-join returns the same rows but the planner flips between a
nested loop and a hash semi join that seq-scans album_asset (up to 2x
slower), so the dedup is done while streaming instead.
Rows streamed (perf DB, all album memberships created before the ack):
| window | user | rows | distinct assets |
| ------ | ---- | ------ | --------------- |
| 1 hour | 600k | 38,780 | 18,625 |
| | 100k | 8,166 | 6,924 |
| | 10k | 478 | 441 |
| 30 day | 600k | 70,750 | 34,601 |
streamAssetPathsForMissingFiles unioned asset and asset_file paths first
and then left joined integrity_report on
(assetId = allPaths.assetId OR fileAssetId = allPaths.fileAssetId). The OR
can't be hashed or merged, so every one of the ~4.3M paths does a BitmapOr
probe of both report indexes. Each union branch only ever matches one side
of the OR (the other id is null), so join each branch on its own column.
Same rows (count, reportId count and row hash compared with 20k fake
reports in a temp table).
4.3M paths, serial (streamed via cursor):
| missing_file reports | before | after |
| -------------------- | ------- | ------- |
| 0 | 1.78 s | 1.05 s |
| 20k | 5.2 s | 1.23 s |
The nightly untracked-files check looks up every file on disk in 10k-path
batches, but asset_file.path has no index: getAssetFilePathsByPaths and
getTrackedPaths seq-scan all 3M asset_file rows per batch, and
getAssetPathsByPaths ORs originalPath with asset_file.path across a left
join, which forces a full hash join of asset x asset_file per batch. The
job is therefore quadratic in library size.
Add a hash index on asset_file.path (equality only; 81 MB vs 392 MB for
a btree, and no collation compares on lookup, which dominate a btree
probe with en_US paths) and split getAssetPathsByPaths into a UNION ALL
so each branch can use its index. The UNION ALL can return an asset twice
when both its original and encoded video are in the batch; the caller
only builds a set of paths, which is identical.
One batch of 10k real paths, 1.36M assets / 3M asset_file rows:
| query | before | after |
| -------------------------- | ------ | ------ |
| getAssetPathsByPaths | 490 ms | 360 ms |
| getAssetFilePathsByPaths | 240 ms | 17 ms |
| getTrackedPaths | 400 ms | 217 ms |
Per night that is ~165 asset batches and ~270 thumbnail batches here
(~2.5 min -> ~1 min of DB time), and the per-batch cost no longer grows
with the table.
upsertTags calls upsertValue for every component of every tag on every
metadata extraction. The ON CONFLICT DO UPDATE always rewrites the row:
the updatedAt trigger bumps updatedAt and the indexed updateId, so each
call is a non-HOT update (new heap tuple, four index entries, WAL) plus a
row lock on the hot root tags that all workers share. Re-extracting a
library with 180k tagged assets rewrites the same ~2k tag rows ~500k times.
Look the tag up first and only fall back to the upsert when it is missing
or its parent changed. RETURNING-based guards (ON CONFLICT ... WHERE)
would return no row for the unchanged case, so a plain select is simpler.
Measured on a copy of the tag tables (with the trigger): upsert of an
existing tag 0.32-0.45 ms and 5/5 updates non-HOT; select 0.07 ms, no
writes.
executeTakeFirst doesn't add a LIMIT, so every call fetched and parsed
all visible faces of the person just to keep the first one. Callers run it
once per person without a feature photo when queueing person thumbnails
and when picking new feature photos.
Largest person group in the perf DB (5k faces): 1-4.5 ms plus 5k rows
sent to node -> 0.06 ms, one row.
stack is the only synced table without an updateId index, so every
StacksV1/PartnerStacksV1 sync walks all of the user's (or partners')
stacks via the ownerId index, filters on the window and sorts, even when
nothing changed. stack_audit has no userId index, so a fresh device scans
the whole audit table for its stack deletes. Both are consistent with the
other synced/audit tables.
12.5k stacks (6k for user0), 11.5k stack_audit rows:
| query | user | before | after |
| ------------------------------ | ------ | ------- | -------- |
| stack.getUpserts, no changes | 600k | 0.8 ms | 0.004 ms |
| partnerStack.getUpserts, same | 600k | 0.6 ms | 0.007 ms |
| stack.getDeletes, no ack | 10k | 0.7 ms | 0.04 ms |
| partnerStack.getDeletes, no ack| 600k | 5 ms | 0.8 ms |
Small in absolute terms, but it runs on every sync and grows linearly
with the number of stacks.
AssetsV2 and PartnerAssetsV2 sync filter on ownerId plus an updateId
window, ordered by updateId. With only single-column indexes the planner
BitmapAnds the user's whole ownerId bitmap with the window and sorts, so an
incremental sync pays for the user's library size whenever other users had
churn, and first syncs / partner backfills sort or filter the full table.
A composite index turns all of them into an ordered range scan.
1.36M assets, 60k updated in the last day (mostly by other users).
Backfill numbers are serial: cursor-streamed queries can't use parallel
workers in production.
| query | user | before | after |
| ---------------------------- | ----- | ------- | ------ |
| asset.getUpserts, 1 day ack | 10k | 12 ms | 0.3 ms |
| | 100k | 26 ms | 3 ms |
| | 600k | 88 ms | 18 ms |
| asset.getUpserts, no ack | 10k | 21 ms | 7 ms |
| | 100k | 94 ms | 70 ms |
| | 600k | 690 ms | 470 ms |
| partnerAsset.getUpserts, 1 d | 325k | 91 ms | 25 ms |
| | 650k | 105 ms | 32 ms |
| partnerAsset.getBackfill | 100k | 180 ms | 89 ms |
| | 200k | 290 ms | 172 ms |
| | 650k | 900 ms | 470 ms |
The generic plan (force_generic_plan) was BitmapAnd + Sort for every
owner; with the index it is the same range scan (600k user, 1 day ack:
62 ms -> 21 ms).
Album create, update (rename, description, cover, sort order, activity
toggle) and add-users json_agg'd every asset of the album with its exif
row, only for mapAlbum to read the count and the first/last
localDateTime. Use getMetadataForIds for those fields, as GET /albums/:id
already does, and drop the now unused withAssets option from getById.
The responses now carry the same date format and lastModifiedAssetTimestamp
as GET /albums/:id.
PATCH /albums/:id on a 150k-asset album, PG 14 (response is ~900 B either
way): 3.4-3.6 s -> 0.13-0.16 s.
Every mobile sync runs assetFace.getUpsertsV3 (and V2 for older apps),
which filters and orders on asset_face.updateId. Unlike the other synced
tables the column had no index, so each sync seq-scanned every face in
the instance, even when nothing had changed.
Incremental sync with nothing changed, PG 14, 425k faces:
| user | before | after |
|-------------|--------|---------|
| 10k assets | 37 ms | 0.15 ms |
| 600k assets | 33 ms | 0.16 ms |
The scan grows with the instance's face count (~10x that at 4M faces).
Since #28686 (v3.1.0) smart search orders by `embedding <=> $q, asset.id`.
On PG < 17 an ordered vector index scan can only satisfy an ORDER BY whose
keys all match the index, so the second key rules clip_index out and every
search computes the distance for all of the user's embeddings (for most
libraries: a parallel seq scan of the whole smart_search table plus an
on-disk sort). Distances are floats, so the tiebreak only matters for
exact duplicates; drop it.
Median, PG 14, vchordrq 2048 lists / 256 probes, 1.25M embeddings, 101 rows:
| user (share of instance) | before | after |
|--------------------------|---------|--------|
| 10k assets (0.8%) | 152 ms | 662 ms |
| 100k assets (8%) | 586 ms | 112 ms |
| 200k assets (16%) | 1880 ms | 143 ms |
| 600k assets (48%) | 1988 ms | 57 ms |
Users owning a tiny share of a large instance get slower: the index scan
post-filters by owner and reads ~13k candidates for 101 hits, which is the
pre-3.1 behaviour. Measured with random embeddings, which cluster worse
than real CLIP vectors.
The search filter's suggestion endpoints (country/state/city/make/model/
lens; opening the filter fires up to six) used DISTINCT ON over the join
of the user's assets with exif, which Postgres can only run as a full sort
of every value followed by Unique. GROUP BY lets it hash-aggregate and
sort only the distinct values; ORDER BY keeps the output order explicit.
Median, PG 14:
| user | city before | city after | make before | make after |
|-------------|-------------|------------|-------------|------------|
| 10k assets | 34 ms | 32 ms | 34 ms | 29 ms |
| 200k assets | 371 ms | 232 ms | 347 ms | 220 ms |
| 600k assets | 873 ms | 402 ms | 501 ms | 390 ms |
With orderBy=createdAt (the Recently added page), getTimeBucket filtered on
date_trunc('MONTH', createdAt AT TIME ZONE 'UTC'), which has no matching
index (only the localDateTime month expression is indexed), so every bucket
request read all of the user's assets. Use the equivalent half-open UTC
range instead so asset_createdAt_idx can be combined with the owner index.
Results are identical.
Median per bucket, PG 14:
| user | before | after |
|-------------|--------|---------|
| 10k assets | 29 ms | 4.5 ms |
| 200k assets | 152 ms | 28 ms |
| 600k assets | 149 ms | 98 ms |
getAssetIdByCity (every Explore page load) first grouped the asset_exif
rows of the whole instance by city, then walked cities alphabetically,
probing each exif row for one of the user's images until 12 cities were
found. The cost followed instance size rather than the user's library, and
a user with fewer than 12 qualifying cities touched every geotagged row.
Aggregate the user's own timeline images by city instead and return the
12 cities with the most of them (at least 5 each).
Behaviour change: this shows the user's most photographed cities rather
than the alphabetically first ones, and the 5-asset threshold now counts
only the user's own images instead of every user's (including trashed).
Median, PG 14, 1.36M-asset instance:
| user | before | after |
|---------------------------|---------|--------|
| 10k assets | 374 ms | 26 ms |
| 10k assets, only 3 cities | 3.9 s | 20 ms |
| 200k assets | 370 ms | 250 ms |
| 600k assets | ~500 ms | ~500 ms (noisy) |
SharedLinkRepository.get (shared link page load, /shared-links/me, and the
OpenGraph tags rendered for /share/:key) json_agg'd every asset of the
album, with its full exif row, ordered by fileCreatedAt. The only things
used from it are the asset count and the first/last localDateTime.
Load the album without assets and fill assetCount/startDate/endDate from
getMetadataForIds, which is what GET /albums/:id already uses. The counts
now also respect the default visibility filter, and the dates are
formatted the same as on /albums/:id.
150k-asset album link, PG 14:
| | before | after |
|------------------------|-----------------|--------|
| query | 4.2-6.5 s | ~0.26 s (metadata aggregate) |
| JSON built by postgres | 261 MB | - |
| GET /shared-links/me | - | 0.28 s, 961 B |
checkSharedLinkAccess runs on every thumbnail, preview, playback and asset
info request made through a shared link. It joined every asset of the
link's album and only then filtered on an array overlap across four
outer-joined columns, so each request cost O(album size).
Split it into two branches (link assets, album assets) that find the
requested ids through the asset pkey / livePhotoVideoId index and then
probe the album_asset pkey.
Median per request, PG 14, 1.36M-asset instance:
| album behind the link | before | after |
|-----------------------|--------|---------|
| 290 assets | 2.1 ms | 0.17 ms |
| 150k assets | 296 ms | 0.19 ms |
When logging feature flags at startup, avoid stringifying the features
in pretty format. Multiline log messages clutter logs and make it harder
to ingest and parse in log aggregation systems like loki, graylog or
logz.io.
* chore(mobile): rust native core
* chore(mobile): move the android byte buffer into the native core
* chore(mobile): move the raw photo rotation into the native core
* move the rotation into immich_core, the jni side only wraps it
* use the pointer loop in rotate again, the bounds checked writes were slower on a pixel 5
* chore(mobile): move the 10 bit conversion into the native core
* move the 10 bit conversion into immich_core, neon for 32 bit arm
* chore(mobile): one thumbhash decoder in the native core
* decode the thumbhash straight into the returned buffer, its jni entry sits next to the c export
* chore(mobile): move the 10 bit conversion into the native core
* move the 10 bit conversion into immich_core, neon for 32 bit arm
* chore(mobile): move the 10 bit conversion into the native core
* move the 10 bit conversion into immich_core, neon for 32 bit arm
* move the android rust flags into native/.cargo/config.toml
bo0tzz