[transfert github] recherche de points lente dans certains cas

Problèmes, bugs et difficultés rencontrés sur le site.
Avatar du membre
sly
Messages : 4743
Enregistré le : 29 févr. 2004, 17:59
Localisation : Chambéry - Savoie

[transfert github] recherche de points lente dans certains cas

Message par sly »

ce qu'on peut voir : si on cherche par exemple "alpette" sans rien changer d'autre au formulaire
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 :
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
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.

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 
qui est executée, n'a aucune condition sur quels points on interroge, elle réalise donc une recherche sur tous les points_gps de notre base pour en trouver tous les polygones auxquels ils appartiennent, alors même que la requête complète ne cherche que "alpette"

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
Modifié en dernier par sly le 05 févr. 2015, 19:14, modifié 11 fois.
Avatar du membre
yip
Messages : 381
Enregistré le : 08 mars 2004, 23:32

Message par yip »

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.
Sur que chercher les polygones d'un point, c'est un calcul qui mets PG a rude epreuve ! Beaucoup plus que chercher les points d'un poly.
Merci pour l'explain, pas evident a analyser mais c'est interessant
qui est executée, n'a aucune condition sur quels points on interroge
Pluss qu'une subquery, c'est une table temporaire.
A ce niveau là, points non plus n'a pas de condition. idem polygones, points_gps. rien n'a de condition, et il n'y a pas de critères. ce sont des tables.
C'est seulement arrivé dans le WHERE que il se rends compte que le critère est "alpette".
C'est vrai que s'il y avait deja moyen de creer une table un peu plus petite ce serait ça de gagné, mais les champs utilisés sont "point_gps" et "poygone", autrement dit rien de critéris-able, pas le nom du point en tout cas. Une jointure de plus avec points me semble pas tres logique.

J'ai commencé par mettre la sous requete dans dans le SELECT, avec donc la condition intégrée. ca me semblait plus logique.
Ca rame beaucoup plus. de 30secondes à 3heures, sur la base de prod en plus :shock:
Il me semble avoir essayé aussi le JOIN et je crois que ça revenait au même. (un JOIN ON est equivalent a un FROM WHERE ?)

C'est surement du a l'indexation. en lançant la sous requete directement sur les 3000 points, il reponds quasi immediatement; surprenant ! alors qu'il doit se farcir 3000 Within !
Ainsi la sous requete n'est lancée qu'une seule fois.
Pour ça surement que c'est plus rapide de la mettre en table temporaire plutot qu'en clause SELECT.
PG doit etre mieux optimisé pour la creation d'une grosse table plutot qu'une myriade de conditions successive.
C'est le temps de creation de cette table temporaire qui m'a convaincu que c'etait la moins-mauvaise façon de faire.
J'ai rien trouvé de mieux. Si tu as d'autres pistes ?
Si on melange OSM_points avec nos pointsGPS, la oui il faudra surement externaliser le truc. La table va commencer a mettre du temps a se creer.
Même pas sur que ça aide si 100 requetes de 1 point sont plus longues que 1 requete de 10000 points ce que je commence a croire

La base test est indéxée ? C'est pas bien de la laisser avec ces 300 000 points, pour tester les problemes de charge.
Avatar du membre
sly
Messages : 4743
Enregistré le : 29 févr. 2004, 17:59
Localisation : Chambéry - Savoie

Message par sly »

yip a écrit : Merci pour l'explain, pas evident a analyser mais c'est interessant
Clair, pas évident, que ça soit en graphique avec couleur ou en brut sorti par PG, c'est dure de dire qu'est-ce qui ralenti ou pas.
Car PG ne semble pas savoir fournir un temps par potion de requête.

Donc, les deux colonnes que j'observe, sont rows (le nombre de ligne qu'il a dû parcourir ou créer en interne) et loop que je ne sais pas trop ce que c'est mais en général, loop, c'est pas bon ;-)

