Lenteur requête "ST_DWithin" lancée malgré la segmentation dynamique

[RemiClaus]

Bonjour à vous,

Contexte
Nous avons détecté des lenteurs importantes sur notre géotrek au chargement dans "rando" de longs itinéraires sur plusieurs étapes.
Ces lenteurs aboutissent parfois au chargement de la page, après plus de 30 secondes d'attente.
Mais parfois, ça bloque et rien ne s'affiche.

Serveur utilisé et base de données
Le serveur est très puissant, 8 coeurs, et plus de 100Go de RAM.
La base contient 250 tronçons, 100 POI environ, 40 treks équivalent à 1 journée d'activité et 4 itinérances comprenant une dizaine de treks en moyenne.
Elle n'est donc pas gigantesque.
La segmentation dynamique est activée.
L'interface "rando" est ici : https://rando.itineraireschalaisiens.fr/search

Analyse du problème
En regardant les logs des fois où le chargement ne se fait pas, on a identifié la requête qui crée cette lenteur :
En la simplifiant, elle ressemble à ceci :

SELECT
	core_topology.id,
	core_topology."geom_3d"::bytea,
	core_topology."autres champs....",
	trekking_poi."des champs....",
	ST_Transform(topology."geom_3d", 4326)::bytea AS "geom3d_transformed",
	trekking_poitype."id",
	poitype."des champs...."
FROM "trekking_poi" as poi
INNER JOIN "core_topology" ON ("trekking_poi"."topo_object_id" = "core_topology"."id")
INNER JOIN "trekking_poitype" ON ("trekking_poi"."type_id" = "trekking_poitype"."id")

WHERE (
	NOT "core_topology"."deleted"
	AND "trekking_poi"."published_fr"
	AND ST_DWithin(topology."geom", ST_GeomFromEWKB('GEOMETRIETRESLONGUE'::bytea), 500.0)
	)
ORDER BY poi."topo_object_id"
ASC LIMIT 37

Dans cette requête, ST_GeomFromEWKB('GEOMETRIETRESLONGUE'::bytea) correspond à l'itinérance à plusieurs étapes que l'on souhaite afficher.

Questions soulevées par cette requête ?
Cette requête cherche à localiser les entités qui se trouvent à 500m de l'itinérance.
Or, les 500m de cette requête ressemblent au paramètre de recherche par défaut quand la segmentation dynamique est désactivée comme indiqué ici : https://geotrek.readthedocs.io/fr/stable/install/advanced-configuration.html#trek-poi-intersection
Pourquoi donc a-t-on cette requete de recherche d'entités par buffer alors que la segmentation dynamique est activée ?

La requête met une bonne vingtaine de secondes pour s'exécuter.
Pendant ce laps de temps, le CPU est à 100%, ce qui empêche d'exécuter d'autres requêtes en même temps.

Autre question, pourquoi cette requête indique "limit 37" alors que quand on l'exécute sans la limite, elle renvoie pile 37 lignes ? C'est comme si la requête savait elle même à l'avance le nombre de lignes qu'elle renvoyait.

Suggestions / Avis ?
Avez-vous déjà rencontré ce problème ?
Avez-vous des pistes de solutions ?
Est-ce que nos itinéraires sont juste trop longs ? (200km pour certains...)

Je reste à dispo, Merci !

Rémi Borel pour les Chemins Chalaisiens

---

PS
En attendant, on a contourné le problème en désactivant l'affichage des POI dans les itinérances avec "enfants" (paramètre displayObjectsRelatedToItinerantTreks).
Mais ça ne résout pas le problème de fond...

[babastienne]

Bonjour @RemiClaus et merci pour cette analyse.

En effet les itinéraires longs créés des problèmes de performances dans Geotrek du fait de certaines requêtes d'intersections. C'est un sujet qui a déjà été identifié et documenté et pour lequel il faudrait développer des optimisations.

Une piste intéressante est celle de stocker lorsqu'on modifie un objet les intersections associée pour ne pas avoir à les recalculer à la volée à chaque fois que quelqu'un souhaite afficher l'itinéraire.

Toutefois à ce jour aucun territoire n'a souhaité ni financer cette évolution ni proposer de contribution au code pour mettre en oeuvre ce sujet.

Quelques tickets qui évoquent les sujets de performances liés à l'API ou à l'affichage de long itinéraires :

• API V2 : Performances et utilisation par Geotrek-rando V3 #3260
• Suite à donner aux perfs API V2 #3398
• Lenteur affichage itinérances Geotrek-rando-v3#753

En effet à défaut d'une solution du problème "à la source", un territoire a souhaité financer le développement d'une fonctionnalité pour pouvoir cacher les POI et objets associées sur un itinéraire qui contient des enfants (donc une itinérance). De même dans Geotrek-Rando il existe un paramètre pour limiter le nombre d'objets affichés. Dans les deux cas ce ne sont pas des solutions mais bien des contournements.

