Come il Cost Based Optimizer elabora il piano di esecuzione ottimale per ogni query SQL

In un mondo in cui i database elaborano quotidianamente volumi colossali di informazioni, la performance delle query SQL è diventata una questione cruciale. Al centro di questa battaglia per l’efficienza, il Cost Based Optimizer (CBO) si impone come il direttore d’orchestra invisibile che rivela tutta la potenza di un DBMS. Il suo ruolo è fondamentale: analizzare le diverse strategie di esecuzione possibili, stimare il loro costo stimato in risorse come CPU, input/output su disco o memoria, e selezionare il piano di esecuzione più adatto per la query SQL proposta. Questa capacità di adattamento garantisce sia rapidità sia risparmio di risorse, una necessità per le aziende che gestiscono enormi basi di dati in ambienti sempre più complessi.

Comparso all’inizio degli anni 1980, il Cost Based Optimizer ha profondamente rivoluzionato il modo in cui i database relazionali funzionavano. Prima della sua apparizione, gli ottimizzatori classici applicavano regole rigide spesso inefficaci di fronte alla varietà e all’evoluzione dei dati. Oggi, il CBO si basa su una raccolta fine e dinamica di statistiche su indicizzazioni, distribuzioni dei dati e cardinalità delle tabelle. Questi elementi gli permettono di modellare i diversi percorsi possibili per eseguire una query e di confrontarne i costi. Una cattiva comprensione o una obsolescenza delle statistiche si traduce direttamente in scelte subottimali, a volte catastrofiche.

L’emergere di ambienti cloud, distribuiti o ibridi ha imposto nuovi sfide al Cost Based Optimizer. Il tempo di risposta deve essere minimizzato nonostante la crescente complessità delle fonti dati e le problematiche legate al trasferimento di rete. Inoltre, progressi recenti come l’ottimizzazione adattativa correggono in tempo reale alcuni scostamenti tra previsioni e realtà di esecuzione, garantendo una flessibilità senza precedenti.

Immergiamoci quindi nel mondo affascinante del Cost Based Optimizer, alla scoperta dei suoi meccanismi precisi, delle statistiche sulle quali si basa, degli algoritmi che sceglie, e delle innovazioni che ne plasmano il futuro nell’ottimizzazione avanzata delle query SQL.

Le basi storiche e teoriche del Cost Based Optimizer nei sistemi relazionali

La storia del Cost Based Optimizer inizia realmente nel 1979 nei laboratori IBM, con la pubblicazione di un articolo fondamentale intitolato “Access Path Selection in a Relational Database Management System” attribuito a Patricia Selinger e al suo team. Questo lavoro pone le basi matematiche per valutare e confrontare quantitativamente diversi piani di esecuzione per query SQL, inaugurando un approccio basato sull’efficienza delle risorse consumate piuttosto che su regole statiche.

Prima di questa rivoluzione, i DBMS utilizzavano principalmente ottimizzatori basati su regole (Rule Based Optimizer). Questi seguivano priorità fisse, per esempio privilegiando sistematicamente l’uso degli indici quando possibile, indipendentemente dal contesto reale dei dati. Questa rigidità danneggiava la performance generale, soprattutto per basi con dimensioni eterogenee o in continua evoluzione.

Il concetto introdotto da Selinger si basa dunque sulla nozione di costo stimato. Ogni piano di esecuzione di una query SQL, cioè una sequenza di operazioni sui dati (scansioni, join, ordinamenti…), riceve un valore numerico espresso sia in unità di accesso disco che in cicli CPU. Il CBO genera così un albero di piani alternativi, con ramificazioni che rappresentano diversi algoritmi di join come nested loop, hash join o merge join.

L’ottimizzatore calcola il costo di ciascuno scenario grazie a un modello probabilistico alimentato da statistiche dettagliate: la cardinalità delle tabelle (numero di righe), la selettività dei filtri, e la distribuzione dei dati attraverso istogrammi. Quest’ultimo aspetto consente ad esempio di capire quanto una colonna sia uniformemente o irregolarmente distribuita, influenzando la pertinenza di un indice o di un metodo di ordinamento.

