PostgreSQL: indici e performance delle query

Quando le query rallentano

Un'applicazione veloce puo trasformarsi in una lenta man mano che i dati crescono. Spesso la causa e la mancanza di indici adeguati. Capire come funzionano e il primo passo per query rapide.

Cos'e un indice

Un indice e una struttura dati (tipicamente un B-tree) che permette al database di trovare le righe senza scorrere l'intera tabella. E come l'indice analitico di un libro: evita di leggere tutte le pagine.

CREATE INDEX idx_letture_dispositivo_data
  ON letture (dispositivo_id, registrata_il);

EXPLAIN: lo strumento essenziale

Per capire perche una query e lenta, si usa EXPLAIN ANALYZE, che mostra il piano di esecuzione reale:

EXPLAIN ANALYZE
SELECT * FROM letture
WHERE dispositivo_id = 7
  AND registrata_il > now() - interval '1 day';

Se vedi un Seq Scan su una tabella grande dove ti aspetti un Index Scan, probabilmente manca un indice utile.

Attenzione agli eccessi

Gli indici non sono gratis: occupano spazio e rallentano le scritture, perche vanno aggiornati a ogni INSERT e UPDATE. Si creano sulle colonne usate in WHERE, JOIN e ORDER BY, non a casaccio.

Conclusione

Indici mirati e analisi dei piani di esecuzione fanno la differenza tra un database che regge e uno che cede. In MUSTNODE ottimizziamo le query partendo dai dati, non dalle supposizioni.