[Feature] Modify person in photo page only show named person.

[ffchung]

I have searched the existing feature requests, both open and closed, to make sure this is not a duplicate request.

• Yes

The feature

The Modify person feature in the photo page is slow when listing the people. I assume we will only select named people. The query will run much faster.

Platform

• Server
• Web
• Mobile

[bo0tzz]

It's not clear to me what you mean exactly. Can you provide steps to reproduce this slowness?

[Minos2020]

I think what he means is that when you use the "Modify faces" feature on a picture, there's no need to show all faces as options, but only the ones that are named (faces you've given a name to).

[ffchung]

I want to change the sql, but it have share with other function.

Org :

explain analyze select "person".* from "person"                                                                                                                                                                
inner join "asset_face"                                                                                                                                                                                                 
on "asset_face"."personId" = "person"."id"                                                                                                                                                                              
inner join "asset"                                                                                                                                                                                                      
on  "asset"."visibility" = 'timeline'                                                                                                                                                                                   
and "asset"."deletedAt" is null                                                                                                                                                                                         
and "asset"."id" = "asset_face"."assetId"                                                                                                                                                                               
where "person"."ownerId" = '4918fb77-e529-4d79-b7df-af1a478f35f0'                                                                                                                                                       
and "asset_face"."deletedAt" is null                                                                                                                                                                                    
and "asset_face"."isVisible" is true                                                                                                                                                                                    
group by "person"."id" having ("person"."name" != '' or count("asset_face"."assetId") >= 1)                                                                                                                             
order by "person"."isHidden" asc, "person"."isFavorite" desc,                                                                                                                                                           
(select "face_search"."embedding" from "face_search" where "face_search"."faceId" = "person"."faceAssetId") <=> (select "face_search"."embedding"                                                                       
from "face_search"                                                                                                                                                                                                      
where "face_search"."faceId" =  '9d56f141-8a70-479f-ad70-ac75c7ef13d0')                                                                                                                                                 
limit 501 offset 0; 

                                                                                                            QUERY PLAN                                                                                                 
                                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------                                                                                                                                                                                                           
 Limit  (cost=232814.24..232815.49 rows=501 width=227) (actual time=5156.796..5168.753 rows=501 loops=1)                                                                                                                
   InitPlan 2 (returns $1)                                                                                                                                                                                              
     ->  Index Scan using face_search_pkey on face_search face_search_1  (cost=0.43..2.84 rows=1 width=18) (actual time=0.017..0.018 rows=1 loops=1)                                                                    
           Index Cond: ("faceId" = '9d56f141-8a70-479f-ad70-ac75c7ef13d0'::uuid)                                                                                                                                        
   ->  Sort  (cost=232811.39..232961.75 rows=60142 width=227) (actual time=5128.194..5140.112 rows=501 loops=1)                                                                                                         
         Sort Key: person."isHidden", person."isFavorite" DESC, (((SubPlan 1) <=> $1))                                                                                                                                  
         Sort Method: top-N heapsort  Memory: 249kB                                                                                                                                                                     
         ->  HashAggregate  (cost=55857.84..229813.71 rows=60142 width=227) (actual time=969.756..4908.068 rows=178257 loops=1)                                                                                         
               Group Key: person.id                                                                                                                                                                                     
               Filter: (((person.name)::text <> ''::text) OR (count(asset_face."assetId") >= 1))                                                                                                                        
               Batches: 1  Memory Usage: 63505kB                                                                                                                                                                        
               ->  Gather  (cost=25262.13..52335.58 rows=704451 width=235) (actual time=280.139..562.276 rows=870004 loops=1)                                                                                           
                     Workers Planned: 2                                                                                                                                                                                 
                     Workers Launched: 2                                                                                                                                                                                
                     ->  Parallel Hash Join  (cost=24262.13..44291.07 rows=293521 width=235) (actual time=234.439..619.752 rows=290001 loops=3)                                                                         
                           Hash Cond: (asset_face."assetId" = asset.id)                                                                                                                                                 
                           ->  Parallel Hash Join  (cost=8714.57..27877.18 rows=330031 width=235) (actual time=42.454..283.058 rows=291932 loops=3)                                                                     
                                 Hash Cond: (asset_face."personId" = person.id)                                                                                                                                         
                                 ->  Parallel Index Only Scan using "asset_face_personId_assetId_notDeleted_isVisible_idx" on asset_face  (cost=0.43..18003.84 rows=441592 width=32) (actual time=0.067..93.387 rows=351
800 loops=3)                                                                                                                                                                                                            
                                       Heap Fetches: 66722                                                                                                                                                              
                                 ->  Parallel Hash  (cost=7776.11..7776.11 rows=75042 width=219) (actual time=42.077..42.078 rows=59786 loops=3)                                                                        
                                       Buckets: 262144  Batches: 1  Memory Usage: 41376kB                                                                                                                               
                                       ->  Parallel Seq Scan on person  (cost=0.00..7776.11 rows=75042 width=219) (actual time=0.043..15.773 rows=59786 loops=3)                                                        
                                             Filter: ("ownerId" = '4918fb77-e529-4d79-b7df-af1a478f35f0'::uuid)                                                                                                         
                                             Rows Removed by Filter: 20099                                                                                                                                              
                           ->  Parallel Hash  (cost=11283.23..11283.23 rows=341146 width=16) (actual time=190.489..190.489 rows=272951 loops=3)                                                                         
                                 Buckets: 1048576  Batches: 1  Memory Usage: 46688kB                                                                                                                                    
                                 ->  Parallel Index Only Scan using "asset_id_timeline_notDeleted_idx" on asset  (cost=0.42..11283.23 rows=341146 width=16) (actual time=0.042..96.506 rows=272951 loops=3)             
                                       Heap Fetches: 89313                                                                                                                                                              
               SubPlan 1                                                                                                                                                                                                
                 ->  Index Scan using face_search_pkey on face_search  (cost=0.43..2.84 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=178257)                                                                 
                       Index Cond: ("faceId" = person."faceAssetId")                                                                                                                                                    
 Planning Time: 2.928 ms                                                                                                                                                                                                
 JIT:                                                                                                                                                                                                                   
   Functions: 67                                                                                                                                                                                                        
   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                                                        
   Timing: Generation 8.093 ms, Inlining 0.000 ms, Optimization 7.101 ms, Emission 46.334 ms, Total 61.528 ms                                                                                                           
 Execution Time: 5179.306 ms                                                                                                                                                                                            