Questo approccio inaugura una gestione dinamica e fine delle query, poiché la scelta del piano ottimale si adatta in base alle caratteristiche dei dati presenti, piuttosto che a una configurazione fissa. Questa innovazione ha influenzato profondamente i sistemi relazionali moderni come Oracle Database, PostgreSQL o SQL Server. Offre un guadagno di performance cruciale per le applicazioni di elaborazione analitica in linea (OLAP), dove miliardi di righe sono interrogate regolarmente.

I progressi teorici iniziati nel 1979 hanno poi portato a una moltitudine di algoritmi di ottimizzazione dei piani, affinati grazie a progressi nelle statistiche e calcoli euristici. Il processo oggi impiega tecniche complesse di ricerca in spazi combinatori enormi, utilizzando per esempio strategie di pruning o metaeuristiche per gestire l’esplosione possibile dei piani quando il numero delle tabelle coinvolte aumenta.

Il ruolo centrale delle statistiche nell’elaborazione del piano di esecuzione ottimale

Il cuore del Cost Based Optimizer si basa indiscutibilmente sulla qualità delle statistiche raccolte. Questi dati descrittivi sulle tabelle, gli indici, la distribuzione e selezione delle righe alimentano le funzioni di stima del costo. Senza una base affidabile, il CBO rischia di generare scelte errate, producendo guadagni miracolosi, ma talvolta enormi perdite di performance.

Tre tipi principali di statistiche governano questi calcoli: la cardinalità, la selettività e gli istogrammi di distribuzione dei valori.

• Cardinalità: Questo parametro indica essenzialmente il numero totale di righe in una tabella o il numero stimato di righe in uscita da un’operazione come un join o un filtro. Questo dato permette di valutare la quantità di dati da trattare.
• Selettività: Specifica la proporzione delle righe selezionate da un predicato dato. Per esempio, la condizione WHERE “age > 50” può filtrare potenzialmente il 20% o solo il 5% delle righe a seconda della distribuzione dei dati.
• Istogrammi: Questi descrivono la distribuzione reale dei valori nelle colonne. Sono sequenze di frequenze che permettono di anticipare distribuzioni non uniformi – un buon CBO si basa su questa profondità per aggiustare le sue stime.

I sistemi di gestione come Oracle offrono procedure integrate come DBMS_STATS.GATHER_TABLE_STATS per automatizzare la raccolta e l’aggiornamento di queste statistiche. Questo processo è generalmente pianificato quotidianamente per garantirne la freschezza. PostgreSQL utilizza il demone autovacuum insieme al comando ANALYZE per rilevare modifiche e aggiornare automaticamente i dati al superamento di una soglia (salvo configurazione specifica). SQL Server attiva di default la proprietà AUTO_UPDATE_STATISTICS con lo stesso scopo.

Questi meccanismi di aggiornamento sono cruciali perché la minima obsolescenza delle statistiche provoca stime errate. Per esempio, dati superati portano il CBO a supporre che un indice sia ottimale per un join, quando in realtà una scansione sequenziale sarebbe più rapida. Questo tipo di errore può moltiplicare i tempi di esecuzione per 10 o anche 100, a seconda del volume.

Per monitorare continuamente la qualità dei dati statistici, soluzioni di terze parti come SolarWinds Database Performance Analyzer o pgStatsTuner si sono imposte in ambienti professionali. Esse segnalano in caso di degrado e forniscono report completi che permettono ai DBA di intervenire rapidamente, garantendo la pertinenza delle scelte del CBO giorno dopo giorno.

Come la granularità delle statistiche influenza la scelta degli algoritmi

