Problemi tipici quando lavori con gli indici

Anche la macchina più moderna si ferma se ci metti la limonata invece della benzina. Stessa cosa con gli indici in PostgreSQL. Sono uno strumento davvero potente, ma vanno usati con la testa. Vediamo insieme qualche problema classico legato agli indici.

Problema 1: troppi indici

Ripassiamo la lezione di due volte fa. Se hai troppi indici su una tabella, PostgreSQL deve lavorare su ognuno di loro per tenerli aggiornati. Questo impatta direttamente sulle operazioni di scrittura, aggiornamento e cancellazione. Ogni indice va aggiornato e sincronizzato!

Supponiamo di avere una tabella students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

E decidi di creare un indice su ogni colonna “tanto per sicurezza”:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Ora immagina di inserire 10 mila nuovi record. PostgreSQL non solo deve scrivere i dati nella tabella, ma anche aggiornare tutti e tre gli indici. Se i dati sono tanti, la velocità di scrittura crolla e le performance ne risentono.

Come evitare questo problema? Prima di creare un indice, fatti due domande:

1. Quanto spesso questa colonna viene usata per filtrare (WHERE), ordinare (ORDER BY) o raggruppare (GROUP BY)?
2. La query userà davvero questo indice o farà comunque una scansione completa della tabella?

Se la risposta a entrambe è “raramente” o “mai”, quell’indice non ti serve.

Problema 2: scelta sbagliata delle colonne per l’indice

Creare un indice su dati poco vari è come versare il tè in una tazza col coperchio incollato: praticamente inutile. Se una colonna ha solo 2-3 valori unici, PostgreSQL probabilmente farà una scansione completa della tabella invece di usare l’indice.

Mettiamo di avere una tabella courses:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Può essere solo 'Beginner', 'Intermediate' o 'Advanced'
);

E decidi di creare un indice sulla colonna level:

CREATE INDEX idx_courses_level ON courses(level);

Ma la query:

SELECT * FROM courses WHERE level = 'Beginner';

potrebbe non usare l’indice, perché PostgreSQL calcola che è più semplice scansionare tutta la tabella che guardare l’indice. Questo succede soprattutto con tabelle piccole e dati poco vari.

Quindi gli indici hanno senso su colonne con alta cardinalità (cioè tanti valori unici). Per dati poco vari meglio usare altri metodi di ottimizzazione, tipo il partizionamento delle tabelle.

Problema 3: indici vecchi e inutilizzati

A volte crei un indice e poi te ne dimentichi, anche se non serve più. È come i file sul desktop: all’inizio sono pochi, poi ti ritrovi a perdere tempo a cercare quello giusto tra mille icone... Ti suona familiare?

Supponiamo di aver creato un indice per una vecchia funzionalità, poi cambi la logica delle query e aggiungi un nuovo indice. Quello vecchio non serve più a nessuno, ma resta lì a occupare spazio e rallentare le scritture.

Per evitarlo, controlla e analizza regolarmente gli indici. PostgreSQL ti dà una metrica comoda:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Qui idx_scan ti dice quante query hanno usato quell’indice. Se il valore è 0, vuol dire che l’indice non viene usato e puoi cancellarlo:

DROP INDEX idx_courses_level;

Problema 4: indici su colonne aggiornate spesso

Se hai un indice su una colonna che aggiorni spesso, PostgreSQL deve ricostruire l’indice a ogni modifica. Questo può rallentare parecchio le performance.

Immagina una tabella con i dati degli ordini:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Può cambiare più volte (tipo "nuovo", "in corso", "completato")
    total NUMERIC(10, 2)
);

Crei un indice sulla colonna status per filtrare più velocemente per stato:

CREATE INDEX idx_orders_status ON orders(status);

Ma se lo status cambia decine di volte per ogni record, l’indice inizia a rallentare tutto.

Per evitare questa situazione, non creare indici su colonne che cambiano spesso. Se proprio ti serve l’indice, valuta l’uso di indici parziali:

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'in corso';

Così l’indice si aggiorna solo per i record con quel valore specifico.

Problema 5: Constraint UNIQUE su colonne inutili

Gli indici unici (UNIQUE) vengono creati automaticamente per garantire l’unicità dei dati. Ma se l’unicità non è davvero necessaria, questi indici sono solo un peso inutile.

Supponiamo di aver creato una tabella per i log:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

Se ogni secondo aggiungi migliaia di record, mantenere l’unicità su created_at crea un carico enorme.

Per stare tranquillo, lascia i constraint UNIQUE solo dove servono davvero. Nel nostro esempio, se l’unicità su created_at non è obbligatoria, sostituisci l’indice con uno normale:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problema 6: uso sbagliato degli indici combinati

Gli indici combinati (multi-column indexes) sono utili se le query filtrano o ordinano su più colonne insieme. Ma vanno creati nel modo giusto, altrimenti restano inutilizzati.

Supponiamo di avere questo indice:

CREATE INDEX idx_students_name_grade ON students(name, grade);

Questo indice viene usato se la query filtra o ordina su entrambe le colonne:

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

Ma la query:

SELECT * FROM students WHERE grade = 90;

non usa questo indice, perché il campo name viene prima.

Per evitare il problema, crea indici combinati solo nell’ordine in cui vengono usati più spesso nelle query. Se ti serve filtrare solo su una colonna, crea indici separati.

Consigli utili

Monitora l’uso degli indici. In PostgreSQL c’è la vista di sistema pg_stat_user_indexes dove puoi vedere quali indici vengono usati e quali no.

Ottimizza le query insieme agli indici. Le query scritte male restano lente anche con gli indici.

Non dimenticare di cancellare. Gli indici vecchi occupano solo spazio e rallentano le scritture.

Ecco qua, ragazzi! Gli indici sono uno strumento potente, ma ricordatevi sempre che da grandi poteri derivano grandi responsabilità. Usate gli indici con consapevolezza e il vostro database volerà come un razzo SpaceX!