Introduzione all'analisi delle performance delle query

Lascia che ti racconti una storia vera. Immagina di lavorare come dev su una grossa piattaforma di e-commerce. Tutto fila liscio, tranne un dettaglio: il report delle vendite mensili è così lento che gli utenti riescono a fare un altro ordine prima che finisca di caricarsi. Tu sei in ansia, il tuo manager pure, il server PostgreSQL sembra un bollitore che sta per esplodere, e tutto sa un po' di apocalisse. Ora immagina di poter trovare la causa e sistemarla in un colpo solo.

L'analisi delle performance delle query è come un check-up medico per il tuo PostgreSQL. Ti aiuta a trovare i "colli di bottiglia" (dolori) e a sistemarli per migliorare l'esperienza utente e risparmiare risorse di sistema.

Come funzionano le query sotto il cofano di PostgreSQL?

Quando scrivi una query semplice tipo:

SELECT * FROM products WHERE price > 100;

PostgreSQL non si butta subito a prendere i dati. Prima analizza la tua query, si inventa il modo migliore per eseguirla, e solo dopo parte davvero.

Fasi principali dell'esecuzione delle query:

1. Parsing. PostgreSQL controlla la tua query per errori di sintassi e la trasforma in una rappresentazione intermedia.
2. Ottimizzazione. L'ottimizzatore valuta vari modi di eseguire la query e sceglie quello più "economico" (in termini di tempo e risorse).
3. Esecuzione. Il server segue il piano scelto ed estrae i dati.

Cos'è un "collo di bottiglia"?

Un "collo di bottiglia" è la parte della query che rallenta tutto il resto. Può essere un'operazione che, a sorpresa, consuma più tempo o risorse del previsto. Per esempio, se PostgreSQL invece di usare un indice veloce fa una scansione completa della tabella (Seq Scan), la query va lenta. Oppure se ci sono molti più dati del previsto e il server perde un sacco di tempo a ordinare, unire, filtrare.

Questi sono i momenti da cercare e ottimizzare per primi: sono proprio i colli di bottiglia.

Strumenti per l'analisi delle performance delle query

PostgreSQL ha diversi strumenti potenti che ti aiutano a trovare i problemi nelle query:

1. EXPLAIN e EXPLAIN ANALYZE. Questi comandi ti mostrano come PostgreSQL pensa di eseguire la query, o la eseguono davvero per misurare le performance reali.

• EXPLAIN: mostra il piano di esecuzione della query senza eseguirla davvero.
• EXPLAIN ANALYZE: esegue la query e mostra il piano reale con le metriche di tempo effettive.

Esempio di utilizzo di EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Output:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Qui si vede che la query fa un "Seq Scan" — cioè una scansione completa della tabella, che non è efficiente su tabelle grandi.

2. pg_stat_statements. Questa è un'estensione extra che tiene traccia delle query eseguite. Ti mostra:

• Quali query vengono eseguite sul server.
• Quanto tempo impiega ogni query.
• Quante righe restituisce la query e quante risorse consuma.

Per attivare pg_stat_statements, devi:

1. Abilitare l'estensione:

CREATE EXTENSION pg_stat_statements;

2. Configurare PostgreSQL: Nel file postgresql.conf aggiungi:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Riavvia PostgreSQL.

Ora puoi analizzare le query:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Questo ti mostra le 5 query più "pesanti" in ordine decrescente di tempo totale di esecuzione.

3. Logging delle query lente. Puoi configurare PostgreSQL per scrivere nei log le query che impiegano troppo tempo (tipo più di 1 secondo).

Per farlo, in postgresql.conf imposta:

log_min_duration_statement = 1000  # Tempo in millisecondi (1 secondo)

Ora le query lente verranno salvate nei log di PostgreSQL.

Metriche principali per l'analisi delle performance

Quando analizzi le performance delle query, guarda queste metriche chiave:

1. Tempo di esecuzione. È il tempo che serve per eseguire la query. Più è veloce, meglio è.
2. Numero di righe. Se la tua query restituisce o scansiona più righe del previsto, potrebbe essere un problema.
3. Uso degli indici. Se la query dovrebbe usare un indice ma invece fa una scansione sequenziale (Seq Scan), è un segnale che va ottimizzata.
4. Buffer e operazioni su disco. Le query che lavorano tanto su disco sono più lente di quelle che usano i dati già in RAM.

Come si applicano queste cose nella pratica?

Esempio 1: Query lenta

Scrivi una query per prendere tutti i prodotti con prezzo sopra 100:

SELECT * FROM products WHERE price > 100;

Noti che la query è troppo lenta. Usi EXPLAIN e vedi:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Problema: la query fa una scansione completa della tabella perché non c'è un indice sulla colonna price.

Soluzione:

Crea un indice:

CREATE INDEX idx_price ON products(price);

Ora la query usa Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Esempio 2: Trovare query lente con pg_stat_statements

Con il comando:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Scopri una query che consuma un sacco di tempo. La apri con EXPLAIN ANALYZE, la sistemi, e diventa più veloce.

Quando inizi a usare EXPLAIN, pg_stat_statements e gli altri strumenti, le tue query andranno più veloci e il tuo server PostgreSQL funzionerà come un orologio svizzero. Nella prossima lezione andremo nei dettagli dei parametri di EXPLAIN, tipo cost, rows e width, così potrai leggere i piani di esecuzione delle query come un libro aperto.