Recherche rapide des espèces à partir d’un taxon de n’importe quel rang

[bouttier]

Plusieurs cas d’usage nécessite de filtrer des observations par taxon de n’importe quel rang :

• Recherche dans la synthèse
• Règles de sensibilité
• Permissions avec filtre taxonomique

Afin de répondre à ces besoins, deux stratégies existent :

• Dans le cas de la sensibilité, les règles sont dupliquées pour chaque espèce enfantes du taxon. Pour cela, on utilise une requête récursive, peu performante, mais le résultat est caché dans une vue matérialisé (à rafraîchir lors d’une mise à jour du référentiel de sensibilité).
• Dans le cas des recherches dans la synthèse ou des permissions avec filtre taxonomique, les taxons sont convertie en liste d’espèces (via find_all_taxons_children), puis les requêtes utilisent une clause IN. Cette solution est très peu performante en cas de recherche à un niveau assez haut dans l’arbre taxonomique.

Postgresql propose un module ltree permettant d’effectuer des recherches sur des structures d’arbres.
L’idée serait :

• d’ajouter une colonne path (nom à définir) à la table taxref
• de la compléter avec le chemin dans l’arbre taxonomique vers le taxon sous la forme {cd_nom}.{cd_nom}.{cd_nom}
• la colonne serait calculé une fois pour toute lors de l’insertion du référentiel TaxRef
• la recherche des taxons descendant de cd_nom peut se faire par la requête suivante :

SELECT
    *
FROM
    taxonomie.taxref
WHERE
    (SELECT path FROM taxonomie.taxref WHERE cd_nom = :cd_nom) @> path

• penser à créer l’index adapté pour l’utilisation de l’opérateur @> :

CREATE INDEX path_gist_idx ON taxonomie.taxref USING GIST (path);

Cette solution apporterait de bien meilleur performance que les SELECT … IN et permettrait de s’affranchir de la vue matérialisé dans le cas du référentiel de sensibilité.

[bouttier]

Déjà proposé dans #269 via une VM taxref_tree et étendu aux attributs des taxons par #284.

[bouttier]

• Vue matérialisée
  + on stock les paths uniquement pour les cd_ref
− il faut joindre taxref_tree à taxref lorsque l’on souhaite filtrer

• Colonne sur taxref
− on duplique les paths pour chaque synonyme
  + inutile de joindre taxref_tree pour filtrer

[camillemonchicourt]

C'est pas mal aussi de ne pas ajouter de colonne maison dans la table Taxref.

[jpm-cbna]

• Colonne sur taxref
− on duplique les paths pour chaque synonyme
  + inutile de joindre taxref_tree pour filtrer

Autre avantage, on élimine une jointure ce qui augmente les performances des requêtes. Ceci dit les exemples proposés dans le ticket #269 nécessite de joindre plusieurs fois la vue stockant le champ path.

L'utilisation d'une table externe ou d'une vue matérialisé (surement plus simple pour la maintenance de son contenu) est donc peut être tout aussi rapide.
Il faudrait comparer les temps d’exécution des 2 solutions.

[camillemonchicourt]

Extension ltree et vue matérialisée taxonomie.vm_taxref_tree ajoutée dans la version 2.0.0