Le sujet ici est donc soit de contribuer dans le code pour corriger ce problème et optimiser ce sujet soir d'aider la communauté à le faire en finançant ce travail je pense.

---

Concernant le sujet de la requête SQL :

pourquoi cette requête indique "limit 37" alors que quand on l'exécute sans la limite, elle renvoie pile 37 lignes ?

En fait la requête affichée das le ticket est une requête qui est "générée" par le code de Geotrek, donc il y a des pré-calculs effectués pour générer les limites, les filtres, etc. : d'un itinéraire à l'autre la requête ne sera pas toujours la même. C'est l'ORM de Django qui s'occupe de générer la requête SQL.

[camillemonchicourt]

OK mais là ce que je ne comprends pas non plus, c'est pourquoi la requête un ST_DWithin de 500m entre la géométrie de l'itinéraire et la géométrie des POI alors que ce Geotrek-admin est en mode segmentation dynamique.
C'est ça qui semble galérer et souvent planter, alors que ce ne devrait pas être une intersection géographique entre itinéraire et POI en mode segmentation dynamique.
Une idée sur pourquoi ça fait un ST_DWithin ?

On n'a pas ces soucis de lenteur ou de plantage sur Grand tour des Ecrins, justement grâce à la segmentation dynamique qui évite de faire des intersections géographiques entre les objets.

Ou alors leur Geotrek-admin est en mode "segmentation dynamique", mais une modification ou une intervention sur le serveur précédente fait qu'il agit comme si il était sans segmentation dynamique ?

[babastienne]

En effet, ça ne devrait pas faire ce genre de requête en mode segmentation dynamique.

Sur une instance locale sur laquelle je switch régulièrement entre mode avec ou sans segmentation dynamique, j'ai testé de voir les requêtes réalisées lorsque j'interroge l'API pour obtenir les POI associés à un trek (url de type http://localhost:8000/api/v2/poi/?trek=85).

Testé en segmentation dynamique

SELECT (CASE WHEN core_topology.id=94 THEN 0 WHEN core_topology.id=95 THEN 1 END) AS "ordering",
       "core_topology"."id",
       "core_topology"."geom_3d"::bytea,
       ...
       "core_topology"."geom_need_update",
       "core_topology"."geom"::bytea,
       "core_topology"."uuid",
       "trekking_poi"."published",
       "trekking_poi"."published_fr",
       ...
       ST_Transform("core_topology"."geom_3d", 4326)::bytea AS "geom3d_transformed",
       "trekking_poitype"."id",
       "trekking_poitype"."date_insert",
       "trekking_poitype"."date_update",
       "trekking_poitype"."pictogram",
      ...
  FROM "trekking_poi"
 INNER JOIN "core_topology"
    ON ("trekking_poi"."topo_object_id" = "core_topology"."id")
 INNER JOIN "trekking_poitype"
    ON ("trekking_poi"."type_id" = "trekking_poitype"."id")
 WHERE (NOT "core_topology"."deleted" AND ("trekking_poi"."published_fr" OR "trekking_poi"."published_en") AND "trekking_poi"."topo_object_id" IN (94, 95) AND NOT ("trekking_poi"."topo_object_id" IN (SELECT U0."topo_object_id" FROM "trekking_poi" U0 INNER JOIN "trekking_trek_pois_excluded" U1 ON (U0."topo_object_id" = U1."poi_id") WHERE U1."trek_id" = 85)))
 ORDER BY 1 ASC
 LIMIT 2

Concrètement on voit bien qu'il n'y a pas de requête d'intersection mais plutôt de liens avec les topologies.

D'un point de vue chronologique :

• En (1) il s'agit de la requête pour récupérer les POI détaillée ci-dessus
• En voit en (2) qu'avant cette requête il y a bien un count d'effectué pour savoir déjà le nombre de POI associés. C'est pour optimiser : c'est peu gourmand comme requête et s'il n'y a aucun POI c'est beaucoup plus rapide pour ne pas avoir à ensuite demander d'interroger tous les champs en base pour rien.
• En (3) on voit qu'en mode segmentation dynamique il y a une requête de réalisée pour récupérer la liste des topologies associées à mon itinéraire. Ensuite c'est à partir de cette liste de topologies qu'on trouvera les liens avec les POI. C'est d'ailleurs cette requête la plus longue à exécuter, c'est plutôt normal.

En mode sans segmentation dynamique

Pour la même requête sur l'API, j'obtiens cette fois la requête SQL suivante :

