CTE, sottoquery o tabella temporanea

Quando una query necessita di un risultato intermedio, dispone di tre strumenti comuni: una sottoquery, una CTE e una tabella temporanea. Gli intervistatori chiedono di confrontarli perché la scelta rivela se comprende la materializzazione e il comportamento dell’ottimizzatore.

Questa lezione costruisce un criterio decisionale che potrà esporre anche sotto pressione.

Una sottoquery è una query incorporata all’interno di un’altra, spesso in FROM, WHERE o SELECT. Fa parte della stessa istruzione e l’ottimizzatore la considera come un’unica unità.

• Non è necessario assegnarle un nome, anche se le tabelle derivate richiedono un alias.
• L’ottimizzatore è libero di integrarla nella query esterna.
• Diventa prolissa e difficile da leggere quando è profondamente annidata.

Una CTE è una sottoquery con nome all’interno di un blocco WITH, con ambito limitato a una sola istruzione. È più leggibile di una sottoquery profondamente annidata e può essere utilizzata più volte.

• Ha un nome, quindi l’intento è documentato.
• Può essere utilizzata più di una volta nella stessa istruzione.
• Rimane comunque limitata a una singola istruzione, dopodiché scompare.

Una tabella temporanea è una tabella reale e fisica che rimane disponibile per la sessione o la transazione. La popola con un’istruzione e la interroga in istruzioni successive e separate.

• Rimane disponibile per più istruzioni nella sessione.
• Può avere indici e statistiche.
• Comporta operazioni di I/O su disco e una pulizia esplicita.

Il concetto chiave che gli intervistatori verificano è la materializzazione: se il risultato intermedio viene scritto fisicamente da qualche parte.

• Le sottoquery e le CTE di solito non vengono materializzate; spesso l’ottimizzatore le integra direttamente nella query.
• Una tabella temporanea viene sempre materializzata nella memoria di archiviazione.
• Alcuni database consentono di forzare o impedire la materializzazione delle CTE tramite hint.

Storicamente, PostgreSQL trattava ogni CTE come una barriera all’ottimizzazione, materializzandola e impedendo il push-down dei predicati. A partire da Postgres 12, le CTE semplici non ricorsive a cui si fa riferimento una sola volta vengono integrate direttamente per impostazione predefinita, con gli hint MATERIALIZED e NOT MATERIALIZED per sovrascrivere questo comportamento.

Menzionare questa precisazione è un ottimo segnale di seniority.

Se fa riferimento più volte allo stesso risultato intermedio in un’unica istruzione, una CTE può essere più chiara rispetto alla ripetizione di una sottoquery. Tuttavia, attenzione: una CTE integrata direttamente potrebbe essere ricalcolata a ogni riferimento.

Quando il ricalcolo è costoso, forzare la materializzazione o utilizzare una tabella temporanea evita di ripetere il lavoro.

Le CTE e le sottoquery rimangono disponibili solo per una singola istruzione. Se serve lo stesso risultato in diverse query separate, la tabella temporanea è lo strumento giusto.

Un caso tipico è un processo ETL o un report in più passaggi, in cui si crea una volta un insieme di dati di staging e poi si eseguono diverse analisi su di esso. Aggiungere un indice alla tabella temporanea può quindi velocizzare ogni query successiva.

Solo una tabella temporanea può contenere indici e statistiche aggiornate. Per un enorme insieme intermedio unito molte volte, questo può essere determinante.

• CTE/sottoquery: l’ottimizzatore effettua le stime basandosi sulle tabelle sottostanti.
• Tabella temporanea: può eseguire ANALYZE su di essa e aggiungere indici ottimizzati per le unioni successive.

Per risultati grandi e riutilizzati intensivamente, una tabella temporanea può quindi offrire prestazioni migliori nonostante i passaggi aggiuntivi.

Una risposta chiara per il colloquio:

• Sottoquery: utilizzo una tantum, poco annidata, leggibilità adeguata.
• CTE: migliora la leggibilità oppure viene utilizzata alcune volte nella stessa istruzione.
• Tabella temporanea: riutilizzata tra più istruzioni, molto grande oppure quando servono indici o statistiche.

Come impostazione predefinita, scelga una CTE per chiarezza; ricorra a una tabella temporanea quando la materializzazione o il riutilizzo tra istruzioni apportano un vantaggio concreto.

Eviti affermazioni assolute come «le CTE sono sempre più lente». Dica invece: le CTE e le sottoquery vengono di solito integrate direttamente, quindi riguardano soprattutto la leggibilità; una tabella temporanea viene materializzata ed è utile quando riutilizzo un risultato grande tra più istruzioni o ho bisogno di un indice.

Riconoscere che il comportamento dipende dal motore e, nel caso di Postgres, anche dalla versione, dimostra una comprensione reale.

Scelga lo scenario in cui una tabella temporanea è chiaramente la scelta migliore.

La scelta dipende dalla materializzazione e dall’ambito.

• Sottoquery e CTE: di solito integrate direttamente, con ambito limitato a una sola istruzione, scelte per la leggibilità.
• Le CTE aggiungono nomi e consentono il riutilizzo all’interno della stessa istruzione.
• Tabelle temporanee: sempre materializzate, disponibili tra più istruzioni e indicizzabili.
• Postgres 12+ integra direttamente le CTE semplici; utilizzi gli hint MATERIALIZED per controllare questo comportamento.

Prossimo argomento: trasformare una query annidata e complessa in CTE chiare.