Problemi di over-indexing

Gli indici sono sicuramente un modo figo per rendere i tuoi database più veloci, ma come si dice, "il meglio è nemico del bene". Non ogni indice porta vantaggi, e averne troppi può fare più danni che altro. Sembra strano, ma è proprio così. Vediamo insieme perché.

Immagina una grande biblioteca dove per trovare i libri ci sono diversi cataloghi — tipo per autore, per genere, per anno di pubblicazione. Ogni catalogo ti aiuta a trovare velocemente il libro che cerchi. Ma se i cataloghi diventano troppi — tipo uno per ogni parola del titolo o per ogni dettaglio — invece di aiutarti ti incasini: ci metti più tempo a cercare, i cataloghi occupano un sacco di spazio e il bibliotecario deve aggiornare continuamente tutte queste liste.

Nel database gli indici funzionano più o meno allo stesso modo: ti aiutano a trovare i dati al volo, ma se ne hai troppi, aggiornarli ogni volta che aggiungi o modifichi un record diventa un casino. Anche lo spazio su disco ne risente. E poi, quando ci sono troppi indici, il sistema può confondersi su quale usare.

Quindi, come coi cataloghi in biblioteca, anche con gli indici non bisogna esagerare — meglio averne pochi ma buoni, che decine inutili.

Facciamo un gioco da "detective di PostgreSQL". Immagina di aver aggiunto tre indici su una sola colonna. L'hai fatto pensando che così sarebbe andato tutto più veloce. Ma pensa un attimo:

• Se la tua tabella è una lista gigante di studenti e ci sono tre indici, ogni volta che aggiungi uno studente devi aggiornare tre indici. Non sembra proprio una "accelerazione", vero?
• E se hai 10 tabelle così, tutte piene di indici? Le performance del database vanno a farsi benedire.

Come capire se hai un problema di over-indexing?

La prima cosa da fare per capire se hai un problema è guardare gli indici che hai già. In PostgreSQL puoi farlo con il comando:

\d nome_tabella

Questo comando ti mostra la tabella, le sue colonne e gli indici collegati. Se vedi una valanga di indici su una sola tabella, è già un campanello d'allarme.

Un altro strumento utile è la vista di sistema pg_stat_user_indexes. Ti fa vedere quanto vengono usati gli indici, così puoi capire se ce n'è qualcuno che è solo "peso morto":

SELECT
    relname AS nome_tabella,
    indexrelname AS nome_indice,
    idx_scan AS scansioni_indice
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Se idx_scan è uguale a 0, vuol dire che quell'indice non è mai stato usato nelle query. Un candidato perfetto per essere eliminato.

Esempio di over-indexing

Immaginiamo una tabella con gli utenti:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

E abbiamo tre indici:

-- Indice su email
CREATE INDEX idx_users_email ON users (email);

-- Indice su username
CREATE INDEX idx_users_username ON users (username);

-- Indice su created_at
CREATE INDEX idx_users_created_at ON users (created_at);

Ora vediamo le query tipiche che facciamo:

1. Ricerca utente per email.
2. Ricerca utente per username.
3. Ordinamento utenti per created_at.

Sembra che gli indici siano utili. Ma ecco il punto: se queste query sono rare (tipo una volta a settimana), creare gli indici non ha senso. Anzi, se alcuni di questi indici non vengono mai usati, sono solo un peso per le operazioni di insert e update.

Per esempio: supponiamo di avere questi dati nella tabella users:

Se le query su username praticamente non vengono mai fatte, l'indice idx_users_username non viene mai usato (idx_scan = 0) e può essere tolto per ottimizzare.

Insomma, l'indice è uno strumento top, ma va usato con la testa. Meglio pochi indici utili e usati spesso, che tanti inutili.

Come evitare l'over-indexing

1. Analizza gli indici che usi. Come abbiamo già detto, controlla le statistiche di utilizzo degli indici con pg_stat_user_indexes. Se un indice non viene quasi mai usato, probabilmente puoi eliminarlo:

DROP INDEX IF EXISTS nome_indice;

2. Crea indici solo per le query usate spesso. Prima di aggiungere un indice, fatti queste domande:

• Questa colonna viene usata spesso in WHERE, ORDER BY, GROUP BY?
• La tabella è molto grande?
• La query è davvero troppo lenta senza indice?

Se la risposta è "no" anche solo a una di queste domande, forse l'indice è inutile.

3. Usa indici composti. Se usi spesso più colonne nella stessa query, invece di creare un indice per ogni colonna, crea un indice composto:

CREATE INDEX idx_users_email_username ON users (email, username);

Questo velocizza le query che filtrano sia per email che per username insieme.

4. Rivedi regolarmente gli indici esistenti. Quando il database cresce, anche le tue query possono cambiare. Quello che era utile un anno fa, oggi può essere inutile. Ogni tanto controlla gli indici e cancella quelli che non servono più.

Minimizzare gli indici: un esempio

Torniamo alla nostra tabella users. Invece di tre indici separati, possiamo ottimizzare così:

• Togli l'indice su created_at se l'ordinamento su questa colonna è raro.
• Invece di due indici separati su email e username, crea un indice composto:

CREATE INDEX idx_users_email_username ON users (email, username);

Conclusioni: qual è il segreto dell'equilibrio?

Come in tanti aspetti del coding, qui vale la regola del minimalismo: "Meno è meglio". Non serve indicizzare ogni colonna solo perché puoi. Pensa a cosa ti serve davvero e a quanto migliora le performance delle query. Sii pragmatico e ricorda che il bravo dev non è quello che mette indici ovunque, ma quello che capisce l'impatto e li usa con criterio.

Ora che hai questo strumento in mano, puoi evitare il disastro dell'over-indexing e rendere il tuo database veloce come un ghepardo, non lento come una tartaruga con lo zaino pieno di indici inutili.