PostgreSQL lento: leggere un piano di query prima di aggiungere un indice

Parliamo del tuo progetto →

Perché partire dal piano di query prima di aggiungere un indice

Quando una query PostgreSQL rallenta, la prima reazione spesso è aggiungere un indice. Può essere utile, ma non è sempre la risposta giusta. La documentazione ufficiale distingue chiaramente tra EXPLAIN, che mostra il piano scelto dal planner, ed EXPLAIN ANALYZE, che esegue davvero la query e restituisce il piano con tempi osservati e righe processate. Prima di cambiare schema, conviene quindi leggere il piano, capire dove si concentra il lavoro e solo dopo valutare se un indice serva davvero. (PostgreSQL docs)

Definire il caso in modo riproducibile

Scenario fittizio: la ricerca degli ordini di un mese impiega più del previsto. Per analizzarla in modo utile, conserva:

  • la query esatta
  • i parametri realmente usati
  • la dimensione del dataset di prova
  • la versione di PostgreSQL
  • il contesto d’uso: singola interrogazione o carico concorrente

Usa un ambiente separato e dati autorizzati. Un benchmark isolato non rappresenta automaticamente il carico reale, e un piano salvato senza contesto rischia di essere poco informativo.

Leggere le stime del piano

Il primo passo è eseguire EXPLAIN e osservare il piano ottenuto. Cerca soprattutto:

  • scansioni sequenziali o su indice
  • filtri applicati
  • ordinamenti
  • stime di righe elaborate

La documentazione PostgreSQL ricorda che il costo stimato serve al planner per confrontare alternative, ma non coincide con un tempo reale in millisecondi. Per questo motivo una scansione sequenziale non dimostra da sola un errore: in certi casi può essere la scelta più conveniente.

Un esempio didattico, con dati simulati:

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42;

Output esemplificativo:

Seq Scan on orders  cost 0.00..1820.00 rows 120
Filter: customer_id = 42

Questo piano indica che il motore preferisce leggere la tabella e filtrare le righe, almeno secondo le statistiche disponibili. Non prova da solo che manchi un indice.

Quando EXPLAIN ANALYZE è davvero utile

Se serve una misura reale, EXPLAIN ANALYZE va eseguito con cautela e preferibilmente in test. La documentazione segnala che questo comando esegue davvero la query; di conseguenza può consumare risorse e, se l’istruzione modifica dati, produrre effetti sul database.

Per questo è più corretto usarlo come strumento di diagnostica controllata, non come anteprima innocua. Il confronto migliore è quello tra una sola modifica alla volta, mantenendo uguali parametri e condizioni.

Un secondo esempio, sempre didattico:

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at;

Output esemplificativo:

Bitmap Heap Scan on orders
Recheck Cond: customer_id = 42
Sort

Qui il piano mostra che il motore recupera un insieme di righe candidate e poi le ordina. Anche in questo caso la domanda giusta non è “aggiungo un indice?”, ma “qual è il costo maggiore: filtro, ordinamento o volume dei dati letti?”.

Valutare l’indice con criteri completi

Prima di distribuire un eventuale indice, considera anche:

  • l’impatto sulle scritture
  • lo spazio occupato
  • il costo di manutenzione
  • le modalità di creazione sul sistema reale

Un indice può migliorare la lettura e peggiorare altri aspetti operativi. Per questo il deliverable utile non è solo “indice sì” o “indice no”, ma un confronto riproducibile con limiti dichiarati.

Procedura pratica in cinque passi

  1. Raccogli la query lenta e il suo contesto d’uso.
  2. Esegui EXPLAIN per vedere il piano scelto.
  3. Identifica scansioni, filtri, ordinamenti e stime di righe.
  4. Se serve, prova EXPLAIN ANALYZE solo in test.
  5. Valuta l’indice insieme a scritture, spazio e concorrenza.

Output atteso di un buon laboratorio

Il risultato non dovrebbe essere solo un indice candidato, ma un piccolo dossier tecnico con tre elementi:

  • il piano salvato prima della modifica
  • la misura osservata con EXPLAIN ANALYZE, se eseguita in test
  • una nota che spieghi perché la modifica è stata accettata o scartata

In altre parole, il valore non sta nell’intuizione, ma nella verifica. Una query può rallentare per selettività bassa, dataset ridotto, ordinamenti costosi o carico concorrente: l’indice è solo una delle possibili risposte.

Controlli di accettazione

Prima di chiudere l’intervento, verifica questi punti:

  • la query testata è identica a quella del caso reale
  • il piano è stato confrontato prima e dopo una sola modifica
  • il risultato funzionale non cambia
  • il test è avvenuto fuori dalla produzione o in una finestra controllata
  • l’effetto su scritture e manutenzione dell’indice è stato discusso

La domanda finale non è solo “la query è più veloce?”, ma anche “il miglioramento regge nel carico reale e nel tempo?”. In PostgreSQL la risposta migliore spesso nasce dal piano, non dall’indice aggiunto per intuizione.

Fonti