SQL Academy · Leçon

Index spatiaux (GiST)

Accélérez les requêtes de localisation.

Index spatiaux (GiST) est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Pourquoi les requêtes de localisation deviennent lentes

Imaginez une table contenant des millions d’emplacements de restaurants. Si vous demandez « Trouver tous les restaurants à moins de 5 km de ma position », la base de données doit parcourir chaque ligne pour calculer la distance. On parle de balayage séquentiel, et cette opération devient extrêmement lente à mesure que la table grandit.

Les index spatiaux résolvent ce problème en organisant les données géométriques dans une structure arborescente, ce qui permet à la base de données d’ignorer instantanément de grandes parties de la table.

Qu’est-ce qu’un index GiST ?

GiST signifie arbre de recherche généralisé. Il s’agit d’une structure d’index flexible intégrée à PostgreSQL qui prend en charge de nombreux types de données, notamment les formes géométriques et les géométries PostGIS.

Contrairement à un index en arbre B, qui fonctionne avec des valeurs pouvant être triées comme les entiers ou les chaînes de caractères, GiST peut indexer des données multidimensionnelles telles que les points, les polygones et les lignes. PostGIS utilise GiST en interne pour créer ses index spatiaux.

Créer un index spatial

La création d’un index GiST sur une colonne géométrique est simple. Utilisez CREATE INDEX avec la clause USING gist. Cette instruction unique peut faire passer une requête de plusieurs minutes à quelques millisecondes.

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Fonctionnement de GiST : les boîtes englobantes

Un index spatial GiST ne stocke pas les géométries exactes. Il stocke plutôt des boîtes englobantes, c’est-à-dire le plus petit rectangle qui englobe chaque géométrie. L’arbre est construit en regroupant les boîtes englobantes proches à chaque niveau.

Lorsqu’une requête est exécutée, PostgreSQL descend dans l’arbre et élimine les branches dont les boîtes englobantes ne chevauchent pas la zone recherchée. Seules les lignes candidates restantes sont ensuite vérifiées précisément. Cette approche en deux phases (exploration de l’index et nouvelle vérification) est extrêmement efficace.

Configuration d’une table d’exemple

Avant d’étudier le comportement des index, créons une table d’exemple contenant des points représentant des villes et remplissons-la avec quelques lignes. La colonne geom stocke chaque ville sous forme de point dans WGS 84 (SRID 4326).

CREATE TABLE cities (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  geom GEOMETRY(Point, 4326)
);

INSERT INTO cities (name, geom) VALUES
  ('Paris',    ST_SetSRID(ST_MakePoint(2.3522,  48.8566), 4326)),
  ('Berlin',   ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)),
  ('Madrid',   ST_SetSRID(ST_MakePoint(-3.7038, 40.4168), 4326)),
  ('Rome',     ST_SetSRID(ST_MakePoint(12.4964, 41.9028), 4326)),
  ('Warsaw',   ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326));

Ajouter l’index GiST

Une fois la table remplie, ajoutez un index GiST sur la colonne geom. Pour les tables de production contenant des millions de lignes, cette instruction peut prendre quelques minutes, mais elle ne doit être exécutée qu’une seule fois. Ensuite, toutes les requêtes spatiales visant cette colonne en bénéficient automatiquement.

CREATE INDEX idx_cities_geom
  ON cities
  USING gist (geom);

-- Verify the index exists
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'cities';

Opérateur de boîte englobante &&

PostGIS fournit l’opérateur && pour vérifier si deux boîtes englobantes se chevauchent. Cet opérateur tient compte de l’index : le planificateur utilise automatiquement l’index GiST. Il est beaucoup plus rapide que le calcul d’intersections géométriques exactes et sert souvent de préfiltre rapide.

-- Find cities whose bounding box overlaps a search rectangle
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Recherche du plus proche voisin avec <->

L’opérateur <-> renvoie la distance entre deux géométries et bénéficie lui aussi de l’accélération de GiST. En le combinant avec ORDER BY ... LIMIT, vous obtenez une requête des K plus proches voisins (KNN) extrêmement rapide, sans balayage complet de la table.

-- Find the 3 cities closest to a reference point (Brussels)
SELECT name,
       ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)::geography
       ) / 1000 AS distance_km
FROM   cities
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)
LIMIT  3;

Vérifier l’utilisation de l’index avec EXPLAIN

Utilisez toujours EXPLAIN ou EXPLAIN ANALYZE pour confirmer que le planificateur utilise réellement votre index. Recherchez un balayage d’index bitmap ou un balayage d’index utilisant idx_cities_geom dans la sortie. Si vous voyez plutôt un balayage séquentiel, la table est peut-être trop petite pour que le planificateur préfère l’index.

EXPLAIN
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Création simultanée d’un index

La création d’un grand index spatial avec la commande standard CREATE INDEX verrouille la table pour les écritures. En production, utilisez CREATE INDEX CONCURRENTLY pour créer l’index sans bloquer les insertions ni les mises à jour. La contrepartie est que l’opération prend plus de temps et ne peut pas être exécutée dans un bloc de transaction.

-- Safe for production tables (no write lock)
CREATE INDEX CONCURRENTLY idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Maintenir votre index spatial

Au fil du temps, un grand nombre d’insertions, de mises à jour et de suppressions peut provoquer un gonflement de l’index : celui-ci devient fragmenté et moins efficace. Utilisez REINDEX pour le reconstruire proprement, ou planifiez régulièrement l’exécution de VACUUM ANALYZE afin de mettre à jour les statistiques et de permettre au planificateur de prendre de meilleures décisions.

-- Rebuild the index to remove bloat
REINDEX INDEX idx_cities_geom;

-- Update planner statistics for the table
ANALYZE cities;

Vérification rapide : index GiST

Vérifiez votre compréhension des index spatiaux avec GiST dans PostGIS.

Récapitulatif : index spatiaux avec GiST

Dans cette leçon, vous avez appris pourquoi les index spatiaux sont essentiels pour obtenir de bonnes performances avec les requêtes de localisation, et comment GiST les rend possibles dans PostgreSQL et PostGIS.

Points essentiels à retenir :

GiST (arbre de recherche généralisé) est un type d’index flexible qui prend en charge les données géométriques multidimensionnelles.
Créez un index spatial avec CREATE INDEX ... USING gist (geom).
GiST stocke des boîtes englobantes et élimine des branches de l’arbre de recherche, évitant ainsi les balayages complets de la table.
L’opérateur && (chevauchement de boîtes englobantes) et l’opérateur <-> (distance/KNN) bénéficient tous deux de l’accélération de GiST.
Utilisez EXPLAIN pour vérifier l’utilisation de l’index et CREATE INDEX CONCURRENTLY en production pour éviter les verrous d’écriture.
Entretenez les index avec REINDEX et ANALYZE afin de conserver des requêtes rapides au fil du temps.

Questions Fréquemment Posées

La leçon « Index spatiaux (GiST) » est-elle gratuite ?

Oui — le texte complet de « Index spatiaux (GiST) » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Index spatiaux (GiST) » ?

Accélérez les requêtes de localisation. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Index spatiaux (GiST) » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à SQL Academy