Ralentissements fiche Espèce

[jpm-cbna]

Sur l'Atlas du SINP AURA (23 millions d'observations), je constate des ralentissements sur la page "Espèce".

Après investigation, le ralentissement provient d'au moins 2 requêtes qui n'arrivent pas à utiliser l'index prévu vm_observations_cd_ref_idx:

SELECT DISTINCT observateurs 
FROM atlas.vm_observations 
WHERE cd_ref IN ( SELECT * FROM atlas.find_all_taxons_childs(104409) ) OR cd_ref = 104409 ;

SELECT DISTINCT (com.insee) AS insee, com.commune_maj 
FROM atlas.vm_communes com 
    JOIN atlas.vm_observations obs 
        ON obs.insee = com.insee 
WHERE obs.cd_ref IN ( SELECT * FROM atlas.find_all_taxons_childs(104409) ) OR obs.cd_ref = 104409 
ORDER BY com.commune_maj ASC ;

Voir les résultats du EXPLAIN pour la première (temps d'execution 5s 817ms) et la seconde (temps d'execution 5s 753ms).

Le problème est lié à la clause WHERE dont l'expression empêche l'utilisation de l'index vm_observations_cd_ref_idx.

Pour résoudre ce souci, il faudrait plutôt utiliser des requêtes construites ainsi:

WITH taxons AS (
    SELECT * FROM (VALUES(104409)) AS taxon(cd_nom)
    UNION
    SELECT * FROM atlas.find_all_taxons_childs(104409) AS taxon_childs(cd_nom)
)
SELECT DISTINCT observateurs 
FROM atlas.vm_observations 
WHERE cd_ref IN ( SELECT cd_nom FROM taxons) ;

WITH taxons AS (
    SELECT * FROM (VALUES(104409)) AS taxon(cd_nom)
    UNION
    SELECT * FROM atlas.find_all_taxons_childs(104409) AS taxon_childs(cd_nom)
)
SELECT DISTINCT (com.insee) AS insee, com.commune_maj 
FROM atlas.vm_communes com 
    JOIN atlas.vm_observations obs 
        ON obs.insee = com.insee 
WHERE obs.cd_ref IN (SELECT cd_nom FROM taxons) 
ORDER BY com.commune_maj ASC ;

Voir les nouveaux résultats du EXPLAIN pour la première (temps d'execution 177ms) et pour la seconde (temps d'execution 276ms ).

En modifiant ces deux requête, le délai d'attente de la réponse du serveur de la page passe de ~12s à 700ms !

[jpm-cbna]

Sur une seconde instance de la base de données dont la base Postgresql est hébergée dans un container Docker, la requête CTE "taxons" qui permet d'agréger les cd_nom ralentissait aussi la requête principale.

Au final, en découpant en 2 requêtes distinctes, c'est le plus rapide. Une première requête récupère les cd_nom à utiliser puis nous les injectons dans la seconde requête.