PostGIS › Optimisation

← Précédent ↑ Retour Suivant →

Objectifs du module

À retenir par coeur

A Index spatial

AA Rappel sur les index PostgreSQL

Tout comme le glossaire d’un livre permet de retrouver les pages contenant un mot donné (grâce à la liste alphabétique), un index de table de base de données permet de retrouver les enregistrements correspondant à une expression . Dans la forme la forme la plus fréquente :
SELECT * FROM table WHERE id = 42

L’opérateur = va tirer profit de la clef primaire id (une clef primaire déclare implicitement un index, seul moyen de garantir l’unicité).

Un index est lié à une table donnée. Il est exploité par les requêtes SELECT et mis à jour par les requêtes INSERT , UPDATE et DELETE . La conception d’une base ne peut pas se passer de la conception des index, en rapport avec les requêtes qu’on prévoit de faire dessus. Mais s’il faut prendre garde à ne pas oublier d’index, il faut aussi penser à leur coût. L’index ralentit l’écriture de la table alors qu’il en accélère la lecture.

Le type d’index le plus courant dans PostgreSQL est btree , l’arbre binaire en Français.

L’index btree fonctionne pour les valeurs triables, telles les textes, les nombres, les dates, etc.

Le type d’index GiST est en revanche une interface fournie par PostgreSQL pour permettre aux extensions et au langage PL/pgSQL de définir des types d’index spécifiques. C’est ainsi que PostGIS implémente son index spatial.

Exécuter la requête suivante :

SELECT * FROM "communes" WHERE insee = ’77083’

Pour connaître son coût et la stratégie employée par PostgreSQL pour l’exécuter, on utilise la requête EXPLAIN :

EXPLAIN SELECT * FROM "communes" WHERE insee = ’77083’

Qui devrait retourner :

"Seq Scan on communes  (cost=0.00..4299.06 rows=1 width=800)"
"  Filter: ((insee)::text = ’77083’::text)"

QUESTION AAA Quel est le coût minimal de la requête ? (il n’y a qu’à lire)
QUESTION AAB Son coût maximal ?
QUESTION AAC Requête SQL de création d’index
QUESTION AAD Résultat de la requête EXPLAIN
QUESTION AAE Coût minimal de la requête SELECT
QUESTION AAF Coût maximal de la requête SELECT
Nous pouvons en déduire que si la requête sans index peut dans de rares cas être plus rapide qu’en présence de l’index, ce dernier réduit considérablement le coût maximal.

Les requêtes EXPLAIN sont incontournables pour concevoir les bon index et optimiser les requêtes , vérifier quel index est utilisé, etc, en particulier pour les jointures.

AB Création d’un index GiST

Prendre le temps de lire cet article du tutoriel Introduction à PostGIS

Bien retenir que :

  • Les index spatiaux de PostGIS sont de type GiST
  • Ce sont les coordonnées de enveloppes (rectangles) qui sont indexées et non pas les géométries elles-mêmes
  • Certaines fonctions exploitent directement les index spatiaux, dont l’opérateur && , et certaines les exploitent pour un filtre préalable, comme la fonction ST_Intersects . D’autres fonctions ne les exploitent pas, comme ST_Disjoint .
SELECT nom FROM communes WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(3.11546, 45.78586), 4326), 0.4)
QUESTION ABA Durée de traitement moyen de la requête sur votre poste, en millisecondes
QUESTION ABB Coût maximal de la requête, d’après EXPLAIN

Changer le rayon 0.4 par 1 et exécuter

QUESTION ABC Coût maximal de la requête avec le rayon 1

À présent, créer un index spatial pour la table communes sur le champ geom et ré-exécuter les 2 requêtes précédentes.

QUESTION ABD SQL de création d’index
QUESTION ABE Durée de traitement moyen de la requête (SELECT ), en millisecondes
QUESTION ABF Coût maximal avec la condition ST_DWithin(geom, [...], 0.4)
QUESTION ABG Coût maximal avec la condition ST_DWithin(geom, [...], 1 )
QUESTION ABH À partir de quel rayon l’index n’est plus rentable ? (au dixième de degré près, d’après essais successifs avec et sans l’index)
Suivant → ← Précédent Retour