Indexation spatiale sous PostGIS
Comme indiqué précédemment, l'utilisation d'index est une façon habituelle d'améliorer les performances d'une base de données.
L'indexation accélère les recherches en organisant les données dans des arbres de recherche qui peuvent être parcourus efficacement pour retrouver une entité particulière.
Mais les index ajoutent aussi une surcharge au système de base de données dans son ensemble, si bien qu'ils doivent être utilisés avec discernement.
L'indexation spatiale sous PostGIS utilise l'index GiST (Generalized Search Tree).
Lorsque une table de PostGIS est chargée avec l'extension pgShapeLoader, celui-ci crée automatiquement un index spatial appelé table_the_geom_gist.
Il est possible de supprimer cet index.
DROP INDEX table_the_geom_gist
Si l'index n'existe pas il est possible de le créer par la commande suivante :
CREATE INDEX table_the_geom_gist ON table USING GIST (the_geom)
Plus la table est grande, plus le temps d'exécution d'une requête utilisant les index diminue par rapport à la même requête exécutée sur une table sans index spatial.
L'optimiseur de requêtes sous PostGIS
PostGIS possède un planificateur/optimiseur de requête qui utilise une fonction d'estimation des coûts des différentes stratégies afin de trouver le chemin le moins coûteux et établir un plan d'exécution optimal.
On trouvera des détails sur Présentation des mécanismes internes de PostgreSQL.
Il est recommandé à l'administrateur de la base de lancer une commande d'analyse et de nettoyage régulièrement afin de maintenir les statistiques utiles à la fonction d'estimation des coûts et de récupération de l'espace libre, suite aux mises à jour. Ceci peut se faire manuellement par une commande VACUUM ANALYSE, et/ou être activé automatiquement par le processus autovacuum.
Plus généralement il est nécessaire de mettre en place des procédures de maintenance, mais ceci est en dehors du périmètre de cette formation.
En ce qui concerne les relations et fonctions spatiales, l'optimisation passe par l'utilisation des index Gist et les opérateurs spatiaux utilisant les rectangles d'encombrement (bounding box) des objets, afin de n'exécuter les fonctions utilisant la géométrie exacte que sur un sous-ensemble d'objets des tables initiales.
Depuis la version 1.3.0 de PostGIS il n'est plus nécessaire d'utiliser explicitement les opérateurs utilisant les rectangles d'encombrements (contrairement à SpatiaLite) à l'exception notable des fonctions ST_Disjoint et ST_Relate.
Un ordre SQL de type EXPLAIN ANALYZE suivi de la requête permet de récupérer la stratégie retenue par l'optimiseur.
Ex : explain analyse select * from d84_commune
renvoi
QUERY PLAN
Seq Scan on d84_commune (cost=0.00..7.51 rows=151 width=4306) (actual time=0.038..0.283 rows=151 loops=1)
QUERY PLAN
Total runtime: 0.421 ms
Ce qui indique un balayage séquentiel de la table d84_commune qui sera exécuté en 0.421 ms
On trouvera ici quelques recommandations pour lesquelles on pourra retenir :
mettre les conditions (where) les plus "rapides" en premier: l'analyseur de requêtes se charge lui-même d'adopter la stratégie d'exécution de la requête théoriquement la plus rapide. Il détermine donc l'ordre d'exécution des conditions. Dans certains cas où l'analyseur n'a pas assez d'éléments pour choisir, mettre en premier la condition la plus "rapide" ou la plus discriminante permet d'optimiser les requêtes.
penser à utiliser les sous-requêtes : dans certains cas, l'utilisation de sous requêtes est plus efficace que les multiples conditions et jointures. Il faut donc penser à tester l'utilisation de sous-requêtes, d'autant plus qu'elles permettent de décomposer un problème complexe en plusieurs problèmes plus simples. L'utilisation de sous-requête peut cependant paraître complexe lorsqu'on débute en SQL... n'hésitez pas à vous faire aider pour mettre au point une requête devant traiter de grosses tables et faire des essais préalables sur des extraits !
Ce site donne également quelques explications sur le fonctionnement de l'optimiseur (planner).
