Uso di pg_stat_statements per analizzare l'utilizzo di indici e filtri

Gli indici sono come i segnalibri in un libro. Ti aiutano a trovare velocemente quello che cerchi. Ma che succede se aggiungiamo un sacco di segnalibri e nessuno li usa? O peggio, se i segnalibri sono messi male e ci costringono a sfogliare tutto il libro dall'inizio alla fine? Ecco perché serve analizzare l'utilizzo degli indici.

Query scritte male possono ignorare gli indici, portando a costose scansioni sequenziali (Seq Scan). Questo rallenta le query e aumenta il carico sul server. Il nostro obiettivo è capire quali query non usano gli indici e perché.

Come capire se gli indici vengono usati?

Diamo un'occhiata a due problemi chiave:

1. Gli indici che abbiamo creato vengono usati?
2. Se sì, sono davvero efficaci?

Per queste domande possiamo analizzare le statistiche delle query in pg_stat_statements, guardando alcune colonne:

• rows: numero di righe elaborate dalla query.
• shared_blks_hit: numero di pagine lette dalla memoria (non dal disco).
• shared_blks_read: numero di pagine effettivamente lette dal disco.

Più basso è il numero di righe elaborate dalla query e più alta è la percentuale di shared_blks_hit rispetto al totale delle pagine, meglio sta lavorando il nostro indice.

Esempio di analisi dell'indicizzazione

Supponiamo di avere una tabella degli studenti:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Aggiungiamo un indice su grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Ora inseriamo dei dati per fare qualche test:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Studente ' || generate_series(1, 100000),
    'Cognome',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Facciamo una query per trovare gli studenti con un certo livello:

SELECT *
FROM students
WHERE grade_level = 10;

Controllo in pg_stat_statements

Dopo aver eseguito la query alcune volte, possiamo controllare le statistiche:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Interpretazione del risultato:

• rows: Se la query restituisce troppe righe, ha senso avere un indice? Forse non serve per condizioni con bassa selettività.
• shared_blks_hit e shared_blks_read: Se molte pagine vengono lette dal disco (shared_blks_read), vuol dire che l'indice non sta funzionando o i dati sono fuori dalla buffer pool.

Ottimizzazione dell'indicizzazione

Creare un indice è solo metà del lavoro. È importante che PostgreSQL lo usi davvero. A volte, nonostante tutti gli sforzi, il database sceglie comunque una scansione sequenziale. Perché succede? Vediamolo insieme.

Prima vediamo perché un indice può essere ignorato anche se sembra utile. Poi vediamo qualche trucco per "ricordare" al database che l'indice c'è e dovrebbe usarlo.

E se l'indice non viene usato?

A volte PostgreSQL ignora l'indice e fa una scansione sequenziale (Seq Scan). Può succedere per diversi motivi:

1. Bassa selettività della condizione. Se la query restituisce più della metà delle righe della tabella, la scansione sequenziale può essere più veloce.
2. Tipo di dato o funzioni. Se nella query usi una funzione sulla colonna indicizzata, l'indice può essere ignorato. Per esempio:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- L'indice non viene usato

In questi casi puoi riscrivere la query:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Usa l'indice

3. Tipo di indice sbagliato. Ad esempio, per la ricerca full-text è meglio usare indici GIN o GiST invece di B-TREE.

4. Statistiche sbagliate. Se le statistiche sono vecchie, l'ottimizzatore può prendere decisioni sbagliate. Usa ANALYZE:

   ANALYZE students;
   

Migliorare la query

Tornando al nostro esempio: se l'indicizzazione non funziona, puoi provare queste cose:

1. Assicurati che la query usi filtri che possono sfruttare l'indice: evita funzioni, conversioni di tipo, ecc.
2. Se il filtro restituisce molti valori, chiediti se serve davvero l'indice. Se la query è frequente, valuta di cambiare la struttura della tabella o aggiungere viste materializzate.
3. Se viene usato Seq Scan per via della mole di dati, prova a partizionare la tabella (PARTITION BY).

Verifica dell'efficacia dell'indicizzazione

Dopo aver ottimizzato, rilancia la query e controlla le statistiche:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Confronta le metriche prima e dopo. Dovresti vedere meno letture da disco (shared_blks_read) e più hit in memoria (shared_blks_hit).

Casi reali

1. Uso sbagliato dell'indice

Abbiamo una tabella dei prodotti con un campo testo description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Indice per la ricerca full-text
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Se facciamo questa query:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

L'indice non verrà usato! Il motivo è che ILIKE non è compatibile con GIN. Per usare l'indice, la query va riscritta così:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Manca l'indice dove serve

Supponiamo questa query:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

fa una scansione sequenziale (Seq Scan). Questo può succedere perché manca un indice su birth_date. Creando l'indice:

CREATE INDEX idx_birth_date ON students(birth_date);

e aggiornando le statistiche (ANALYZE students), puoi velocizzare di molto questa query.