Compreface makes suboptimal queries to DB on UPDATE event

[ivan-kripakov-m10]

Describe the bug Our installation: 1) CompreFace 1.1.0 in k8s (3 api servers) with resourses:

    resources:
      limits:
        cpu: 3
        memory: 21Gi
      requests:
        cpu: 1
        memory: 15Gi

2) Subjects number ~ 189000 3) Faces number (examples) ~ 243000

We use compreface's recognition API as a second factor for 2FA and see in pg logs sql queries like

select embedding0_.id as col_0_0_, embedding0_.embedding as col_1_0_, subject1_.subject_name as col_2_0_
from public.Embedding embedding0_ left outer join public.Subject subject1_ on embedding0_.subject_id=subject1_.id
where subject1_.api_key=$1

which takes much time to execute on our data (query explain analyse is below):

QUERY PLAN                                                                                                                                         |
---------------------------------------------------------------------------------------------------------------------------------------------------+
Gather  (cost=7094.81..101440.07 rows=242447 width=1085) (actual time=91471.818..102814.254 rows=241970 loops=1)                                   |
  Workers Planned: 2                                                                                                                               |
  Workers Launched: 2                                                                                                                              |
  ->  Parallel Hash Join  (cost=6094.81..76195.37 rows=101020 width=1085) (actual time=91475.228..91532.997 rows=80657 loops=3)                    |
        Hash Cond: (embedding0_.subject_id = subject1_.id)                                                                                         |
        ->  Parallel Seq Scan on embedding embedding0_  (cost=0.00..41755.33 rows=101033 width=1080) (actual time=0.007..27.440 rows=80663 loops=3)|
        ->  Parallel Hash  (cost=3891.00..3891.00 rows=108465 width=37) (actual time=91295.915..91295.916 rows=62535 loops=3)                      |
              Buckets: 65536  Batches: 4  Memory Usage: 3872kB                                                                                     |
              ->  Parallel Seq Scan on subject subject1_  (cost=0.00..3891.00 rows=108465 width=37) (actual time=0.013..21.257 rows=62535 loops=3) |
                    Filter: ((api_key)::text = 'XXXXXXXXXXXXXXXXXXXXXXXX'::text)                                                       |
                    Rows Removed by Filter: 7                                                                                                      |
Planning Time: 0.214 ms                                                                                                                            |
Execution Time: 102825.281 ms                                                                                                                      |

I made a small research and found that this query is used for cache updating: 1) compreface api receives an update event through pg notify command 2) compreface api creates new EmbeddingCollection for cache and rewrite the cache entry by api key

Expected behavior

Compreface API uses more optimal approach to update EmbeddingCollection cache