Basi come PostgreSQL permettono di modificare il parametro default_statistics_target che controlla la finezza degli istogrammi. Più alta è la granularità, più il CBO ha informazioni precise per calcolare il costo stimato di ogni fase. In cambio, questo aumento genera un sovraccosto nella raccolta.

Per esempio, in una query che coinvolge tre tabelle, il CBO può generare mezza dozzina di piani di join potenziali, modulando i metodi (nested loop, hash join, merge join) secondo la selettività. Per query complesse con otto tabelle o più, le alternative si contano a centinaia o migliaia, rendendo la qualità delle statistiche ancora più decisiva per potare efficacemente lo spazio di ricerca.

Scelta dell’algoritmo e strategie di join: nested loop, hash join e merge join

Una delle decisioni principali del Cost Based Optimizer riguarda il tipo di join da applicare tra più tabelle coinvolte in una query SQL. Si distinguono tre algoritmi principali: il nested loop join, il hash join e il merge join. La scelta ottimale dipende essenzialmente dal volume dei dati, dalla presenza di indici e dalle statistiche disponibili.

Il nested loop join è spesso preferito quando la tabella esterna è piccola e la tabella interna è indicizzata. Funziona come due cicli annidati, testando ogni riga della tabella esterna con quelle corrispondenti della tabella interna. La sua semplicità è efficace su piccoli volumi, ma la complessità cresce quadraticamente con la dimensione dei dati.

Il hash join si basa su una fase di costruzione di una tabella hash in memoria a partire da una delle tabelle, seguita da una fase di sondaggio delle tuple della seconda tabella tramite questa struttura. Questo meccanismo è particolarmente efficiente su grandi tabelle non indicizzate e quando la memoria disponibile è sufficiente a contenere la struttura hash, riducendo drasticamente il tempo di elaborazione rispetto al nested loop.

Il merge join sfrutta l’ordinamento dei dati. Le due tabelle sono ordinate sulla chiave di join, il che permette di fondere semplicemente le righe corrispondenti senza ricerche ripetute. Questo metodo è altamente efficiente per insiemi già ordinati o indicizzati, ma la fase di ordinamento preliminare può generare un sovraccarico in termini di risorse.

Il Cost Based Optimizer valuta queste alternative in base al suo modello di costo stimato e alla disponibilità delle indicizzazioni. Ad esempio, su un volume elevato dove l’indice è frammentato o parzialmente invalido, l’hash join può prevalere nonostante il maggior bisogno di memoria. Al contrario, su tabelle di piccola dimensione, il nested loop è spesso il più veloce.

I sistemi moderni come Oracle o PostgreSQL integrano modulatori nell’ottimizzatore, che permettono al CBO di adottare piani ibridi. Possono così iniziare con un join nested loop su sottoinsiemi di dati, per poi passare a un hash join su altri segmenti, massimizzando così la performance complessiva.

L’analisi dei piani di esecuzione per migliorare l’ottimizzazione delle query SQL

La comprensione approfondita del piano di esecuzione generato dal Cost Based Optimizer è indispensabile per tutti gli sviluppatori e amministratori che desiderano padroneggiare la performance delle query SQL nei loro sistemi.

Un piano di esecuzione descrive dettagliatamente la sequenza di operazioni che il motore del database esegue, comprendendo gli accessi alle tabelle, i metodi di lettura (full scan, index scan), i diversi tipi di join e l’ordinamento dei dati. Ogni fase è associata a un costo stimato calcolato dalle statistiche, che rappresenta il consumo previsto di CPU, memoria o accessi disco.

L’esplorazione di questo piano permette di identificare in particolare:

• Gli scan costosi legati a indici usati in modo inefficace o assenti.
• Scelte di join subottimali che generano cicli esponenziali.
• Operazioni di ordinamento e raggruppamento che possono essere ridotte o evitate.
• L’impatto di clausole WHERE complesse sulla cardinalità stimata.

