🔝 Retour au Sommaire

18.2.2. Index Spatiaux : GiST et SP-GiST

Introduction

Imaginez que vous devez trouver tous les restaurants dans un rayon de 1 km autour de votre position parmi des millions d'établissements. Sans index spatial, PostgreSQL devrait calculer la distance entre votre position et chaque restaurant de la base. C'est extrêmement lent !

Les index spatiaux sont la solution : ils organisent les données géographiques de manière intelligente pour accélérer drastiquement les requêtes spatiales. Ils peuvent transformer une requête de plusieurs minutes en quelques millisecondes.

Dans ce chapitre, nous allons découvrir les deux principaux types d'index spatiaux utilisés avec PostGIS :

GiST (Generalized Search Tree) : L'index spatial par excellence
SP-GiST (Space-Partitioned GiST) : Pour des structures spatiales spécifiques

1. Pourquoi Avons-nous Besoin d'Index Spatiaux ?

Le Problème Sans Index

Considérons une table avec 1 million de restaurants :

-- Sans index : SCAN SÉQUENTIEL (très lent)
SELECT nom, adresse  
FROM restaurants  
WHERE ST_DWithin(  
    position::geography,
    ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
    1000  -- 1 km
);
-- Temps d'exécution : 30-60 secondes (scan de TOUTE la table)

PostgreSQL doit :

Parcourir TOUS les restaurants un par un
Calculer la distance pour CHACUN
Filtrer ceux qui sont à moins de 1 km

Coût : O(n) où n = nombre total de restaurants

La Solution : Index Spatial

Avec un index spatial :

-- Créer l'index
CREATE INDEX idx_restaurants_position  
ON restaurants  
USING GIST(position);  

-- Même requête : utilise l'INDEX (ultra rapide)
SELECT nom, adresse  
FROM restaurants  
WHERE ST_DWithin(  
    position::geography,
    ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
    1000
);
-- Temps d'exécution : 5-50 millisecondes (scan d'une fraction de la table)

Coût : O(log n) - gain de performance de 100× à 1000×

2. L'Index GiST (Generalized Search Tree)

Qu'est-ce que GiST ?

GiST (Generalized Search Tree) est une structure d'indexation générique et extensible. Dans le contexte de PostGIS, il est utilisé pour organiser efficacement les données spatiales.

Caractéristiques principales :

Structure d'arbre équilibré
Basé sur les boîtes englobantes (bounding boxes)
Supporte toutes les géométries PostGIS (Point, LineString, Polygon, etc.)
Polyvalent et performant pour la plupart des cas d'usage

Comment Fonctionne GiST ?

GiST organise l'espace géographique de manière hiérarchique en utilisant des rectangles englobants (bounding boxes ou MBR - Minimum Bounding Rectangle).

Principe de la Boîte Englobante

Chaque géométrie est entourée d'un rectangle qui la contient complètement :

Point :                 LineString :            Polygon :

    ┌─────┐               ┌──────────┐           ┌──────────┐
    │  •  │               │    /─\   │           │  ┌────┐  │
    └─────┘               │   /   \  │           │  │    │  │
  Bounding Box            │  /     \ │           │  └────┘  │
                          └─────────┘            └──────────┘
                         Bounding Box           Bounding Box

Structure Hiérarchique (Arbre)

GiST organise ces boîtes en arbre :

                        Niveau 0 (Racine)
                     ┌──────────────────┐
                     │   France entière │
                     └──────────────────┘
                              │
              ┌───────────────┼───────────────┐
              │               │               │
         Niveau 1        Niveau 1        Niveau 1
      ┌──────────┐     ┌──────────┐     ┌──────────┐
      │ Île-de-  │     │  Rhône-  │     │ Provence │
      │  France  │     │  Alpes   │     │   PACA   │
      └──────────┘     └──────────┘     └──────────┘
           │                │                 │
      ┌────┼────┐      ┌────┼────┐      ┌────┼────┐
      │    │    │      │    │    │      │    │    │
   Paris Lyon Rouen Lyon  Grenoble  Marseille Nice Toulon
   [•] [•] [•]   [•]    [•]        [•]      [•]  [•]

   Niveau 2 (Feuilles) : Géométries réelles

