Analisi comparativa di EXPLAIN ANALYZE e pg_stat_statements

A questo punto potresti farti una domanda logica: perché abbiamo due strumenti diversi per l’analisi? Quale si usa di più: EXPLAIN ANALYZE o pg_stat_statements? Vediamo insieme questi due approcci, i loro punti di forza e debolezza, e dove e quando conviene usare ciascuno di essi.

Problemi risolti dagli strumenti

EXPLAIN ANALYZE: è lo strumento per l’analisi approfondita di una singola query. Se vuoi sapere come PostgreSQL esegue una query, quali nodi usa, quante righe vengono processate e quanto tempo impiega ogni operazione, questo è quello che fa per te. Ti aiuta a rispondere alla domanda: "Perché questa query specifica è lenta?"

pg_stat_statements: è uno strumento di monitoring a livello più alto, che ti dà info sulle performance di tutte le query eseguite nel database. È la scelta giusta se vuoi una panoramica generale: "Quali sono le query più lente nel mio database?" oppure "Quali query stanno stressando di più il server?"

Quando usare EXPLAIN ANALYZE

EXPLAIN ANALYZE è il tuo tool di debug per capire come PostgreSQL esegue una query specifica. Usalo in questi casi:

Ottimizzazione mirata di una query Se qualcuno si lamenta che una pagina della tua app ci mette una vita a caricarsi, la prima cosa che fai è trovare la query responsabile e usare EXPLAIN ANALYZE. Così vedi il piano di esecuzione e metriche reali come il tempo di esecuzione e il numero di righe processate.

Scelta dell’indice giusto Quando crei un nuovo indice o modifichi uno esistente, usa EXPLAIN ANALYZE per vedere se PostgreSQL lo usa davvero. Se non lo fa, forse hai creato un indice che non serve a ottimizzare le query.

Debug di query complesse Se scrivi una query complicata con tanti JOIN o WHERE, analizzare il piano di esecuzione reale con EXPLAIN ANALYZE ti aiuta a trovare i colli di bottiglia, tipo scan sequenziali inutili (ciao, Seq Scan).

Esempio: Ottimizzazione di una query con EXPLAIN ANALYZE

-- Query che va lenta
SELECT *
FROM students
WHERE name = 'Alice';

-- Analizziamo il piano di esecuzione
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Se vedi che viene usato Seq Scan, probabilmente ti sei dimenticato di creare un indice:

-- Creiamo un indice sulla colonna name
CREATE INDEX idx_students_name ON students(name);

-- Ricontrolliamo
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Quando usare pg_stat_statements

Questo strumento è insostituibile per analizzare le performance dell’intero sistema. Usalo in questi scenari:

Monitoring in produzione pg_stat_statements mostra statistiche sulle query eseguite in un certo periodo. Puoi trovare facilmente le query più lente grazie alla colonna total_time, che mostra il tempo totale di esecuzione di ogni query.

Trovare le query "pesanti" Vuoi sapere quali query stressano di più il tuo database? Ordina le query per numero di letture dalla memoria (shared_blks_hit) o per il numero di righe processate (rows).

Individuare query eseguite molto spesso A volte non è solo la query lenta a creare problemi, ma anche quelle che vengono eseguite di continuo. Se una query gira 100 volte al minuto, anche una piccola ottimizzazione può alleggerire parecchio il carico sul server.

Esempio: Trovare query lente con pg_stat_statements

-- Visualizza statistiche delle query
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Questa query ti mostra le top 5 query che consumano più tempo.

Confronto tra gli approcci: qual è la differenza?

Usare entrambi gli strumenti insieme

Come sempre nella programmazione, non esiste il bottone magico che risolve tutto. Il miglior approccio è usare entrambi gli strumenti insieme. Ad esempio:

1. Usa pg_stat_statements per individuare le query più lente o più frequenti nel tuo sistema.

2. Poi analizza queste query con EXPLAIN ANALYZE per capire il motivo dei loro problemi.

Esempio pratico: approccio completo

-- Passo 1: Trova la query più lenta
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- Passo 2: Analizza questa query
EXPLAIN ANALYZE
<incolla la query dal passo precedente>;

Errori tipici nell’uso

Quando lavori con EXPLAIN ANALYZE e pg_stat_statements ci sono alcuni errori che fanno spesso i principianti:

1. Dimenticare la rilevanza dei dati. Se analizzi una query su una tabella vuota, l’output di EXPLAIN ANALYZE può essere fuorviante. Assicurati che il tuo database di test rifletta i volumi reali di dati.

2. Ignorare il consumo di risorse del monitoring. Se hai l’estensione pg_stat_statements attiva in produzione, assicurati che sia configurata bene e non causi troppo carico.

3. Leggere il piano teorico invece di quello reale. Ricorda che EXPLAIN da solo mostra solo il piano teorico. Usa EXPLAIN ANALYZE per avere i dati reali.

Ora hai tutte le conoscenze che ti servono non solo per combattere le query lente, ma anche per prevenirle. PostgreSQL ti dà strumenti potenti, e saperli combinare bene ti permette di ottenere performance ottimali anche su sistemi molto carichi.

1

Sondaggio/quiz

Ottimizzazione delle query, livello 42, lezione 4

Non disponibile

Ottimizzazione delle query

Ottimizzazione delle query

Apri