Repository: PnX-SI/TaxHub (language: python)

Issue:
[Optimisation] Route allnamebylist dans la recherche par nom "search_name"
Salut !

Dans le cadre d'une prestation avec l'Agence Régionale de la Biodiversité en île de France, un problème de performance a été noté sur la recherche de taxon dans Occtax. Cette recherche s'appuie sur la route `/allnamebylist` avec utilisation d'un `query_string`  "search_name".

### État des lieux
Cette route appelle la vue matérialisée `taxonomie.vm_taxref_list_forautocomplete` et un appel à cette route du type `https://demo.geonature.fr/taxhub/api/taxref/allnamebylist?search_name=lynx` génère la requête SQL suivante : 

```sql
SELECT 
    taxonomie.vm_taxref_list_forautocomplete.gid AS taxonomie_vm_taxref_list_forautocomplete_gid, 
    taxonomie.vm_taxref_list_forautocomplete.cd_nom AS taxonomie_vm_taxref_list_forautocomplete_cd_nom, 
    taxonomie.vm_taxref_list_forautocomplete.search_name AS taxonomie_vm_taxref_list_forautocomplete_search_name, 
    taxonomie.vm_taxref_list_forautocomplete.cd_ref AS taxonomie_vm_taxref_list_forautocomplete_cd_ref, 
    taxonomie.vm_taxref_list_forautocomplete.nom_valide AS taxonomie_vm_taxref_list_forautocomplete_nom_valide, 
    taxonomie.vm_taxref_list_forautocomplete.lb_nom AS taxonomie_vm_taxref_list_forautocomplete_lb_nom, 
    taxonomie.vm_taxref_list_forautocomplete.nom_vern AS taxonomie_vm_taxref_list_forautocomplete_nom_vern, 
    taxonomie.vm_taxref_list_forautocomplete.regne AS taxonomie_vm_taxref_list_forautocomplete_regne, 
    taxonomie.vm_taxref_list_forautocomplete.group2_inpn AS taxonomie_vm_taxref_list_forautocomplete_group2_inpn, 
    similarity(
      taxonomie.vm_taxref_list_forautocomplete.search_name, 
      'lynx'
    ) AS idx_trgm 
  FROM 
    taxonomie.vm_taxref_list_forautocomplete 
  WHERE 
    unaccent(
      taxonomie.vm_taxref_list_forautocomplete.search_name
    ) ILIKE unaccent('%lynx%') 
  ORDER BY 
    idx_trgm DESC, 
    taxonomie.vm_taxref_list_forautocomplete.cd_nom = taxonomie.vm_taxref_list_forautocomplete.cd_ref desc
```

### Analyse
**Avertissement** : je ne suis pas un pro de SQL, je vous écris ce que j'ai compris mais il est possible que j'écrive des grosses bêtises, n'hésitez surtout pas à me corriger si c'est le cas !

Avec un simple `EXPLAIN`, Cette requête prend du temps dans le ILIKE avec le unaccent (Parallel Seq Scan) : 
``` 
Gather Merge  (cost=23853.19..24360.02 rows=4344 width=207)
  Workers Planned: 2
  ->  Sort  (cost=22853.16..22858.59 rows=2172 width=207)
        Sort Key: (similarity(search_name, 'lynx'::text)) DESC, ((cd_nom = cd_ref)) DESC
        ->  Parallel Seq Scan on vm_taxref_list_forautocomplete  (cost=0.00..22732.78 rows=2172 width=207)
              Filter: (unaccent(search_name) ~~* unaccent('%lynx%'::text))
```

Après analyse de la requête de la vue matérialisée et quelques recherches, il s'avère que l'index suivant ne permet pas d'assez optimiser la requête : 

```sql
CREATE INDEX i_tri_vm_taxref_list_forautocomplete_search_name ON taxonomie.vm_taxref_list_forautocomplete USING gist (search_name  gist_trgm_ops);
```

### Potentielle solution 

1. Après recherches sur les types d'index possibles il s'avère que l'index de type GIN est plus performant que le GiST mais est plus long à mettre à jour. Donc GIN est parfait pour les données statiques comme c'est le cas ici : https://www.postgresql.org/docs/9.1/textsearch-indexes.html

