Ottimizzazione Query SQL con EXPLAIN ANALYZE — Trova e Risolvi i Colli di Bottiglia nelle Performance

La tua query impiega secondi invece di millisecondi. Il database rallenta l'applicazione. I clienti aspettano. E tu non sai da dove cominciare per risolvere. Succede a tutti, prima o poi. La differenza sta in come reagisci: o butti risorse hardware sul problema, o impari a leggere quello che il database ti sta già dicendo.

Noi, di Meteora Web, abbiamo ottimizzato query per e-commerce con migliaia di ordini al giorno, per piattaforme SaaS e per siti WordPress con WooCommerce che gestivano cataloghi di decine di migliaia di prodotti. Il primo passo è sempre lo stesso: EXPLAIN ANALYZE. Non è magia, è diagnostica. Te lo mostriamo subito.

Perché le query SQL rallentano e come diagnosticare il problema

Un database relazionale è un motore di calcolo. Ogni query è un piano di esecuzione che il database sceglie tra molteplici alternative. Se il piano è sbagliato – o se mancano le informazioni per scegliere quello giusto – la query diventa lenta. Le cause tipiche: full table scan su tabelle grandi, join senza indici, sort su dataset enormi, subquery non ottimizzate. La diagnosi inizia con un comando: EXPLAIN ANALYZE.

Cos'è EXPLAIN ANALYZE e come si usa

EXPLAIN ANALYZE esegue la query e mostra il piano di esecuzione reale con tempi e conteggi effettivi, non stime. Supportato da PostgreSQL e MySQL 8.0.18+. Su MySQL si chiama EXPLAIN ANALYZE (dal 8.0.18), su MariaDB è ANALYZE SELECT .... Il comando va preposto alla query selettiva.

Comando base e sintassi

EXPLAIN ANALYZE
SELECT o.id, o.data_ordine, SUM(d.importo) AS totale
FROM ordini o
JOIN dettagli_ordine d ON o.id = d.ordine_id
WHERE o.data_ordine >= '2025-01-01'
GROUP BY o.id, o.data_ordine
ORDER BY totale DESC
LIMIT 10;

Esegui questo comando nel tuo client SQL. L'output sarà qualcosa del genere:

Limit  (cost=... actual time=1234.567..1234.789 rows=10 loops=1)
  ->  Sort  (cost=... actual time=1234.567..1234.678 rows=10 loops=1)
        Sort Key: (sum(d.importo)) DESC
        ->  HashAggregate  (cost=... actual time=1234.000..1234.500 rows=5000 loops=1)
              ->  Hash Join  (cost=... actual time=800.000..1000.000 rows=50000 loops=1)
                    Hash Cond: (d.ordine_id = o.id)
                    ->  Seq Scan on dettagli_ordine d  (cost=... actual time=0.000..400.000 rows=100000 loops=1)
                    ->  Hash  (cost=... actual time=0.050..0.050 rows=1000 loops=1)
                          ->  Seq Scan on ordini o  (cost=... actual time=0.000..0.050 rows=1000 loops=1)
                              Filter: (data_ordine >= '2025-01-01'::date)

Ogni riga è un nodo del piano. Leggiamo dal basso verso l'alto: Seq Scan significa scansione sequenziale (full table scan) su dettagli_ordine con 100.000 righe. Tempo reale 400 ms. Pochi millisecondi per la scansione di ordini con filtro data (1000 righe). Poi Hash Join, poi aggregazione, poi Sort, poi Limit. Il collo di bottiglia è evidente: la scansione completa della tabella dettagli_ordine.

Come leggere l'output di EXPLAIN ANALYZE

I campi fondamentali sono:

• actual time: tempo reale di esecuzione del nodo (primo valore = avvio, secondo = fine). In millisecondi.
• rows: numero effettivo di righe processate dal nodo.
• loops: quante volte il nodo è stato eseguito (tipicamente 1, ma può essere >1 in subquery correlate).
• cost: stima del costo (unità arbitrarie) – utile per confronto, ma i tempi reali sono più affidabili.
• actual time per row: puoi calcolarlo (actual time / rows) per capire il costo unitario.

Un tempo alto su un nodo con molte righe indica la necessità di un indice. Un tempo alto su un nodo con poche righe può indicare un problema di configurazione (es. buffer pool troppo piccolo).

Quali sono i colli di bottiglia più comuni nelle query SQL