Nel 2026, un esempio ricorrente riguarda un’azienda di e-commerce che analizza le sue transazioni giornaliere. In una query SQL su più tabelle, l’analisi del piano di esecuzione ha mostrato che il CBO sottostimava massicciamente la cardinalità di un join, provocando un nested loop inefficace. Dopo l’aggiornamento e la raccolta precisa delle statistiche, il CBO ha scelto un hash join più adatto, riducendo il tempo di risposta dell’85%.

I DBMS moderni offrono strumenti grafici per visualizzare i piani di esecuzione. SQL Server Management Studio propone viste dettagliate, Oracle SQL Developer integra rappresentazioni ad albero, e PostgreSQL offre EXPLAIN ANALYZE, uno strumento che combina piano e risultati reali per affinare l’analisi.

È anche comune utilizzare hint o direttive nella query SQL per forzare temporaneamente l’uso di un piano specifico quando il CBO sbaglia. Tuttavia, questa pratica deve rimanere eccezionale perché limita la capacità di adattamento dinamico del motore e può deteriorare le performance a medio termine.

Limitazioni attuali e sfide del Cost Based Optimizer di fronte a query complesse

Nonostante i progressi importanti, il Cost Based Optimizer affronta difficoltà crescenti, specialmente quando le query diventano molto complesse, coinvolgendo molte tabelle, aggregazioni o schemi dimensionali sofisticati. Infatti, ogni errore nella stima iniziale della cardinalità può propagarsi e amplificarsi attraverso i passaggi successivi, un fenomeno noto come amplificazione degli errori di stima.

Gli schemi a stella dei data warehouse illustrano bene questo problema: i join multipli su tabelle di fatti di grandi dimensioni e sulle loro dimensioni inducono una cascata di stime a volte distorte. In alcuni casi, il piano scelto può essere subottimale nel 15-25% delle query, secondo benchmark TPC-DS pubblicati nell’ultimo decennio.

Per far fronte a queste sfide, diverse basi hanno integrato meccanismi detti di ottimizzazione adattativa. Per esempio, Oracle 12c ha introdotto l’Adaptive Query Optimization, capace di correggere durante l’esecuzione un piano inizialmente valutato subottimale, rivalutando le statistiche osservate realmente. PostgreSQL 14 e SQL Server 2022 hanno anche migliorato il loro stimatore di cardinalità modellando più precisamente la correlazione tra colonne, riducendo l’errore di un fattore da tre a cinque in alcuni casi.

Tuttavia, i predicati complessi su colonne correlate rimangono un punto debole, poiché la raccolta statistica automatica non cattura sempre queste dipendenze. Alcuni strumenti di machine learning esplorano oggi approcci ibridi, usando lo storico di esecuzione per modellare meglio questi aspetti difficili.

Il Cost Based Optimizer negli ambienti cloud e distribuiti: nuove sfide e adattamenti

Con la massiccia crescita del cloud computing e delle architetture distribuite, il Cost Based Optimizer evolve per gestire contesti ancora più complessi. La sfida consiste nell’ottimizzare query che sfruttano dati dispersi su cluster di più nodi, spesso con formati di archiviazione columnar come Parquet o ORC.

Il concetto classico deve integrare un nuovo fattore: il costo di rete generato dal trasferimento tra nodi. Mentre in un sistema centralizzato contano solo risorse CPU e disco, in un ambiente distribuito il CBO deve anche minimizzare la quantità di dati scambiati per evitare latenza e congestione di rete.

Progetti come Apache Spark hanno dato il via nel 2017 con l’introduzione di un CBO nativo attivato tramite spark.sql.cbo.enabled=true, capace di generare guadagni da 2 a 8 volte su join multi-tabella. Allo stesso modo, Presto (ora Trino) ha sviluppato un modello specifico basato sull’annotazione dei costi nell’albero di piano percorso nodo per nodo.

