Ottimizzazione delle query con CTE

Oggi ci tuffiamo nel mondo affascinante (e un po’ spaventoso) dell’ottimizzazione delle query usando i CTE (Common Table Expressions). Se hai già imparato a creare i CTE (ne abbiamo parlato nelle lezioni precedenti), ora è il momento di parlare dei dettagli interni, dei “tranelli” e di come spremere il massimo dell’efficienza da questi strumenti.

A prima vista, i CTE sembrano perfetti: sono puliti, facili da scrivere, ti permettono di dividere il codice in blocchi logici. Ma c’è una piccola (o non così piccola) particolarità. PostgreSQL ha una strategia specifica per lavorare con i CTE che influisce sulle performance.

Quando PostgreSQL vede WITH, di solito materializza il risultato del CTE. Questo significa che i dati restituiti dal CTE vengono prima calcolati e salvati come tabella temporanea, che poi viene usata nella query principale. È comodo se devi riutilizzare i dati, ma può diventare un problema se:

1. La quantità di dati nel CTE è enorme, ma il risultato viene usato solo in parte.
2. Il CTE viene chiamato troppe volte, aumentando l’overhead.
3. Creiamo CTE inutilmente complessi che in realtà non servono.

Facciamo conoscenza con la materializzazione

La materializzazione è il processo in cui PostgreSQL salva il risultato del CTE in memoria o su disco (a seconda della dimensione dei dati). Questo significa che i dati vengono estratti solo una volta, ma se usi il CTE solo in un punto, la materializzazione può essere superflua. Per esempio:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

In questo caso PostgreSQL prima crea una tabella temporanea con il risultato completo del CTE (grade > 60), poi filtra le righe dove grade > 90. Questo aggiunge un passaggio intermedio inutile e impatta sulle performance.

Come evitare la materializzazione inutile?

Da PostgreSQL 12 è possibile evitare la materializzazione del CTE quando non serve. Si usano le parole chiave MATERIALIZED (default) o NOT MATERIALIZED. Esempio:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Qui diciamo a PostgreSQL di non materializzare i dati di large_set, ma di integrare la query direttamente nell’espressione principale. Così la query è più efficiente, perché non viene creata una tabella intermedia.

Quando la materializzazione è utile?

Non pensare che la materializzazione sia sempre negativa! Se i dati del CTE vengono usati più volte nella query o devono essere calcolati in modo indipendente, la materializzazione può essere utile. Esempio:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Qui la materializzazione evita di ricalcolare il filtro grade > 60 più volte.

Ottimizzazione delle query con gli indici

Per far andare più veloci i CTE, dovresti usare gli indici sulle tabelle di base da cui prendi i dati. Per esempio:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

L’indice sulla colonna grade permette a PostgreSQL di estrarre più velocemente le righe che rispettano la condizione grade > 90. Questo è fondamentale quando lavori con tabelle grandi.

Scomporre i CTE grandi in più piccoli

Se un CTE restituisce tanti dati che poi vengono filtrati o aggregati, è meglio dividerlo in più passaggi. Invece di un CTE complicato, è più comodo crearne diversi più semplici:

Male (CTE grande):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Meglio (suddiviso in passaggi):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

Questo approccio aiuta PostgreSQL a ottimizzare meglio l’esecuzione delle query.

Esempio pratico: analisi della struttura e ottimizzazione

Vediamo un esempio più complesso. Abbiamo le tabelle degli studenti, dei corsi e dei voti. Vogliamo trovare gli studenti con una media alta e mostrare la loro lista insieme ai corsi corrispondenti:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Questa query si può ottimizzare aggiungendo indici alle tabelle grades e enrollments, così filtraggio e join saranno più veloci.

Monitoring: analisi delle performance

Per capire quanto è efficiente una query, usa EXPLAIN o EXPLAIN ANALYZE. Per esempio:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Questa query ti mostra quanto tempo impiega ogni passaggio e ti aiuta a capire dove puoi migliorare le performance.

Parleremo di EXPLAIN ANALYZE più in dettaglio nei prossimi livelli :P

Errori comuni nell’ottimizzazione dei CTE

1. Dimenticarsi degli indici. Se filtri i dati in un CTE ma la tabella di base non ha un indice, le performance ne risentono.
2. Usare CTE troppo grandi. Se una query fa troppe cose, può portare alla materializzazione di grandi quantità di dati.
3. Abusare di NOT MATERIALIZED. In certi casi la materializzazione è necessaria per evitare di ricalcolare il CTE più volte.
4. Ignorare il monitoring. Senza analizzare con EXPLAIN potresti non accorgerti che le query sono lente.

Ora sei pronto a ottimizzare le query con i CTE, evitando le trappole e aumentando le performance! Ricorda che i CTE sono uno strumento, non una soluzione magica. Usali con criterio e diventeranno i tuoi migliori amici in PostgreSQL.