Errori tipici quando lavori con CTE e come evitarli

Ed eccoci arrivati al lato oscuro dei CTE — gli errori tipici. Anche la query più figa può andare in crash se usi male questi strumenti potenti. Ma tranquillo, abbiamo una guida pronta per te su come diagnosticarli e prevenirli!

1. Errore: materializzazione dei CTE e le sue conseguenze

Una delle cose fondamentali di PostgreSQL quando lavori con i CTE è la loro materializzazione di default. Vuol dire che il risultato del CTE viene processato e salvato temporaneamente in memoria (o su disco, se i dati sono troppi). Se hai tante query o una mole di dati enorme, questo può rallentare parecchio l’esecuzione.

Esempio:

WITH heavy_data AS (
    SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;

A prima vista sembra che il CTE filtri semplicemente i dati. Ma in realtà heavy_data viene prima caricato e materializzato tutto, e solo dopo viene applicato il filtro. Questo può richiedere un sacco di tempo.

Come evitarlo?

Dalla versione 12 di PostgreSQL puoi usare i CTE come inline expression (tipo subquery), risolvendo il problema della materializzazione. Basta che il CTE venga usato una sola volta e non serva salvare risultati intermedi.

Esempio di approccio ottimizzato:

WITH inline_data AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;

Consiglio: se vuoi che la materializzazione avvenga, scrivi MATERIALIZED. Se non vuoi — usa NOT MATERIALIZED.

2. Errore: CTE ricorsivi che vanno in loop infinito

I CTE ricorsivi sono una bomba, ma se li usi senza limiti sulla profondità delle iterazioni rischi di creare loop infiniti. Non solo rallenta tutto, ma ti mangia anche tutte le risorse disponibili.

Esempio:

WITH RECURSIVE endless_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM endless_loop
)
SELECT * FROM endless_loop;

Questa roba genera righe all’infinito, perché manca una condizione che fermi la ricorsione.

Come evitarlo?

Aggiungi una condizione di stop chiara usando WHERE. Tipo così:

WITH RECURSIVE limited_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM limited_loop
    WHERE value < 10
)
SELECT * FROM limited_loop;

Consiglio: se usi CTE ricorsivi per grosse gerarchie, limita la profondità della ricorsione con l’opzione max_recursion_depth di PostgreSQL.

3. Errore: uso sbagliato di UNION e UNION ALL

Quando unisci la query base e quella ricorsiva in un CTE, scegliere male tra UNION e UNION ALL può darti risultati strani. Per esempio, UNION elimina le righe duplicate, ma questo ha un costo computazionale extra.

Esempio:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION  -- Qui sarebbe meglio usare UNION ALL

    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

In questo esempio UNION può togliere righe importanti dalla gerarchia se si ripetono per sbaglio. E in più rallenta la query!

Come risolvere?

Usa UNION ALL se non hai bisogno di togliere i duplicati:

UNION ALL

4. Errore: troppi CTE in una sola query

Nel tentativo di rendere la query super strutturata, qualcuno inizia ad aggiungere decine di CTE. Non solo il codice diventa un casino, ma anche il planner di PostgreSQL va in crisi.

Esempio:

WITH cte1 AS (...),
     cte2 AS (...),
     cte3 AS (...),
     ...
     cte20 AS (...)
SELECT ...
FROM cte20;

Sembra un incubo notturno per qualsiasi dev.

Come risolvere?

— Dividi la query in più parti semplici. Invece di un’unica mega-query con decine di CTE, fai più query indipendenti.

— Un’altra opzione: per risultati intermedi che ti servono più volte, salva i dati in tabelle temporanee.

5. Errore: CTE complessi senza indici

Se il CTE lavora con tanti dati ma ti dimentichi di mettere gli indici sulle tabelle, le query saranno lentissime. Gli indici sono come il turbo per il tuo database.

Esempio:

WITH filtered_data AS (
    SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;

Come risolvere?

Prima di usare i CTE, assicurati che le tue tabelle siano ottimizzate:

CREATE INDEX idx_large_table ON large_table(unindexed_column);

6. Errore: usare i CTE per richiamare dati più volte

Il CTE viene creato, eseguito e poi “congelato”. Se ti serve il risultato in più punti, i dati non verranno ricalcolati — e a volte questo porta a errori.

Esempio:

WITH data AS (
    SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- Se ti serve ricalcolare data, non succederà.

Come risolvere?

Se ti serve dinamicità o ricalcolo, forse il CTE non è la scelta giusta. Usa le subquery.

7. Errore: niente commenti

I CTE sono uno strumento pazzesco, ma chi vuole una query SQL complicata che nessuno, nemmeno tu, capirà tra due settimane?

Esempio:

WITH data_filtered AS (
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

Tra un mese nessuno si ricorderà perché questi dati venivano filtrati!

Quindi commenta le query, soprattutto se usi CTE complessi o ricorsivi:

WITH data_filtered AS (
    -- Filtro dei dati dove il valore di some_column > 100
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

8. Errore: abuso dei CTE invece delle tabelle temporanee

A volte le tabelle temporanee sono molto meglio. Per esempio, se il risultato ti serve più volte in query diverse o lavori con un set di dati enorme.

Esempio:

WITH temp_data AS (
    SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;

Una query così con i CTE verrà eseguita due volte, anche se i dati non cambiano!

Come risolvere?

Crea una tabella temporanea se i dati ti servono più volte:

CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;

SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;

Consiglio finale

Come per ogni funzione potente, quando lavori con i CTE è importante capire che non sono sempre la soluzione migliore. Pensa bene a perché e come li usi. L’approccio “più CTE, meglio è” può davvero peggiorare le performance e la leggibilità del codice. E ovviamente, non dimenticare di fare test di performance e ottimizzare le query.