Sul fronte dei giganti come Google BigQuery, il CBO è proprietario e invisibile all’utente finale, che beneficia comunque di un’ottimizzazione dinamica automatica. La sfida principale risiede nella qualità delle statistiche raccolte su fonti eterogenee, che vanno dai data lake ai connettori JDBC verso database tradizionali. La mancanza di statistiche robuste costringe talvolta i motori ad adottare euristiche generiche, degradando la qualità finale dei piani.

Gli attori del dato devono quindi impegnarsi ad arricchire e standardizzare i dati statistici in questi ecosistemi ibridi, per garantire l’efficacia del cost based optimizer e ottimizzare i costi di esecuzione in cloud dove ogni risorsa consumata si traduce in una spesa finanziaria.

Costi, licenze e differenze funzionali degli ottimizzatori basati sul costo nel 2026

Il mercato nel 2026 presenta un’offerta ricca di soluzioni che integrano ottimizzatori basati sul costo, ma funzionalità avanzate come l’ottimizzazione adattativa o l’aggiornamento automatico delle statistiche restano spesso riservate a livelli di licenza premium.

I seguenti tavoli illustrano bene questa segmentazione tariffaria e funzionale:

Soluzione	Edizione	Ottimizzatore Adattativo	Aggiornamento Automatico Statistiche	Prezzo Indicativo
Oracle Database	Enterprise Edition	Sì (AQO)	Sì (DBMS_STATS)	~25.000 € / processore
Oracle Database	Standard Edition 2	No	Parziale	~5.000 € / processore
SQL Server	Enterprise	Sì (CE v160)	Sì (AUTO_UPDATE)	~14.256 € / core
SQL Server	Standard	Limitato	Sì (AUTO_UPDATE)	~3.945 € / core
PostgreSQL	Open Source	Parziale (v14+)	Sì (autovacuum)	Gratuito
Google BigQuery	On-demand	Sì (proprietario)	Sì (automatico)	~6 $ / TB processato
Apache Spark	Open Source	CBO nativo dalla v2.2+	Manuale	Gratuito (infra a parte)
Databricks	Enterprise (DBU)	Sì (Photon Engine)	Sì (Delta Statistics)	~0,75 $ / DBU

Questa tabella sottolinea quanto il deployment di un Cost Based Optimizer efficace dipenda non solo da algoritmi e statistiche, ma anche dai budget e dalle esigenze aziendali. Per ambienti ad alta volumetria e con forti esigenze di performance, l’investimento in edizioni avanzate è spesso ampiamente giustificato dai risparmi di tempo e efficienza.

Fasi chiave dell’ottimizzazione di una query SQL con il Cost Based Optimizer

Per comprendere meglio la complessità del processo, ecco una descrizione semplificata che illustra come un Cost Based Optimizer elabora un piano di esecuzione ottimale:

1. Analisi sintattica: Il motore traduce la query SQL in una rappresentazione ad albero delle operazioni possibili.
2. Riscrittura e semplificazione: Alcune regole semplificano o trasformano la query per ridurre lo spazio di ricerca.
3. Raccolta delle statistiche: Esame di tabelle, indici, istogrammi, cardinalità e selettività disponibili.
4. Esplorazione dei piani: Generazione di un insieme di alternative di esecuzione, combinando tipi di join, ordini di operazioni e metodi di accesso.
5. Costo stimato: Calcolo del costo predittivo di ogni scenario basato su statistiche e modelli.
6. Selezione del piano: Scelta del piano con il costo totale più basso.
7. Esecuzione: Avvio della query secondo il piano selezionato.
8. Ottimizzazione adattativa (nei sistemi supportati): Possibili aggiustamenti dinamici se la realtà dell’esecuzione diverge.

Ogni fase è essenziale per ottenere un piano ottimale. Alcune basi come Oracle o SQL Server integrano attività specifiche durante la raccolta per prevedere l’effetto di piani paralleli o parzialmente disruptive, il che complica ulteriormente l’algoritmo.

L’intero processo spiega perché il tuning delle performance SQL è una professione a sé, che unisce conoscenza profonda del DBMS, statistica informatica ed esperienza sul campo.