immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-02-05 00:30:57 +03:00
Code Issues 16 Packages Projects Releases 10 Wiki Activity

800,000+ Assets, Performance is unusable #2174

New Issue
Closed
opened 2026-02-05 05:28:03 +03:00 by OVERLORD · 26 comments
OVERLORD commented 2026-02-05 05:28:03 +03:00
Owner
Copy Link

Originally created by @mingqlin on GitHub (Feb 17, 2024).

The bug

Love Immich

I have 800,000+ Photos and Videos, but the performance is unusable. Mobile app never able to finish "Building the timeline". It appears this query takes too long. My Unraid server are Intel 13th Gen I7. I run query manually, following query take 1 to 2 second to finish. I hope all database query should be optimized

The OS that Immich Server is running on

Unraid

Version of Immich Server

v1.94.1

Version of Immich Mobile App

v1.94.1

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

UnRaid

Your .env content

UnRaid

Reproduction steps

` SELECT DISTINCT "distinctAlias"."AssetEntity_id" AS "ids_AssetEntity_id", "distinctAlias"."AssetEntity_fileCreatedAt"
FROM (SELECT "AssetEntity"."id"                                            AS "AssetEntity_id",
             "AssetEntity"."deviceAssetId"                                 AS "AssetEntity_deviceAssetId",
             "AssetEntity"."ownerId"                                       AS "AssetEntity_ownerId",
             "AssetEntity"."libraryId"                                     AS "AssetEntity_libraryId",
             "AssetEntity"."deviceId"                                      AS "AssetEntity_deviceId",
             "AssetEntity"."type"                                          AS "AssetEntity_type",
             "AssetEntity"."originalPath"                                  AS "AssetEntity_originalPath",
             "AssetEntity"."resizePath"                                    AS "AssetEntity_resizePath",
             "AssetEntity"."webpPath"                                      AS "AssetEntity_webpPath",
             "AssetEntity"."thumbhash"                                     AS "AssetEntity_thumbhash",
             "AssetEntity"."encodedVideoPath"                              AS "AssetEntity_encodedVideoPath",
             "AssetEntity"."createdAt"                                     AS "AssetEntity_createdAt",
             "AssetEntity"."updatedAt"                                     AS "AssetEntity_updatedAt",
             "AssetEntity"."deletedAt"                                     AS "AssetEntity_deletedAt",
             "AssetEntity"."fileCreatedAt"                                 AS "AssetEntity_fileCreatedAt",
             "AssetEntity"."localDateTime"                                 AS "AssetEntity_localDateTime",
             "AssetEntity"."fileModifiedAt"                                AS "AssetEntity_fileModifiedAt",
             "AssetEntity"."isFavorite"                                    AS "AssetEntity_isFavorite",
             "AssetEntity"."isArchived"                                    AS "AssetEntity_isArchived",
             "AssetEntity"."isExternal"                                    AS "AssetEntity_isExternal",
             "AssetEntity"."isReadOnly"                                    AS "AssetEntity_isReadOnly",
             "AssetEntity"."isOffline"                                     AS "AssetEntity_isOffline",
             "AssetEntity"."checksum"                                      AS "AssetEntity_checksum",
             "AssetEntity"."duration"                                      AS "AssetEntity_duration",
             "AssetEntity"."isVisible"                                     AS "AssetEntity_isVisible",
             "AssetEntity"."livePhotoVideoId"                              AS "AssetEntity_livePhotoVideoId",
             "AssetEntity"."originalFileName"                              AS "AssetEntity_originalFileName",
             "AssetEntity"."sidecarPath"                                   AS "AssetEntity_sidecarPath",
             "AssetEntity"."stackId"                                       AS "AssetEntity_stackId",
             "AssetEntity__AssetEntity_exifInfo"."assetId"                 AS "AssetEntity__AssetEntity_exifInfo_assetId",
             "AssetEntity__AssetEntity_exifInfo"."description"             AS "AssetEntity__AssetEntity_exifInfo_description",
             "AssetEntity__AssetEntity_exifInfo"."exifImageWidth"          AS "AssetEntity__AssetEntity_exifInfo_exifImageWidth",
             "AssetEntity__AssetEntity_exifInfo"."exifImageHeight"         AS "AssetEntity__AssetEntity_exifInfo_exifImageHeight",
             "AssetEntity__AssetEntity_exifInfo"."fileSizeInByte"          AS "AssetEntity__AssetEntity_exifInfo_fileSizeInByte",
             "AssetEntity__AssetEntity_exifInfo"."orientation"             AS "AssetEntity__AssetEntity_exifInfo_orientation",
             "AssetEntity__AssetEntity_exifInfo"."dateTimeOriginal"        AS "AssetEntity__AssetEntity_exifInfo_dateTimeOriginal",
             "AssetEntity__AssetEntity_exifInfo"."modifyDate"              AS "AssetEntity__AssetEntity_exifInfo_modifyDate",
             "AssetEntity__AssetEntity_exifInfo"."timeZone"                AS "AssetEntity__AssetEntity_exifInfo_timeZone",
             "AssetEntity__AssetEntity_exifInfo"."latitude"                AS "AssetEntity__AssetEntity_exifInfo_latitude",
             "AssetEntity__AssetEntity_exifInfo"."longitude"               AS "AssetEntity__AssetEntity_exifInfo_longitude",
             "AssetEntity__AssetEntity_exifInfo"."projectionType"          AS "AssetEntity__AssetEntity_exifInfo_projectionType",
             "AssetEntity__AssetEntity_exifInfo"."city"                    AS "AssetEntity__AssetEntity_exifInfo_city",
             "AssetEntity__AssetEntity_exifInfo"."livePhotoCID"            AS "AssetEntity__AssetEntity_exifInfo_livePhotoCID",
             "AssetEntity__AssetEntity_exifInfo"."autoStackId"             AS "AssetEntity__AssetEntity_exifInfo_autoStackId",
             "AssetEntity__AssetEntity_exifInfo"."state"                   AS "AssetEntity__AssetEntity_exifInfo_state",
             "AssetEntity__AssetEntity_exifInfo"."country"                 AS "AssetEntity__AssetEntity_exifInfo_country",
             "AssetEntity__AssetEntity_exifInfo"."make"                    AS "AssetEntity__AssetEntity_exifInfo_make",
             "AssetEntity__AssetEntity_exifInfo"."model"                   AS "AssetEntity__AssetEntity_exifInfo_model",
             "AssetEntity__AssetEntity_exifInfo"."lensModel"               AS "AssetEntity__AssetEntity_exifInfo_lensModel",
             "AssetEntity__AssetEntity_exifInfo"."fNumber"                 AS "AssetEntity__AssetEntity_exifInfo_fNumber",
             "AssetEntity__AssetEntity_exifInfo"."focalLength"             AS "AssetEntity__AssetEntity_exifInfo_focalLength",
             "AssetEntity__AssetEntity_exifInfo"."iso"                     AS "AssetEntity__AssetEntity_exifInfo_iso",
             "AssetEntity__AssetEntity_exifInfo"."exposureTime"            AS "AssetEntity__AssetEntity_exifInfo_exposureTime",
             "AssetEntity__AssetEntity_exifInfo"."profileDescription"      AS "AssetEntity__AssetEntity_exifInfo_profileDescription",
             "AssetEntity__AssetEntity_exifInfo"."colorspace"              AS "AssetEntity__AssetEntity_exifInfo_colorspace",
             "AssetEntity__AssetEntity_exifInfo"."bitsPerSample"           AS "AssetEntity__AssetEntity_exifInfo_bitsPerSample",
             "AssetEntity__AssetEntity_exifInfo"."fps"                     AS "AssetEntity__AssetEntity_exifInfo_fps",
             "AssetEntity__AssetEntity_tags"."id"                          AS "AssetEntity__AssetEntity_tags_id",
             "AssetEntity__AssetEntity_tags"."type"                        AS "AssetEntity__AssetEntity_tags_type",
             "AssetEntity__AssetEntity_tags"."name"                        AS "AssetEntity__AssetEntity_tags_name",
             "AssetEntity__AssetEntity_tags"."userId"                      AS "AssetEntity__AssetEntity_tags_userId",
             "AssetEntity__AssetEntity_tags"."renameTagId"                 AS "AssetEntity__AssetEntity_tags_renameTagId",
             "AssetEntity__AssetEntity_stack"."id"                         AS "AssetEntity__AssetEntity_stack_id",
             "AssetEntity__AssetEntity_stack"."primaryAssetId"             AS "AssetEntity__AssetEntity_stack_primaryAssetId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."id"               AS "bd93d5747511a4dad4923546c51365bf1a803774_id",
             "bd93d5747511a4dad4923546c51365bf1a803774"."deviceAssetId"    AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceAssetId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."ownerId"          AS "bd93d5747511a4dad4923546c51365bf1a803774_ownerId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."libraryId"        AS "bd93d5747511a4dad4923546c51365bf1a803774_libraryId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."deviceId"         AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."type"             AS "bd93d5747511a4dad4923546c51365bf1a803774_type",
             "bd93d5747511a4dad4923546c51365bf1a803774"."originalPath"     AS "bd93d5747511a4dad4923546c51365bf1a803774_originalPath",
             "bd93d5747511a4dad4923546c51365bf1a803774"."resizePath"       AS "bd93d5747511a4dad4923546c51365bf1a803774_resizePath",
             "bd93d5747511a4dad4923546c51365bf1a803774"."webpPath"         AS "bd93d5747511a4dad4923546c51365bf1a803774_webpPath",
             "bd93d5747511a4dad4923546c51365bf1a803774"."thumbhash"        AS "bd93d5747511a4dad4923546c51365bf1a803774_thumbhash",
             "bd93d5747511a4dad4923546c51365bf1a803774"."encodedVideoPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_encodedVideoPath",
             "bd93d5747511a4dad4923546c51365bf1a803774"."createdAt"        AS "bd93d5747511a4dad4923546c51365bf1a803774_createdAt",
             "bd93d5747511a4dad4923546c51365bf1a803774"."updatedAt"        AS "bd93d5747511a4dad4923546c51365bf1a803774_updatedAt",
             "bd93d5747511a4dad4923546c51365bf1a803774"."deletedAt"        AS "bd93d5747511a4dad4923546c51365bf1a803774_deletedAt",
             "bd93d5747511a4dad4923546c51365bf1a803774"."fileCreatedAt"    AS "bd93d5747511a4dad4923546c51365bf1a803774_fileCreatedAt",
             "bd93d5747511a4dad4923546c51365bf1a803774"."localDateTime"    AS "bd93d5747511a4dad4923546c51365bf1a803774_localDateTime",
             "bd93d5747511a4dad4923546c51365bf1a803774"."fileModifiedAt"   AS "bd93d5747511a4dad4923546c51365bf1a803774_fileModifiedAt",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isFavorite"       AS "bd93d5747511a4dad4923546c51365bf1a803774_isFavorite",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isArchived"       AS "bd93d5747511a4dad4923546c51365bf1a803774_isArchived",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isExternal"       AS "bd93d5747511a4dad4923546c51365bf1a803774_isExternal",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isReadOnly"       AS "bd93d5747511a4dad4923546c51365bf1a803774_isReadOnly",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isOffline"        AS "bd93d5747511a4dad4923546c51365bf1a803774_isOffline",
             "bd93d5747511a4dad4923546c51365bf1a803774"."checksum"         AS "bd93d5747511a4dad4923546c51365bf1a803774_checksum",
             "bd93d5747511a4dad4923546c51365bf1a803774"."duration"         AS "bd93d5747511a4dad4923546c51365bf1a803774_duration",
             "bd93d5747511a4dad4923546c51365bf1a803774"."isVisible"        AS "bd93d5747511a4dad4923546c51365bf1a803774_isVisible",
             "bd93d5747511a4dad4923546c51365bf1a803774"."livePhotoVideoId" AS "bd93d5747511a4dad4923546c51365bf1a803774_livePhotoVideoId",
             "bd93d5747511a4dad4923546c51365bf1a803774"."originalFileName" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalFileName",
             "bd93d5747511a4dad4923546c51365bf1a803774"."sidecarPath"      AS "bd93d5747511a4dad4923546c51365bf1a803774_sidecarPath",
             "bd93d5747511a4dad4923546c51365bf1a803774"."stackId"          AS "bd93d5747511a4dad4923546c51365bf1a803774_stackId"
      FROM "assets" "AssetEntity"
               LEFT JOIN "exif" "AssetEntity__AssetEntity_exifInfo"
                         ON "AssetEntity__AssetEntity_exifInfo"."assetId" = "AssetEntity"."id"
               LEFT JOIN "tag_asset" "AssetEntity_AssetEntity__AssetEntity_tags"
                         ON "AssetEntity_AssetEntity__AssetEntity_tags"."assetsId" = "AssetEntity"."id"
               LEFT JOIN "tags" "AssetEntity__AssetEntity_tags"
                         ON "AssetEntity__AssetEntity_tags"."id" = "AssetEntity_AssetEntity__AssetEntity_tags"."tagsId"
               LEFT JOIN "asset_stack" "AssetEntity__AssetEntity_stack"
                         ON "AssetEntity__AssetEntity_stack"."id" = "AssetEntity"."stackId"
               LEFT JOIN "assets" "bd93d5747511a4dad4923546c51365bf1a803774"
                         ON "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" = "AssetEntity__AssetEntity_stack"."id"
      WHERE ("AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND
             "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08')) "distinctAlias"
ORDER BY "distinctAlias"."AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC
LIMIT 5000 OFFSET 215000`

