Raccolta delle statistiche sull'utilizzo di indici e tabelle

Immagina che il tuo database sia come un grande magazzino. Gli indici sono i cataloghi e le liste che ti aiutano a trovare subito quello che ti serve. Le tabelle sono la merce sugli scaffali. Se un indice viene usato poco, è come se il catalogo fosse buttato in un angolo e nessuno lo aprisse mai. Se una tabella viene usata tanto ma ha una struttura pessima o dati inutili, il nostro magazzino (cioè il database) si stressa e rallenta tutto.

Obiettivi principali dell'analisi:

1. Valutare l'efficacia dell'utilizzo degli indici. Tipo, il tuo indice super costoso sta lì a prendere polvere? Buttalo!
2. Capire la frequenza delle operazioni di lettura e scrittura. Ti aiuta a vedere quali tabelle sono davvero usate.
3. Ottimizzazione delle query. Le statistiche ti fanno capire dove puoi velocizzare le cose aggiungendo o cambiando indici.

Le view pg_stat_user_indexes e pg_stat_user_tables

In PostgreSQL ci sono due view super utili per raccogliere statistiche: pg_stat_user_indexes e pg_stat_user_tables. Vediamole meglio.

pg_stat_user_indexes: come vengono usati gli indici?

Campi principali:

• relname — nome della tabella a cui si riferisce l'indice.
• indexrelname — nome dell'indice.
• idx_scan — quante volte l'indice è stato usato per cercare.
• idx_tup_read — quante righe sono state lette tramite l'indice.
• idx_tup_fetch — quante righe sono state effettivamente restituite (dopo i filtri).

Esempio di query:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Qui cosa facciamo:

• ordiniamo i dati per numero di chiamate all'indice (idx_scan), così vediamo quali indici sono più gettonati.
• se un indice praticamente non viene mai usato (idx_scan = 0), chiediti: serve davvero?

Applicazione pratica:

Stai lanciando una nuova versione dell'app e hai aggiunto un nuovo indice. Con pg_stat_user_indexes puoi controllare se la tua query usa davvero il nuovo indice, oppure PostgreSQL continua a ignorare il tuo capolavoro di ottimizzazione.

pg_stat_user_tables: vedere i dati sulle tabelle

Campi principali:

• relname — nome della tabella.
• seq_scan — numero di scansioni sequenziali della tabella (senza indici).
• seq_tup_read — quante righe sono state restituite dalla tabella durante le scansioni sequenziali.
• idx_scan — numero di scansioni tramite indice per la tabella.
• n_tup_ins — quante righe sono state inserite
• n_tup_upd — quante righe sono state aggiornate.
• n_tup_del — quante righe sono state cancellate.

Esempio di query:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Cosa vediamo qui?

• Le tabelle con tante scansioni sequenziali (seq_scan) ti fanno capire che forse serve un indice.
• Il numero di insert, update e delete ti fa vedere quanto spesso cambiano i dati nella tabella.

Applicazione pratica: Stai lavorando sulla tabella users, dove ci sono tutti gli utenti della tua app. Con pg_stat_user_tables scopri che le scansioni sequenziali (seq_scan) su questa tabella sono alle stelle. È un segnale: è ora di creare indici sulle colonne più usate per velocizzare le query.

Esempio: analisi di indici e tabelle in un database reale

Supponiamo di avere un database con le tabelle orders (ordini) e products (prodotti). Vogliamo capire quanto sono usati bene tabelle e indici.

Analisi degli indici:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Vedi che l'indice orders_customer_id_idx è stato usato 50 mila volte, mentre orders_date_idx solo 5 volte. Forse orders_date_idx non serve proprio.

Analisi delle tabelle:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

La tabella products viene scansionata in sequenza tutto il tempo. È un segnale: mancano indici nel catalogo prodotti.

Errori tipici e come evitarli

La trappola classica per chi inizia è ignorare le statistiche. Tipo, aggiungi un nuovo indice pensando: "Ok, ora le query volano", ma PostgreSQL non lo usa perché le statistiche non si sono aggiornate da sole. Dopo cambi grossi nelle tabelle, ricordati di aggiornare le statistiche a mano con il comando ANALYZE.

Un altro errore comune è aggiungere indici a caso. Ricorda: ogni indice occupa spazio su disco e rallenta insert, update e delete. Usa le statistiche di pg_stat_user_indexes per essere sicuro che l'indice serva davvero e non sia solo un peso morto.

Utilità pratica di queste conoscenze: dove ti servono?

Nello sviluppo vero: se il database va lento, la prima cosa che controlli sono tabelle e indici.

Al colloquio: domande sull'ottimizzazione degli indici sono un classico delle interviste SQL. Sai spiegare pg_stat_user_indexes? Sei già a metà strada.

Nell'amministrazione dei database: il monitoring è la routine quotidiana del DBA. Senza statistiche su tabelle e indici, non migliori nulla.