[Optimisation] Route allnamebylist dans la recherche par nom "search_name"

[mvergez]

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 :

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 :

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 :

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 :

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/

[camillemonchicourt]

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.

[mvergez]

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) :

Après optimisation :

Honnêtement, il n'est pas possible de déterminer celui qui a raison ici.

[TheoLechemia]

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 ..

[camillemonchicourt]

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 ?

[mvergez]

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 !

[TheoLechemia]

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

[camillemonchicourt]

Intégré dans la 1.11.2.