Recherche de points lente dans certains cas

[sletuffe]

origine de la décription du bug : https://www.refuges.info/forum/viewtopic.php?f=2&t=5222&p=18833
déplacé du forum vers ici car bien trop technique pour intéresser les autres :
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

[sletuffe]

Option 1 pour résoudre le problème :
Une requête encore plus énorme, et une construction de collection de point encore plus grosse

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

[sletuffe]

Option 2 vers laquelle je pense finalement me tourner vu que la requête de recherche de point est devenu vraiment trop difficile à maintenir, une nouvelle fonction (méthode ?) pour récupérer les polygones auquel un point appartient... et boucler.
A faire...