Additional information

No response

Originally created by @mingqlin on GitHub (Feb 17, 2024). ### The bug Love Immich I have 800,000+ Photos and Videos, but the performance is unusable. Mobile app never able to finish "Building the timeline". It appears this query takes too long. My Unraid server are Intel 13th Gen I7. I run query manually, following query take 1 to 2 second to finish. I hope all database query should be optimized ### The OS that Immich Server is running on Unraid ### Version of Immich Server v1.94.1 ### Version of Immich Mobile App v1.94.1 ### Platform with the issue - [X] Server - [X] Web - [X] Mobile ### Your docker-compose.yml content ```YAML UnRaid ``` ### Your .env content ```Shell UnRaid ``` ### Reproduction steps ```bash ` SELECT DISTINCT "distinctAlias"."AssetEntity_id" AS "ids_AssetEntity_id", "distinctAlias"."AssetEntity_fileCreatedAt" FROM (SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."deviceAssetId" AS "AssetEntity_deviceAssetId", "AssetEntity"."ownerId" AS "AssetEntity_ownerId", "AssetEntity"."libraryId" AS "AssetEntity_libraryId", "AssetEntity"."deviceId" AS "AssetEntity_deviceId", "AssetEntity"."type" AS "AssetEntity_type", "AssetEntity"."originalPath" AS "AssetEntity_originalPath", "AssetEntity"."resizePath" AS "AssetEntity_resizePath", "AssetEntity"."webpPath" AS "AssetEntity_webpPath", "AssetEntity"."thumbhash" AS "AssetEntity_thumbhash", "AssetEntity"."encodedVideoPath" AS "AssetEntity_encodedVideoPath", "AssetEntity"."createdAt" AS "AssetEntity_createdAt", "AssetEntity"."updatedAt" AS "AssetEntity_updatedAt", "AssetEntity"."deletedAt" AS "AssetEntity_deletedAt", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt", "AssetEntity"."localDateTime" AS "AssetEntity_localDateTime", "AssetEntity"."fileModifiedAt" AS "AssetEntity_fileModifiedAt", "AssetEntity"."isFavorite" AS "AssetEntity_isFavorite", "AssetEntity"."isArchived" AS "AssetEntity_isArchived", "AssetEntity"."isExternal" AS "AssetEntity_isExternal", "AssetEntity"."isReadOnly" AS "AssetEntity_isReadOnly", "AssetEntity"."isOffline" AS "AssetEntity_isOffline", "AssetEntity"."checksum" AS "AssetEntity_checksum", "AssetEntity"."duration" AS "AssetEntity_duration", "AssetEntity"."isVisible" AS "AssetEntity_isVisible", "AssetEntity"."livePhotoVideoId" AS "AssetEntity_livePhotoVideoId", "AssetEntity"."originalFileName" AS "AssetEntity_originalFileName", "AssetEntity"."sidecarPath" AS "AssetEntity_sidecarPath", "AssetEntity"."stackId" AS "AssetEntity_stackId", "AssetEntity__AssetEntity_exifInfo"."assetId" AS "AssetEntity__AssetEntity_exifInfo_assetId", "AssetEntity__AssetEntity_exifInfo"."description" AS "AssetEntity__AssetEntity_exifInfo_description", "AssetEntity__AssetEntity_exifInfo"."exifImageWidth" AS "AssetEntity__AssetEntity_exifInfo_exifImageWidth", "AssetEntity__AssetEntity_exifInfo"."exifImageHeight" AS "AssetEntity__AssetEntity_exifInfo_exifImageHeight", "AssetEntity__AssetEntity_exifInfo"."fileSizeInByte" AS "AssetEntity__AssetEntity_exifInfo_fileSizeInByte", "AssetEntity__AssetEntity_exifInfo"."orientation" AS "AssetEntity__AssetEntity_exifInfo_orientation", "AssetEntity__AssetEntity_exifInfo"."dateTimeOriginal" AS "AssetEntity__AssetEntity_exifInfo_dateTimeOriginal", "AssetEntity__AssetEntity_exifInfo"."modifyDate" AS "AssetEntity__AssetEntity_exifInfo_modifyDate", "AssetEntity__AssetEntity_exifInfo"."timeZone" AS "AssetEntity__AssetEntity_exifInfo_timeZone", "AssetEntity__AssetEntity_exifInfo"."latitude" AS "AssetEntity__AssetEntity_exifInfo_latitude", "AssetEntity__AssetEntity_exifInfo"."longitude" AS "AssetEntity__AssetEntity_exifInfo_longitude", "AssetEntity__AssetEntity_exifInfo"."projectionType" AS "AssetEntity__AssetEntity_exifInfo_projectionType", "AssetEntity__AssetEntity_exifInfo"."city" AS "AssetEntity__AssetEntity_exifInfo_city", "AssetEntity__AssetEntity_exifInfo"."livePhotoCID" AS "AssetEntity__AssetEntity_exifInfo_livePhotoCID", "AssetEntity__AssetEntity_exifInfo"."autoStackId" AS "AssetEntity__AssetEntity_exifInfo_autoStackId", "AssetEntity__AssetEntity_exifInfo"."state" AS "AssetEntity__AssetEntity_exifInfo_state", "AssetEntity__AssetEntity_exifInfo"."country" AS "AssetEntity__AssetEntity_exifInfo_country", "AssetEntity__AssetEntity_exifInfo"."make" AS "AssetEntity__AssetEntity_exifInfo_make", "AssetEntity__AssetEntity_exifInfo"."model" AS "AssetEntity__AssetEntity_exifInfo_model", "AssetEntity__AssetEntity_exifInfo"."lensModel" AS "AssetEntity__AssetEntity_exifInfo_lensModel", "AssetEntity__AssetEntity_exifInfo"."fNumber" AS "AssetEntity__AssetEntity_exifInfo_fNumber", "AssetEntity__AssetEntity_exifInfo"."focalLength" AS "AssetEntity__AssetEntity_exifInfo_focalLength", "AssetEntity__AssetEntity_exifInfo"."iso" AS "AssetEntity__AssetEntity_exifInfo_iso", "AssetEntity__AssetEntity_exifInfo"."exposureTime" AS "AssetEntity__AssetEntity_exifInfo_exposureTime", "AssetEntity__AssetEntity_exifInfo"."profileDescription" AS "AssetEntity__AssetEntity_exifInfo_profileDescription", "AssetEntity__AssetEntity_exifInfo"."colorspace" AS "AssetEntity__AssetEntity_exifInfo_colorspace", "AssetEntity__AssetEntity_exifInfo"."bitsPerSample" AS "AssetEntity__AssetEntity_exifInfo_bitsPerSample", "AssetEntity__AssetEntity_exifInfo"."fps" AS "AssetEntity__AssetEntity_exifInfo_fps", "AssetEntity__AssetEntity_tags"."id" AS "AssetEntity__AssetEntity_tags_id", "AssetEntity__AssetEntity_tags"."type" AS "AssetEntity__AssetEntity_tags_type", "AssetEntity__AssetEntity_tags"."name" AS "AssetEntity__AssetEntity_tags_name", "AssetEntity__AssetEntity_tags"."userId" AS "AssetEntity__AssetEntity_tags_userId", "AssetEntity__AssetEntity_tags"."renameTagId" AS "AssetEntity__AssetEntity_tags_renameTagId", "AssetEntity__AssetEntity_stack"."id" AS "AssetEntity__AssetEntity_stack_id", "AssetEntity__AssetEntity_stack"."primaryAssetId" AS "AssetEntity__AssetEntity_stack_primaryAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."id" AS "bd93d5747511a4dad4923546c51365bf1a803774_id", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceAssetId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."ownerId" AS "bd93d5747511a4dad4923546c51365bf1a803774_ownerId", "bd93d5747511a4dad4923546c51365bf1a803774"."libraryId" AS "bd93d5747511a4dad4923546c51365bf1a803774_libraryId", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceId", "bd93d5747511a4dad4923546c51365bf1a803774"."type" AS "bd93d5747511a4dad4923546c51365bf1a803774_type", "bd93d5747511a4dad4923546c51365bf1a803774"."originalPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalPath", "bd93d5747511a4dad4923546c51365bf1a803774"."resizePath" AS "bd93d5747511a4dad4923546c51365bf1a803774_resizePath", "bd93d5747511a4dad4923546c51365bf1a803774"."webpPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_webpPath", "bd93d5747511a4dad4923546c51365bf1a803774"."thumbhash" AS "bd93d5747511a4dad4923546c51365bf1a803774_thumbhash", "bd93d5747511a4dad4923546c51365bf1a803774"."encodedVideoPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_encodedVideoPath", "bd93d5747511a4dad4923546c51365bf1a803774"."createdAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_createdAt", "bd93d5747511a4dad4923546c51365bf1a803774"."updatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_updatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."deletedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_deletedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."fileCreatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileCreatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."localDateTime" AS "bd93d5747511a4dad4923546c51365bf1a803774_localDateTime", "bd93d5747511a4dad4923546c51365bf1a803774"."fileModifiedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileModifiedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."isFavorite" AS "bd93d5747511a4dad4923546c51365bf1a803774_isFavorite", "bd93d5747511a4dad4923546c51365bf1a803774"."isArchived" AS "bd93d5747511a4dad4923546c51365bf1a803774_isArchived", "bd93d5747511a4dad4923546c51365bf1a803774"."isExternal" AS "bd93d5747511a4dad4923546c51365bf1a803774_isExternal", "bd93d5747511a4dad4923546c51365bf1a803774"."isReadOnly" AS "bd93d5747511a4dad4923546c51365bf1a803774_isReadOnly", "bd93d5747511a4dad4923546c51365bf1a803774"."isOffline" AS "bd93d5747511a4dad4923546c51365bf1a803774_isOffline", "bd93d5747511a4dad4923546c51365bf1a803774"."checksum" AS "bd93d5747511a4dad4923546c51365bf1a803774_checksum", "bd93d5747511a4dad4923546c51365bf1a803774"."duration" AS "bd93d5747511a4dad4923546c51365bf1a803774_duration", "bd93d5747511a4dad4923546c51365bf1a803774"."isVisible" AS "bd93d5747511a4dad4923546c51365bf1a803774_isVisible", "bd93d5747511a4dad4923546c51365bf1a803774"."livePhotoVideoId" AS "bd93d5747511a4dad4923546c51365bf1a803774_livePhotoVideoId", "bd93d5747511a4dad4923546c51365bf1a803774"."originalFileName" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalFileName", "bd93d5747511a4dad4923546c51365bf1a803774"."sidecarPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_sidecarPath", "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" AS "bd93d5747511a4dad4923546c51365bf1a803774_stackId" FROM "assets" "AssetEntity" LEFT JOIN "exif" "AssetEntity__AssetEntity_exifInfo" ON "AssetEntity__AssetEntity_exifInfo"."assetId" = "AssetEntity"."id" LEFT JOIN "tag_asset" "AssetEntity_AssetEntity__AssetEntity_tags" ON "AssetEntity_AssetEntity__AssetEntity_tags"."assetsId" = "AssetEntity"."id" LEFT JOIN "tags" "AssetEntity__AssetEntity_tags" ON "AssetEntity__AssetEntity_tags"."id" = "AssetEntity_AssetEntity__AssetEntity_tags"."tagsId" LEFT JOIN "asset_stack" "AssetEntity__AssetEntity_stack" ON "AssetEntity__AssetEntity_stack"."id" = "AssetEntity"."stackId" LEFT JOIN "assets" "bd93d5747511a4dad4923546c51365bf1a803774" ON "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" = "AssetEntity__AssetEntity_stack"."id" WHERE ("AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08')) "distinctAlias" ORDER BY "distinctAlias"."AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 215000` ``` ### Additional information _No response_
OVERLORD commented 2026-02-05 05:28:20 +03:00
Author
Owner
Copy Link

