Immich•3d ago

Slow response while going from image to image within an album

14M assets, 160k albums. Within a smallish album with 784 assets, the api/albums?assetId=<assetID> call can take up to 10s. Database and immich_server are on separate machines. The DB is on a VM with 16 cores@2,7GHz , 64 GiB RAM, the immich_server is on a Mac Mini M1 under asahi Linux. Currently running v.1.138.1, but this problem has been present for a while now. Going through favourites does not seem to take as long as going image-by-image though the assets of an album.

28 Replies

Immich•3d ago

:wave: Hey @O.Koslowski, Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:. References - Container Logs: docker compose logs docs - Container Status: docker ps -a docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy - Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA Checklist I have... 1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: read applicable release notes. 3. :ballot_box_with_check: reviewed the FAQs for known issues. 4. :ballot_box_with_check: reviewed Github for known issues. 5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy). 6. :ballot_box_with_check: uploaded the relevant information (see below). 7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable (an item can be marked as "complete" by reacting with the appropriate number) Information In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider: - Your docker-compose.yml and .env files. - Logs from all the containers and their status (see above). - All the troubleshooting steps you've tried so far. - Any recent changes you've made to Immich or your system. - Details about your system (both software/OS and hardware). - Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h). - The version of the Immich server, mobile app, and other relevant pieces. - Any other information that you think might be relevant. Please paste files and logs with proper code formatting, and especially avoid blurry screenshots. Without the right information we can't work out what the problem is. Help us help you ;) If this ticket can be closed you can use the /close command, and re-open it later if needed. Successfully submitted, a tag has been added to inform contributors. :white_check_mark:

Daniel•3d ago

Okay, so you're gonna need two things: 1. an asset id. You already got that in the request URL 2. your user id. You can find that in the admin settings (https://my.immich.app/admin/users) when you click on the respective user

O.KoslowskiOP•3d ago

Got it an asset id and my user id.

Daniel•3d ago

Are you familiar with running SQL queries?

O.KoslowskiOP•3d ago

Yep.

Daniel•3d ago

Cool that makes it easy!

explain analyze (select
  "album".*,
  (
    select
      to_json(obj)
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt"
        from
          "user"
        where
          "user"."id" = "album"."ownerId"
      ) as obj
  ) as "owner",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "album_user"."role",
          (
            select
              to_json(obj)
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt"
                from
                  "user"
                where
                  "user"."id" = "album_user"."usersId"
              ) as obj
          ) as "user"
        from
          "album_user"
        where
          "album_user"."albumsId" = "album"."id"
      ) as agg
  ) as "albumUsers"
from
  "album"
  inner join "album_asset" on "album_asset"."albumsId" = "album"."id"
where
  (
    "album"."ownerId" = '<your user id here>'
    or exists (
      select
      from
        "album_user"
      where
        "album_user"."albumsId" = "album"."id"
        and "album_user"."usersId" = '<your user id here>'
    )
  )
  and "album_asset"."assetsId" = '<the asset id here>'
  and "album"."deletedAt" is null
order by
  "album"."createdAt" desc,
  "album"."createdAt" desc);

explain analyze (select
  "album".*,
  (
    select
      to_json(obj)
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt"
        from
          "user"
        where
          "user"."id" = "album"."ownerId"
      ) as obj
  ) as "owner",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "album_user"."role",
          (
            select
              to_json(obj)
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt"
                from
                  "user"
                where
                  "user"."id" = "album_user"."usersId"
              ) as obj
          ) as "user"
        from
          "album_user"
        where
          "album_user"."albumsId" = "album"."id"
      ) as agg
  ) as "albumUsers"
from
  "album"
  inner join "album_asset" on "album_asset"."albumsId" = "album"."id"
where
  (
    "album"."ownerId" = '<your user id here>'
    or exists (
      select
      from
        "album_user"
      where
        "album_user"."albumsId" = "album"."id"
        and "album_user"."usersId" = '<your user id here>'
    )
  )
  and "album_asset"."assetsId" = '<the asset id here>'
  and "album"."deletedAt" is null
order by
  "album"."createdAt" desc,
  "album"."createdAt" desc);

Run this against the Immich DB :P

O.KoslowskiOP•3d ago

Okay, one sec, got to insert those IDs.

Daniel•3d ago

Should be in three places :)

O.KoslowskiOP•3d ago

Hm. I am getting a "relation album does not exist" o.O

Daniel•3d ago

Do you have the DB selected? Are you sure you're on 1.138? With psql you can use \dt to view all tables

O.KoslowskiOP•3d ago