2. Puisqu'on utilise `unaccent`, il faudrait à mon avis le mettre dans l'index du "search_name" directement. Le soucis c'est qu'`unnaccent` doit être rendu immutable (si j'ai bien compris). Donc, selon stackoverflow, il faut générer une fonction `f_unnaccent` comme ceci : 
 ```sql
CREATE OR REPLACE FUNCTION public.f_unaccent(text)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$function$
;
 ```

Enfin le nouvel index devient : 
```sql
CREATE INDEX i_tri_vm_taxref_list_forautocomplete_search_name ON taxonomie.vm_taxref_list_forautocomplete USING gin (f_unaccent(search_name) gin_trgm_ops);
```

Et les performances sont incomparables :

```
Sort  (cost=300.12..300.28 rows=65 width=207)
  Sort Key: (similarity(search_name, 'lynx'::text)) DESC, ((cd_nom = cd_ref)) DESC
  ->  Bitmap Heap Scan on vm_taxref_list_forautocomplete  (cost=32.51..298.16 rows=65 width=207)
        Recheck Cond: (f_unaccent(search_name) ~~* '%lynx%'::text)
        ->  Bitmap Index Scan on i_tri_vm_taxref_list_forautocomplete_search_name  (cost=0.00..32.49 rows=65 width=0)
              Index Cond: (f_unaccent(search_name) ~~* '%lynx%'::text)
```

Et la recherche sur occtax est beaucoup plus agréable avec des temps d'une quinzaines de millisecondes contre 1-2 secondes voire plus.

J'ai effectué plusieurs essais, et ça retourne les mêmes résultats mais il faudrait trouver un moyen de valider que cela retourne bien **tout le temps** les mêmes résultats.

Désolé pour le long roman...
Si ça vous convient, je peux faire une PR et en attendant, n'hésitez pas à me dire ce que vous en pensez !

Bonus : la lecture qui m'a bien aidé : https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/ 


Discussion / hints:
Concernant cette recherche avait pas mal creusé le volet pertinence détaillé ici - https://si.ecrins-parcnational.com/blog/2019-01-fuzzy-search-taxons.html

Sur les performances et propositions, je ne sais pas.
Je laisse @TheoLechemia et @amandine-sahl répondre.
Merci pour cet article très intéressant @camillemonchicourt (merci @TheoLechemia par la même occasion !)

L'objectif n'est pas de modifier le fonctionnement mais plutôt de l'optimiser. En effet, en testant "ibex" on a une inversion de quelques suggestions mais pas les 3 premières : 

**Actuellement** (demo.geonature.fr) : 
![image](https://user-images.githubusercontent.com/85738261/233999612-65b62b10-42f9-4a5b-b694-9ba0b0bfa7d7.png)

**Après optimisation** : 
![image](https://user-images.githubusercontent.com/85738261/233999739-868012ad-1394-4abb-b3c0-b48570aa4a62.png)

Honnêtement, il n'est pas possible de déterminer celui qui a raison ici. 
On vient de creuser un peu avec Maxime. Le nouvel index est vraiment performant. 
Je me demandais juste si ce n'était pas plus judicieux d'ajouter une colonne `search_name_unaccent` qui copie `search_name` sans les accent, et de mettre l'index sur cette colonne, plutôt que de créer une fonction `f_unnaccent` en immuable ? La VM prend 50Mo au passage, forcément ..
Je ne me souviens plus comment on met à jour cette VM et à quelle fréquence. 
Mais si c'est que de temps en temps, ça me semble pas un soucis que la génération de l'index soit un peu long.
Si ça peut éviter d'ajouter une colonne et de stockage ?
A mon avis, il faut la mettre à jour à chaque mise à jour de Taxref donc pour le temps de génération c'est pas grand chose face à l'expérience utilisateur à mon avis.

La question ici est : vaut-il mieux ajouter une colonne ou créer une fonction `public.f_unaccent` qui rend la fonction `unaccent` immutable. 
Sachant que créer une fonction veut dire la maintenir mais elle pourrait bénéficier à d'autres index (pour d'autres recherches notamment). 
Créer une colonne augmente la taille forcément de la VM. Mais cette taille restera assez fixe dans le temps (dépendra du nombre de taxons).

En faisant confiance aux 👍, je fais une PR avec la solution de @TheoLechemia

Merci à vous !
La VM est uniquement créée à l'installation de la BDD, et à la MAJ de Taxref. C'est une copie de Taxref remise en forme pour les besoin de la recherche

Fix patch:
diff --git a/apptax/migrations/versions/3bd542b72955_optimize_vm_taxref_for_autocomplete.py b/apptax/migrations/versions/3bd542b72955_optimize_vm_taxref_for_autocomplete.py
new file mode 100644
index 00000000..6ca4177e
--- /dev/null
+++ b/apptax/migrations/versions/3bd542b72955_optimize_vm_taxref_for_autocomplete.py
@@ -0,0 +1,113 @@
+"""optimize_vm_taxref_for_autocomplete
+
+Revision ID: 3bd542b72955
+Revises: 188bc535258a
+Create Date: 2023-04-25 09:46:47.311160
+
+"""
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = "3bd542b72955"
+down_revision = "23c25552d707"
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    op.execute(
+        """
+        DROP MATERIALIZED VIEW taxonomie.vm_taxref_list_forautocomplete;
+        CREATE MATERIALIZED VIEW taxonomie.vm_taxref_list_forautocomplete AS
+        SELECT row_number() OVER () AS gid,
+        t.cd_nom,
+        t.cd_ref,
+        t.search_name,
+        unaccent(t.search_name) AS unaccent_search_name,
+        t.nom_valide,
+        t.lb_nom,
+        t.nom_vern,
+        t.regne,
+        t.group2_inpn
+        FROM ( SELECT t_1.cd_nom,
+                    t_1.cd_ref,
+                    concat(t_1.lb_nom, ' = <i>', t_1.nom_valide, '</i>', ' - [', t_1.id_rang, ' - ', t_1.cd_nom, ']') AS search_name,
+                    t_1.nom_valide,
+                    t_1.lb_nom,
+                    t_1.nom_vern,
+                    t_1.regne,
+                    t_1.group2_inpn
+                FROM taxonomie.taxref t_1
+                UNION
+                SELECT DISTINCT t_1.cd_nom,
+                    t_1.cd_ref,
+                    concat(split_part(t_1.nom_vern::text, ','::text, 1), ' = <i>', t_1.nom_valide, '</i>', ' - [', t_1.id_rang, ' - ', t_1.cd_ref, ']') AS search_name,
+                    t_1.nom_valide,
+                    t_1.lb_nom,
+                    t_1.nom_vern,
+                    t_1.regne,
+                    t_1.group2_inpn
+                FROM taxonomie.taxref t_1
+                WHERE t_1.nom_vern IS NOT NULL AND t_1.cd_nom = t_1.cd_ref
+                )t 
+        WITH DATA;
+        """
+    )
+
+    op.execute(
+        """
+        CREATE INDEX i_tri_vm_taxref_list_forautocomplete_search_name ON taxonomie.vm_taxref_list_forautocomplete USING gin (unaccent_search_name gin_trgm_ops);
+        CREATE INDEX i_vm_taxref_list_forautocomplete_cd_nom ON taxonomie.vm_taxref_list_forautocomplete USING btree (cd_nom);
+        CREATE UNIQUE INDEX i_vm_taxref_list_forautocomplete_gid ON taxonomie.vm_taxref_list_forautocomplete USING btree (gid);
+        """
+    )
+
+
+def downgrade():
+    op.execute(
+        """
+        DROP MATERIALIZED VIEW taxonomie.vm_taxref_list_forautocomplete;
+        CREATE MATERIALIZED VIEW taxonomie.vm_taxref_list_forautocomplete AS
+        SELECT row_number() OVER () AS gid,
+        t.cd_nom,
+        t.cd_ref,
+        t.search_name,
+        t.nom_valide,
+        t.lb_nom,
+        t.nom_vern,
+        t.regne,
+        t.group2_inpn
+        FROM ( SELECT t_1.cd_nom,
+                    t_1.cd_ref,
+                    concat(t_1.lb_nom, ' = <i>', t_1.nom_valide, '</i>', ' - [', t_1.id_rang, ' - ', t_1.cd_nom, ']') AS search_name,
+                    t_1.nom_valide,
+                    t_1.lb_nom,
+                    t_1.nom_vern,
+                    t_1.regne,
+                    t_1.group2_inpn
+                FROM taxonomie.taxref t_1
+                UNION
+                SELECT DISTINCT t_1.cd_nom,
+                    t_1.cd_ref,
+                    concat(split_part(t_1.nom_vern::text, ','::text, 1), ' = <i>', t_1.nom_valide, '</i>', ' - [', t_1.id_rang, ' - ', t_1.cd_ref, ']') AS search_name,
+                    t_1.nom_valide,
+                    t_1.lb_nom,
+                    t_1.nom_vern,
+                    t_1.regne,
+                    t_1.group2_inpn
+                FROM taxonomie.taxref t_1
+                WHERE t_1.nom_vern IS NOT NULL AND t_1.cd_nom = t_1.cd_ref
+                )t 
+        WITH DATA;       
+        """
+    )
+
+    op.execute(
+        """
+        CREATE unique INDEX i_vm_taxref_list_forautocomplete_gid ON taxonomie.vm_taxref_list_forautocomplete (gid);
+        CREATE INDEX i_vm_taxref_list_forautocomplete_cd_nom ON taxonomie.vm_taxref_list_forautocomplete (cd_nom ASC NULLS LAST);
+        CREATE INDEX i_vm_taxref_list_forautocomplete_search_name ON taxonomie.vm_taxref_list_forautocomplete (search_name ASC NULLS LAST);
+        CREATE INDEX i_tri_vm_taxref_list_forautocomplete_search_name ON taxonomie.vm_taxref_list_forautocomplete USING gist (search_name  gist_trgm_ops);
+        """
+    )
diff --git a/apptax/taxonomie/models.py b/apptax/taxonomie/models.py
index cb73d9de..8cc3315b 100644
--- a/apptax/taxonomie/models.py
+++ b/apptax/taxonomie/models.py
@@ -230,6 +230,7 @@ class VMTaxrefListForautocomplete(db.Model):
     gid = db.Column(db.Integer, primary_key=True)
     cd_nom = db.Column(db.Integer)
     search_name = db.Column(db.Unicode)
+    unaccent_search_name = db.Column(db.Unicode)
     cd_ref = db.Column(db.Integer)
     nom_valide = db.Column(db.Unicode)
     lb_nom = db.Column(db.Unicode)


Test patch:
diff --git a/apptax/taxonomie/routestaxref.py b/apptax/taxonomie/routestaxref.py
index ee1b4625..71b8720a 100644
--- a/apptax/taxonomie/routestaxref.py
+++ b/apptax/taxonomie/routestaxref.py
@@ -373,11 +373,13 @@ def get_AllTaxrefNameByListe(code_liste=None):
     search_name = request.args.get("search_name")
     if search_name:
         q = q.add_columns(
-            func.similarity(VMTaxrefListForautocomplete.search_name, search_name).label("idx_trgm")
+            func.similarity(VMTaxrefListForautocomplete.unaccent_search_name, search_name).label(
+                "idx_trgm"
+            )
         )
         search_name = search_name.replace(" ", "%")
         q = q.filter(
-            func.unaccent(VMTaxrefListForautocomplete.search_name).ilike(
+            VMTaxrefListForautocomplete.unaccent_search_name.ilike(
                 func.unaccent("%" + search_name + "%")
             )
         ).order_by(desc("idx_trgm"))
@@ -406,9 +408,9 @@ def get_AllTaxrefNameByListe(code_liste=None):
     data = q.paginate(page=page, per_page=limit, error_out=False)
 
     if search_name:
-        return [d[0].as_dict() for d in data.items]
+        return [d[0].as_dict(exclude=["unaccent_search_name"]) for d in data.items]
     else:
-        return [d.as_dict() for d in data.items]
+        return [d.as_dict(exclude=["unaccent_search_name"]) for d in data.items]
 
 
 @adresses.route("/bib_habitats", methods=["GET"])