Individuare e risolvere le query lente

Questo è il prompt conclusivo del colloquio: l'intervistatore le presenta una query lenta e un piano EXPLAIN ANALYZE e le chiede di diagnosticarla. Vuole verificare un metodo, non trucchi imparati a memoria.

Una risposta efficace segue ad alta voce una checklist: misurare, leggere il piano, individuare il costo dominante, formulare un'ipotesi, proporre una correzione e verificare. Questa lezione costruisce la checklist passo dopo passo.

Rimanga sistematico e descriva il proprio ragionamento: è questo che le fa ottenere una valutazione senior.

Non faccia mai supposizioni basandosi solo sull'SQL. Ottenga il piano reale con EXPLAIN (ANALYZE, BUFFERS).

ANALYZE fornisce i tempi e il numero effettivo di righe; BUFFERS mostra se i dati provengono dalla cache o vengono letti dal disco. Insieme, indicano se la query è vincolata dalla CPU, dall'I/O o semplicemente sta svolgendo troppo lavoro.

Lo esegua un paio di volte: la prima esecuzione può risentire della penalizzazione di una cache fredda e falsare i tempi.

Non legga il piano dall'alto verso il basso cercando a caso. Individui il nodo in cui viene effettivamente impiegato il tempo maggiore.

Calcoli il tempo proprio di ciascun nodo: il suo actual time totale meno il tempo dei nodi figli, moltiplicato per loops. Il nodo con la quota maggiore è il suo obiettivo; tutto il resto è rumore.

Nei colloqui dica: l'80% del tempo di esecuzione è concentrato in questo Seq Scan, quindi è qui che mi concentrerò. Ottimizzare qualsiasi altra cosa sarebbe uno spreco di tempo.

Nel nodo dominante, confronti il numero stimato di righe con quello effettivo. Una grande differenza significa che il planner sta procedendo alla cieca e probabilmente ha scelto un piano errato, per esempio l'algoritmo di join o il metodo di accesso sbagliato.

L'esempio mostra una sottostima di 1000 volte. Prima di riprogettare qualsiasi cosa, aggiorni le statistiche: questo singolo comando spesso corregge il piano senza costi.

ANALYZE ricalcola le statistiche delle colonne; VACUUM ANALYZE elimina anche le tuple obsolete e aggiorna la visibility map.

Il bug correggibile più frequente è una funzione o un cast che avvolge la colonna in WHERE: l'indice non può essere utilizzato e il motore esegue una scansione sequenziale.

Nell'esempio viene forzata una scansione completa perché DATE() viene applicata a ogni riga. La si riscriva come predicato su un intervallo che utilizza direttamente la colonna, in forma sargable, e l'indice su created_at entrerà in funzione.

Lo stesso vale per WHERE lower(email)=...: memorizzi i dati normalizzati, interroghi direttamente la colonna oppure crei un indice su espressione.

Se il nodo dominante è una Seq Scan con un filtro altamente selettivo, oppure un Nested Loop con un numero enorme di loops su una chiave interna non indicizzata, la soluzione è generalmente un indice.

Aggiunga un indice sulla colonna filtrata o usata nel join. Nell'esempio ne viene creato uno su customer_id, così il join può passare dalle scansioni sequenziali alle scansioni tramite indice e il planner può scegliere un piano molto più economico.

Verifichi rieseguendo EXPLAIN ANALYZE; non dia per scontato che l'indice sia stato utile.

SELECT * trasferisce ogni colonna dal disco e attraverso la rete, impedendo inoltre le scansioni che usano solo l'indice, perché raramente l'indice contiene tutte le colonne.

Selezioni solo le colonne necessarie. In questo modo riduce la larghezza delle righe, diminuisce l'I/O e può abilitare una scansione tramite il solo indice con copertura.

Quando un intervistatore inserisce SELECT *, vuole che lei lo noti. Ridurre l'elenco delle colonne è spesso una correzione rapida che produce un miglioramento concreto sulle tabelle con righe ampie.

Se un nodo Sort o Hash segnala l'uso del disco (Sort Method: external merge Disk: 25000kB o Batches: > 1), l'operazione ha superato work_mem e ha scritto dati su disco.

Le opzioni sono: aumentare work_mem per la sessione, ridurre il numero di righe che raggiungono l'ordinamento o l'hash filtrando prima, oppure aggiungere un indice che fornisca i dati già ordinati, evitando del tutto l'ordinamento.

Questa è una diagnosi precisa, di livello senior, che gli intervistatori valutano positivamente.

Faccia attenzione a Rows Removed by Filter: 9500000. La query ha letto dieci milioni di righe e ne ha scartate quasi tutte: è il classico lavoro sprecato.

Le correzioni possibili sono: aggiungere un indice affinché il filtro venga applicato durante l'accesso e non dopo, rendere il predicato più selettivo oppure anticipare il filtraggio nella query, così meno righe risalgono nell'albero.

Il principio è semplice: svolgere meno lavoro possibile e filtrare il più presto e nel modo più economico possibile.

Ripeta questa sequenza durante il colloquio e non perderà la direzione:

• Misuri con EXPLAIN (ANALYZE, BUFFERS).
• Individui il nodo che consuma più tempo.
• Confronti le righe stimate con quelle effettive e corregga prima le statistiche obsolete.
• Verifichi la sargability e rimuova le funzioni dalle colonne filtrate.
• Indicizzi i filtri selettivi e le chiavi di join.
• Riduca le colonne ed eviti SELECT *.
• Controlli le scritture su disco e il recupero di troppe righe.
• Verifichi rieseguendo il piano.

Esamini ad alta voce un esempio completo. Il piano mostra un Seq Scan su una tabella orders da 50 milioni di righe, con filtro customer_id = 42, Rows Removed by Filter vicino a 50 milioni e una stima che corrisponde all'incirca ai valori effettivi.

Diagnosi: filtro selettivo, nessun indice; il costo dominante è la scansione. Correzione: CREATE INDEX ON orders(customer_id). Eseguendo di nuovo la query, il piano passa a un Index Scan e il tempo scende da secondi a meno di un millisecondo.

Questo ciclo di misurazione, diagnosi, correzione e verifica è il modello di risposta per qualsiasi domanda su una query lenta.

Una query applica il filtro WHERE YEAR(order_date) = 2026 e il piano mostra un Seq Scan completo nonostante esista già un indice B-tree su order_date. Qual è la soluzione migliore da provare per prima?

Ora dispone di un metodo ripetibile per affrontare le domande sulle query lente:

• Esegua sempre una misurazione con EXPLAIN (ANALYZE, BUFFERS) e si concentri sul nodo dominante.
• Corregga prima le statistiche obsolete quando le stime divergono dai valori effettivi.
• Renda i predicati sargable, aggiunga indici per i filtri selettivi e le chiavi di join e riduca l'uso di SELECT *.
• Risolva i problemi di scrittura temporanea su disco e di recupero eccessivo di dati, quindi verifichi il nuovo piano.

Esporre la checklist, proporre una modifica concreta e rieseguire il piano per dimostrarne l'efficacia: questa è la risposta da senior.