EXPLAIN, ottimizzazione e tuning delle performance

Lunedì mattina, il dashboard impiega dodici minuti a caricare, il PM chiede un filtro in più e il costo del warehouse continua a salire senza che la query diventi più stabile. La tentazione è riscrivere tutto da capo. Prima però conviene capire cosa sta facendo davvero il motore di database, e per quello si parte dal query plan, non dall’intuizione.

Il problema che si vuole risolvere

Con le query analitiche complesse la difficoltà è doppia: scriverle corrette e tenerle veloci, anche quando grain, finestre, coorti e casi limite complicano la logica. Il tuning non è un esercizio accademico per far girare i numeri più in fretta. È il modo di rendere affidabili le decisioni che da quei numeri dipendono, con dati su cui ci si può appoggiare e azioni concrete da prendere.

Come ragionare prima di intervenire

Conviene tenere a mente uno schema semplice che lega la domanda al dato, il dato al controllo e il controllo all’azione. Ogni intervento tecnico, dall’aggiunta di un indice alla riscrittura di un join, ha senso solo se rafforza almeno uno di questi passaggi.

Phase	What to clarify	Output
Question	Which real choice needs improvement?	Decision to make
Measure	Which observable signal represents the problem?	Metric or source data
Control	Which baseline makes the result interpretable?	Credible comparison
Action	What changes after the analysis?	Next operational step

Lo schema operativo

Prima di toccare EXPLAIN conviene fissare alcuni punti. L’unità di lavoro va dichiarata, che si tratti di righe, partizioni, finestre, join, coorti o metriche temporali. Va scelto il segnale principale tra correttezza, performance, presenza di duplicati, grain e stabilità. Serve una baseline, di solito il periodo precedente, un gruppo comparabile o un benchmark. Va detto cosa ci si aspetta di produrre, sia esso una query, un modello, un test o un pattern riusabile. E va riconosciuto il rischio, che resta sempre lo stesso: confondere un numero con una prova.

Element	Requested specification
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Signal	correttezza, performance, duplicati, grain, stabilità
Baseline	periodo precedente, gruppo comparabile, benchmark o controfattuale
Decision	query, intermediate model, SQL test, or reusable pattern
Risk	scambiare un numero disponibile per una prova sufficiente

Lo schema regge quando un altro analista può riprodurre la logica e arrivare alla stessa decisione.

Leggere EXPLAIN: anatomia di un query plan

EXPLAIN mostra cosa fa il database con la query: quali scansioni esegue, come unisce le tabelle, dove aggrega e dove filtra. Vale la pena conoscere le operazioni più ricorrenti e sapere quando devono far suonare un campanello.

Operation	Meaning	Alert if…
Seq Scan / Full Scan	Reads the entire table	Solo su tabelle piccole o quando serve tutto
Index Scan	Uses an index to find rows	Good if selective
Index Only Scan	L’indice copre tutte le colonne	Excellent, no table access
Hash Join	Builds a hash table in memory	OK se la tabella inner è piccola; allarme se grande
Nested Loop	Per ogni riga di A cerca in B	Alert if B is large and without index
Sort	Ordina dati (GROUP BY, ORDER BY)	Allarme se sort su disco (memoria insufficiente)

Caso reale in PostgreSQL

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'IT'
  AND o.order_date >= '2024-01-01'
GROUP BY c.name;

L’output mostra due Seq Scan, un Hash Join e un HashAggregate in memoria. Il collo di bottiglia è la lettura completa di orders, che conta 1.2M righe. Aggiungendo un indice su orders(customer_id, order_date), la Seq Scan diventa Index Only Scan e il tempo cala del 70%.

Strategie di ottimizzazione: una gerarchia di interventi

