Indexation spatiale sous PostGIS
Dans le cas d'une grosse base de données, les requêtes Sql peuvent être coûteuses en temps de calcul, a fortiori les requêtes spatiales qui utilisent la géométrie des objets.
Créer des index (spatiaux ou non) peut permettre d'améliorer les temps de traitement. Ce n'est cependant pas une recette miracle.
Dans un SGBD élaboré comme PostGIS le planificateur de requête choisit de façon adaptée d'utiliser ou non les index et l'index spatial (de type Gist que nous verrons plus tard) est primordial. La seule restriction d'utilisation est celle des tables avec de très gros objets en petit nombre (ex : tache urbaine départementale répartie en 10 périodes soit 10 enregistrements).
On trouvera quelques explications sur le principe de l'algorithme R-Tree utilisé par spatialite ici et sur les index Gist ici.
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.
Exemple :
explain analyse select * from d84_commune
renvoie
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
Voici quelques recommandations à 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 !