On y voit : "Subquery Scan on liste_polys" = 2507 lignes analysées (je ne sais pas trop pourquoi 2507, mais ça semble très proche du nombre de points dans points_gps
GroupAggregate, idem.

C'est ce qui va en direction de mes soupçon concernant la sous-requête qui s'execute avant que les conditions du point ne viennent la limiter.
Une jointure de plus avec points me semble pas tres logique.
Et pourtant, c'est la seule que je vois (sauf à faire 2 requêtes, celle des points d'abord, celle de leurs points_gps ensuite) car c'est dans celle-là que se trouve la condition hypre restrictive : "nom='%Alpette%'
C'est surement du a l'indexation. en lançant la sous requete directement sur les 3000 points, il reponds quasi immediatement; surprenant ! alors qu'il doit se farcir 3000 Within !
Il est impressionnant PostGIS hein ?
Mais la même sur la base "test" et là, ça devient tout de suite plus long.
J'ai rien trouvé de mieux. Si tu as d'autres pistes ?
Un join, faut que je tente de voir si c'est viable et réaliste.
La base test est indéxée ?
oui.
C'est pas bien de la laisser avec ces 300 000 points, pour tester les problemes de charge.
C'est "pas" bien ou c'est bien ?
L'avoir grosse comme ça, je trouve ça bien pour nous, pauvres humains qui ne somme pas capable de remarquer une requête à 300ms alors que quand ça atteint les ~10s ça se voit tout de suite plus.
Avatar du membre
yip
Messages : 381
Enregistré le : 08 mars 2004, 23:32

Message par yip »

oui, typo, c'est bien qu'elle soit Big la base de test
Avatar du membre
sly
Messages : 4743
Enregistré le : 29 févr. 2004, 17:59
Localisation : Chambéry - Savoie

Message par sly »

Pour info, car je n'ai pas oublié ce bug, voici un prototype de requête pour la résolution du problème :

Code : Tout sélectionner

SELECT pt.nom,polygones.nom_polygone,polygone_type.type_polygone FROM 
(
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
         
  FROM 
  points NATURAL JOIN points_gps NATURAL JOIN type_precision_gps NATURAL JOIN point_type  
  WHERE 
     1=1
     AND unaccent(points.nom) ILIKE unaccent('%alpet%')
 AND points.id_point_type IN (7,9,10) 

 AND modele!=1 
  
LIMIT 2
)
AS pt
JOIN polygones ON ST_Within(pt.geom, polygones.geom ) NATURAL JOIN polygone_type

order by pt.id_point,polygone_type.ordre_taille desc
Résultat :

Code : Tout sélectionner

nom	nom_polygone	type_polygone
Refuge de l'Alpette	Alpes Occidentales	zone
Refuge de l'Alpette	Alpes	zone
Refuge de l'Alpette	France métropolitaine	pays
Refuge de l'Alpette	Rhône-Alpes	région
Refuge de l'Alpette	Isère	département
Refuge de l'Alpette	Chartreuse	massif
Refuge de l'Alpette	IGN 3333OT - Massif de la Chartreuse Nord	carte
Refuge de l'Alpette	Réserve Naturelle des Hauts de Chartreuse	zone réglementée
Chalet de l'Alpette	Alpes	zone
Chalet de l'Alpette	Alpes Occidentales	zone
Chalet de l'Alpette	France métropolitaine	pays
Chalet de l'Alpette	Rhône-Alpes	région
Chalet de l'Alpette	Haute-Savoie	département
Chalet de l'Alpette	Bauges	massif
Chalet de l'Alpette	IGN 3432ET - Albertville	carte
Lancée sur la base test, elle prend 100 millisecondes, toutefois :
- ça n'effectue plus la conversion lignes-> colonnes, il faudra donc faire ça en php
- elle change pas mal de tête, il faut que j'arrive à faire le remplacement tout en préservant l'API
- ça manque évidement de tests, rien ne dit que ça n'oublie pas la moitié des trucs
Avatar du membre
sly
Messages : 4743
Enregistré le : 29 févr. 2004, 17:59
Localisation : Chambéry - Savoie

Re: [transfert github] recherche de points lente dans certains cas

Message par sly »

Répondre