[Synthese] Lenteur de la vue gn_synthese.v_area_taxon

[mvergez]

Salut !

L'Agence Régionale de la Biodiversité en île de France m'a fait remarquer de grosses lenteurs sur la vue gn_synthese.v_area_taxon qui met plus de 30 secondes à s'exécuter. Cette vue est utilisée par gn_synthese.v_color_taxon_area et notamment par la route api/synthese/color_taxon.

Comme cette route est utilisée par occtax-mobile, nous avons dû en urgence transformer cette vue en vue materialisée mais c'est juste une réparation temporaire. (PnX-SI/gn_mobile_occtax#232)

J'ai essayé de transformer la requête pour par exemple sélectionner toutes les mailles activées pour réduire le JOIN mais cela n'améliore les performances qu'à partir d'un grand nombre de données et que de quelques secondes. En rajoutant des index cela n'aide pas grandement non plus...

J'ai l'impression que c'est le JOIN sur cor_area_synthese qui prend beaucoup de temps mais également le GROUP BY qui semble faire qu'un LIMIT ne permet pas d'énormément réduire le temps de la requête...

La base contient 3 millions d'obs ce qui ne semble pas énorme donc peut-être que ça vient de la base. Pensez-vous qu'un Vaccum analyse permettrait de résoudre un peu le problème ?

Si vous avez d'autres solutions/idées je suis preneur !

Merci beaucoup !

---

L'analyse de postgres :

Proposition de requête mais les perfs sont pas nettement mieux :

WITH occtax_areas AS (
SELECT
	id_area
FROM
	ref_geo.l_areas la
JOIN ref_geo.bib_areas_types bat ON
	bat.id_type = la.id_type
WHERE
	la."enable" = TRUE
	AND bat.type_code = (
	SELECT
		tp.parameter_value
	FROM
		gn_commons.t_parameters tp
	WHERE
		tp.parameter_name = 'occtaxmobile_area_type'
	LIMIT 1))
SELECT
	s.cd_nom,
	c.id_area,
	count(s.id_synthese) AS nb_obs,
	max(s.date_min) AS last_date
FROM
	gn_synthese.synthese s
JOIN gn_synthese.cor_area_synthese c ON
	s.id_synthese = c.id_synthese
JOIN occtax_areas ON
	occtax_areas.id_area = c.id_area
GROUP BY
	c.id_area,
	s.cd_nom;

Avec un index sur le enable :

CREATE INDEX ON ref_geo.l_areas((1)) WHERE "enable";

[camillemonchicourt]

Au départ, c'était stocké dans une table alimentée par un trigger, mais c'était lourd donc on a souhaité basculer sur une vue matérialisée.
Après tests et pour éviter un décalage d'infos, on est partie sur une vue.
Voir #997
Aussi discuté sur #617

Il faut que le type de zonage utilisé soit adapté à la taille du territoire et au nombre de taxons pour que le volume de données à récupérer soit viable.
Il me semble d'ailleurs que par défaut, dans GeoNature, le paramètre occtaxmobile_area_type est sur les M5 (mailles de 5km), alors que dans Occtax-mobile le paramètre code_area_type est suggéré 'ou pas défaut ?) sur les M1 (mailles de 1 km).

Il y a peut-être un truc à remettre en cohérence.
Mais c'est sur que sur un grand territoire avec beaucoup de taxons, les mailles de 1km c'est bien trop fin comme info. Il faut passer sur les mailles 5, ou 10, voire d'autres zonages plus grands (plus grandes mailles, communes, départements...).

[mvergez]

Merci pour ton retour @camillemonchicourt

En effet, c'est un petit territoire (île de France) et le paramètre occtaxmobile_area_type est à M5 mais même à M10 la requête est lente (trentaine de secondes).

Donc je me pose toujours la question de la performance de cette requête ou de la base de données en question...