Introduzione agli indici

Probabilmente hai già incontrato gli indici prima. Oggi però li vediamo per bene, così capisci tutti i dettagli di come funzionano. Gli indici sono una figata, possono velocizzare le tue query di centinaia o anche migliaia di volte. Ti piaceranno. Ma cosa sono esattamente gli indici?

Pensa di cercare un libro specifico in una biblioteca enorme. Se non ci fosse nessun ordine, ci metteresti una vita. Per velocizzare la ricerca, i libri di solito sono ordinati per alfabeto o per categorie. Un indice in un database funziona più o meno allo stesso modo.

Formalmente, un indice è una struttura dati speciale aggiuntiva, creata per trovare velocemente le righe in una tabella. Riduce la quantità di dati che devi scansionare per trovare quello che ti serve.

In un certo senso, gli indici sono come l’indice di un’enciclopedia, se immagini il libro come una tabella.
Invece di leggere tutta l’enciclopedia dall’inizio alla fine, usi l’indice per andare subito alla pagina giusta.

Immagina che la tabella employees abbia 100 mila dipendenti. Se cerchi un dipendente con il cognome "Min", senza indice dovresti scorrere ogni riga della tabella (che è lento). Con un indice la ricerca è molto più veloce, perché Postgres sa già dove guardare.

Come funzionano gli indici?

Per capire come gli indici velocizzano le query, devi sapere cosa succede dietro le quinte. Un indice viene creato su una o più colonne della tabella e organizza i dati in una struttura facile da cercare.

Gli indici in PostgreSQL usano di solito la struttura dati B-Tree (albero bilanciato). Ecco come funziona:

1. Creazione dell’indice: i dati delle colonne su cui crei l’indice vengono organizzati in un albero.
2. Durante la ricerca: PostgreSQL usa l’indice per trovare velocemente le righe giuste, invece di scansionare tutta la tabella.
3. Struttura ad albero: l’albero è ordinato, quindi puoi saltare velocemente da un record all’altro (algoritmo di ricerca binaria).

Esempio: supponiamo di avere una tabella con 1 milione di righe. Senza indice devi controllare un milione di record. Con l’indice PostgreSQL può ridurre la ricerca a poche decine.

Quando usare gli indici?

Gli indici aumentano tantissimo la velocità delle operazioni di lettura, tipo: ricerca, filtro, ordinamento. Però c’è un prezzo: rallentano le operazioni di scrittura (tipo INSERT, UPDATE, DELETE), perché l’indice va aggiornato ogni volta che i dati cambiano.

Situazioni in cui gli indici sono indispensabili

Query di ricerca frequenti: Quando cerchi spesso righe con valori specifici, tipo:

SELECT * FROM employees WHERE last_name = 'Min';

Filtraggio dei dati: Quando usi condizioni in WHERE o HAVING:

SELECT * FROM employees WHERE salary > 50000;

Ordinamento dei dati: Quando usi ORDER BY:

SELECT * FROM employees ORDER BY hire_date DESC;

Join tra tabelle (JOIN): Se unisci spesso tabelle usando colonne specifiche.

*Quando *NON conviene usare gli indici

• Se la tabella è molto piccola (tipo 10-50 righe), l’indice non dà nessun vantaggio reale.
• Se la colonna su cui vuoi creare l’indice viene usata raramente nelle query.
• Se i dati nella colonna hanno pochissimi valori unici (tipo il campo gender, che ha solo 2-3 valori).

Esempio: creare un indice sul campo status che ha solo due possibili valori — "attivo" e "non attivo" — non ha senso.

Impatto degli indici sulle performance

Come avrai già capito, gli indici velocizzano le query di lettura, ma possono rallentare le scritture. Il motivo è che quando aggiungi, aggiorni o cancelli righe, PostgreSQL deve aggiornare anche l’indice.

Bilanciare uso degli indici e performance

• Per tabelle che cambiano spesso, meglio non esagerare con il numero di indici.
• Per tabelle dove la maggior parte del lavoro è leggere dati, gli indici sono fondamentali.

Utilità degli indici nella vita reale

Nella pratica, gli indici si usano tantissimo per:

1. Ottimizzare le web app: velocizzare il caricamento delle pagine grazie a query SQL più rapide.
2. Gestire tabelle grandi: senza indici, le query su tabelle con milioni di righe possono durare minuti.
3. Scalabilità: gli indici permettono di gestire grandi quantità di dati in modo efficiente.

Per esempio, un e-commerce con un database di prodotti e ordini non può fare a meno degli indici, altrimenti i clienti aspetterebbero il caricamento delle pagine per anni.