@bo0tzz commented on GitHub (Feb 17, 2024):

How is the performance on the web client?

@bo0tzz commented on GitHub (Feb 17, 2024): How is the performance on the web client?
OVERLORD commented 2026-02-05 05:28:24 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 17, 2024):

Moving cursor on the right vertical timeline, freeze up the browser most of the time. Initial loading is ok (when open the "Photos" page), but when click on timeline (vertical to the right), the browser freeze up

@mingqlin commented on GitHub (Feb 17, 2024): Moving cursor on the right vertical timeline, freeze up the browser most of the time. Initial loading is ok (when open the "Photos" page), but when click on timeline (vertical to the right), the browser freeze up
OVERLORD commented 2026-02-05 05:28:26 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 17, 2024):

try run "ALTER DATABASE immich SET log_min_duration_statement = 500;" and the console log will show all query taking too long

@mingqlin commented on GitHub (Feb 17, 2024): try run "ALTER DATABASE immich SET log_min_duration_statement = 500;" and the console log will show all query taking too long
OVERLORD commented 2026-02-05 05:28:29 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 17, 2024):

image

Above is sequencial scan report

@mingqlin commented on GitHub (Feb 17, 2024): ![image](https://github.com/immich-app/immich/assets/39827861/a420a2b5-fa23-40f4-a822-f7af2e7ac7fd) Above is sequencial scan report
OVERLORD commented 2026-02-05 05:28:35 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 17, 2024):

Running android mobile app (on XiaoMi 13T Pro), "Building the timeline" on my 1GB LAN from Immich Server running on Unraid (Intel I7 13Gen), my sync data transfer is less than 2MB/s, most of time just idle, I hope the sync could be much faster. I remember before than paging change, transfer stay constant some faster than 8MB/s

This time snapshot shows how long it takes to go from OFFSET 320000 to OFFSET 820000, it took 9 minutes. The worst part is after the "Building the timeline" is completed, only small amount of photo/videos (less than 50) are show up on my app, after pull down to refresh, the "Building the timeline" start all over again

LIMIT 5000 OFFSET 320000
2024-02-17 08:01:04.672

LIMIT 5000 OFFSET 335000
2024-02-17 08:01:14.664

LIMIT 5000 OFFSET 425000
2024-02-17 08:02:31.085 PST

LIMIT 5000 OFFSET 550000
2024-02-17 08:04:42.475 PST

LIMIT 5000 OFFSET 625000
2024-02-17 08:06:12.399 PST

ASC LIMIT 5000 OFFSET 820000
2024-02-17 08:10:09.525

@mingqlin commented on GitHub (Feb 17, 2024): Running android mobile app (on XiaoMi 13T Pro), "Building the timeline" on my 1GB LAN from Immich Server running on Unraid (Intel I7 13Gen), my sync data transfer is less than 2MB/s, most of time just idle, I hope the sync could be much faster. I remember before than paging change, transfer stay constant some faster than 8MB/s This time snapshot shows how long it takes to go from OFFSET 320000 to OFFSET 820000, it took 9 minutes. The worst part is after the "Building the timeline" is completed, only small amount of photo/videos (less than 50) are show up on my app, after pull down to refresh, the "Building the timeline" start all over again --------------- LIMIT 5000 OFFSET 320000 2024-02-17 08:01:04.672 LIMIT 5000 OFFSET 335000 2024-02-17 08:01:14.664 LIMIT 5000 OFFSET 425000 2024-02-17 08:02:31.085 PST LIMIT 5000 OFFSET 550000 2024-02-17 08:04:42.475 PST LIMIT 5000 OFFSET 625000 2024-02-17 08:06:12.399 PST ASC LIMIT 5000 OFFSET 820000 2024-02-17 08:10:09.525
OVERLORD commented 2026-02-05 05:28:37 +03:00
Author
Owner
Copy Link

@alextran1502 commented on GitHub (Feb 17, 2024):

Thank you for the report. This is very useful as we haven't been able to setup an instance with this many assets yet ❤️