Dopo anni a leggere piani di esecuzione, abbiamo visto tre pattern ricorrenti:

Full Table Scan (Seq Scan)

Su tabelle con migliaia di righe non è un problema. Su centinaia di migliaia o milioni, diventa drammatico. La soluzione è un indice sulla colonna usata nel WHERE o nella JOIN. Nell'esempio sopra, un indice su dettagli_ordine.ordine_id trasformerebbe il Seq Scan in un Index Scan, riducendo i temi da 400 ms a pochi millisecondi.

Join inefficienti (Nested Loop vs Hash Join vs Merge Join)

Un Nested Loop su due tabelle grandi senza indici può eseguire milioni di iterazioni. Il piano ottimale dipende dalla cardinalità. Se vedi Nested Loop con actual time alto e rows grandi, probabilmente manca un indice nella condizione di join o una statistica aggiornata.

Sort (Order By / Group By) costosi

L'operazione Sort richiede memoria. Se il dataset supera work_mem (PostgreSQL) o sort_buffer_size (MySQL), il database scrive su disco (external sort) e i tempi esplodono. Soluzioni: aumentare il parametro, aggiungere un indice sulla colonna di ordinamento, o limitare i dati prima del sort con WHERE e LIMIT.

Come risolvere i colli di bottiglia con esempi pratici

Vediamo tre azioni concrete che puoi eseguire subito dopo aver letto l'output.

Aggiunta di indici mirati

-- Per il Seq Scan su dettagli_ordine
CREATE INDEX idx_dettagli_ordine_id ON dettagli_ordine (ordine_id);

-- Per il filtro data su ordini
CREATE INDEX idx_ordini_data ON ordini (data_ordine);

-- Per il GROUP BY con ORDINE su totale (indice composito)
CREATE INDEX idx_ordini_data_id ON ordini (data_ordine, id);

Dopo l'indice, riesegui EXPLAIN ANALYZE. Il piano dovrebbe mostrare Index Scan o Index Only Scan e tempi ridotti dell'80-95%.

Riscrittura della query

A volte l'indice non basta. Esempio: ORDER BY (SELECT ...) o subquery correlate. Prova a sostituire con JOIN o window function. Un altro caso tipico: DISTINCT su colonne non indicizzate. Se puoi, ristruttura la logica applicativa per ridurre il set prima della distinct.

Partizionamento delle tabelle

Se una tabella supera i 10 milioni di righe e il filtro principale è temporale, il partizionamento (per mese/anno) può ridurre drasticamente i dati da scansionare. In PostgreSQL:

CREATE TABLE ordini_part (
    id INT,
    data_ordine DATE,
    ...
) PARTITION BY RANGE (data_ordine);

CREATE TABLE ordini_2025_01 PARTITION OF ordini_part
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Poi modifica le query per usare la tabella partizionata. Il query planner salterà automaticamente le partizioni non interessate.

EXPLAIN ANALYZE vs EXPLAIN: quando usare l'uno o l'altro

EXPLAIN mostra solo stime di costo basate sulle statistiche. È veloce (non esegue la query) ma può essere impreciso se le statistiche sono obsolete. EXPLAIN ANALYZE esegue la query e mostra il costo reale. Usa EXPLAIN per un'analisi preliminare su query di test, e EXPLAIN ANALYZE per la diagnosi definitiva su query che puoi permetterti di eseguire (attenzione: scrive dati se la query è DML). Mai su produzione senza controllo.

Cosa fare adesso

1. Abilita il slow query log sul tuo database (es. log_min_duration_statement = 200 in PostgreSQL).
2. Prendi una delle query più lente e appiccica EXPLAIN ANALYZE davanti.
3. Identifica il nodo con il tempo più alto e il maggior numero di righe (Seq Scan, Nested Loop, Sort).
4. Aggiungi l'indice mancante o riscrivi la query.
5. Riesegui EXPLAIN ANALYZE e confronta i tempi.
6. Ripeti fino a che il tempo rientra nella soglia accettabile (di solito sotto i 100 ms per query critiche).

Noi di Meteora Web lo facciamo ogni giorno per i nostri clienti. Se vuoi approfondire, leggi la nostra guida completa su SQL e Database Relazionali oppure contattaci per un audit delle performance del tuo database. Ricorda: un secondo di latenza in più su una pagina e-commerce può costarti il 7% delle conversioni. Non aspettare che sia troppo tardi.