SELECT "core_topology"."id",
       "core_topology"."date_insert",
       "core_topology"."date_update",
       "core_topology"."deleted",
       "core_topology"."geom_3d"::bytea,
       ...
       "core_topology"."geom"::bytea,
       "core_topology"."uuid",
       "trekking_poi"."published",
       ...
       "trekking_poi"."description_en",
       "trekking_poi"."type_id",
       "trekking_poi"."eid",
       "trekking_poi"."provider",
       ST_Transform("core_topology"."geom_3d", 4326)::bytea AS "geom3d_transformed",
       ST_LineLocatePoint(ST_Transform(ST_GeomFromEWKB('GEOM'::bytea), 2154), ST_Transform("core_topology"."geom", 2154)) AS "locate",
       "trekking_poitype"."id",
       "trekking_poitype"."date_insert",
       "trekking_poitype"."date_update",
       "trekking_poitype"."pictogram",
       "trekking_poitype"."label",
       "trekking_poitype"."label_fr",
       "trekking_poitype"."label_en",
       "trekking_poitype"."cirkwi_id"
  FROM "trekking_poi"
 INNER JOIN "core_topology"
    ON ("trekking_poi"."topo_object_id" = "core_topology"."id")
 INNER JOIN "trekking_poitype"
    ON ("trekking_poi"."type_id" = "trekking_poitype"."id")
 WHERE (NOT "core_topology"."deleted" AND ("trekking_poi"."published_fr" OR "trekking_poi"."published_en") AND ST_Intersects("core_topology"."geom", ST_GeomFromEWKB('MA_GEOM'::bytea)) AND NOT ("trekking_poi"."topo_object_id" IN (SELECT U0."topo_object_id" FROM "trekking_poi" U0 INNER JOIN "trekking_trek_pois_excluded" U1 ON (U0."topo_object_id" = U1."poi_id") WHERE U1."trek_id" = 85)))
 ORDER BY 34 ASC
 LIMIT 2

Je n'observe pas de ST_WITHIN mais un ST_Intersect.

---

En tout cas on voir qu'en segmentation dynamique ça n'effectue pas ce genre de calcul gourmand. Donc plusieurs questions soulevées de mon côté :

• Comment les requêtes SQL ont été obtenues ?
• Quelle est le détail de la configuration du Geotrek-Admin ?

[seegwen]

Bonjour @camillemonchicourt @babastienne. Je suis Gwen, développeur en charge du support et de l'hébergement sur le projet. Merci pour vos réponses.
La requête transmise par @RemiClaus vient du fichier de log (un extrait - docker compose logs postgres > postgres.log), et il y en a à répétition. Ce que n'a pas dit @RemiClaus c'est que ces requêtes vont au Timeout et que la plupart du temps elles n'aboutissent pas.

Le détail de la config est qu'il n'y a aucune config faite dans les fichiers dans var/conf/custom.py ou bien geotrek/settings/custom.py.dist. Tout est commenté et je n'y ai jamais touché. Y a-t-il un ou des paramètres pertinents à faire du coté de l'admin geotrek ?

[camillemonchicourt]