Sort (cost=15.41..15.42 rows=1 width=184) (actual time=1.078..1.082 rows=1 loops=1) " Sort Key: album.""createdAt"" DESC" Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.98..15.40 rows=1 width=184) (actual time=0.932..0.938 rows=1 loops=1) " -> Index Scan using ""album_asset_assetsId_idx"" on album_asset (cost=0.56..2.98 rows=1 width=16) (actual time=0.332..0.334 rows=1 loops=1)" " Index Cond: (""assetsId"" = '79433e12-8e95-4f88-9de2-a4aa3f77a312'::uuid)" -> Index Scan using album_pkey on album (cost=0.42..4.48 rows=1 width=120) (actual time=0.174..0.174 rows=1 loops=1) " Index Cond: (id = album_asset.""albumsId"")" " Filter: ((""deletedAt"" IS NULL) AND ((""ownerId"" = 'da20e233-d72e-4382-bc05-04f0a9117db1'::uuid) OR (SubPlan 4)))" SubPlan 4 -> Index Only Scan using album_user_pkey on album_user album_user_1 (cost=0.42..1.64 rows=1 width=0) (never executed) " Index Cond: ((""albumsId"" = album.id) AND (""usersId"" = 'da20e233-d72e-4382-bc05-04f0a9117db1'::uuid))" Heap Fetches: 0 SubPlan 1 " -> Index Scan using ""user_updatedAt_id_idx"" on ""user"" (cost=0.12..2.54 rows=1 width=32) (actual time=0.302..0.306 rows=1 loops=1)" " Index Cond: (id = album.""ownerId"")" SubPlan 3 -> Aggregate (cost=5.39..5.40 rows=1 width=32) (actual time=0.081..0.082 rows=1 loops=1) " -> Index Scan using ""album_user_albumsId_idx"" on album_user (cost=0.42..2.84 rows=1 width=23) (actual time=0.072..0.072 rows=0 loops=1)" " Index Cond: (""albumsId"" = album.id)" SubPlan 2 " -> Index Scan using ""user_updatedAt_id_idx"" on ""user"" user_1 (cost=0.12..2.54 rows=1 width=32) (never executed)" " Index Cond: (id = album_user.""usersId"")" Planning Time: 12.811 ms Execution Time: 1.812 ms

Daniel•3d ago

That only took 1ms? 👀 Well two I guess

O.KoslowskiOP•3d ago

I'll take another assetID to get another timing.

Daniel•3d ago

I also have another query for you

explain analyze (select
  "album_asset"."albumsId" as "albumId",
  min(
    ("asset"."localDateTime" AT TIME ZONE 'UTC'::text)::date
  ) as "startDate",
  max(
    ("asset"."localDateTime" AT TIME ZONE 'UTC'::text)::date
  ) as "endDate",
  max("asset"."updatedAt") as "lastModifiedAssetTimestamp",
  count("asset"."id")::int as "assetCount"
from
  "asset"
  inner join "album_asset" on "album_asset"."assetsId" = "asset"."id"
where
  "asset"."visibility" in ('archive', 'timeline')
  and "album_asset"."albumsId" in ('<album id here>')
  and "asset"."deletedAt" is null
group by
  "album_asset"."albumsId");

explain analyze (select
  "album_asset"."albumsId" as "albumId",
  min(
    ("asset"."localDateTime" AT TIME ZONE 'UTC'::text)::date
  ) as "startDate",
  max(
    ("asset"."localDateTime" AT TIME ZONE 'UTC'::text)::date
  ) as "endDate",
  max("asset"."updatedAt") as "lastModifiedAssetTimestamp",
  count("asset"."id")::int as "assetCount"
from
  "asset"
  inner join "album_asset" on "album_asset"."assetsId" = "asset"."id"
where
  "asset"."visibility" in ('archive', 'timeline')
  and "album_asset"."albumsId" in ('<album id here>')
  and "asset"."deletedAt" is null
group by
  "album_asset"."albumsId");

Fill in your album id there

O.KoslowskiOP•3d ago

With that query it's Planning Time: 8.863 ms Execution Time: 250.542 ms

Daniel•3d ago

Okay can you show the plan of that?

O.KoslowskiOP•3d ago

GroupAggregate (cost=1.12..1230.46 rows=402 width=36) (actual time=250.319..250.321 rows=1 loops=1) " Group Key: album_asset.""albumsId""" -> Nested Loop (cost=1.12..1216.35 rows=404 width=48) (actual time=0.322..235.001 rows=4071 loops=1) -> Index Only Scan using album_asset_pkey on album_asset (cost=0.56..13.63 rows=404 width=32) (actual time=0.217..12.350 rows=4071 loops=1) " Index Cond: (""albumsId"" = '489bf8f0-babd-4f83-b245-45ead26da82c'::uuid)" Heap Fetches: 4071 " -> Index Scan using ""asset_id_stackId_idx"" on asset (cost=0.56..2.98 rows=1 width=32) (actual time=0.052..0.052 rows=1 loops=4071)" " Index Cond: (id = album_asset.""assetsId"")" " Filter: ((""deletedAt"" IS NULL) AND (visibility = ANY ('{archive,timeline}'::asset_visibility_enum[])))" Planning Time: 8.863 ms Execution Time: 250.542 ms That is a huge album, though.

Daniel•3d ago

And tbf it's still not anywhere close those 10s

O.KoslowskiOP•3d ago

