SQL Academy · Lezione

Indici B-tree, Hash, GiST e GIN

Confronti i principali tipi di indice in PostgreSQL e scelga quello adatto per query di uguaglianza, intervalli, geometrie, JSON e ricerca full-text.

Indici B-tree, Hash, GiST e GIN è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 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.

Panoramica dei tipi di indice

PostgreSQL dispone di diversi tipi di indice, ciascuno ottimizzato per diversi modelli di accesso:

B-tree — uguaglianze e intervalli, impostazione predefinita
Hash — solo uguaglianze
GiST — dati geometrici, ricerca full-text e casi personalizzati
GIN — valori compositi, come array, JSONB e full-text
BRIN — intervalli di blocchi, per tabelle enormi e ordinate
SP-GiST — alberi partizionati per spazio

B-tree: l'impostazione predefinita

Viene utilizzato nel 95% dei casi. Supporta =, <, <=, >, >=, BETWEEN e ORDER BY:

CREATE INDEX users_email_idx ON users(email);
CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

Indice Hash

Solo ricerche per uguaglianza. È sicuro in caso di crash a partire da PG 10. È più piccolo e leggermente più veloce di B-tree per le sole uguaglianze, ma ha un ambito di utilizzo molto ristretto:

CREATE INDEX sessions_token_hash ON sessions USING HASH (token);
-- Useful for very high-cardinality equality lookups; usually B-tree is fine.

Indice GiST

Generalised Search Tree: è estensibile e supporta tipi intervallo, tipi geometrici, indirizzi IP e ricerca full-text:

CREATE INDEX events_during_idx ON events USING GIST (during);
-- 'during' is a tstzrange — finds overlapping ranges efficiently.

CREATE INDEX places_location_idx ON places USING GIST (location);
-- PostGIS geometry — nearest neighbour, intersects.

Indice GIN

Generalised Inverted Index: è ideale per valori compositi in cui ogni elemento corrisponde a molte righe:

CREATE INDEX articles_tags_gin ON articles USING GIN (tags);
-- tags is TEXT[]; query with @> or && operators

CREATE INDEX articles_doc_gin ON articles USING GIN (search_doc);
-- For tsvector full-text search

CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);
-- For JSONB containment queries

Indice BRIN

Block Range INdexes riassume gli intervalli di valori per N pagine. È minuscolo, pochi kilobyte per tabelle da terabyte, ma è efficace solo quando i dati sono fisicamente ordinati in base alla colonna indicizzata:

CREATE INDEX events_ts_brin ON events USING BRIN (ts);
-- Excellent for append-only time-series tables.

Confrontare le dimensioni

Per una tabella con un miliardo di righe:

B-tree su un BIGINT: ~30 GB
BRIN su un TIMESTAMPTZ: ~1 MB

BRIN è molto più piccolo, ma supera B-tree solo per query sequenziali o su dati ordinati.

Scegliere il tipo di indice

Flusso decisionale:

Uguaglianza e intervalli su uno scalare → B-tree
Uguaglianza su un insieme enorme di valori scalari → B-tree, Hash solo dopo aver effettuato misurazioni
Array, JSONB o ricerca full-text → GIN
Tipi intervallo, dati geometrici o testo con ricerca approssimata → GiST
Tabella enorme e ordinata, con soli inserimenti in coda → BRIN

Compromessi di GIN

GIN è il più veloce per le query che cercano "tutte le righe contenenti X", ma è più lento in INSERT/UPDATE rispetto a B-tree. Per tabelle con un volume molto elevato di scritture, consideri fastupdate=off per controllare la lista in attesa di GIN.

Classi di operatori

Ogni tipo di indice funziona con operatori specifici. JSONB utilizza jsonb_path_ops per indici più piccoli e veloci, limitati ai controlli di contenimento:

CREATE INDEX e_data_gin ON events USING GIN (data jsonb_path_ops);
-- Half the size of default jsonb_ops, supports @> only.

Indici composti per tipo

Gli indici B-tree composti utilizzano la corrispondenza del prefisso più a sinistra. Gli indici GIN composti funzionano, ma sono più grandi; in genere è preferibile creare indici GIN separati su una sola colonna.

Riepilogo

Scelga il tipo di indice in base alla query.

B-tree: impostazione predefinita
GIN: array, JSONB e ricerca full-text
GiST: intervalli, dati geometrici e ricerca approssimata
BRIN: scansioni sequenziali e inserimenti in coda

Verifica rapida

Sta indicizzando una colonna TEXT[] per query di "contenimento". Quale tipo di indice è adatto?

Domande Frequenti

La lezione «Indici B-tree, Hash, GiST e GIN» è gratuita?

Sì — il testo completo di «Indici B-tree, Hash, GiST e GIN» è 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 B-tree, Hash, GiST e GIN»?

Confronti i principali tipi di indice in PostgreSQL e scelga quello adatto per query di uguaglianza, intervalli, geometrie, JSON e ricerca full-text. 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 1 di 4.

Quanto tempo richiede la lezione «Indici B-tree, Hash, GiST e GIN»?

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