Analisi delle performance di funzioni e procedure: uso di EXPLAIN ANALYZE

EXPLAIN ANALYZE ti aiuta a capire come PostgreSQL "ragiona" quando esegue la tua query:

• Quali step vengono fatti per processare i dati.
• Quanto tempo ci mette ogni step.
• Perché una certa query è lenta — magari fa uno scan completo della tabella (Seq Scan) o manca un indice.

Il comando EXPLAIN ANALYZE in pratica esegue la query e mostra come PostgreSQL ottimizza l’esecuzione. È come se smontassi un orologio per capire come funziona il meccanismo. EXPLAIN ANALYZE fa lo stesso, ma con le tue query SQL.

Sintassi di EXPLAIN ANALYZE

Partiamo easy. Ecco la forma base del comando:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Questa query esegue il comando SELECT e mostra come PostgreSQL processa i dati.

L’output di EXPLAIN ANALYZE è un albero di esecuzione della query. Ogni livello dell’albero descrive uno step che PostgreSQL fa:

• Operation Type — tipo di operazione (tipo Seq Scan, Index Scan).
• Cost — quanto PostgreSQL pensa che costi quell’operazione.
• Rows — quante righe si aspetta e quante ne ha trovate davvero.
• Time — quanto tempo ha impiegato l’operazione.

Esempio di output:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Guarda Seq Scan on students. Significa che PostgreSQL sta leggendo TUTTE le righe della tabella students. Se la tabella è grossa, può essere MOLTO LENTO.

Esempi di uso di EXPLAIN ANALYZE

Dai, vediamo qualche esempio pratico dove impari a trovare e risolvere problemi nelle query.

Esempio 1: scan completo della tabella

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Il problema qui è che PostgreSQL fa un Seq Scan, cioè scorre tutte le righe della tabella. Se ci sono milioni di righe, diventa un collo di bottiglia per la performance.

Soluzione: creiamo un indice sulla colonna age.

CREATE INDEX idx_students_age ON students(age);

Ora esegui di nuovo la stessa query:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Ora vediamo Index Scan invece di Seq Scan. Grande, la query ora vola!

Esempio 2: query complessa con JOIN

Immagina di avere due tabelle: students e courses. Vogliamo sapere i nomi degli studenti e i nomi dei corsi a cui sono iscritti.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

L’output potrebbe essere tipo così:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Come vedi, PostgreSQL tiene tutto sotto controllo: usa gli indici sulle tabelle enrollments e courses e l’esecuzione è veloce. Ma se manca qualche indice, potresti vedere un Seq Scan che rallenta tutto.

Ottimizzazione delle performance delle funzioni

Ora immagina di avere una funzione che restituisce la lista degli studenti più vecchi di una certa età:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Possiamo analizzare la performance di questa funzione con EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Velocizzare l’esecuzione della funzione

Se la funzione è lenta, probabilmente il problema è lo scan completo della tabella. Per sistemare:

1. Assicurati che la colonna usata nei filtri (age) abbia un indice.
2. Controlla quante righe ci sono nella tabella e valuta il partizionamento se i dati sono troppi.

Colli di bottiglia e come risolverli

1. Scan completo delle tabelle (Seq Scan). Usa gli indici per velocizzare la ricerca delle righe. Ma occhio: troppi indici possono rallentare gli insert.

2. Troppe righe nel risultato. Se la query restituisce milioni di righe, pensa di aggiungere filtri (WHERE, LIMIT) o paginazione (OFFSET).

3. Operazioni "costose". Alcune operazioni, tipo sort, aggregazioni o join di tabelle grosse, possono usare un sacco di risorse. Usa indici o spezza la query in più step.