Gli interventi non hanno tutti lo stesso peso, e conviene affrontarli in ordine. Il primo guadagno arriva quasi sempre riducendo il volume di dati letto, cioè filtrando presto con WHERE su colonne indicizzate o partizionate. Poi si alleggerisce il lavoro di join, usando anti-join, semi-join o pre-aggregazioni dove la logica lo permette. A seguire vengono gli indici compositi e parziali, scelti in modo che coprano le colonne effettivamente richieste. Solo alla fine si mette mano alla memoria per sort e hash join, attraverso parametri come work_mem e join_buffer_size. Saltare l’ordine, partendo dai parametri di memoria su una query che legge tutto, di solito sposta poco.

ClickHouse: un paradigma diverso

ClickHouse è column-oriented e memorizza ogni colonna separatamente. Questo gli permette di leggere solo le colonne che servono, ed è il motivo per cui regge bene le query analitiche su grandi dataset.

Le leve di ottimizzazione qui sono altre. L’ORDER BY della tabella funziona da indice primario e consente di saltare i blocchi inutili. I codec di compressione riducono lo spazio occupato e accelerano la decompressione. Le materialized column spostano all’INSERT il calcolo delle espressioni costose, così non vanno rifatte a ogni query. Le projection, infine, sono mini-tabelle ordinate in modo diverso e pensate per le query più frequenti.

Caso reale: Cloudflare

Cloudflare ha portato una query di aggregazione da 45 a 0.8 secondi con una sola mossa: una materialized column che calcola il paese a partire dall’IP al momento dell’INSERT, evitando di rifare quel calcolo a ogni interrogazione.

Esercizio

Per allenarsi serve un dataset realistico: una tabella orders da 5M righe e una customers da 500K righe vanno benissimo. Si comincia eseguendo EXPLAIN ANALYZE su una query con join e aggregazione e individuando il collo di bottiglia. Poi si aggiunge un indice mirato e si verifica quanto cambia il piano. Infine si riscrive la query usando un anti-join o una pre-aggregazione, confrontando i due piani per capire dove è andato il guadagno.

Vale la pena chiudere l’esercizio con un passaggio decisionale, non solo tecnico. Scrivi quale decisione concreta questa analisi dovrebbe migliorare, qual è la metrica principale e quale rischio va tenuto sotto controllo. Poi costruisci una piccola tabella che metta in fila baseline, segnale osservato, interpretazione prudente e azione consigliata. Chi vuole spingersi oltre può trasformare il tutto in un memo decisionale, con assunzioni esplicite, limiti, criterio di stop e controllo successivo. Come materiale vanno bene un export reale, un dataset sintetico o una dashboard già esistente, purché contengano una domanda, una metrica e una scelta da prendere.

Typical mistake to avoid

L’errore ricorrente è usare EXPLAIN e tuning come etichetta invece che come processo: mostrare grafici senza una decisione collegata, metriche senza baseline, conclusioni senza riconoscere i rischi. C’è una domanda che smaschera subito il problema: se i dati fossero instabili, quale scelta sbaglieresti? Se non sai rispondere, il legame tra analisi e azione non c’è ancora, e nessuna ottimizzazione lo crea al posto tuo.

Domande di controllo

Per fissare le idee, prova a rispondere a queste domande. Quando una Seq Scan è accettabile e quando invece è un problema? Qual è la gerarchia con cui affrontare l’ottimizzazione di una query SQL? Perché ClickHouse è più veloce sulle query analitiche che toccano poche colonne? Cosa sono le materialized column e quale costo spostano? E, la domanda che lega tutto, come si traduce un piano EXPLAIN in una decisione concreta da presentare a chi non legge query?

Operational Summary

EXPLAIN, ottimizzazione e tuning diventano strumenti decisionali solo quando producono scelte chiare e azioni concrete, non quando si fermano al grafico più veloce. La disciplina sta nel collegare dati, modelli e decisioni in un processo che un’altra persona può ripetere e contestare. Applica questo metodo a una decisione reale: chiediti quale scelta deve cambiare, quale evidenza hai in mano, quali assunzioni stai facendo e quale rischio resta aperto. Se il caso non rende visibile questo passaggio, non è ancora pronto per essere automatizzato.