Come scegliere il tipo di indice giusto

Abbiamo già esplorato la teoria degli indici, conosciuto i loro tipi, imparato a crearli e cancellarli, e capito come indicizzare tipi di dati complessi come array e JSONB. Ora è il momento di parlare di come scegliere proprio quell’indice che funzionerà bene per i tuoi casi — perché una scelta sbagliata può portare a grossi problemi.

Pensa al tuo database come a una biblioteca, e alle query come ai visitatori che cercano libri. Se i libri sono sparsi sul pavimento, cercare qualcosa diventa un giro infinito. Gli indici sono come scaffali e cataloghi organizzati che ti aiutano a trovare subito quello che ti serve, senza perdere tempo a guardare tutto.

Ma se metti lo scaffale o il catalogo sbagliato, tipo usi un HASH-index dove ti serve un indice per la ricerca per range, è come se il bibliotecario cercasse i libri per titolo ma avesse solo il catalogo per anno di pubblicazione — il processo si allunga e tutti iniziano a lamentarsi. Nel database questo si traduce in query lente e carico alto sul sistema.

Oggi vediamo come scegliere l’indice giusto, così le tue query volano e il database non si stanca. Il risultato altrimenti sarà tragico: query lente, risorse mangiate, il bibliotecario (PostgreSQL) in depressione.

Criteri per scegliere l’indice: checklist

Quando scegli un indice, fatti queste domande:

1. Che tipo di dati hai in questa colonna?
   • Per esempio, numeri INTEGER, FLOAT spesso vogliono un indice B-TREE, array — GIN, campi di testo — dipende dal caso.

2. Che tipo di query fai più spesso?

   • WHERE field = value? Ricerca diretta? Probabilmente ti serve B-TREE o HASH.
   • Ricerche su array o JSONB? Guarda verso GIN.
   • Geodati, range? Pensa a GiST.

3. Cosa succede ai tuoi dati?

   • Se hai una tabella con tanti insert e update, evita di mettere troppi indici, perché aumentano l’overhead.

4. Devi garantire l’unicità?

   • In questo caso devi usare un indice con attributo UNIQUE.

Casi: esempi reali di scelta dell’indice

Diamo un’occhiata a qualche scenario reale.

1. Ricerca semplice per uguaglianza

Lavori con un database di studenti e vuoi trovare velocemente uno studente tramite la sua email:

SELECT * FROM students WHERE email = 'student@example.com';

Cosa conta qui? Cerchiamo per uguaglianza. La scelta migliore è un indice B-TREE, perché è perfetto per le ricerche di corrispondenza esatta.

CREATE INDEX idx_students_email ON students (email);

Oppure, se l’email deve essere unica:

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. Ricerca per range

Ora supponiamo che vuoi trovare studenti con più di 18 anni:

SELECT * FROM students WHERE age > 18;

Per la ricerca per range B-TREE va benissimo, perché la sua struttura è fatta apposta per le ricerche ordinate.

CREATE INDEX idx_students_age ON students (age);

3. Filtraggio su array

Hai una tabella courses, dove una colonna contiene un array con gli ID degli studenti iscritti al corso. Vuoi trovare tutti i corsi dove è iscritto lo studente con ID 123.

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

Per queste query l’indice GIN è perfetto, perché è ottimizzato per lavorare con gli array.

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. Estrazione dati da JSONB

Supponiamo che hai una tabella con dati JSONB, dove c’è info sugli ordini. Vuoi trovare tutti gli ordini dove il cliente è di "Moscow":

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

Qui va bene un indice GIN, che permette di cercare in modo efficiente per chiavi e valori JSONB.

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. Dati geografici

Se lavori con dati geografici, tipo vuoi trovare tutti i punti dentro un certo raggio, usa un indice GiST. Questo tipo di indice è ottimo per geometrie e range.

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

Confronto delle performance dei vari indici

Prendiamo un esempio reale con la ricerca di studenti per email. La tabella ha 1 milione di record. Proviamo a fare la query con diversi indici e senza indice:

Conclusione: in questo caso usare l’indice B-TREE rende la query più veloce di oltre 500 volte.

Errori nella scelta degli indici

L’errore più comune è creare indici "tanto per". Tipo, decidi di indicizzare ogni colonna della tabella, ma poi ti accorgi che le performance degli insert sono crollate. Ricorda, l’indice non è una bacchetta magica che funziona sempre e ovunque. È uno strumento potente nelle mani giuste, ma usato male può fare danni.

Un altro errore tipico è scegliere il tipo di indice sbagliato. Tipo, usi un HASH index per una ricerca per range, e le tue query diventano lentissime. Tutto perché il HASH index serve solo per ricerche esatte.

Consigli per scegliere l’indice

• Se fai spesso ricerche per uguaglianza o ordinamento, usa B-TREE.
• Per corrispondenze esatte ma con poca memoria, puoi usare HASH.
• Se lavori con array o JSONB, la scelta è GIN.
• Per range o dati geografici usa GiST.

E infine, il consiglio principale: analizza sempre le tue query! Usa il comando EXPLAIN e EXPLAIN ANALYZE per capire come PostgreSQL usa gli indici e dove puoi migliorare.

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

Questo è tutto per oggi! Ora hai le conoscenze per scegliere gli indici come un jedi sceglie la sua spada laser. Vai piano, non creare indici dove non servono, e controlla sempre come influenzano le performance.