Ottimizzazione delle query tramite analisi del piano: EXPLAIN ANALYZE

Ecco il momento della verità: le query SQL non sono solo righe di codice, ma un vero dialogo con il database. Se le sussurri un tenero "SELECT *", il database probabilmente ti capirà e farà quello che chiedi senza protestare. Ma se gli lanci addosso un romanzo SQL tutto incasinato, il database potrebbe pensarci su... e poi iniziare a rallentare.

L’ottimizzazione delle query è l’arte di parlare al database in modo chiaro e conciso. Quando una query è scritta bene ed efficiente, viene eseguita velocemente, non stressa il sistema e non dà fastidio agli altri processi. Ma una query fatta male può rallentare tutto: il database inizierà a consumare più CPU e RAM, il disco sarà impegnato in letture e scritture inutili, e anche le app che usano il database inizieranno a laggare.

EXPLAIN ANALYZE ti aiuta a trovare questi punti critici e a capire dove la query "soffre". È come una diagnosi: senza di essa, è difficile curare le performance.

Problemi tipici nelle query e come scoprirli

Ora è il momento di conoscere i sospetti che peggiorano le performance. Per questo ci armiamo di EXPLAIN ANALYZE.

Problema 1: Scansione sequenziale (Seq Scan)

Seq Scan (scansione sequenziale) è quando PostgreSQL cerca i dati leggendo ogni riga della tabella. Va bene se la tabella è piccola, ma sulle tabelle grandi può essere un incubo.

Come capire se viene usato Seq Scan? Basta fare l’analisi con EXPLAIN ANALYZE. Esempio:

EXPLAIN ANALYZE
SELECT * 
FROM studenti 
WHERE id_studente = 123;

Il risultato potrebbe essere così (nota il Seq Scan):

Seq Scan on studenti  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Come risolvere?

Crea un indice su id_studente, se non esiste già:

CREATE INDEX idx_id_studente ON studenti(id_studente);

Dopo, rilancia EXPLAIN ANALYZE. Dovresti vedere Index Scan invece di Seq Scan.

Problema 2: bassa selettività delle condizioni

La selettività è quante righe devi processare per trovare quello che cerchi. Se il filtro prende quasi tutta la tabella, l’indice non ti salva.

Esempio di query con bassa selettività:

EXPLAIN ANALYZE
SELECT * 
FROM studenti 
WHERE corso = 'Informatica';

Se il 90% degli studenti nella tabella studia Informatica, la query potrebbe usare Seq Scan anche se c’è un indice su corso.

Come migliorare la query?

1. Rivedi la logica della query: magari puoi aggiungere altre condizioni per restringere il filtro.
2. Assicurati che le statistiche della tabella siano aggiornate (aiuta PostgreSQL a stimare bene la selettività):

ANALYZE studenti;

3. Se la query usa l’indice senza motivo invece della scansione sequenziale, prova a forzare PostgreSQL a usarlo:

SET enable_seqscan = OFF;

Problema 3: troppe operazioni di ordinamento

L’ordinamento (Sort) può essere costoso, soprattutto se i dati non stanno in RAM. Un classico è ORDER BY.

Esempio di problema:

EXPLAIN ANALYZE
SELECT * 
FROM studenti
ORDER BY cognome;

Potresti vedere qualcosa tipo:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Come velocizzare l’ordinamento? Se ordini spesso per una colonna, crea un indice:

CREATE INDEX idx_cognome ON studenti(cognome);

Ora PostgreSQL può usare l’indice per estrarre i dati già ordinati, evitando l’operazione di sort.

Problema 4: Mancanza di limiti (LIMIT)

Se fai una SELECT senza limitare il numero di righe restituite, la query può processare tutta la tabella anche se ti serve solo la prima riga.

Come si presenta:

EXPLAIN ANALYZE
SELECT * 
FROM studenti
WHERE media > 3.5;

Se il database ha un milione di righe e il filtro media > 3.5 restituisce l’80% della tabella, probabilmente dovrai aspettare un bel po’.

Se ti servono solo i 10 migliori studenti, usa LIMIT:

SELECT *
FROM studenti
WHERE media > 3.5
ORDER BY media DESC
LIMIT 10;

In più, con LIMIT puoi usare anche OFFSET per la paginazione.

Gestione dei parametri di esecuzione: SET

Il comando SET in PostgreSQL serve per modificare i parametri della sessione o della query. È come una configurazione temporanea che influenza il comportamento del database solo nella connessione attuale.

In parole povere, SET è un modo per gestire "l’umore" di PostgreSQL al volo, senza toccare le impostazioni globali.

Dove si usa?

• Cambiare lingua o formato data prima di lanciare un report.
• Aumentare la memoria per una query pesante.
• Disattivare il logging durante un caricamento massivo.
• Cambiare temporaneamente il percorso di ricerca degli schemi (search_path).
• Gestire la sicurezza (tipo abbassare temporaneamente i privilegi di un utente).

Sintassi generale

SET parametro = valore;

Per vedere il valore attuale di un parametro, usa:

SHOW parametro;

Per ripristinare il valore di default:

RESET parametro;

Esempio di ottimizzazione completa

Supponiamo di avere questo compito: trovare gli ultimi 10 studenti con la media più alta (media) che studiano Informatica. Ecco la query di partenza:

SELECT *
FROM studenti
WHERE corso = 'Informatica'
ORDER BY media DESC
LIMIT 10;

1. Analisi della query: Per prima cosa, lancia EXPLAIN ANALYZE:

   EXPLAIN ANALYZE
   SELECT * 
   FROM studenti
   WHERE corso = 'Informatica'
   ORDER BY media DESC
   LIMIT 10;
   

   Se vedi scansione sequenziale e ordinamento, è ora di ottimizzare.

2. Indice su filtro e ordinamento:

   Crea un indice composto che includa entrambe le colonne:

   CREATE INDEX idx_corso_media
   ON studenti(corso, media DESC);
   

3. Verifica i miglioramenti:

   Rilancia EXPLAIN ANALYZE. Ora la query dovrebbe usare l’indice sopra, evitando sort e scansione sequenziale.

Metodo per ottimizzare le query

1. Parti dall’analisi del piano di esecuzione attuale. Usa EXPLAIN ANALYZE per trovare le operazioni problematiche.

2. Trova i colli di bottiglia. Cerca i nodi del piano che richiedono più tempo o risorse.

3. Crea gli indici giusti. Guarda quali colonne sono usate per filtri e ordinamenti e crea gli indici necessari.

4. Riduci il volume dei dati. Usa LIMIT, OFFSET e condizioni di filtro precise.

5. Aggiorna le statistiche. Esegui ANALYZE per assicurarti che PostgreSQL abbia info fresche sulla distribuzione dei dati.

6. Testa le modifiche. Dopo l’ottimizzazione, rilancia EXPLAIN ANALYZE per vedere se le performance sono migliorate.

E ora?

Hai appena fatto un mini-corso express sull’ottimizzazione delle query. Complimenti! Più giocherai con EXPLAIN ANALYZE, più capirai come ragiona PostgreSQL sotto il cofano. E ricorda: nessun indice magico ti salva se la query è troppo complicata o scritta in modo fumoso. SQL, come ogni lingua, ama la chiarezza.