Analisi delle query lente con pg_stat_statements

Quando lavori su progetti reali, la tua app può essere usata da migliaia di utenti allo stesso tempo. Mandano query al database, aggiungono dati, li leggono, li aggiornano... E a un certo punto ti accorgi che il tuo server inizia a "lamentarsi". Questo è un segnale che le tue query non sono proprio ottimizzate. A volte una query che "sulla carta" sembrava carina, nella realtà può diventare un disastro per le performance. Ed è qui che entra in scena pg_stat_statements.

pg_stat_statements ti permette di:

1. Tracciare le query lente.
2. Capire quante volte sono state eseguite certe query.
3. Vedere quanto tempo hanno impiegato.
4. Vedere il tempo medio di esecuzione di una query.
5. Non fare l'errore fatale di riscrivere tutta l'app!

Esploriamo la struttura di pg_stat_statements

Dopo aver attivato l'estensione, nel tuo database appare una vista speciale: pg_stat_statements. Qui ci sono tutti i dati sulle query eseguite. Vediamo cosa contiene:

SELECT * FROM pg_stat_statements LIMIT 1;

Il risultato può essere così (versione semplificata):

Spiegazione veloce:

• query — la query SQL vera e propria.
• calls — quante volte è stata eseguita questa query.
• total_time — quanto tempo totale ha impiegato la query.
• rows — quante righe ha restituito la query.
• shared_blks_read — quanti blocchi sono stati letti (si va a prendere dal disco se non usi la cache).

Analisi dei risultati

Ora che pg_stat_statements è attivo, vediamo come trovare le query lente.

Le query più lente

Per capire quali query consumano più tempo, puoi usare questa query:

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

Qui:

• mean_time — è il tempo medio di esecuzione di una query (total_time / calls).
• ORDER BY total_time DESC — ordina per tempo totale decrescente.

Le query più frequenti

A volte il problema non sono le query lente, ma quelle che girano troppo spesso. Per esempio:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Ottimizzazione delle query

1. Usa gli indici

Se vedi che le query su certe colonne sono lente, controlla se c'è un indice su quelle colonne. Supponiamo che hai una tabella studenti con un sacco di righe e accedi spesso al campo cognome. Vale la pena creare un indice:

CREATE INDEX idx_studenti_cognome ON studenti (cognome);

2. Riscrivi la query

Supponiamo che vedi che una query tipo SELECT * FROM ordini WHERE importo > 1000 è troppo lenta. Probabilmente invece di "tutto su tutto" dovresti prendere solo le colonne che ti servono:

SELECT id_ordine, importo FROM ordini WHERE importo > 1000;

Reset delle statistiche

A volte, per vedere solo i risultati nuovi (tipo dopo un'ottimizzazione), devi pulire i dati in pg_stat_statements. Si fa così:

SELECT pg_stat_statements_reset();

Funziona come il tasto "Reset" sulla tua calcolatrice. Dopo averlo fatto, le statistiche ripartono da zero.

Trovare le query problematiche

Immagina di essere l'admin del database di un'università e gli studenti si lamentano in massa che il loro profilo si carica troppo lentamente. Decidi di controllare pg_stat_statements:

Passo 1: Trova la query più lenta

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

Vedi che una query tipo SELECT * FROM studenti WHERE stato = 'attivo' ci mette 30 secondi. Wow. Bisogna fare qualcosa subito.

Passo 2: Controlla gli indici Analizzando la tabella studenti, capisci che la colonna stato non ha un indice. Lo sistemi così:

CREATE INDEX idx_studenti_stato ON studenti (stato);

Passo 3: Controlla il risultato Dopo l'ottimizzazione, ricontrolli pg_stat_statements e vedi che la query ora gira in 0.5 secondi. Vittoria!

Errori comuni con pg_stat_statements

A volte gli admin fanno errori quando analizzano le query:

1. Estensione non attivata. Se ti dimentichi di mettere pg_stat_statements in shared_preload_libraries, le statistiche non verranno raccolte.
2. Ignorare gli indici. Anche se le query sembrano lente, il problema spesso si risolve aggiungendo gli indici giusti.
3. Non resettare le statistiche. Se non lanci pg_stat_statements_reset(), i dati vecchi ti confondono l'analisi di quelli attuali.

Usare pg_stat_statements nel tuo lavoro è come avere un navigatore GPS per il database: ti dice esattamente dove sei bloccato nel "traffico" e ti suggerisce pure come aggirarlo. Se lo configuri bene, puoi migliorare di brutto le performance dei tuoi database.