OK, merci pour ces investigations et précisions.
Y a bien un truc bizarre sur ce Geotrek-admin.
Déjà en regardant sa version (https://geotrek.itineraireschalaisiens.fr/api/v2/version), il est en 2.92.2 qui date de décembre 2022.
D'ailleurs si le Geotrek-rando-v3 a été mis à jour depuis, il y a peut-être des soucis de compatibilité entre la version du GTR et celle du GTA.
Et peut-être que des évolutions/optimisations ont été faites depuis la 2.92.2 de GTA qui expliquerait pourquoi dans leur cas il fait un ST_DWithin mais pas dans tes tests avec un GTA récent. Dans tous les cas, il devrait le faire que quand on est en mode SANS SEGMENTATION DYNAMIQUE (SD)...
A voir aussi si ce GTA n'a pas été installé AVEC SD puis basculé SANS SD, puis remis AVEC SD, ce qui aurait laissé des traces...

[camillemonchicourt]

En effet, un élément semble indiquer que SANS SD, avant, on faisait des ST_DWithin et qu'on a basculé en ST_Intersect : #3260 (comment)
Mais dans tous les cas, AVEC SD, pour les POI GTA ne fait ni l'un ni l'autre comme indiqué par les tests de @babastienne.

[seegwen]

Je n'ai pas fait ces changements de mode, donc s'ils ont eu lieu, c'est au cours des versions. Le geotrek a été installé en 2022 et a été mis à jour plusieurs fois depuis. Les données dans la bdd sont toutes fraiches (< 2semaines) et le geotrek n'a pas été mis à jour entre temps.

Là ce que je me dis c'est que j'ai peut-être mal suivi les procédures de mise à jour. Ma procédure:

git fetch && git checkout main

# update containers
docker compose pull && docker compose down && docker compose up -d

# Run migrations
docker-compose run --rm web update.sh

Et effectivement @camillemonchicourt dans mon .env à la racine j'ai bien ça: GEOTREK_VERSION=2.92.2 avec le docker-compose.yml qui a ça: image: geotrekce/admin:${GEOTREK_VERSION:-latest}

[babastienne]

Je pense qu'avant tout en effet la priorité est déjà de mettre à jour l'instance Geotrek-Admin. Normalement les commandes listées devraient fonctionner. Il faut dans le fichier .env indiquer plutôt comme version GEOTREK_VERSION=latest et ne pas toucher au fichier docker-compose.yml.

Ensuite lancer les commandes de MAJ et normalement ça devrait mettre à jour Geotrek. Attention à bien consulter le changelog et les releases notes depuis la version 2.92 jusqu'à la dernière pour vérifier qu'il n'y a pas de breaking changes à prendre en compte.

---

Ensuite il est recommandé d'avoir un minimum de configuration dans le fichier custom.py, à minima avec la configuration suivante : https://geotrek.readthedocs.io/en/2.111.0/install/configuration.html#mandatory-settings

Par défaut si rien n'est indiqué dans le fichier Geotrek va en prendre une générique mais ce n'est pas l'idéale.

---

Redites nous après mise à jour ce que donne le comportement de votre Geotrek.

[camillemonchicourt]

OK OK, je n'ai jamais utilisé ni testé un déploiement de GTA avec Docker, mais de ce que je vois.
En version 2.92.2 le docker-compose était fait pour taper dans la version "latest" : https://github.com/GeotrekCE/Geotrek-admin/blob/2.92.2/.env-prod.dist#L1
Mais tu as du fixer sur la 2.92.2 donc c'est normal que ça mette pas à jour ton GTA.
Depuis ça a évolué, on n'a plus qu'un fichier docker-compose proposé (et non plus un de dev et un de prod comme en 2.92.2) : https://github.com/GeotrekCE/Geotrek-admin/blob/master/docker-compose.yml
Et il semble bien prévu par défaut pour taper sur la latest.

[seegwen]

Oui. J'ai besoin de fixer la version car je suis obligé de modifier le docker-compose pour l'intégrer à mon infrastructure (notamment au load-balancer traefik avec des labels et pour provisionner les certificats automatiquement). Mais effectivement j'ai omis certains détails dans le processus d'upgrade. Merci pour les pistes. Je suis en train de tout mettre à jour (y a une mise à jour majeure de postgres au milieu également) je reviens ici dès que c'est fait et que j'ai testé le tout.

[seegwen]

J'ai eu quelques soucis un peu profonds avec les changements drastiques du docker-compose.yml entre la v2.92 et la v2.111 pour l'adapter à mon infrastructure. C'est maintenant en place.

Effectivement il y a eu de grosses améliorations au niveau de la charge serveur et du temps de réponse ! C'est une toute autre histoire !

Merci pour les pistes et m'avoir permis de voir que mon process d'upgrade était incorrect.

Du coté de geotrek-admin, on dirait bien que tout est rentré dans l'ordre.

[camillemonchicourt]

OK super.
Ça resterait intéressant de vérifier et savoir si les requêtes qui sont faites sur votre Geotrek-admin pour récupérer les POI d'un itinéraire sont fait avec la requête du mode SANS SD ou avec la requête du mode AVEC SD.
Car dans tous les cas, dans votre cas, elles devraient correspondre au mode AVEC SD mais elles semblent en mode SANS SD...

[seegwen]

Vous auriez une méthode pour vérifier ça ? Un log du serveur postgres ?

[camillemonchicourt]

Oui, voir #4423 (comment) où ce sont les logs PostgreSQL lors de l'appel API des POI d'un itinéraire.

[babastienne]

La présence de # TREKKING_TOPOLOGY_ENABLED = False dans le fichier custom (cf GeotrekCE/Geotrek-rando-v3#1330 (comment)) alors que ce n'est pas présent par défaut à l'installation suggère un passage à un moment donné à un mode sans segmentation dynamique.

Ca va dans le sens d'une bascule d'un mode à l'autre qui aurait engendré ces requêtes.

[camillemonchicourt]

Oui c'est aussi ce que je suspecte aussi.

[seegwen]

Non ils n'ont jamais été décommentés. Je les ai ajoutés là quand @RemiClaus a mentionné le drapeau (dans un email ou il évoquait une piste pour notre problème).

[seegwen]

@babastienne et @camillemonchicourt, merci encore pour vos éclairages.
Oserais-je demander comment obtenir la liste des requêtes postgres svp ?
Est-ce un pur log postgres à configurer/récupérer dans le container postgres ou une autre méthode liée à l'appli geotrek-admin ? Ou autre chose ?