Recherche avec GiST

Lorsque vous cherchez des points dans une zone :

Niveau racine : Vérifier quelles régions intersectent la zone de recherche
Descendre uniquement dans les branches pertinentes
Niveau feuille : Vérifier les géométries réelles

Exemple : Chercher des restaurants près de Paris

❌ Ignore les branches "Rhône-Alpes" et "PACA"
✅ Descend uniquement dans "Île-de-France" → Paris
Résultat : 99% des données ignorées sans calcul !

Créer un Index GiST

Syntaxe de Base

-- Index GiST sur une colonne de géométrie
CREATE INDEX nom_index  
ON nom_table  
USING GIST(colonne_geometrie);

Exemples Pratiques

-- 1. Index sur une table de points (restaurants)
CREATE INDEX idx_restaurants_position  
ON restaurants  
USING GIST(position);  

-- 2. Index sur une table de polygones (arrondissements)
CREATE INDEX idx_arrondissements_zone  
ON arrondissements  
USING GIST(zone);  

-- 3. Index sur une table de lignes (routes)
CREATE INDEX idx_routes_trace  
ON routes  
USING GIST(trace);  

-- 4. Index avec FILLFACTOR personnalisé (pour données évolutives)
CREATE INDEX idx_lieux_position  
ON lieux  
USING GIST(position)  
WITH (fillfactor = 90);  
-- fillfactor = 90 laisse 10% d'espace libre pour les futures insertions

Opérations Accélérées par GiST

L'index GiST améliore la performance des opérateurs spatiaux suivants :

Opérateur/Fonction	Description	Exemple
`&&`	Boîtes englobantes se chevauchent	`geom1 && geom2`
`ST_Intersects()`	Géométries se croisent	`ST_Intersects(a, b)`
`ST_Contains()`	Une géométrie en contient une autre	`ST_Contains(polygon, point)`
`ST_Within()`	Une géométrie est dans une autre	`ST_Within(point, polygon)`
`ST_DWithin()`	Distance inférieure à	`ST_DWithin(a, b, 1000)`
`ST_Covers()`	Une géométrie couvre une autre	`ST_Covers(a, b)`
`ST_CoveredBy()`	Une géométrie est couverte par	`ST_CoveredBy(a, b)`
`ST_Overlaps()`	Géométries se chevauchent	`ST_Overlaps(a, b)`

Exemple : Requête Optimisée

-- SANS index : scan séquentiel
EXPLAIN ANALYZE  
SELECT r.nom  
FROM restaurants r  
JOIN arrondissements a ON ST_Contains(a.zone, r.position)  
WHERE a.nom = '5e arrondissement';  

-- Plan d'exécution SANS index :
-- Seq Scan on restaurants (cost=0..10000 rows=50000)
-- Temps : 250ms

-- AVEC index : scan d'index
EXPLAIN ANALYZE  
SELECT r.nom  
FROM restaurants r  
JOIN arrondissements a ON ST_Contains(a.zone, r.position)  
WHERE a.nom = '5e arrondissement';  

-- Plan d'exécution AVEC index :
-- Index Scan using idx_restaurants_position (cost=0..150 rows=50)
-- Temps : 5ms
-- ⚡ Amélioration : 50× plus rapide !

Maintenance des Index GiST

Surveillance de la Taille

-- Taille des index GiST
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille
FROM pg_stat_user_indexes  
WHERE indexrelname LIKE 'idx_%position%'  
ORDER BY pg_relation_size(indexrelid) DESC;

Statistiques d'Utilisation

-- Statistiques d'utilisation des index
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS nombre_scans,
    idx_tup_read AS tuples_lus,
    idx_tup_fetch AS tuples_recuperes
FROM pg_stat_user_indexes  
WHERE indexrelname LIKE 'idx_%position%'  
ORDER BY idx_scan DESC;

Reconstruction (REINDEX)

Parfois, un index devient "gonflé" (bloat) après de nombreuses modifications. Il faut le reconstruire :

-- Reconstruire un index spécifique
REINDEX INDEX idx_restaurants_position;