@alextran1502 commented on GitHub (Feb 17, 2024): Thank you for the report. This is very useful as we haven't been able to setup an instance with this many assets yet ❤️
OVERLORD commented 2026-02-05 05:28:41 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 17, 2024):

Thank you, Alex and the team, amazing work!!

@mingqlin commented on GitHub (Feb 17, 2024): Thank you, Alex and the team, amazing work!!
OVERLORD commented 2026-02-05 05:28:48 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

Would you be able to add EXPLAIN ANALYZE to the start of this query and show the query plan? That would help narrow down the slowest part of the query.

@mertalev commented on GitHub (Feb 18, 2024): Would you be able to add `EXPLAIN ANALYZE` to the start of this query and show the query plan? That would help narrow down the slowest part of the query.
OVERLORD commented 2026-02-05 05:29:02 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

Thank you @mertalev for looking into this issue.

Below is query:
EXPLAIN ANALYZE SELECT DISTINCT "distinctAlias"."AssetEntity_id" AS "ids_AssetEntity_id", "distinctAlias"."AssetEntity_fileCreatedAt" FROM (SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."deviceAssetId" AS "AssetEntity_deviceAssetId", "AssetEntity"."ownerId" AS "AssetEntity_ownerId", "AssetEntity"."libraryId" AS "AssetEntity_libraryId", "AssetEntity"."deviceId" AS "AssetEntity_deviceId", "AssetEntity"."type" AS "AssetEntity_type", "AssetEntity"."originalPath" AS "AssetEntity_originalPath", "AssetEntity"."resizePath" AS "AssetEntity_resizePath", "AssetEntity"."webpPath" AS "AssetEntity_webpPath", "AssetEntity"."thumbhash" AS "AssetEntity_thumbhash", "AssetEntity"."encodedVideoPath" AS "AssetEntity_encodedVideoPath", "AssetEntity"."createdAt" AS "AssetEntity_createdAt", "AssetEntity"."updatedAt" AS "AssetEntity_updatedAt", "AssetEntity"."deletedAt" AS "AssetEntity_deletedAt", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt", "AssetEntity"."localDateTime" AS "AssetEntity_localDateTime", "AssetEntity"."fileModifiedAt" AS "AssetEntity_fileModifiedAt", "AssetEntity"."isFavorite" AS "AssetEntity_isFavorite", "AssetEntity"."isArchived" AS "AssetEntity_isArchived", "AssetEntity"."isExternal" AS "AssetEntity_isExternal", "AssetEntity"."isReadOnly" AS "AssetEntity_isReadOnly", "AssetEntity"."isOffline" AS "AssetEntity_isOffline", "AssetEntity"."checksum" AS "AssetEntity_checksum", "AssetEntity"."duration" AS "AssetEntity_duration", "AssetEntity"."isVisible" AS "AssetEntity_isVisible", "AssetEntity"."livePhotoVideoId" AS "AssetEntity_livePhotoVideoId", "AssetEntity"."originalFileName" AS "AssetEntity_originalFileName", "AssetEntity"."sidecarPath" AS "AssetEntity_sidecarPath", "AssetEntity"."stackId" AS "AssetEntity_stackId", "AssetEntity__AssetEntity_exifInfo"."assetId" AS "AssetEntity__AssetEntity_exifInfo_assetId", "AssetEntity__AssetEntity_exifInfo"."description" AS "AssetEntity__AssetEntity_exifInfo_description", "AssetEntity__AssetEntity_exifInfo"."exifImageWidth" AS "AssetEntity__AssetEntity_exifInfo_exifImageWidth", "AssetEntity__AssetEntity_exifInfo"."exifImageHeight" AS "AssetEntity__AssetEntity_exifInfo_exifImageHeight", "AssetEntity__AssetEntity_exifInfo"."fileSizeInByte" AS "AssetEntity__AssetEntity_exifInfo_fileSizeInByte", "AssetEntity__AssetEntity_exifInfo"."orientation" AS "AssetEntity__AssetEntity_exifInfo_orientation", "AssetEntity__AssetEntity_exifInfo"."dateTimeOriginal" AS "AssetEntity__AssetEntity_exifInfo_dateTimeOriginal", "AssetEntity__AssetEntity_exifInfo"."modifyDate" AS "AssetEntity__AssetEntity_exifInfo_modifyDate", "AssetEntity__AssetEntity_exifInfo"."timeZone" AS "AssetEntity__AssetEntity_exifInfo_timeZone", "AssetEntity__AssetEntity_exifInfo"."latitude" AS "AssetEntity__AssetEntity_exifInfo_latitude", "AssetEntity__AssetEntity_exifInfo"."longitude" AS "AssetEntity__AssetEntity_exifInfo_longitude", "AssetEntity__AssetEntity_exifInfo"."projectionType" AS "AssetEntity__AssetEntity_exifInfo_projectionType", "AssetEntity__AssetEntity_exifInfo"."city" AS "AssetEntity__AssetEntity_exifInfo_city", "AssetEntity__AssetEntity_exifInfo"."livePhotoCID" AS "AssetEntity__AssetEntity_exifInfo_livePhotoCID", "AssetEntity__AssetEntity_exifInfo"."autoStackId" AS "AssetEntity__AssetEntity_exifInfo_autoStackId", "AssetEntity__AssetEntity_exifInfo"."state" AS "AssetEntity__AssetEntity_exifInfo_state", "AssetEntity__AssetEntity_exifInfo"."country" AS "AssetEntity__AssetEntity_exifInfo_country", "AssetEntity__AssetEntity_exifInfo"."make" AS "AssetEntity__AssetEntity_exifInfo_make", "AssetEntity__AssetEntity_exifInfo"."model" AS "AssetEntity__AssetEntity_exifInfo_model", "AssetEntity__AssetEntity_exifInfo"."lensModel" AS "AssetEntity__AssetEntity_exifInfo_lensModel", "AssetEntity__AssetEntity_exifInfo"."fNumber" AS "AssetEntity__AssetEntity_exifInfo_fNumber", "AssetEntity__AssetEntity_exifInfo"."focalLength" AS "AssetEntity__AssetEntity_exifInfo_focalLength", "AssetEntity__AssetEntity_exifInfo"."iso" AS "AssetEntity__AssetEntity_exifInfo_iso", "AssetEntity__AssetEntity_exifInfo"."exposureTime" AS "AssetEntity__AssetEntity_exifInfo_exposureTime", "AssetEntity__AssetEntity_exifInfo"."profileDescription" AS "AssetEntity__AssetEntity_exifInfo_profileDescription", "AssetEntity__AssetEntity_exifInfo"."colorspace" AS "AssetEntity__AssetEntity_exifInfo_colorspace", "AssetEntity__AssetEntity_exifInfo"."bitsPerSample" AS "AssetEntity__AssetEntity_exifInfo_bitsPerSample", "AssetEntity__AssetEntity_exifInfo"."fps" AS "AssetEntity__AssetEntity_exifInfo_fps", "AssetEntity__AssetEntity_tags"."id" AS "AssetEntity__AssetEntity_tags_id", "AssetEntity__AssetEntity_tags"."type" AS "AssetEntity__AssetEntity_tags_type", "AssetEntity__AssetEntity_tags"."name" AS "AssetEntity__AssetEntity_tags_name", "AssetEntity__AssetEntity_tags"."userId" AS "AssetEntity__AssetEntity_tags_userId", "AssetEntity__AssetEntity_tags"."renameTagId" AS "AssetEntity__AssetEntity_tags_renameTagId", "AssetEntity__AssetEntity_stack"."id" AS "AssetEntity__AssetEntity_stack_id", "AssetEntity__AssetEntity_stack"."primaryAssetId" AS "AssetEntity__AssetEntity_stack_primaryAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."id" AS "bd93d5747511a4dad4923546c51365bf1a803774_id", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceAssetId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."ownerId" AS "bd93d5747511a4dad4923546c51365bf1a803774_ownerId", "bd93d5747511a4dad4923546c51365bf1a803774"."libraryId" AS "bd93d5747511a4dad4923546c51365bf1a803774_libraryId", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceId", "bd93d5747511a4dad4923546c51365bf1a803774"."type" AS "bd93d5747511a4dad4923546c51365bf1a803774_type", "bd93d5747511a4dad4923546c51365bf1a803774"."originalPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalPath", "bd93d5747511a4dad4923546c51365bf1a803774"."resizePath" AS "bd93d5747511a4dad4923546c51365bf1a803774_resizePath", "bd93d5747511a4dad4923546c51365bf1a803774"."webpPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_webpPath", "bd93d5747511a4dad4923546c51365bf1a803774"."thumbhash" AS "bd93d5747511a4dad4923546c51365bf1a803774_thumbhash", "bd93d5747511a4dad4923546c51365bf1a803774"."encodedVideoPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_encodedVideoPath", "bd93d5747511a4dad4923546c51365bf1a803774"."createdAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_createdAt", "bd93d5747511a4dad4923546c51365bf1a803774"."updatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_updatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."deletedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_deletedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."fileCreatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileCreatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."localDateTime" AS "bd93d5747511a4dad4923546c51365bf1a803774_localDateTime", "bd93d5747511a4dad4923546c51365bf1a803774"."fileModifiedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileModifiedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."isFavorite" AS "bd93d5747511a4dad4923546c51365bf1a803774_isFavorite", "bd93d5747511a4dad4923546c51365bf1a803774"."isArchived" AS "bd93d5747511a4dad4923546c51365bf1a803774_isArchived", "bd93d5747511a4dad4923546c51365bf1a803774"."isExternal" AS "bd93d5747511a4dad4923546c51365bf1a803774_isExternal", "bd93d5747511a4dad4923546c51365bf1a803774"."isReadOnly" AS "bd93d5747511a4dad4923546c51365bf1a803774_isReadOnly", "bd93d5747511a4dad4923546c51365bf1a803774"."isOffline" AS "bd93d5747511a4dad4923546c51365bf1a803774_isOffline", "bd93d5747511a4dad4923546c51365bf1a803774"."checksum" AS "bd93d5747511a4dad4923546c51365bf1a803774_checksum", "bd93d5747511a4dad4923546c51365bf1a803774"."duration" AS "bd93d5747511a4dad4923546c51365bf1a803774_duration", "bd93d5747511a4dad4923546c51365bf1a803774"."isVisible" AS "bd93d5747511a4dad4923546c51365bf1a803774_isVisible", "bd93d5747511a4dad4923546c51365bf1a803774"."livePhotoVideoId" AS "bd93d5747511a4dad4923546c51365bf1a803774_livePhotoVideoId", "bd93d5747511a4dad4923546c51365bf1a803774"."originalFileName" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalFileName", "bd93d5747511a4dad4923546c51365bf1a803774"."sidecarPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_sidecarPath", "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" AS "bd93d5747511a4dad4923546c51365bf1a803774_stackId" FROM "assets" "AssetEntity" LEFT JOIN "exif" "AssetEntity__AssetEntity_exifInfo" ON "AssetEntity__AssetEntity_exifInfo"."assetId" = "AssetEntity"."id" LEFT JOIN "tag_asset" "AssetEntity_AssetEntity__AssetEntity_tags" ON "AssetEntity_AssetEntity__AssetEntity_tags"."assetsId" = "AssetEntity"."id" LEFT JOIN "tags" "AssetEntity__AssetEntity_tags" ON "AssetEntity__AssetEntity_tags"."id" = "AssetEntity_AssetEntity__AssetEntity_tags"."tagsId" LEFT JOIN "asset_stack" "AssetEntity__AssetEntity_stack" ON "AssetEntity__AssetEntity_stack"."id" = "AssetEntity"."stackId" LEFT JOIN "assets" "bd93d5747511a4dad4923546c51365bf1a803774" ON "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" = "AssetEntity__AssetEntity_stack"."id" WHERE ("AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08')) "distinctAlias" ORDER BY "distinctAlias"."AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 215000

This is the result:
Limit (cost=236820.22..237427.55 rows=5000 width=24) (actual time=967.281..1030.582 rows=5000 loops=1) -> Unique (cost=210704.92..310837.64 rows=824365 width=24) (actual time=893.274..999.371 rows=220000 loops=1) -> Gather Merge (cost=210704.92..306715.81 rows=824365 width=24) (actual time=893.264..987.952 rows=220000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=209704.90..210563.61 rows=343485 width=24) (actual time=823.161..833.805 rows=74101 loops=3) " Sort Key: ""AssetEntity"".""fileCreatedAt"" DESC, ""AssetEntity"".id" Sort Method: external merge Disk: 9032kB Worker 0: Sort Method: external merge Disk: 9208kB Worker 1: Sort Method: external merge Disk: 9240kB -> Merge Left Join (cost=169346.25..171076.15 rows=343485 width=24) (actual time=588.474..639.898 rows=274201 loops=3) " Merge Cond: (""AssetEntity"".id = ""AssetEntity_AssetEntity__AssetEntity_tags"".""assetsId"")" -> Sort (cost=169251.87..170110.58 rows=343485 width=24) (actual time=588.093..627.491 rows=274201 loops=3) " Sort Key: ""AssetEntity"".id" Sort Method: external merge Disk: 9032kB Worker 0: Sort Method: external merge Disk: 9208kB Worker 1: Sort Method: external merge Disk: 9240kB -> Hash Left Join (cost=67324.24..130623.12 rows=343485 width=24) (actual time=15.583..423.047 rows=274201 loops=3) " Hash Cond: (""AssetEntity"".""stackId"" = ""AssetEntity__AssetEntity_stack"".id)" " -> Parallel Seq Scan on assets ""AssetEntity"" (cost=0.00..62010.79 rows=343485 width=40) (actual time=14.930..405.618 rows=274201 loops=3)" " Filter: (""isVisible"" AND (""updatedAt"" <= '2024-02-17 15:14:14.296+00'::timestamp with time zone) AND (""ownerId"" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3'::uuid))" Rows Removed by Filter: 431 -> Hash (cost=67307.24..67307.24 rows=1360 width=16) (actual time=0.110..0.127 rows=0 loops=3) Buckets: 2048 Batches: 1 Memory Usage: 16kB -> Hash Right Join (cost=40.60..67307.24 rows=1360 width=16) (actual time=0.106..0.118 rows=0 loops=3) " Hash Cond: (bd93d5747511a4dad4923546c51365bf1a803774.""stackId"" = ""AssetEntity__AssetEntity_stack"".id)" -> Seq Scan on assets bd93d5747511a4dad4923546c51365bf1a803774 (cost=0.00..65102.47 rows=824447 width=16) (never executed) -> Hash (cost=23.60..23.60 rows=1360 width=16) (actual time=0.092..0.097 rows=0 loops=3) Buckets: 2048 Batches: 1 Memory Usage: 16kB " -> Seq Scan on asset_stack ""AssetEntity__AssetEntity_stack"" (cost=0.00..23.60 rows=1360 width=16) (actual time=0.091..0.092 rows=0 loops=3)" -> Sort (cost=94.38..97.78 rows=1360 width=32) (actual time=0.185..0.190 rows=0 loops=3) " Sort Key: ""AssetEntity_AssetEntity__AssetEntity_tags"".""assetsId""" Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB " -> Seq Scan on tag_asset ""AssetEntity_AssetEntity__AssetEntity_tags"" (cost=0.00..23.60 rows=1360 width=32) (actual time=0.120..0.122 rows=0 loops=3)" Planning Time: 4.352 ms JIT: Functions: 78 " Options: Inlining false, Optimization false, Expressions true, Deforming true" " Timing: Generation 6.218 ms, Inlining 0.000 ms, Optimization 7.933 ms, Emission 65.082 ms, Total 79.233 ms" Execution Time: 1586.561 ms

Screenshot of the result, you can see it takes 1s 613ms
image

@mingqlin commented on GitHub (Feb 18, 2024): Thank you @mertalev for looking into this issue. Below is query: `EXPLAIN ANALYZE SELECT DISTINCT "distinctAlias"."AssetEntity_id" AS "ids_AssetEntity_id", "distinctAlias"."AssetEntity_fileCreatedAt" FROM (SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."deviceAssetId" AS "AssetEntity_deviceAssetId", "AssetEntity"."ownerId" AS "AssetEntity_ownerId", "AssetEntity"."libraryId" AS "AssetEntity_libraryId", "AssetEntity"."deviceId" AS "AssetEntity_deviceId", "AssetEntity"."type" AS "AssetEntity_type", "AssetEntity"."originalPath" AS "AssetEntity_originalPath", "AssetEntity"."resizePath" AS "AssetEntity_resizePath", "AssetEntity"."webpPath" AS "AssetEntity_webpPath", "AssetEntity"."thumbhash" AS "AssetEntity_thumbhash", "AssetEntity"."encodedVideoPath" AS "AssetEntity_encodedVideoPath", "AssetEntity"."createdAt" AS "AssetEntity_createdAt", "AssetEntity"."updatedAt" AS "AssetEntity_updatedAt", "AssetEntity"."deletedAt" AS "AssetEntity_deletedAt", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt", "AssetEntity"."localDateTime" AS "AssetEntity_localDateTime", "AssetEntity"."fileModifiedAt" AS "AssetEntity_fileModifiedAt", "AssetEntity"."isFavorite" AS "AssetEntity_isFavorite", "AssetEntity"."isArchived" AS "AssetEntity_isArchived", "AssetEntity"."isExternal" AS "AssetEntity_isExternal", "AssetEntity"."isReadOnly" AS "AssetEntity_isReadOnly", "AssetEntity"."isOffline" AS "AssetEntity_isOffline", "AssetEntity"."checksum" AS "AssetEntity_checksum", "AssetEntity"."duration" AS "AssetEntity_duration", "AssetEntity"."isVisible" AS "AssetEntity_isVisible", "AssetEntity"."livePhotoVideoId" AS "AssetEntity_livePhotoVideoId", "AssetEntity"."originalFileName" AS "AssetEntity_originalFileName", "AssetEntity"."sidecarPath" AS "AssetEntity_sidecarPath", "AssetEntity"."stackId" AS "AssetEntity_stackId", "AssetEntity__AssetEntity_exifInfo"."assetId" AS "AssetEntity__AssetEntity_exifInfo_assetId", "AssetEntity__AssetEntity_exifInfo"."description" AS "AssetEntity__AssetEntity_exifInfo_description", "AssetEntity__AssetEntity_exifInfo"."exifImageWidth" AS "AssetEntity__AssetEntity_exifInfo_exifImageWidth", "AssetEntity__AssetEntity_exifInfo"."exifImageHeight" AS "AssetEntity__AssetEntity_exifInfo_exifImageHeight", "AssetEntity__AssetEntity_exifInfo"."fileSizeInByte" AS "AssetEntity__AssetEntity_exifInfo_fileSizeInByte", "AssetEntity__AssetEntity_exifInfo"."orientation" AS "AssetEntity__AssetEntity_exifInfo_orientation", "AssetEntity__AssetEntity_exifInfo"."dateTimeOriginal" AS "AssetEntity__AssetEntity_exifInfo_dateTimeOriginal", "AssetEntity__AssetEntity_exifInfo"."modifyDate" AS "AssetEntity__AssetEntity_exifInfo_modifyDate", "AssetEntity__AssetEntity_exifInfo"."timeZone" AS "AssetEntity__AssetEntity_exifInfo_timeZone", "AssetEntity__AssetEntity_exifInfo"."latitude" AS "AssetEntity__AssetEntity_exifInfo_latitude", "AssetEntity__AssetEntity_exifInfo"."longitude" AS "AssetEntity__AssetEntity_exifInfo_longitude", "AssetEntity__AssetEntity_exifInfo"."projectionType" AS "AssetEntity__AssetEntity_exifInfo_projectionType", "AssetEntity__AssetEntity_exifInfo"."city" AS "AssetEntity__AssetEntity_exifInfo_city", "AssetEntity__AssetEntity_exifInfo"."livePhotoCID" AS "AssetEntity__AssetEntity_exifInfo_livePhotoCID", "AssetEntity__AssetEntity_exifInfo"."autoStackId" AS "AssetEntity__AssetEntity_exifInfo_autoStackId", "AssetEntity__AssetEntity_exifInfo"."state" AS "AssetEntity__AssetEntity_exifInfo_state", "AssetEntity__AssetEntity_exifInfo"."country" AS "AssetEntity__AssetEntity_exifInfo_country", "AssetEntity__AssetEntity_exifInfo"."make" AS "AssetEntity__AssetEntity_exifInfo_make", "AssetEntity__AssetEntity_exifInfo"."model" AS "AssetEntity__AssetEntity_exifInfo_model", "AssetEntity__AssetEntity_exifInfo"."lensModel" AS "AssetEntity__AssetEntity_exifInfo_lensModel", "AssetEntity__AssetEntity_exifInfo"."fNumber" AS "AssetEntity__AssetEntity_exifInfo_fNumber", "AssetEntity__AssetEntity_exifInfo"."focalLength" AS "AssetEntity__AssetEntity_exifInfo_focalLength", "AssetEntity__AssetEntity_exifInfo"."iso" AS "AssetEntity__AssetEntity_exifInfo_iso", "AssetEntity__AssetEntity_exifInfo"."exposureTime" AS "AssetEntity__AssetEntity_exifInfo_exposureTime", "AssetEntity__AssetEntity_exifInfo"."profileDescription" AS "AssetEntity__AssetEntity_exifInfo_profileDescription", "AssetEntity__AssetEntity_exifInfo"."colorspace" AS "AssetEntity__AssetEntity_exifInfo_colorspace", "AssetEntity__AssetEntity_exifInfo"."bitsPerSample" AS "AssetEntity__AssetEntity_exifInfo_bitsPerSample", "AssetEntity__AssetEntity_exifInfo"."fps" AS "AssetEntity__AssetEntity_exifInfo_fps", "AssetEntity__AssetEntity_tags"."id" AS "AssetEntity__AssetEntity_tags_id", "AssetEntity__AssetEntity_tags"."type" AS "AssetEntity__AssetEntity_tags_type", "AssetEntity__AssetEntity_tags"."name" AS "AssetEntity__AssetEntity_tags_name", "AssetEntity__AssetEntity_tags"."userId" AS "AssetEntity__AssetEntity_tags_userId", "AssetEntity__AssetEntity_tags"."renameTagId" AS "AssetEntity__AssetEntity_tags_renameTagId", "AssetEntity__AssetEntity_stack"."id" AS "AssetEntity__AssetEntity_stack_id", "AssetEntity__AssetEntity_stack"."primaryAssetId" AS "AssetEntity__AssetEntity_stack_primaryAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."id" AS "bd93d5747511a4dad4923546c51365bf1a803774_id", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceAssetId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceAssetId", "bd93d5747511a4dad4923546c51365bf1a803774"."ownerId" AS "bd93d5747511a4dad4923546c51365bf1a803774_ownerId", "bd93d5747511a4dad4923546c51365bf1a803774"."libraryId" AS "bd93d5747511a4dad4923546c51365bf1a803774_libraryId", "bd93d5747511a4dad4923546c51365bf1a803774"."deviceId" AS "bd93d5747511a4dad4923546c51365bf1a803774_deviceId", "bd93d5747511a4dad4923546c51365bf1a803774"."type" AS "bd93d5747511a4dad4923546c51365bf1a803774_type", "bd93d5747511a4dad4923546c51365bf1a803774"."originalPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalPath", "bd93d5747511a4dad4923546c51365bf1a803774"."resizePath" AS "bd93d5747511a4dad4923546c51365bf1a803774_resizePath", "bd93d5747511a4dad4923546c51365bf1a803774"."webpPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_webpPath", "bd93d5747511a4dad4923546c51365bf1a803774"."thumbhash" AS "bd93d5747511a4dad4923546c51365bf1a803774_thumbhash", "bd93d5747511a4dad4923546c51365bf1a803774"."encodedVideoPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_encodedVideoPath", "bd93d5747511a4dad4923546c51365bf1a803774"."createdAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_createdAt", "bd93d5747511a4dad4923546c51365bf1a803774"."updatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_updatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."deletedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_deletedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."fileCreatedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileCreatedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."localDateTime" AS "bd93d5747511a4dad4923546c51365bf1a803774_localDateTime", "bd93d5747511a4dad4923546c51365bf1a803774"."fileModifiedAt" AS "bd93d5747511a4dad4923546c51365bf1a803774_fileModifiedAt", "bd93d5747511a4dad4923546c51365bf1a803774"."isFavorite" AS "bd93d5747511a4dad4923546c51365bf1a803774_isFavorite", "bd93d5747511a4dad4923546c51365bf1a803774"."isArchived" AS "bd93d5747511a4dad4923546c51365bf1a803774_isArchived", "bd93d5747511a4dad4923546c51365bf1a803774"."isExternal" AS "bd93d5747511a4dad4923546c51365bf1a803774_isExternal", "bd93d5747511a4dad4923546c51365bf1a803774"."isReadOnly" AS "bd93d5747511a4dad4923546c51365bf1a803774_isReadOnly", "bd93d5747511a4dad4923546c51365bf1a803774"."isOffline" AS "bd93d5747511a4dad4923546c51365bf1a803774_isOffline", "bd93d5747511a4dad4923546c51365bf1a803774"."checksum" AS "bd93d5747511a4dad4923546c51365bf1a803774_checksum", "bd93d5747511a4dad4923546c51365bf1a803774"."duration" AS "bd93d5747511a4dad4923546c51365bf1a803774_duration", "bd93d5747511a4dad4923546c51365bf1a803774"."isVisible" AS "bd93d5747511a4dad4923546c51365bf1a803774_isVisible", "bd93d5747511a4dad4923546c51365bf1a803774"."livePhotoVideoId" AS "bd93d5747511a4dad4923546c51365bf1a803774_livePhotoVideoId", "bd93d5747511a4dad4923546c51365bf1a803774"."originalFileName" AS "bd93d5747511a4dad4923546c51365bf1a803774_originalFileName", "bd93d5747511a4dad4923546c51365bf1a803774"."sidecarPath" AS "bd93d5747511a4dad4923546c51365bf1a803774_sidecarPath", "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" AS "bd93d5747511a4dad4923546c51365bf1a803774_stackId" FROM "assets" "AssetEntity" LEFT JOIN "exif" "AssetEntity__AssetEntity_exifInfo" ON "AssetEntity__AssetEntity_exifInfo"."assetId" = "AssetEntity"."id" LEFT JOIN "tag_asset" "AssetEntity_AssetEntity__AssetEntity_tags" ON "AssetEntity_AssetEntity__AssetEntity_tags"."assetsId" = "AssetEntity"."id" LEFT JOIN "tags" "AssetEntity__AssetEntity_tags" ON "AssetEntity__AssetEntity_tags"."id" = "AssetEntity_AssetEntity__AssetEntity_tags"."tagsId" LEFT JOIN "asset_stack" "AssetEntity__AssetEntity_stack" ON "AssetEntity__AssetEntity_stack"."id" = "AssetEntity"."stackId" LEFT JOIN "assets" "bd93d5747511a4dad4923546c51365bf1a803774" ON "bd93d5747511a4dad4923546c51365bf1a803774"."stackId" = "AssetEntity__AssetEntity_stack"."id" WHERE ("AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08')) "distinctAlias" ORDER BY "distinctAlias"."AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 215000` This is the result: `Limit (cost=236820.22..237427.55 rows=5000 width=24) (actual time=967.281..1030.582 rows=5000 loops=1) -> Unique (cost=210704.92..310837.64 rows=824365 width=24) (actual time=893.274..999.371 rows=220000 loops=1) -> Gather Merge (cost=210704.92..306715.81 rows=824365 width=24) (actual time=893.264..987.952 rows=220000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=209704.90..210563.61 rows=343485 width=24) (actual time=823.161..833.805 rows=74101 loops=3) " Sort Key: ""AssetEntity"".""fileCreatedAt"" DESC, ""AssetEntity"".id" Sort Method: external merge Disk: 9032kB Worker 0: Sort Method: external merge Disk: 9208kB Worker 1: Sort Method: external merge Disk: 9240kB -> Merge Left Join (cost=169346.25..171076.15 rows=343485 width=24) (actual time=588.474..639.898 rows=274201 loops=3) " Merge Cond: (""AssetEntity"".id = ""AssetEntity_AssetEntity__AssetEntity_tags"".""assetsId"")" -> Sort (cost=169251.87..170110.58 rows=343485 width=24) (actual time=588.093..627.491 rows=274201 loops=3) " Sort Key: ""AssetEntity"".id" Sort Method: external merge Disk: 9032kB Worker 0: Sort Method: external merge Disk: 9208kB Worker 1: Sort Method: external merge Disk: 9240kB -> Hash Left Join (cost=67324.24..130623.12 rows=343485 width=24) (actual time=15.583..423.047 rows=274201 loops=3) " Hash Cond: (""AssetEntity"".""stackId"" = ""AssetEntity__AssetEntity_stack"".id)" " -> Parallel Seq Scan on assets ""AssetEntity"" (cost=0.00..62010.79 rows=343485 width=40) (actual time=14.930..405.618 rows=274201 loops=3)" " Filter: (""isVisible"" AND (""updatedAt"" <= '2024-02-17 15:14:14.296+00'::timestamp with time zone) AND (""ownerId"" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3'::uuid))" Rows Removed by Filter: 431 -> Hash (cost=67307.24..67307.24 rows=1360 width=16) (actual time=0.110..0.127 rows=0 loops=3) Buckets: 2048 Batches: 1 Memory Usage: 16kB -> Hash Right Join (cost=40.60..67307.24 rows=1360 width=16) (actual time=0.106..0.118 rows=0 loops=3) " Hash Cond: (bd93d5747511a4dad4923546c51365bf1a803774.""stackId"" = ""AssetEntity__AssetEntity_stack"".id)" -> Seq Scan on assets bd93d5747511a4dad4923546c51365bf1a803774 (cost=0.00..65102.47 rows=824447 width=16) (never executed) -> Hash (cost=23.60..23.60 rows=1360 width=16) (actual time=0.092..0.097 rows=0 loops=3) Buckets: 2048 Batches: 1 Memory Usage: 16kB " -> Seq Scan on asset_stack ""AssetEntity__AssetEntity_stack"" (cost=0.00..23.60 rows=1360 width=16) (actual time=0.091..0.092 rows=0 loops=3)" -> Sort (cost=94.38..97.78 rows=1360 width=32) (actual time=0.185..0.190 rows=0 loops=3) " Sort Key: ""AssetEntity_AssetEntity__AssetEntity_tags"".""assetsId""" Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB " -> Seq Scan on tag_asset ""AssetEntity_AssetEntity__AssetEntity_tags"" (cost=0.00..23.60 rows=1360 width=32) (actual time=0.120..0.122 rows=0 loops=3)" Planning Time: 4.352 ms JIT: Functions: 78 " Options: Inlining false, Optimization false, Expressions true, Deforming true" " Timing: Generation 6.218 ms, Inlining 0.000 ms, Optimization 7.933 ms, Emission 65.082 ms, Total 79.233 ms" Execution Time: 1586.561 ms ` Screenshot of the result, you can see it takes 1s 613ms ![image](https://github.com/immich-app/immich/assets/39827861/06930f9d-1a35-47e5-83c9-1eaf56238c2a)
OVERLORD commented 2026-02-05 05:29:04 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

It looks to me like the slowest parts are the filtering for isVisible and updatedAt and getting distinct rows. The former is a parallel seq scan and the latter adds a gather merge to the query.

@mertalev commented on GitHub (Feb 18, 2024): It looks to me like the slowest parts are the filtering for `isVisible` and `updatedAt` and getting distinct rows. The former is a parallel seq scan and the latter adds a gather merge to the query.
OVERLORD commented 2026-02-05 05:29:17 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

It looks like this query is a lot more complex than it needs to be. It ultimately just returns id and fileCreatedAt, so not only is the subquery full of joins pointless, the DISTINCT has to deduplicate all of those rows. fileCreatedAt also doesn't have an index, so ordering by this forces a table scan.

Lastly, since pagination is implemented with LIMIT/OFFSET, the query is executed many many times since the mobile app fetches each page during sync. This part is harder to optimize, but the query being faster should still help a lot.

Can you run this to compare the performance?

CREATE INDEX IF NOT EXISTS idx_asset_file_created_at ON assets ("fileCreatedAt");

SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt"
FROM "assets" "AssetEntity"
WHERE "AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND 
      "AssetEntity"."isVisible" = 't' AND
      "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08'
ORDER BY "AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC
LIMIT 5000 OFFSET 215000;
@mertalev commented on GitHub (Feb 18, 2024): It looks like this query is a lot more complex than it needs to be. It ultimately just returns `id` and `fileCreatedAt`, so not only is the subquery full of joins pointless, the DISTINCT has to deduplicate all of those rows. `fileCreatedAt` also doesn't have an index, so ordering by this forces a table scan. Lastly, since pagination is implemented with LIMIT/OFFSET, the query is executed many many times since the mobile app fetches each page during sync. This part is harder to optimize, but the query being faster should still help a lot. Can you run this to compare the performance? ```sql CREATE INDEX IF NOT EXISTS idx_asset_file_created_at ON assets ("fileCreatedAt"); SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt" FROM "assets" "AssetEntity" WHERE "AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08' ORDER BY "AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 215000; ```
OVERLORD commented 2026-02-05 05:29:19 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

I have a correction, After shutdown all my Unraid Immich background jobs (this morning videos encoding background job was running.)
Original query take about average ~300ms
New query table about average ~165ms (The index "idx_asset_file_created_at" make no difference, I run query before and after create index, don't see any performance difference)

This is EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt" FROM "assets" "AssetEntity" WHERE "AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08' ORDER BY "AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 210000;

Result:

Limit (cost=66490.29..68073.35 rows=5000 width=24) (actual time=121.279..124.602 rows=5000 loops=1) -> Incremental Sort (cost=1.68..260831.42 rows=823814 width=24) (actual time=0.081..121.962 rows=215000 loops=1) " Sort Key: ""fileCreatedAt"" DESC, id" " Presorted Key: ""fileCreatedAt""" Full-sort Groups: 6299 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Pre-sorted Groups: 48 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB " -> Index Scan Backward using idx_asset_file_created_at on assets ""AssetEntity"" (cost=0.42..229153.94 rows=823814 width=24) (actual time=0.029..98.880 rows=215011 loops=1)" " Filter: (""isVisible"" AND (""updatedAt"" <= '2024-02-17 15:14:14.296+00'::timestamp with time zone) AND (""ownerId"" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3'::uuid))" Rows Removed by Filter: 1982 Planning Time: 0.069 ms Execution Time: 124.682 ms

@mingqlin commented on GitHub (Feb 18, 2024): I have a correction, After shutdown all my Unraid Immich background jobs (this morning videos encoding background job was running.) Original query take about average ~300ms New query table about average ~165ms (The index "idx_asset_file_created_at" make no difference, I run query before and after create index, don't see any performance difference) This is EXPLAIN ANALYZE: `EXPLAIN ANALYZE SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt" FROM "assets" "AssetEntity" WHERE "AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND "AssetEntity"."isVisible" = 't' AND "AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08' ORDER BY "AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC LIMIT 5000 OFFSET 210000;` **Result:** `Limit (cost=66490.29..68073.35 rows=5000 width=24) (actual time=121.279..124.602 rows=5000 loops=1) -> Incremental Sort (cost=1.68..260831.42 rows=823814 width=24) (actual time=0.081..121.962 rows=215000 loops=1) " Sort Key: ""fileCreatedAt"" DESC, id" " Presorted Key: ""fileCreatedAt""" Full-sort Groups: 6299 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Pre-sorted Groups: 48 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB " -> Index Scan Backward using idx_asset_file_created_at on assets ""AssetEntity"" (cost=0.42..229153.94 rows=823814 width=24) (actual time=0.029..98.880 rows=215011 loops=1)" " Filter: (""isVisible"" AND (""updatedAt"" <= '2024-02-17 15:14:14.296+00'::timestamp with time zone) AND (""ownerId"" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3'::uuid))" Rows Removed by Filter: 1982 Planning Time: 0.069 ms Execution Time: 124.682 ms `
OVERLORD commented 2026-02-05 05:29:30 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

That's interesting, it was still doing a seq scan for me until I added the index so I'd expect it to make a difference.

Overall though I think this is probably the best this query's going to get until we switch to cursor-based pagination. Switching to that would mean instead of running the query each time to get the next page, the query just continues from where it left off.

@mertalev commented on GitHub (Feb 18, 2024): That's interesting, it was still doing a seq scan for me until I added the index so I'd expect it to make a difference. Overall though I think this is probably the best this query's going to get until we switch to cursor-based pagination. Switching to that would mean instead of running the query each time to get the next page, the query just continues from where it left off.
OVERLORD commented 2026-02-05 05:29:33 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

I am doing complete "Building the timeline", I uninstalled the Immich Android app, reinstall the latest [v1.94.1], login

1. Log every SQL query attached (Start from Login, until I stop video recording recording)
postgresql15-2024-02-18T03-39-02.zip

2. Video recording of the android "Build the timeline" (record start right after login)
https://youtu.be/yuue0A5zp60

3. Check the Android Immich app log:
Image_20240217224917

Android app Error log Detail:
`
MESSAGE

Error while getting remote assets: ApiException 400: HTTP connection failed: GET /asset (Inner exception: Connection closed before full header was received)

#0 IOClient.send (package:http/src/io_client.dart:90)

#1 BaseClient._sendUnstreamed (package:http/src/base_client.dart:93)

#2 ApiClient.invokeAPI (package:openapi/api_client.dart:101)

#3 AssetApi.getAllAssets (package:openapi/api/asset_api.dart:400)

#4 AssetService._getRemoteAssets (package:immich_mobile/shared/services/asset.service.dart:72)

#5 SyncService._syncRemoteAssetsFull (package:immich_mobile/shared/services/sync.service.dart:209)

#6 SyncService.syncRemoteAssetsToDb. (package:immich_mobile/shared/services/sync.service.dart:53)

#7 AsyncMutex.run. (package:immich_mobile/utils/async_mutex.dart:15)

FROM
AssetService

Stack TRACES
#0 ApiClient.invokeAPI (package:openapi/api_client.dart:125)

#1 AssetApi.getAllAssets (package:openapi/api/asset_api.dart:400)

#2 AssetService._getRemoteAssets (package:immich_mobile/shared/services/asset.service.dart:72)

#3 SyncService._syncRemoteAssetsFull (package:immich_mobile/shared/services/sync.service.dart:209)

#4 SyncService.syncRemoteAssetsToDb. (package:immich_mobile/shared/services/sync.service.dart:53)

#5 AsyncMutex.run. (package:immich_mobile/utils/async_mutex.dart:15)
`

I hope from the SQL query log, you can find more problems with the database query
From the video recording, you could see the KB/s or MB/s of network speed at the top of the screen, the network goes idle after a couple of minutes, but "Building the timeline" never stops

@mingqlin commented on GitHub (Feb 18, 2024): I am doing complete "Building the timeline", I uninstalled the Immich Android app, reinstall the latest [v1.94.1], login **1. Log every SQL query attached (Start from Login, until I stop video recording recording)** [postgresql15-2024-02-18T03-39-02.zip](https://github.com/immich-app/immich/files/14321039/postgresql15-2024-02-18T03-39-02.zip) **2. Video recording of the android "Build the timeline" (record start right after login)** https://youtu.be/yuue0A5zp60 **3. Check the Android Immich app log:** ![Image_20240217224917](https://github.com/immich-app/immich/assets/39827861/3f1109cd-1a42-423e-bcd2-1369cd23eccb) Android app Error log Detail: ` MESSAGE Error while getting remote assets: ApiException 400: HTTP connection failed: GET /asset (Inner exception: Connection closed before full header was received) #0 IOClient.send (package:http/src/io_client.dart:90) <asynchronous suspension> #1 BaseClient._sendUnstreamed (package:http/src/base_client.dart:93) <asynchronous suspension> #2 ApiClient.invokeAPI (package:openapi/api_client.dart:101) <asynchronous suspension> #3 AssetApi.getAllAssets (package:openapi/api/asset_api.dart:400) <asynchronous suspension> #4 AssetService._getRemoteAssets (package:immich_mobile/shared/services/asset.service.dart:72) <asynchronous suspension> #5 SyncService._syncRemoteAssetsFull (package:immich_mobile/shared/services/sync.service.dart:209) <asynchronous suspension> #6 SyncService.syncRemoteAssetsToDb.<anonymous closure> (package:immich_mobile/shared/services/sync.service.dart:53) <asynchronous suspension> #7 AsyncMutex.run.<anonymous closure> (package:immich_mobile/utils/async_mutex.dart:15) <asynchronous suspension> FROM AssetService Stack TRACES #0 ApiClient.invokeAPI (package:openapi/api_client.dart:125) <asynchronous suspension> #1 AssetApi.getAllAssets (package:openapi/api/asset_api.dart:400) <asynchronous suspension> #2 AssetService._getRemoteAssets (package:immich_mobile/shared/services/asset.service.dart:72) <asynchronous suspension> #3 SyncService._syncRemoteAssetsFull (package:immich_mobile/shared/services/sync.service.dart:209) <asynchronous suspension> #4 SyncService.syncRemoteAssetsToDb.<anonymous closure> (package:immich_mobile/shared/services/sync.service.dart:53) <asynchronous suspension> #5 AsyncMutex.run.<anonymous closure> (package:immich_mobile/utils/async_mutex.dart:15) <asynchronous suspension>` I hope from the SQL query log, you can find more problems with the database query From the video recording, you could see the KB/s or MB/s of network speed at the top of the screen, the network goes idle after a couple of minutes, but "Building the timeline" never stops
OVERLORD commented 2026-02-05 05:29:36 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

I'm not very familiar with how the app sync works, but it might be fetching 5000 IDs (with the query you posted) and querying the server for the assets of those IDs.

@mertalev commented on GitHub (Feb 18, 2024): I'm not very familiar with how the app sync works, but it might be fetching 5000 IDs (with the query you posted) and querying the server for the assets of those IDs.
OVERLORD commented 2026-02-05 05:29:46 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

Also thanks for sharing the query logs! That will be very helpful

@mertalev commented on GitHub (Feb 18, 2024): Also thanks for sharing the query logs! That will be very helpful
OVERLORD commented 2026-02-05 05:29:51 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields.

@mertalev commented on GitHub (Feb 18, 2024): Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields.
OVERLORD commented 2026-02-05 05:29:53 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

Screenshot_2024-02-17-23-19-24-977_app alextran immich

More error log reported by Android App
Screenshot_2024-02-17-23-22-47-376_app alextran immich

Screenshot_2024-02-17-23-23-12-811_app alextran immich

@mingqlin commented on GitHub (Feb 18, 2024): ![Screenshot_2024-02-17-23-19-24-977_app alextran immich](https://github.com/immich-app/immich/assets/39827861/b70ae13d-f3b8-415b-857d-2c4d880bc6f3) More error log reported by Android App ![Screenshot_2024-02-17-23-22-47-376_app alextran immich](https://github.com/immich-app/immich/assets/39827861/8d82a74b-7b70-4c7a-a420-d400119e0ac4) ![Screenshot_2024-02-17-23-23-12-811_app alextran immich](https://github.com/immich-app/immich/assets/39827861/8431999e-6222-476a-aed0-f37fc6d7dc95)
OVERLORD commented 2026-02-05 05:29:59 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields.

IF ORM is doing from Client app, it is very large query data need to send over the network. I hope there is way to optimize it, since the same ID was return from the paging, any additinal data, should be query along the first paging and send over the network at the same time, to prevent secondary query by giving it all 5000 IDs back to the server (which just returned from the server).

Sorry, I am very new to Immich library, but I do a programmer, some of the things I say, may not be accurate

@mingqlin commented on GitHub (Feb 18, 2024): > Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields. IF ORM is doing from Client app, it is very large query data need to send over the network. I hope there is way to optimize it, since the same ID was return from the paging, any additinal data, should be query along the first paging and send over the network at the same time, to prevent secondary query by giving it all 5000 IDs back to the server (which just returned from the server). Sorry, I am very new to Immich library, but I do a programmer, some of the things I say, may not be accurate
OVERLORD commented 2026-02-05 05:30:02 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

Oh wow. I don't know anything about Isar (the local database in the app), so maybe one of the mobile devs can comment on what's happening there.

Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields.

IF ORM is doing from Client app, it is very large query data need to send over the network. I hope there is way to optimize it, since the same ID was return from the paging, any additinal data, should be query along the first paging and send over the network at the same time, to prevent secondary query by giving it all 5000 IDs back to the server (which just returned from the server)

The new query won't have this issue since it gets all the data in one pass.

@mertalev commented on GitHub (Feb 18, 2024): Oh wow. I don't know anything about Isar (the local database in the app), so maybe one of the mobile devs can comment on what's happening there. > > Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields. > > IF ORM is doing from Client app, it is very large query data need to send over the network. I hope there is way to optimize it, since the same ID was return from the paging, any additinal data, should be query along the first paging and send over the network at the same time, to prevent secondary query by giving it all 5000 IDs back to the server (which just returned from the server) The new query won't have this issue since it gets all the data in one pass.
OVERLORD commented 2026-02-05 05:30:07 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

For the Flutter developer, please check out this link, it may explain about the Isar DB Full exception, and possible solution:

https://github.com/isar/isar/issues/813

@mingqlin commented on GitHub (Feb 18, 2024): For the Flutter developer, please check out this link, it may explain about the Isar DB Full exception, and possible solution: https://github.com/isar/isar/issues/813
OVERLORD commented 2026-02-05 05:30:11 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

Thanks for the link! I think the way the mobile app syncs is by fetching all the assets from all pages before adding them to the local DB. With a large number of assets, this is probably too much for the database to handle in one transaction. Syncing one page at a time should avoid this issue.

@mertalev commented on GitHub (Feb 18, 2024): Thanks for the link! I think the way the mobile app syncs is by fetching all the assets from all pages before adding them to the local DB. With a large number of assets, this is probably too much for the database to handle in one transaction. Syncing one page at a time should avoid this issue.
OVERLORD commented 2026-02-05 05:30:15 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

Thanks for the link! I think the way the mobile app syncs is by fetching all the assets from all pages before adding them to the local DB. With a large number of assets, this is probably too much for the database to handle in one transaction. Syncing one page at a time should avoid this issue.

800,000 Assets need to Isar upsert 800,000/5000 = 160 times, If Isar 160 write is a performance issue, please consider adjusting 5000 per page, maybe increase it to 10000, or even higher. Not sure how 5000 per page was decided

@mingqlin commented on GitHub (Feb 18, 2024): > Thanks for the link! I think the way the mobile app syncs is by fetching all the assets from all pages before adding them to the local DB. With a large number of assets, this is probably too much for the database to handle in one transaction. Syncing one page at a time should avoid this issue. 800,000 Assets need to Isar upsert 800,000/5000 = 160 times, If Isar 160 write is a performance issue, please consider adjusting 5000 per page, maybe increase it to 10000, or even higher. Not sure how 5000 per page was decided
OVERLORD commented 2026-02-05 05:30:20 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

We should be able to increase it. That would also help with the fact that the query is run for each page: a page of 10000 reduces the number of queries from 160 to 80.

@mertalev commented on GitHub (Feb 18, 2024): We should be able to increase it. That would also help with the fact that the query is run for each page: a page of 10000 reduces the number of queries from 160 to 80.
OVERLORD commented 2026-02-05 05:30:22 +03:00
Author
Owner
Copy Link

@mingqlin commented on GitHub (Feb 18, 2024):

Google Chrome Browser and Microsft Edge Web Browser, clicking on the timeline (jump to different date) will freeze up the browser, I couldn't find any log to help fixing this issue. I hope developer could find a way to test with 1M+ assets Immich server to reproduce this issue

@mingqlin commented on GitHub (Feb 18, 2024): Google Chrome Browser and Microsft Edge Web Browser, clicking on the timeline (jump to different date) will freeze up the browser, I couldn't find any log to help fixing this issue. I hope developer could find a way to test with 1M+ assets Immich server to reproduce this issue
OVERLORD commented 2026-02-05 05:30:31 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Feb 18, 2024):

We're planning on adding OpenTelemetry integration throughout the server in the future. That will make it easier to identify these kinds of performance issues.

@mertalev commented on GitHub (Feb 18, 2024): We're planning on adding OpenTelemetry integration throughout the server in the future. That will make it easier to identify these kinds of performance issues.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:translation
cli
date-time
documentation
external-library
format
good first issue
needs-answer
nice to have
sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#2174
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?