Search endpoint is taking forever

[flomonster]

What happened?

When searching for a simple operational point, it takes too long —3 seconds and sometimes more (~8s).

Note

Can't reproduce in local

The DB query takes all the time. See the analyze of the query:

 Subquery Scan on _result  (cost=117355.55..226675.37 rows=1 width=32) (actual time=1504.854..15140.787 rows=10 loops=1)
   ->  Limit  (cost=117355.55..226675.36 rows=1 width=268) (actual time=1504.805..15140.300 rows=10 loops=1)
         ->  Nested Loop  (cost=117355.55..226675.36 rows=1 width=268) (actual time=1504.803..15140.289 rows=10 loops=1)
               Join Filter: (((op.obj_id)::text = (infra_layer_operational_point.obj_id)::text) AND (op.infra_id = infra_layer_operational_point.infra_id))
               Rows Removed by Join Filter: 2422699
               ->  Nested Loop  (cost=0.72..1657.25 rows=1 width=717) (actual time=0.923..12.532 rows=10 loops=1)
                     ->  Index Scan using search_operational_point_infra_id on search_operational_point  (cost=0.30..1648.81 rows=1 width=8) (actual time=0.911..12.353 rows=10 loops=1)
                           Index Cond: (infra_id = 145)
                           Filter: (((trigram)::text ~~* 'PLY'::text) OR (name ~~* '%ply%'::text))
                           Rows Removed by Filter: 8456
                     ->  Index Scan using infra_object_operational_point_pkey on infra_object_operational_point op  (cost=0.42..8.44 rows=1 width=725) (actual time=0.012..0.012 rows=1 loops=10)
                           Index Cond: (id = search_operational_point.id)
               ->  Unique  (cost=117354.83..222821.13 rows=86828 width=148) (actual time=966.759..1477.090 rows=242271 loops=10)
                     ->  Gather Merge  (cost=117354.83..218479.76 rows=868275 width=148) (actual time=966.757..1365.681 rows=765973 loops=10)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  Sort  (cost=116354.80..117259.26 rows=361781 width=148) (actual time=822.665..986.088 rows=255692 loops=30)
                                 Sort Key: infra_layer_operational_point.infra_id, infra_layer_operational_point.obj_id
                                 Sort Method: external sort  Disk: 24544kB
                                 Worker 0:  Sort Method: external merge  Disk: 25168kB
                                 Worker 1:  Sort Method: external merge  Disk: 25168kB
                                 ->  Parallel Seq Scan on infra_layer_operational_point  (cost=0.00..28542.81 rows=361781 width=148) (actual time=0.012..48.130 rows=280160 loops=30)
 Planning Time: 1.603 ms
 Execution Time: 15146.305 ms

What did you expect to happen?

It should takes max 100ms

How can we reproduce it (as minimally and precisely as possible)?

1. Go to STDCM, and search for an OP
2. Observe the time taken by the endpoint

On which environments the bug occurs?

Recette (SNCF)

On which browser the bug occurs?

Firefox

OSRD version (top right corner Account button > Informations)

6580cfe

[flomonster]

Slow query: https://explain.dalibo.com/plan/0ga6g194e3hhghc4
Target query: https://explain.dalibo.com/plan/faaa8b7g440hge52