-- Reconstruire tous les index d'une table
REINDEX TABLE restaurants;

-- Version non bloquante (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_restaurants_position;

Quand reconstruire ?

Après des modifications massives (millions d'UPDATE/DELETE)
Si les performances se dégradent
En cas de bloat important (>50% de l'espace est inutilisé)

3. L'Index SP-GiST (Space-Partitioned GiST)

Qu'est-ce que SP-GiST ?

SP-GiST (Space-Partitioned GiST) est une variante de GiST qui utilise un partitionnement de l'espace plutôt que des boîtes englobantes.

Différences avec GiST :

Aspect	GiST	SP-GiST
Partitionnement	Boîtes englobantes qui se chevauchent	Partitions qui ne se chevauchent PAS
Structure	Arbre équilibré	Arbre potentiellement non équilibré
Cas d'usage	Général (toutes géométries)	Spécialisé (points, quadtrees)
Performance Points	Bonne	Excellente
Performance Polygones	Excellente	Moins bonne

Comment Fonctionne SP-GiST ?

SP-GiST utilise un partitionnement récursif de l'espace, typiquement avec une structure Quadtree (arbre quaternaire).

Principe du Quadtree

L'espace est divisé récursivement en 4 quadrants :

Niveau 0 : Espace initial
┌─────────────────┐
│                 │
│                 │
│        •        │  ← Point à indexer
│                 │
│                 │
└─────────────────┘

Niveau 1 : Division en 4 quadrants
┌────────┬────────┐
│   NW   │   NE   │
│        │        │
├────────┼────────┤
│   SW   │•  SE   │  ← Point dans quadrant SE
│        │        │
└────────┴────────┘

Niveau 2 : Subdivision du quadrant SE
┌────────┬────────┐
│   NW   │   NE   │
│        │        │
├────────┼───┬─┬──┤
│   SW   │NW │•│NE│  ← Point dans SE.NE.SW
│        ├───┼─┼──┤
│        │SW │ │SE│
└────────┴───┴─┴──┘

Avantages du Quadtree :

Partitions disjointes (pas de chevauchement)
Excellent pour les requêtes de points
Bon pour les données très denses dans certaines zones

Créer un Index SP-GiST

Syntaxe de Base

-- Index SP-GiST sur une colonne de géométrie
CREATE INDEX nom_index  
ON nom_table  
USING SPGIST(colonne_geometrie);

Exemples Pratiques

-- 1. Index SP-GiST sur des points (capteurs IoT)
CREATE INDEX idx_capteurs_position  
ON capteurs_iot  
USING SPGIST(position);  

-- 2. Index SP-GiST sur des points GPS
CREATE INDEX idx_traces_gps_point  
ON traces_gps  
USING SPGIST(point_gps);  

-- 3. Index SP-GiST avec stratégie Quadtree explicite
CREATE INDEX idx_marqueurs_position  
ON marqueurs  
USING SPGIST(position quad_point_ops);  
-- quad_point_ops = stratégie quadtree pour les points 2D

Quand Utiliser SP-GiST vs GiST ?

Utiliser SP-GiST pour :

✅ Données exclusivement de type Point

-- Table de capteurs (uniquement des points)
CREATE TABLE capteurs (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    position GEOMETRY(Point, 4326)
);

-- SP-GiST optimal pour cette table
CREATE INDEX idx_capteurs_position  
ON capteurs  
USING SPGIST(position);

✅ Requêtes de type "point dans une zone"

-- Trouver tous les capteurs dans un rectangle
SELECT * FROM capteurs  
WHERE position && ST_MakeEnvelope(2.3, 48.8, 2.4, 48.9, 4326);  
-- SP-GiST excellent pour ce type de requête

✅ Données avec distribution spatiale inégale

Beaucoup de points concentrés dans certaines zones
Peu de points ailleurs
SP-GiST s'adapte mieux à cette hétérogénéité

Utiliser GiST pour :

✅ Géométries mixtes ou complexes

-- Table avec des polygones
CREATE TABLE zones (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    geometrie GEOMETRY(Polygon, 4326)  -- Polygones
);

-- GiST préférable pour les polygones
CREATE INDEX idx_zones_geometrie  
ON zones  
USING GIST(geometrie);

✅ Requêtes complexes (intersections, contenance)

-- Trouver les zones qui intersectent un polygone
SELECT * FROM zones  
WHERE ST_Intersects(geometrie, autre_polygone);  
-- GiST plus efficace pour ces opérations

✅ Cas général (par défaut)

Si vous hésitez, choisissez GiST
C'est l'index "par défaut" et le plus polyvalent

Comparaison de Performance

Benchmark : Table de 1 Million de Points

-- Créer une table de test avec 1M de points
CREATE TABLE points_test (
    id SERIAL PRIMARY KEY,
    position GEOMETRY(Point, 4326)
);

-- Insérer 1 million de points aléatoires en France
INSERT INTO points_test (position)  
SELECT ST_SetSRID(  
    ST_MakePoint(
        -5 + random() * 15,  -- Longitude : -5 à 10
        41 + random() * 10   -- Latitude : 41 à 51
    ),
    4326
)
FROM generate_series(1, 1000000);

-- Test 1 : Index GiST
CREATE INDEX idx_gist ON points_test USING GIST(position);  
ANALYZE points_test;  

EXPLAIN ANALYZE  
SELECT COUNT(*) FROM points_test  
WHERE ST_DWithin(  
    position::geography,
    ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
    5000
);
-- Résultat GiST : ~15ms, 1234 lignes scannées

-- Test 2 : Index SP-GiST
DROP INDEX idx_gist;  
CREATE INDEX idx_spgist ON points_test USING SPGIST(position);  
ANALYZE points_test;  

EXPLAIN ANALYZE  
SELECT COUNT(*) FROM points_test  
WHERE ST_DWithin(  
    position::geography,
    ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
    5000
);
-- Résultat SP-GiST : ~12ms, 1198 lignes scannées
-- ⚡ SP-GiST légèrement plus rapide pour cette requête sur points

Résultats typiques :

Points uniquement : SP-GiST 10-20% plus rapide
Géométries mixtes : GiST 20-50% plus rapide
Insertion/Mise à jour : Performance comparable

4. Optimisations Avancées

Index sur Sous-Ensemble (Index Partiel)

Créer un index uniquement sur une partie des données :

-- Index uniquement sur les restaurants actifs
CREATE INDEX idx_restaurants_actifs_position  
ON restaurants  
USING GIST(position)  
WHERE actif = true;  

-- Avantages : index plus petit, plus rapide, moins de maintenance

Index avec INCLUDE (Covering Index)

Ajouter des colonnes supplémentaires à l'index pour éviter d'accéder à la table :

-- Index incluant le nom du restaurant (PostgreSQL 11+)
CREATE INDEX idx_restaurants_position_covering  
ON restaurants  
USING GIST(position)  
INCLUDE (nom, adresse);  

-- La requête peut tout récupérer depuis l'index (plus rapide)
SELECT nom, adresse  
FROM restaurants  
WHERE ST_DWithin(position::geography, point_ref::geography, 1000);

Index Multi-Colonnes (Rare avec Spatial)

Combiner une colonne spatiale avec une autre colonne :

-- Index combiné (spatial + attribut)
-- ATTENTION : rarement utile en pratique avec spatial
CREATE INDEX idx_restaurants_position_type  
ON restaurants  
USING GIST(position, type);

Tuning du FILLFACTOR

Le paramètre fillfactor contrôle le taux de remplissage des pages d'index :

-- fillfactor = 100 (défaut GiST) : pages pleines
-- Bon pour : données statiques ou en lecture seule
CREATE INDEX idx_static  
ON table_statique  
USING GIST(geom)  
WITH (fillfactor = 100);  

-- fillfactor = 90 : laisse 10% d'espace libre
-- Bon pour : données fréquemment mises à jour
CREATE INDEX idx_dynamic  
ON table_dynamique  
USING GIST(geom)  
WITH (fillfactor = 90);  

-- fillfactor = 70 : laisse 30% d'espace libre
-- Bon pour : insertions massives fréquentes
CREATE INDEX idx_high_write  
ON table_insertion_massive  
USING GIST(geom)  
WITH (fillfactor = 70);

5. Vérifier l'Utilisation des Index

Avec EXPLAIN ANALYZE

-- Vérifier si l'index est utilisé
EXPLAIN (ANALYZE, BUFFERS)  
SELECT nom  
FROM restaurants  
WHERE ST_DWithin(  
    position::geography,
    ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
    1000
);

-- Sortie attendue avec index :
-- Index Scan using idx_restaurants_position on restaurants
--   Index Cond: (position && '...'::geometry)
--   Filter: ST_DWithin(...)
--   Buffers: shared hit=45
-- Planning Time: 0.123 ms
-- Execution Time: 3.456 ms

-- Sortie SANS index (mauvais) :
-- Seq Scan on restaurants
--   Filter: ST_DWithin(...)
--   Rows Removed by Filter: 999950
--   Buffers: shared hit=12543
-- Planning Time: 0.089 ms
-- Execution Time: 245.678 ms

Forcer l'Utilisation d'un Index

Parfois, le planificateur ne choisit pas l'index. Vous pouvez le forcer :

-- Désactiver le scan séquentiel (forcer l'index)
SET enable_seqscan = off;

-- Exécuter la requête
SELECT nom FROM restaurants WHERE ...;

-- Réactiver (important !)
SET enable_seqscan = on;

⚠️ Attention : Forcer un index n'est généralement pas une bonne idée. Si PostgreSQL ne l'utilise pas, c'est souvent parce que le scan séquentiel est vraiment plus rapide.

Statistiques d'Index

-- Vérifier les statistiques des index spatiaux
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS scans_index,
    idx_tup_read AS tuples_lus,
    idx_tup_fetch AS tuples_extraits,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille_index
FROM pg_stat_user_indexes  
WHERE indexrelname LIKE 'idx_%'  
ORDER BY idx_scan DESC;  

-- Index inutilisés (candidats à la suppression)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille
FROM pg_stat_user_indexes  
WHERE idx_scan = 0  
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

6. Problèmes Courants et Solutions

Problème 1 : L'Index N'est Pas Utilisé

Symptôme : EXPLAIN montre un Seq Scan au lieu d'un Index Scan

Causes possibles :

SRID manquant ou différent

-- ❌ MAUVAIS : SRID non défini
WHERE ST_DWithin(position, ST_MakePoint(2.3, 48.8), 1000);

-- ✅ BON : SRID explicite et cohérent
WHERE ST_DWithin(
    position,
    ST_SetSRID(ST_MakePoint(2.3, 48.8), 4326),
    1000
);

Statistiques obsolètes

-- Mettre à jour les statistiques
ANALYZE restaurants;

Table trop petite

-- Si la table a moins de ~1000 lignes,
-- un scan séquentiel peut être plus rapide que l'index
-- C'est normal !

Requête retourne trop de lignes

-- Si la requête retourne >10-20% de la table,
-- PostgreSQL préfère un scan séquentiel
-- Solution : affiner la requête ou accepter le scan séquentiel

Problème 2 : Performances Dégradées Après Mises à Jour

Symptôme : L'index était rapide, il devient lent progressivement

Cause : Bloat (gonflement) de l'index après de nombreuses modifications

Solution :

-- 1. Vérifier le bloat
SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille_actuelle
FROM pg_stat_user_indexes  
WHERE indexrelname = 'idx_restaurants_position';  

-- 2. Reconstruire l'index
REINDEX INDEX CONCURRENTLY idx_restaurants_position;

-- 3. Analyser la table
ANALYZE restaurants;

Problème 3 : Index Trop Volumineux

Symptôme : L'index prend plus de place que la table elle-même

Causes et solutions :

Trop de colonnes INCLUDE

-- ❌ MAUVAIS : trop de colonnes
CREATE INDEX idx_bad  
ON restaurants  
USING GIST(position)  
INCLUDE (nom, adresse, telephone, email, description, ...);  

-- ✅ BON : uniquement les colonnes essentielles
CREATE INDEX idx_good  
ON restaurants  
USING GIST(position)  
INCLUDE (nom);

Index sur toutes les lignes alors qu'un sous-ensemble suffit

-- ✅ BON : index partiel (uniquement restaurants actifs)
CREATE INDEX idx_restaurants_actifs  
ON restaurants  
USING GIST(position)  
WHERE actif = true;

Problème 4 : Insertions Lentes

Symptôme : Les INSERT/UPDATE sont devenus très lents

Cause : Maintien des index lors des écritures

Solutions :

Insertions massives : Désactiver temporairement les index

-- Sauvegarder la définition de l'index
\d+ restaurants

-- Supprimer l'index
DROP INDEX idx_restaurants_position;

-- Faire les insertions massives
INSERT INTO restaurants SELECT ...;

-- Recréer l'index
CREATE INDEX idx_restaurants_position  
ON restaurants  
USING GIST(position);  

-- Analyser
ANALYZE restaurants;

Ajuster le FILLFACTOR pour des écritures fréquentes

-- Laisser plus d'espace libre
CREATE INDEX idx_restaurants_position  
ON restaurants  
USING GIST(position)  
WITH (fillfactor = 70);

7. Bonnes Pratiques

✅ Créer l'Index APRÈS l'Import de Données

-- 1. Créer la table
CREATE TABLE restaurants (...);

-- 2. Importer les données (COPY, INSERT massif)
COPY restaurants FROM '/tmp/restaurants.csv' CSV HEADER;

-- 3. PUIS créer l'index (beaucoup plus rapide)
CREATE INDEX idx_restaurants_position  
ON restaurants  
USING GIST(position);  

-- 4. Analyser
ANALYZE restaurants;

Raison : Créer l'index après l'import est ~10× plus rapide que de l'avoir pendant l'import.

✅ Toujours Analyser Après Création d'Index

CREATE INDEX idx_restaurants_position ON restaurants USING GIST(position);  
ANALYZE restaurants;  -- ← IMPORTANT !

Raison : ANALYZE met à jour les statistiques que le planificateur utilise pour choisir d'utiliser l'index.

✅ Un Seul Index Spatial par Table (Généralement)

-- ❌ ÉVITER : plusieurs index spatiaux sur la même colonne
CREATE INDEX idx_gist ON restaurants USING GIST(position);  
CREATE INDEX idx_spgist ON restaurants USING SPGIST(position);  
-- Gaspillage d'espace et de temps de maintenance

-- ✅ BON : un seul index, bien choisi
CREATE INDEX idx_restaurants_position ON restaurants USING GIST(position);

✅ Préférer GiST par Défaut

-- Sauf cas spécifique (points uniquement), utiliser GiST
CREATE INDEX idx_default ON ma_table USING GIST(geom);

✅ Nommer les Index de Manière Cohérente

-- Convention recommandée : idx_<table>_<colonne>_<type>
CREATE INDEX idx_restaurants_position_gist  
ON restaurants  
USING GIST(position);  

CREATE INDEX idx_zones_geometrie_gist  
ON zones  
USING GIST(geometrie);

✅ Surveiller la Santé des Index

-- Script de monitoring mensuel
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille,
    CASE
        WHEN idx_scan = 0 THEN '⚠️ Jamais utilisé'
        WHEN idx_scan < 10 THEN '⚠️ Rarement utilisé'
        ELSE '✅ Utilisé régulièrement'
    END AS statut
FROM pg_stat_user_indexes  
WHERE indexrelname LIKE 'idx_%gist%'  
   OR indexrelname LIKE 'idx_%spgist%'
ORDER BY pg_relation_size(indexrelid) DESC;

8. Cas d'Usage Réels

Cas 1 : Application de Livraison (Type Uber Eats)

Besoin : Trouver les restaurants dans un rayon de 3 km autour d'un client

-- Table restaurants
CREATE TABLE restaurants (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    cuisine VARCHAR(50),
    position GEOMETRY(Point, 4326),
    actif BOOLEAN DEFAULT true
);

-- Index spatial optimisé (partiel : uniquement restaurants actifs)
CREATE INDEX idx_restaurants_actifs_position  
ON restaurants  
USING GIST(position)  
WHERE actif = true;  

-- Requête de recherche
SELECT
    id,
    nom,
    cuisine,
    ST_Distance(position::geography, client_position::geography) AS distance_m
FROM restaurants  
WHERE actif = true  
  AND ST_DWithin(
      position::geography,
      ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)::geography,
      3000  -- 3 km
  )
ORDER BY distance_m  
LIMIT 20;  

-- Performance : <10ms pour 1M de restaurants

Cas 2 : Système de Tracking GPS (Flottes de Véhicules)

Besoin : Stocker et requêter des millions de positions GPS

-- Table de positions (très volumineuse)
CREATE TABLE positions_gps (
    id BIGSERIAL PRIMARY KEY,
    vehicule_id INTEGER,
    timestamp TIMESTAMPTZ,
    position GEOMETRY(Point, 4326),
    vitesse NUMERIC(5, 2)
);

-- Index spatial SP-GiST (optimal pour points)
CREATE INDEX idx_positions_gps_position  
ON positions_gps  
USING SPGIST(position);  

-- Index composite pour requêtes temporelles + spatiales
CREATE INDEX idx_positions_gps_timestamp  
ON positions_gps(vehicule_id, timestamp DESC);  

-- Requête : Position des véhicules dans une zone à un moment donné
SELECT
    vehicule_id,
    timestamp,
    ST_AsText(position) AS position,
    vitesse
FROM positions_gps  
WHERE timestamp > NOW() - INTERVAL '1 hour'  
  AND position && ST_MakeEnvelope(2.3, 48.8, 2.4, 48.9, 4326)
ORDER BY timestamp DESC;

Cas 3 : Analyse de Zones Géographiques (SIG)

Besoin : Requêtes d'intersection entre polygones (quartiers, parcelles)

-- Table de parcelles cadastrales
CREATE TABLE parcelles (
    id SERIAL PRIMARY KEY,
    reference VARCHAR(50),
    proprietaire VARCHAR(100),
    superficie NUMERIC(10, 2),
    geometrie GEOMETRY(Polygon, 2154)  -- Lambert 93 (France)
);

-- Index GiST (meilleur pour polygones)
CREATE INDEX idx_parcelles_geometrie  
ON parcelles  
USING GIST(geometrie);  

-- Requête : Parcelles intersectant une zone d'urbanisme
SELECT
    p.reference,
    p.proprietaire,
    ST_Area(ST_Intersection(p.geometrie, zone.geometrie)) AS surface_impactee
FROM parcelles p  
JOIN zones_urbanisme zone ON ST_Intersects(p.geometrie, zone.geometrie)  
WHERE zone.type = 'Zone constructible'  
ORDER BY surface_impactee DESC;

9. Résumé : GiST vs SP-GiST

Critère	GiST	SP-GiST
Type de données	Tous types géométriques	Principalement Points
Structure	Arbre avec boîtes englobantes chevauchantes	Quadtree avec partitions disjointes
Performance Points	⭐⭐⭐⭐ Très bonne	⭐⭐⭐⭐⭐ Excellente
Performance Polygones	⭐⭐⭐⭐⭐ Excellente	⭐⭐⭐ Acceptable
Opérations spatiales	Toutes	Limitées
Équilibrage	Automatique	Peut être déséquilibré
Taille de l'index	Moyenne	Légèrement plus petite
Maintenance	Standard	Standard
Recommandation	Index par défaut	Points uniquement + haute densité

10. Checklist de Mise en Place

Avant de Créer un Index Spatial

La table contient > 1000 lignes (sinon l'index est inutile)
Les requêtes spatiales sont lentes (vérifier avec EXPLAIN)
Le SRID est défini correctement sur toutes les géométries
Les données sont propres (géométries valides)

Création de l'Index

Choisir le bon type (GiST par défaut, SP-GiST pour points uniquement)
Nommer l'index de manière cohérente
Considérer un index partiel si applicable
Définir le FILLFACTOR si données fréquemment modifiées
Exécuter ANALYZE après création

Après Création

Vérifier avec EXPLAIN que l'index est utilisé
Mesurer les performances (avant/après)
Surveiller la taille de l'index
Planifier la maintenance (REINDEX si nécessaire)
Documenter la stratégie d'indexation

Conclusion

Les index spatiaux GiST et SP-GiST sont essentiels pour obtenir de bonnes performances avec PostGIS. Ils transforment des requêtes de plusieurs secondes en quelques millisecondes.

Points clés à retenir :

GiST est l'index spatial par excellence :
- Polyvalent (tous types de géométries)
- Performant pour la plupart des cas
- À utiliser par défaut
SP-GiST est spécialisé :
- Optimal pour les points uniquement
- Structure Quadtree
- Meilleur avec distributions spatiales inégales
Toujours :
- Créer l'index APRÈS l'import de données
- Exécuter ANALYZE après création
- Vérifier avec EXPLAIN
- Surveiller les performances
Index = Trade-off :
- Accélère les lectures (SELECT)
- Ralentit les écritures (INSERT/UPDATE)
- Occupe de l'espace disque
- Nécessite de la maintenance

Règle d'or : Un index bien placé peut améliorer les performances de 100× à 1000×. Mais un index mal placé gaspille des ressources. Mesurez toujours l'impact réel !

Ressources complémentaires :

Documentation PostGIS sur les index : https://postgis.net/docs/using_postgis_dbmanagement.html#idxing
PostgreSQL GiST : https://www.postgresql.org/docs/current/gist.html
PostgreSQL SP-GiST : https://www.postgresql.org/docs/current/spgist.html
Comparaison des index PostgreSQL : https://www.postgresql.org/docs/current/indexes-types.html

⏭️ Fonctions spatiales (ST_Distance, ST_Intersects)

FilesExpand file tree

02.2-index-spatiaux.md

Latest commit

History

02.2-index-spatiaux.md

File metadata and controls

18.2.2. Index Spatiaux : GiST et SP-GiST

Introduction

1. Pourquoi Avons-nous Besoin d'Index Spatiaux ?

Le Problème Sans Index

La Solution : Index Spatial

2. L'Index GiST (Generalized Search Tree)

Qu'est-ce que GiST ?

Comment Fonctionne GiST ?

Principe de la Boîte Englobante

Structure Hiérarchique (Arbre)

Recherche avec GiST

Créer un Index GiST

Syntaxe de Base

Exemples Pratiques

Opérations Accélérées par GiST

Exemple : Requête Optimisée

Maintenance des Index GiST

Surveillance de la Taille

Statistiques d'Utilisation

Reconstruction (REINDEX)

3. L'Index SP-GiST (Space-Partitioned GiST)

Qu'est-ce que SP-GiST ?

Comment Fonctionne SP-GiST ?

Principe du Quadtree

Créer un Index SP-GiST

Syntaxe de Base

Exemples Pratiques

Quand Utiliser SP-GiST vs GiST ?

Utiliser SP-GiST pour :

Utiliser GiST pour :

Comparaison de Performance

Benchmark : Table de 1 Million de Points

4. Optimisations Avancées

Index sur Sous-Ensemble (Index Partiel)

Index avec INCLUDE (Covering Index)

Index Multi-Colonnes (Rare avec Spatial)

Tuning du FILLFACTOR

5. Vérifier l'Utilisation des Index

Avec EXPLAIN ANALYZE

Forcer l'Utilisation d'un Index

Statistiques d'Index

6. Problèmes Courants et Solutions

Problème 1 : L'Index N'est Pas Utilisé

Problème 2 : Performances Dégradées Après Mises à Jour

Problème 3 : Index Trop Volumineux

Problème 4 : Insertions Lentes

7. Bonnes Pratiques

✅ Créer l'Index APRÈS l'Import de Données

✅ Toujours Analyser Après Création d'Index

✅ Un Seul Index Spatial par Table (Généralement)

✅ Préférer GiST par Défaut

✅ Nommer les Index de Manière Cohérente

✅ Surveiller la Santé des Index

8. Cas d'Usage Réels

Cas 1 : Application de Livraison (Type Uber Eats)

Cas 2 : Système de Tracking GPS (Flottes de Véhicules)

Cas 3 : Analyse de Zones Géographiques (SIG)

9. Résumé : GiST vs SP-GiST

10. Checklist de Mise en Place

Avant de Créer un Index Spatial

Création de l'Index

Après Création

Conclusion