Thumbnail of partner photo take 2 second to load in my timeline

[yuanl]

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

• Yes

The bug

I notice that both web and iOS mobile app takes longer time to load photos that my partner shared to me. Then I enable PostgreSQL slowlog and found below logs.

2025-09-25 13:30:14.177 UTC [56] LOG:  duration: 2158.242 ms  execute <unnamed>: select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array[$1]::uuid[]  and ("album"."ownerId" = $2 or "user"."id" = $3) and "album"."deletedAt" is null
2025-09-25 13:30:14.177 UTC [56] DETAIL:  parameters: $1 = 'b9964216-3c02-4d22-a4ce-28e779f0b99f', $2 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb', $3 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'
2025-09-25 13:30:14.202 UTC [61] LOG:  duration: 2064.491 ms  execute <unnamed>: select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array[$1]::uuid[]  and ("album"."ownerId" = $2 or "user"."id" = $3) and "album"."deletedAt" is null
2025-09-25 13:30:14.202 UTC [61] DETAIL:  parameters: $1 = 'c438ea18-6f43-4c00-b5e2-abca9024863c', $2 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb', $3 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'

One slowlog above should has something to do with image at this URL, I believe https://FQDN.domain/photos/b9964216-3c02-4d22-a4ce-28e779f0b99f

All the photo upload by myself load very fast in my timeline. To rule out the effect of cache, I did restart the whole immich stack. My thumbnail still load way faster in my timeline.

On the other hand, my partner has no issue loading my photos in their timeline. However, I do have a much bigger library then my partner. Could it be relative? Just for you info.

As show below I set DB_STORAGE_TYPE: 'HDD' in my PostgreSQL environment. To my understand HDD is sinificant slower than SSD. But it shouldn't make SQL query my partner's this slower compare to mine.

The OS that Immich Server is running on

Debian Trixie

Version of Immich Server

v1.143.1

Version of Immich Mobile App

v1.143.1

Platform with the issue

• Server
• Web
• Mobile

Device make and model

No response

Your docker-compose.yml content

#
# WARNING: Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.
#

name: immich

include:
  - immich-drop-compose.yaml

networks:
  frontend:
    external: true

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
      - /tank/photo:/mnt/media/photo
      # Geodata in Chinese
      - ./geodata:/build/geodata
      - ./i18n-iso-countries/langs:/usr/src/app/server/node_modules/i18n-iso-countries/langs
    env_file:
      - .env
    user: 1000:100
    networks:
      - frontend
      - default
    expose:
      - 3001
    depends_on:
      - redis
      - database
    restart: always
    healthcheck:
      disable: false
    labels:
      - traefik.enable=true
      - traefik.http.routers.immich.rule=Host(`REDACTED`)
      # - traefik.services.immich.loadbalancer.server.port=3001

  immich-machine-learning:
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - ./model-cache:/cache
    env_file:
      - .env
    restart: always
    healthcheck:
      disable: false

  redis:
    container_name: immich_redis
    image: docker.io/valkey/valkey:8-bookworm@sha256:fea8b3e67b15729d4bb70589eb03367bab9ad1ee89c876f54327fc7c6e618571
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:c44be5f2871c59362966d71eab4268170eb6f5653c0e6170184e72b38ffdf107
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
      # Uncomment the DB_STORAGE_TYPE: 'HDD' var if your database isn't stored on SSDs
      DB_STORAGE_TYPE: 'HDD'
    volumes:
      # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    shm_size: 128mb
    restart: always

Your .env content

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/tank/photo/immich-upload
# The location where your database files are stored
DB_DATA_LOCATION=./postgres

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
# TZ=Etc/UTC

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release

IMMICH_TRUSTED_PROXIES=172.16.0.0/12

# IMMICH_LOG_LEVEL=debug

# Connection secret for postgres. You should change it to a random password
# Please use only the characters `A-Za-z0-9`, without special characters or spaces
DB_PASSWORD=REDACTED

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

...

Relevant log output

Additional information

No response

[skatsubo]

Thanks for reporting this.

Could you also enable autoexplain (in addition to slow query logging)? Something along these lines:

# enable autoexplain
CONF="
shared_preload_libraries = 'vchord.so, vectors.so, auto_explain'
auto_explain.log_min_duration = '500ms'
"
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

[yuanl]

Thanks for the suggestion. Below are two of the auto_explain logs:

2025-09-26 06:45:49.354 UTC [57] LOG:  duration: 1740.506 ms  plan:
	Query Text: select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array[$1]::uuid[]  and ("album"."ownerId" = $2 or "user"."id" = $3) and "album"."deletedAt" is null
	Nested Loop  (cost=18.48..2141.31 rows=1 width=32)
	  ->  Hash Left Join  (cost=18.05..1242.69 rows=198 width=16)
	        Hash Cond: ("albumUsers"."usersId" = "user".id)
	        Filter: ((album."ownerId" = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'::uuid) OR ("user".id = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'::uuid))
	        ->  Nested Loop Left Join  (cost=16.01..1239.58 rows=395 width=48)
	              ->  Hash Join  (cost=13.83..1198.86 rows=259 width=48)
	                    Hash Cond: ("albumAssets"."albumsId" = album.id)
	                    ->  Seq Scan on album_asset "albumAssets"  (cost=0.00..1054.35 rows=49235 width=32)
	                    ->  Hash  (cost=13.80..13.80 rows=2 width=32)
	                          ->  Seq Scan on album  (cost=0.00..13.80 rows=2 width=32)
	                                Filter: ("deletedAt" IS NULL)
	              ->  Memoize  (cost=2.18..9.29 rows=3 width=32)
	                    Cache Key: album.id
	                    Cache Mode: logical
	                    ->  Bitmap Heap Scan on album_user "albumUsers"  (cost=2.17..9.28 rows=3 width=32)
	                          Recheck Cond: ("albumsId" = album.id)
	                          ->  Bitmap Index Scan on "album_user_albumsId_idx"  (cost=0.00..2.17 rows=3 width=0)
	                                Index Cond: ("albumsId" = album.id)
	        ->  Hash  (cost=2.02..2.02 rows=2 width=16)
	              ->  Seq Scan on "user"  (cost=0.00..2.02 rows=2 width=16)
	                    Filter: ("deletedAt" IS NULL)
	  ->  Index Scan using asset_pkey on asset  (cost=0.42..4.54 rows=1 width=32)
	        Index Cond: (id = "albumAssets"."assetsId")
	        Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{50ffba07-2b85-449e-9a9b-3ba1c02482ee}'::uuid[]))
2025-09-26 06:45:49.385 UTC [70] LOG:  duration: 2208.329 ms  execute <unnamed>: select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array[$1]::uuid[]  and ("album"."ownerId" = $2 or "user"."id" = $3) and "album"."deletedAt" is null
2025-09-26 06:45:49.385 UTC [70] DETAIL:  parameters: $1 = 'd4ca7d45-385e-4b54-b849-16f87a7dc2dc', $2 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb', $3 = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'
2025-09-26 06:45:49.386 UTC [70] LOG:  duration: 2208.311 ms  plan:
	Query Text: select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array[$1]::uuid[]  and ("album"."ownerId" = $2 or "user"."id" = $3) and "album"."deletedAt" is null
	Nested Loop  (cost=18.48..2141.31 rows=1 width=32)
	  ->  Hash Left Join  (cost=18.05..1242.69 rows=198 width=16)
	        Hash Cond: ("albumUsers"."usersId" = "user".id)
	        Filter: ((album."ownerId" = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'::uuid) OR ("user".id = 'cb10fabd-5fce-497e-ab95-f29b6af0d0bb'::uuid))
	        ->  Nested Loop Left Join  (cost=16.01..1239.58 rows=395 width=48)
	              ->  Hash Join  (cost=13.83..1198.86 rows=259 width=48)
	                    Hash Cond: ("albumAssets"."albumsId" = album.id)
	                    ->  Seq Scan on album_asset "albumAssets"  (cost=0.00..1054.35 rows=49235 width=32)
	                    ->  Hash  (cost=13.80..13.80 rows=2 width=32)
	                          ->  Seq Scan on album  (cost=0.00..13.80 rows=2 width=32)
	                                Filter: ("deletedAt" IS NULL)
	              ->  Memoize  (cost=2.18..9.29 rows=3 width=32)
	                    Cache Key: album.id
	                    Cache Mode: logical
	                    ->  Bitmap Heap Scan on album_user "albumUsers"  (cost=2.17..9.28 rows=3 width=32)
	                          Recheck Cond: ("albumsId" = album.id)
	                          ->  Bitmap Index Scan on "album_user_albumsId_idx"  (cost=0.00..2.17 rows=3 width=0)
	                                Index Cond: ("albumsId" = album.id)
	        ->  Hash  (cost=2.02..2.02 rows=2 width=16)
	              ->  Seq Scan on "user"  (cost=0.00..2.02 rows=2 width=16)
	                    Filter: ("deletedAt" IS NULL)
	  ->  Index Scan using asset_pkey on asset  (cost=0.42..4.54 rows=1 width=32)
	        Index Cond: (id = "albumAssets"."assetsId")
	        Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{d4ca7d45-385e-4b54-b849-16f87a7dc2dc}'::uuid[]))