Monitoraggio delle query lente con pg_stat_statements

pg_stat_statements è un'estensione built-in di PostgreSQL che ti permette di vedere quali query vengono effettivamente eseguite nel database e come si comportano. In pratica, è come un assistente silenzioso ma super attento che registra ogni passo: quali query SQL sono state lanciate, quanto ci hanno messo, quante volte sono state eseguite e quanto hanno stressato il sistema.

Perché serve? Prima di tutto, per trovare le query problematiche. A volte il database va lento non per colpa di un solo "cattivo", ma per decine di query pesanti uguali che girano troppo spesso. In più, le statistiche ti aiutano a vedere quali query stanno consumando risorse — CPU, RAM, disco. E puoi anche capire se gli indici funzionano come avevi previsto: magari da qualche parte non vengono usati per niente, o magari ne servirebbero di più.

pg_stat_statements ti permette di non andare a caso, ma di vedere numeri reali — e su quelli basare le tue decisioni e ottimizzazioni.

Come trovare le query lente?

Ora viene il bello! Usando la tabella pg_stat_statements, possiamo cercare le query che ci mettono tanto o che caricano troppo il server.

Idea principale:

Ogni riga nella tabella pg_stat_statements rappresenta le statistiche di una query. Le query sono raggruppate per il loro testo (campo query), e per ognuna vengono calcolate queste metriche:

• total_time — tempo totale di esecuzione della query, in millisecondi.
• calls — quante volte la query è stata eseguita.
• mean_time — tempo medio di esecuzione della query (total_time / calls).
• rows — quante righe ha restituito la query.

Esempio di analisi semplice

Proviamo a trovare le query più lente in base al tempo medio di esecuzione:

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

Questa query ti mostra la TOP-5 delle query che ci mettono di più. Occhio al campo mean_time: se i valori in millisecondi superano i 500-1000, è un segnale che quelle query vanno ottimizzate.

Esempio di analisi delle query lente

Vediamo un esempio:

Ecco il risultato della query precedente:

Cosa vediamo?

Query sulla tabella orders: viene eseguita molto raramente (solo 10 chiamate), ma ogni volta tira fuori ben 10 mila righe. Probabilmente la tabella è enorme e la query non usa indici.

Query sulla tabella products: viene chiamata migliaia di volte, magari in un ciclo nell'applicazione. Ogni volta restituisce solo 100 righe, ma per la frequenza anche questa può essere un problema.

Query sulla tabella customers: è veloce (543 ms), ma viene chiamata troppo spesso.

Ottimizzazione delle query lente

Ora che abbiamo trovato le query problematiche, dobbiamo guardare il loro piano di esecuzione con EXPLAIN ANALYZE. Per esempio, per la query sulla tabella orders:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'nuovo';

Cosa possiamo vedere?

Seq Scan: se la query usa una scansione sequenziale, bisogna aggiungere un indice:

CREATE INDEX idx_orders_status ON orders (status);

Problemi di filtraggio: se la query tira fuori troppe righe, rivedi il testo della query. Magari serve aggiungere altre condizioni o limitare i risultati:

SELECT * FROM orders WHERE status = 'nuovo' LIMIT 100;

Output delle statistiche sui tempi di esecuzione

A volte le query problematiche non sono così ovvie. Per esempio, query che chiamano spesso funzioni o sottoquery. In questi casi è utile guardare la colonna total_time:

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

Questa query ti mostra le query più "costose" in termini di tempo totale di esecuzione.

Ottimizzazione degli indici

Spesso le query lente sono dovute alla mancanza di indici giusti. Usa pg_stat_statements per capire quali query non usano indici. Se vedi tante query con gli stessi filtri (tipo sul campo status), ma sono lente, aggiungi l'indice giusto:

CREATE INDEX idx_orders_status ON orders (status);

Dopo, controlla di nuovo le performance della query con EXPLAIN ANALYZE.

Usando pg_stat_statements puoi monitorare in modo efficace le performance delle query, trovare i "colli di bottiglia" e migliorare le prestazioni del tuo database. Ricorda: prima inizi ad analizzare le query, più facile sarà ottimizzare tutto il sistema.