Les bases de données spatiales peuvent contenir des millions d’objets géographiques. Dans certains cas, une requête PostGIS peut prendre plusieurs secondes, voire plusieurs minutes.
Ce problème apparaît souvent lorsque :
- les index spatiaux ne sont pas utilisés
- les géométries sont complexes
- les requêtes spatiales sont mal construites
- ou les statistiques de la base ne sont pas à jour.
Dans cet article, nous allons voir les étapes classiques pour diagnostiquer et optimiser une requête PostGIS lente.
1. Commencer par analyser la requête
La première étape consiste à comprendre comment PostgreSQL exécute la requête.
Pour cela on utilise :
EXPLAIN ANALYZE
Exemple :
EXPLAIN ANALYZE
SELECT *
FROM routes r
JOIN communes c
ON ST_Intersects(r.geom, c.geom);
Cette commande permet de voir :
- si un index spatial est utilisé
- combien de lignes sont analysées
- combien de temps prend chaque étape.
Si vous voyez :
Seq Scan
cela signifie que PostgreSQL parcourt toute la table.
2. Vérifier la présence d’index spatiaux
Les index sont essentiels pour les requêtes spatiales.
Un index spatial se crée généralement ainsi :
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Les index permettent à PostgreSQL de réduire fortement le nombre de géométries à analyser.
Sans index spatial, les performances peuvent être catastrophiques.
3. Utiliser les opérateurs spatiaux indexés
Certaines fonctions PostGIS utilisent automatiquement les index.
Par exemple :
ST_IntersectsST_WithinST_Contains
Mais il est souvent utile d’ajouter un filtre par bounding box :
geom && other_geom
Exemple :
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
Le test de bounding box est beaucoup plus rapide.
4. Vérifier la complexité des géométries
Les géométries très détaillées peuvent ralentir fortement certaines opérations.
Dans certains cas, il peut être utile de simplifier les géométries :
ST_Simplify(geom, tolerance)
ou de créer une table simplifiée pour certaines analyses.
5. Éviter certaines fonctions dans les clauses WHERE
Certaines fonctions empêchent l’utilisation des index.
Par exemple :
ST_Buffer(geom, 100)
dans une clause WHERE peut ralentir fortement la requête.
Mauvais exemple :
WHERE ST_Intersects(ST_Buffer(geom,100), zone)
Il est souvent préférable de reformuler la requête.
6. Vérifier les statistiques de la base
PostgreSQL utilise des statistiques pour choisir le plan d’exécution.
Après de nombreuses modifications de données, il est recommandé d’exécuter :
ANALYZE
ou
VACUUM ANALYZE
Cela permet d’améliorer le plan de requête.
7. Limiter les données analysées
Il est souvent utile de réduire le volume de données avant les opérations spatiales.
Par exemple avec un filtre attributaire :
WHERE type = 'autoroute'
Cela permet de réduire le nombre d’objets testés.
8. Tester différentes approches
Dans certains cas, plusieurs formulations de requêtes peuvent donner des performances très différentes.
Il est donc utile de tester différentes stratégies :
- jointure spatiale
- sous-requête
- matérialisation intermédiaire.
9. Les 5 erreurs PostGIS les plus fréquentes qui ralentissent une requête
Certaines erreurs reviennent très souvent dans les bases de données spatiales.
Absence d’index spatial
C’est la cause la plus fréquente.
Sans index spatial, PostgreSQL doit analyser toutes les géométries de la table.
Créer un index :
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Mauvais ordre des opérations
Certaines requêtes exécutent directement une opération spatiale coûteuse sur toute la table.
Exemple inefficace :
SELECT *
FROM batiments
WHERE ST_Intersects(geom, ST_Buffer(:zone,100));
Dans ce cas, la fonction ST_Buffer est recalculée pour chaque ligne.
Fonction spatiale utilisée sans filtre spatial
Une jointure spatiale peut être très lente si l’on n’utilise pas de filtre de bounding box.
Exemple recommandé :
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
Le test && utilise l’index spatial.
Géométries trop complexes
Certaines géométries contiennent des milliers de sommets.
Dans certains cas il peut être utile d’utiliser :
ST_Simplify(geom, tolerance)
ou de stocker une version simplifiée.
Statistiques PostgreSQL obsolètes
Si la table a été fortement modifiée, PostgreSQL peut choisir un mauvais plan d’exécution.
Mettre à jour les statistiques :
VACUUM ANALYZE
Exemple réel d’optimisation
Supposons une requête qui cherche les routes dans une commune.
Requête initiale :
SELECT r.*
FROM routes r, communes c
WHERE ST_Intersects(r.geom, c.geom)
AND c.nom = 'Paris';
Temps d’exécution : 45 secondes
Version optimisée
SELECT r.*
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom)
WHERE c.nom = 'Paris';
Avec :
- index spatial
- filtre bounding box
Temps d’exécution : moins d’une seconde
Checklist rapide pour diagnostiquer une requête PostGIS lente
Lorsqu’une requête prend trop de temps, vérifiez :
✔ la requête avec EXPLAIN ANALYZE
✔ la présence d’un index spatial
✔ l’utilisation des opérateurs compatibles avec les index
✔ la complexité des géométries
✔ la présence d’un filtre attributaire
✔ les statistiques PostgreSQL (ANALYZE)
Conclusion
Lorsqu’une requête PostGIS est lente, il est important de suivre une démarche progressive :
- analyser la requête avec
EXPLAIN ANALYZE - vérifier les index spatiaux
- utiliser les opérateurs compatibles avec les index
- simplifier les géométries si nécessaire
- maintenir les statistiques de la base.
Avec ces quelques étapes, il est souvent possible de réduire le temps d’exécution de plusieurs minutes à quelques secondes.
Dans la majorité des cas, une requête PostGIS lente est liée à un index manquant ou à un mauvais plan d’exécution. Une analyse méthodique avec EXPLAIN ANALYZE permet presque toujours d’identifier le problème.
