C'est plus visible ici http://sly.refuges.info/point_formulaire_recherche.php que là http://www.refuges.info/point_formulaire_recherche.php
La recherche prend environ 3 secondes.
C'est pas dramatique car ça marche plus vite sur www, mais ça cache sans doute un problème latent.
= technique =
J'ai analysé la situation pour retrouver le même type de problème qu'avec l'export des points OSM : les sub-query, c'est à manipuler avec précaution.
Voici la requête en question :
Quand la table des points_gps n'en contient que ~2000 le temps tombe vers 200ms/300ms, mais sur la base test qui en contient bien plus ça passe à 6 secondes.SELECT points.*,
points_gps.*,
type_precision_gps.*,
point_type.*,
ST_X(points_gps.geom) as longitude,ST_Y(points_gps.geom) as latitude,
extract('epoch' from date_derniere_modification) as date_modif_timestamp,
extract('epoch' from date_creation) as date_creation_timestamp
,polygones.site_web,polygones.url_exterieure,polygones.message_information_polygone,polygones.source,polygones.nom_polygone,polygones.article_partitif,polygones.id_polygone_type,polygones.id_polygone,liste_polys.liste_polygones
FROM points,point_type,type_precision_gps,points_gps LEFT JOIN polygones ON (ST_Within(points_gps.geom, polygones.geom ) and id_polygone_type=1),(
SELECT
pgps.id_point_gps,
STRING_AGG(pg.id_polygone::text,',' ORDER BY pty.ordre_taille DESC) AS liste_polygones
FROM
polygones pg NATURAL JOIN polygone_type pty,
points_gps pgps
WHERE
ST_Within(pgps.geom, pg.geom)
AND
pty.categorie_polygone_type='montagnarde'
GROUP BY pgps.id_point_gps
) As liste_polys
WHERE
points.id_point_type=point_type.id_point_type
AND points_gps.id_point_gps=points.id_point_gps
AND points_gps.id_type_precision_gps=type_precision_gps.id_type_precision_gps
AND points.nom ILIKE '%alpette%'
AND liste_polys.id_point_gps=points_gps.id_point_gps
AND points.id_point_type IN (7,9,10)
AND modele!=1
AND points.ferme=''
AND (points.id_point_type!=26)
ORDER BY liste_polygones
LIMIT 40
1) la base "test" révèle ce problème latent car sa table points_gps contient toujours une grande quantité de points (ceux des polygones étant toujours là) ce qui est d'ailleurs très bien comme ça, ça m'a permis de repéré le problème ;-)
2) la requête qui consiste à passer d'un mode "ligne à un mode colonne" est une sous requête qui présente un problème de performance, qui est gommé par la petite taille de notre base, mais qui pourrait devenir problématique si on choisi par exemple de mettre les coordonnées des points osm dans points_gps
Ce petit outil en ligne, qui donne une vue un peu plus graphique d'un "explain analyse" sur la requête précédente, donne un peu d'info, grâce à la colonne "rows" par exemple de ce qui se passe :
http://explain.depesz.com/s/ziKn
Mon analyse est que la sous requête
Code : Tout sélectionner
SELECT
pgps.id_point_gps,
STRING_AGG(pg.id_polygone::text,',' ORDER BY pty.ordre_taille DESC) AS liste_polygones
FROM
polygones pg NATURAL JOIN polygone_type pty,
points_gps pgps
WHERE
ST_Within(pgps.geom, pg.geom)
AND
pty.categorie_polygone_type='montagnarde'
GROUP BY pgps.id_point_gps
La solution n'est pour autant pas simple à trouver. Soit on part sur 2 requêtes (je ne suis même pas sûr que ça puisse régler le problème) soit on fait le JOIN de la mort qui tue.
Le bonheur de l'optimisation postgresql
Note: on pourrait considérer ce problème comme de peu d'importance vu que ça marche avec 2000 points (la puissance du serveur compensant la moins bonne requête) seulement cette lenteur pourrait géner 2 possibles évolutions :
- celle de garder toutes les anciennes version des fiches comme point dans la table
- celle d'augmenter le nombre de polygones (communes, me voilà !)
- celle d'ajouter en provenance d'openstreetmap des hôtels, chambre d'hôte et autres points que nous ne faisons pas, mais qui pourraient faire un bon complément à nos cartes et recherches