(38 rows)                                                                                                                                   

Only Named :

explain analyze select "person".* from "person"                                                                                                                                                        
inner join "asset_face"                                                                                                                                                                                                 
on "asset_face"."personId" = "person"."id"                                                                                                                                                                              
inner join "asset"                                                                                                                                                                                                      
on  "asset"."visibility" = 'timeline'                                                                                                                                                                                   
and "asset"."deletedAt" is null                                                                                                                                                                                         
and "asset"."id" = "asset_face"."assetId"                                                                                                                                                                               
where "person"."ownerId" = '4918fb77-e529-4d79-b7df-af1a478f35f0'                                                                                                                                                       
and "person"."name" != '' and "asset_face"."deletedAt" is null                                                                                                                                                          
and "asset_face"."isVisible" is true                                                                                                                                                                                    
group by "person"."id" having ("person"."name" != '' or count("asset_face"."assetId") >= 1)                                                                                                                             
order by "person"."isHidden" asc, "person"."isFavorite" desc,                                                                                                                                                           
(select "face_search"."embedding" from "face_search" where "face_search"."faceId" = "person"."faceAssetId") <=> (select "face_search"."embedding"                                                                       
from "face_search"                                                                                                                                                                                                      
where "face_search"."faceId" =  '9d56f141-8a70-479f-ad70-ac75c7ef13d0')   

                                                                                                       QUERY PLAN                                                                                                       
                                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-                                                                                                                                                                                                                       
 Limit  (cost=9562.81..9562.95 rows=54 width=227) (actual time=959.593..974.033 rows=153 loops=1)                                                                                                                       
   InitPlan 2 (returns $1)                                                                                                                                                                                              
     ->  Index Scan using face_search_pkey on face_search face_search_1  (cost=0.43..2.84 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=1)                                                                    
           Index Cond: ("faceId" = '9d56f141-8a70-479f-ad70-ac75c7ef13d0'::uuid)                                                                                                                                        
   ->  Sort  (cost=9559.97..9560.10 rows=54 width=227) (actual time=959.591..974.017 rows=153 loops=1)                                                                                                                  
         Sort Key: person."isHidden", person."isFavorite" DESC, (((SubPlan 1) <=> $1))                                                                                                                                  
         Sort Method: quicksort  Memory: 65kB                                                                                                                                                                           
         ->  GroupAggregate  (cost=9385.15..9558.41 rows=54 width=227) (actual time=802.247..973.557 rows=153 loops=1)                                                                                                  
               Group Key: person.id                                                                                                                                                                                     
               Filter: (((person.name)::text <> ''::text) OR (count(asset_face."assetId") >= 1))                                                                                                                        
               ->  Gather Merge  (cost=9385.15..9399.05 rows=633 width=235) (actual time=802.009..900.013 rows=347399 loops=1)                                                                                          
                     Workers Planned: 2                                                                                                                                                                                 
                     Workers Launched: 2                                                                                                                                                                                
                     ->  Sort  (cost=8385.12..8385.78 rows=264 width=235) (actual time=527.154..534.821 rows=115800 loops=3)                                                                                            
                           Sort Key: person.id                                                                                                                                                                          
                           Sort Method: quicksort  Memory: 33424kB                                                                                                                                                      
                           Worker 0:  Sort Method: quicksort  Memory: 16080kB                                                                                                                                           
                           Worker 1:  Sort Method: quicksort  Memory: 47764kB                                                                                                                                           
                           ->  Nested Loop  (cost=0.85..8374.51 rows=264 width=235) (actual time=0.250..449.603 rows=115800 loops=3)                                                                                    
                                 ->  Nested Loop  (cost=0.43..8242.02 rows=297 width=235) (actual time=0.196..72.052 rows=117069 loops=3)                                                                               
                                       ->  Parallel Seq Scan on person  (cost=0.00..8027.14 rows=68 width=219) (actual time=0.101..17.687 rows=51 loops=3)                                                              
                                             Filter: (((name)::text <> ''::text) AND ("ownerId" = '4918fb77-e529-4d79-b7df-af1a478f35f0'::uuid))                                                                        
                                             Rows Removed by Filter: 79835                                                                                                                                              
                                       ->  Index Only Scan using "asset_face_personId_assetId_notDeleted_isVisible_idx" on asset_face  (cost=0.43..2.60 rows=56 width=32) (actual time=0.015..0.638 rows=2295 loops=153)
                                             Index Cond: ("personId" = person.id)                                                                                                                                       
                                             Heap Fetches: 20301                                                                                                                                                        
                                 ->  Index Only Scan using "asset_id_timeline_notDeleted_idx" on asset  (cost=0.42..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=351207)                                
                                       Index Cond: (id = asset_face."assetId")                                                                                                                                          
                                       Heap Fetches: 37376                                                                                                                                                              
               SubPlan 1                                                                                                                                                                                                
                 ->  Index Scan using face_search_pkey on face_search  (cost=0.43..2.84 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=153)                                                                    
                       Index Cond: ("faceId" = person."faceAssetId")                                                                                                                                                    
 Planning Time: 1.054 ms                                                                                                                                                                                                
 Execution Time: 974.156 ms                                                                                                                                                                                             
(34 rows)                                                                                                                                                                                                               
                                                                                           

[bo0tzz]

How many people do you have in your instance?

[ffchung]

immich=# select count(*) from person;                                            
 count                                                                           
--------                                                                         
 239684                                                                          
(1 row)                                                                          
                                                                                 
immich=# select count(*) from asset;                                             
 count                                                                           
--------                                                                         
 924139                                                                          
(1 row)                                                                          
                                                                                 
immich=# select count(*) from asset_face;                                        
  count                                                                          
---------                                                                        
 1055470                                                                         
(1 row)