GroupAggregate (cost=1.12..1230.46 rows=402 width=36) (actual time=20.040..20.042 rows=1 loops=1) " Group Key: album_asset.""albumsId""" -> Nested Loop (cost=1.12..1216.35 rows=404 width=48) (actual time=0.167..18.192 rows=784 loops=1) -> Index Only Scan using album_asset_pkey on album_asset (cost=0.56..13.63 rows=404 width=32) (actual time=0.082..1.188 rows=795 loops=1) " Index Cond: (""albumsId"" = 'a46c2372-d630-4e0f-a907-e081a8cbca37'::uuid)" Heap Fetches: 795 " -> Index Scan using ""asset_id_stackId_idx"" on asset (cost=0.56..2.98 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=795)" " Index Cond: (id = album_asset.""assetsId"")" " Filter: ((""deletedAt"" IS NULL) AND (visibility = ANY ('{archive,timeline}'::asset_visibility_enum[])))" Rows Removed by Filter: 0 Planning Time: 1.370 ms Execution Time: 20.161 ms What I don't quite undestand is why I can flick through a collection of favourited images in no time, but it will choke after two or three assets within the same album.

Daniel•3d ago

It's probably one query that isn't optimized And that's related to albums I'm gonna throw you another query to try shortly Sorry I'll have to come back at this a little later or tomorrow

O.KoslowskiOP•3d ago

No problem. Thank you so much for looking into this!

Daniel•3d ago

We got some issues around our build pipeline/dev tooling, which leads to me being unable to get more SQL queries for you right now 😅

Sergey Katsubo•3d ago

In principle, we can enable slow query log and autoexplain in Postgres. Smth along these lines:

# enable
CONF="
shared_preload_libraries = 'vchord.so, vectors.so, auto_explain'
log_min_duration_statement  = '20ms'
auto_explain.log_min_duration = '100ms'
log_temp_files = 0
"
echo "$CONF" | docker exec -i immich_postgres tee /var/lib/postgresql/data/postgresql.override.conf
docker restart immich_postgres

# view
docker logs --tail 100 -f immich_postgres

# undo
docker exec immich_postgres rm -f /var/lib/postgresql/data/postgresql.override.conf
docker restart immich_postgres

# enable
CONF="
shared_preload_libraries = 'vchord.so, vectors.so, auto_explain'
log_min_duration_statement  = '20ms'
auto_explain.log_min_duration = '100ms'
log_temp_files = 0
"
echo "$CONF" | docker exec -i immich_postgres tee /var/lib/postgresql/data/postgresql.override.conf
docker restart immich_postgres

# view
docker logs --tail 100 -f immich_postgres

# undo
docker exec immich_postgres rm -f /var/lib/postgresql/data/postgresql.override.conf
docker restart immich_postgres

Daniel•3d ago

Oh true that would be good too to get a broader idea of what's going on

O.KoslowskiOP•2d ago

I have enabled the logging, opened an album with 255 assets, selected an asset to be viewed full screen and viewed the next two images.

plan.log

Daniel•2d ago

Yeah ok this explains it In total you have quite a lot of albums? @O.Koslowski could you run this for us?

explain analyze update "album" 
set "albumThumbnailAssetId" = (
  select "album_asset"."assetsId" 
  from "album_asset" 
  inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
    and "asset"."deletedAt" is null 
  where "album_asset"."albumsId" = "album"."id" 
  order by "asset"."fileCreatedAt" desc limit 1
)
where (
  ("albumThumbnailAssetId" is null and exists (
    select 1 as "1" 
    from "album_asset" 
    inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
      and "asset"."deletedAt" is null 
    where "album_asset"."albumsId" = "album"."id"
  )) 
  or ("albumThumbnailAssetId" is not null and not exists (
    select 1 as "1" 
    from "album_asset" 
    inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
      and "asset"."deletedAt" is null 
    where "album_asset"."albumsId" = "album"."id" 
      and "album"."albumThumbnailAssetId" = "album_asset"."assetsId")));

explain analyze update "album" 
set "albumThumbnailAssetId" = (
  select "album_asset"."assetsId" 
  from "album_asset" 
  inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
    and "asset"."deletedAt" is null 
  where "album_asset"."albumsId" = "album"."id" 
  order by "asset"."fileCreatedAt" desc limit 1
)
where (
  ("albumThumbnailAssetId" is null and exists (
    select 1 as "1" 
    from "album_asset" 
    inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
      and "asset"."deletedAt" is null 
    where "album_asset"."albumsId" = "album"."id"
  )) 
  or ("albumThumbnailAssetId" is not null and not exists (
    select 1 as "1" 
    from "album_asset" 
    inner join "asset" on "album_asset"."assetsId" = "asset"."id" 
      and "asset"."deletedAt" is null 
    where "album_asset"."albumsId" = "album"."id" 
      and "album"."albumThumbnailAssetId" = "album_asset"."assetsId")));

O.KoslowskiOP•2d ago

Yeah, I have more than 130k albums. It's very much an edge case, I know. 😉 I'm sorry, that query did not work for me ("[2025-08-23 15:57:17] [42601] ERROR: syntax error at or near "update" [2025-08-23 15:57:17] Position: 18"

Alex Tran•2d ago

It’s good to have these edge cases to find out unoptimized queries 😁 thanks for helping

Gaming

Programming

Slow response while going from image to image within an album

Did you find this page helpful?