SQL Academy · Lezione

Indici spaziali (GiST)

Renda rapide le query sulla posizione.

Indici spaziali (GiST) è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Perché le query sulla posizione diventano lente

Immagini una tabella con milioni di posizioni di ristoranti. Se chiede "Trovi tutti i ristoranti entro 5 km da me," il database deve controllare ogni singola riga per calcolare la distanza. Questa operazione si chiama scansione sequenziale e diventa estremamente lenta con la crescita della tabella.

Gli indici spaziali risolvono il problema organizzando i dati geometrici in una struttura ad albero, che consente al database di saltare istantaneamente ampie porzioni della tabella.

Che cos'è un indice GiST?

GiST è l'acronimo di Generalized Search Tree. È un framework flessibile per gli indici, integrato in PostgreSQL, che supporta molti tipi di dati, incluse le forme geometriche e le geometrie PostGIS.

A differenza di un indice B-tree, che funziona su valori ordinabili come interi o stringhe, GiST può indicizzare dati multidimensionali come punti, poligoni e linee. PostGIS usa internamente GiST per creare i propri indici spaziali.

Creazione di un indice spaziale

Creare un indice GiST su una colonna geometrica è semplice. Si usa CREATE INDEX con la clausola USING gist. Questa singola istruzione può trasformare una query da un'esecuzione di alcuni minuti a una di pochi millisecondi.

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Come funziona GiST: riquadri delimitatori

Un indice spaziale GiST non memorizza le geometrie esatte. Memorizza invece i riquadri delimitatori, cioè il rettangolo più piccolo che racchiude ogni geometria. L'albero viene costruito raggruppando i riquadri delimitatori vicini a ogni livello.

Quando viene eseguita una query, PostgreSQL percorre l'albero eliminando i rami i cui riquadri delimitatori non si sovrappongono all'area di ricerca. Solo le righe candidate rimaste vengono quindi verificate con precisione. Questo approccio in due fasi (accesso all'indice + riverifica) è estremamente efficiente.

Configurazione di una tabella di esempio

Prima di esaminare il comportamento degli indici, creiamo una tabella di esempio con punti di città e popoliamola con alcune righe. La colonna geom memorizza ogni città come un Point in 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));

Aggiunta dell'indice GiST

Con la tabella popolata, aggiunga un indice GiST sulla colonna geom. Per le tabelle di produzione con milioni di righe, questa istruzione potrebbe richiedere alcuni minuti, ma deve essere eseguita una sola volta. Dopodiché, ogni query spaziale su questa colonna ne trarrà automaticamente vantaggio.

CREATE INDEX idx_cities_geom
  ON cities
  USING gist (geom);

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

Operatore dei riquadri delimitatori &&

PostGIS espone l'operatore && per verificare se due riquadri delimitatori si sovrappongono. Questo operatore tiene conto dell'indice: il pianificatore usa automaticamente l'indice GiST. È molto più veloce del calcolo delle intersezioni geometriche esatte e viene spesso utilizzato come filtro preliminare rapido.

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

Ricerca del vicino più prossimo con <->

L'operatore <-> restituisce la distanza tra due geometrie ed è anch'esso accelerato da GiST. Combinandolo con ORDER BY ... LIMIT si ottiene una query k-nearest-neighbor (KNN) estremamente rapida, senza bisogno di una scansione completa della tabella.

-- 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;

Verifica dell'uso dell'indice con EXPLAIN

Usi sempre EXPLAIN o EXPLAIN ANALYZE per confermare che il pianificatore stia effettivamente utilizzando l'indice. Verifichi la presenza di Bitmap Index Scan o Index Scan using idx_cities_geom nell'output. Se vede invece Seq Scan, la tabella potrebbe essere troppo piccola perché il pianificatore preferisca l'indice.

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

Creazione concorrente dell'indice

La creazione di un indice spaziale di grandi dimensioni con il comando standard CREATE INDEX blocca la tabella per le operazioni di scrittura. In produzione, usi CREATE INDEX CONCURRENTLY per creare l'indice senza bloccare inserimenti o aggiornamenti. Il compromesso è che l'operazione richiede più tempo e non può essere eseguita all'interno di un blocco di transazione.

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

Manutenzione dell'indice spaziale

Con il tempo, numerosi inserimenti, aggiornamenti ed eliminazioni possono causare la frammentazione dell'indice: l'indice cresce frammentato e meno efficiente. Usi REINDEX per ricostruirlo in modo pulito oppure pianifichi esecuzioni periodiche di VACUUM ANALYZE per aggiornare le statistiche, così che il pianificatore delle query possa prendere decisioni migliori.

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

-- Update planner statistics for the table
ANALYZE cities;

Verifica rapida: indici GiST

Verifichi la sua comprensione degli indici spaziali con GiST in PostGIS.

Riepilogo: indici spaziali con GiST

In questa lezione ha imparato perché gli indici spaziali sono essenziali per ottenere query sulle posizioni efficienti e come GiST li renda possibili in PostgreSQL e PostGIS.

Elementi chiave:

GiST (Generalized Search Tree) è un tipo di indice flessibile che supporta dati geometrici multidimensionali.
Crei un indice spaziale con CREATE INDEX ... USING gist (geom).
GiST memorizza i riquadri delimitatori e riduce l'albero di ricerca, evitando scansioni complete della tabella.
L'operatore && (sovrapposizione dei riquadri delimitatori) e l'operatore <-> (distanza/KNN) sono entrambi accelerati da GiST.
Usi EXPLAIN per verificare l'uso dell'indice e CREATE INDEX CONCURRENTLY in produzione per evitare blocchi di scrittura.
Mantenga gli indici con REINDEX e ANALYZE per mantenere elevate le prestazioni delle query nel tempo.

Domande Frequenti

La lezione «Indici spaziali (GiST)» è gratuita?

Sì — il testo completo di «Indici spaziali (GiST)» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Indici spaziali (GiST)»?

Renda rapide le query sulla posizione. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Indici spaziali (GiST)»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy