Padroneggiare EXPLAIN ANALYZE: Guida all'Ottimizzazione dei Piani di Esecuzione in PostgreSQL

EXPLAIN ANALYZE è ciò che utilizzo quando una query PostgreSQL sembra lenta e le solite supposizioni non bastano più. Forse la query sembra innocua nel codice dell'applicazione. Forse la tabella ha un indice e tutti presumono che il database lo stia usando. Forse la query è veloce in staging ma lenta in produzione. Il piano è dove queste supposizioni reggono o crollano.

L'abitudine utile è leggere il piano come una storia del lavoro svolto da PostgreSQL: quali righe si aspettava di toccare, quali righe ha effettivamente toccato, dove ha fatto join, dove ha ordinato, se è rimasto in memoria e se ha dovuto leggere dal disco. Non è necessario memorizzare ogni nodo del piano prima che questo diventi utile. Devi solo rallentare e confrontare le stime con la realtà.

Capire EXPLAIN vs. EXPLAIN ANALYZE

La differenza tra EXPLAIN e EXPLAIN ANALYZE è importante perché uno è una previsione e l'altro è una misurazione.

EXPLAIN

Quando esegui una query preceduta da EXPLAIN, PostgreSQL genera il piano di esecuzione previsto senza eseguire effettivamente la query. Questo è utile per:

• Anteprima del piano: Puoi vedere cosa PostgreSQL si aspetta sia il modo più economico per eseguire la tua query.
• Stima dei costi: Fornisce stime di costo per ogni nodo nel piano, dandoti un'idea relativa dell'utilizzo delle risorse.

Esempio:

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE va oltre. Non solo mostra l'esecuzione pianificata, ma esegue anche la query e poi riporta le statistiche di esecuzione effettive. Questo significa che ottieni:

• Tempi di esecuzione effettivi: Quanto tempo ogni passo ha realmente impiegato.
• Conteggi di righe effettivi: Quante righe sono state effettivamente elaborate in ogni nodo.
• Conferma delle stime: Puoi confrontare i conteggi di righe stimati con quelli effettivi per vedere se il planner di PostgreSQL sta facendo previsioni accurate.

Questo rende EXPLAIN ANALYZE lo strumento migliore per l'ottimizzazione reale, ma ha un lato tagliente: esegue la query. Una SELECT può ancora essere costosa perché potrebbe scansionare molti dati, acquisire lock o competere per la cache. Un UPDATE, DELETE o INSERT modificherà effettivamente i dati a meno che non lo racchiudi in una transazione e la annulli:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

Questo schema è utile in una finestra di manutenzione o in una copia di staging. Non è un lasciapassare per eseguire istruzioni pericolose su un database di produzione occupato.

Esempio:

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

Decodificare l'Output di EXPLAIN ANALYZE

L'output di EXPLAIN ANALYZE può sembrare denso all'inizio, ma comprendere i suoi componenti chiave è fondamentale.

Componenti Principali:

• Tipo di Nodo: Identifica l'operazione eseguita (es., Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate).
• Costo: Presentato come (startup_cost .. total_cost).
  • startup_cost: Il costo per recuperare la prima riga.
  • total_cost: Il costo per recuperare tutte le righe.
  • Nota: I costi sono unità arbitrarie usate per il confronto, non tempo o memoria direttamente.
• Righe: Il numero stimato di righe che il planner si aspetta di restituire da questo nodo.
• Larghezza: La larghezza media stimata (in byte) delle righe restituite da questo nodo.
• Tempo Effettivo: Presentato come (startup_time .. total_time). Questo è il tempo effettivo in millisecondi per eseguire questo nodo.
  • startup_time: Tempo effettivo per restituire la prima riga.
  • total_time: Tempo effettivo per restituire tutte le righe.
• Righe Effettive: Il numero effettivo di righe restituite da questo nodo.
• Loop: Il numero di volte che questo nodo è stato eseguito. Per i nodi di primo livello, di solito è 1. Per operazioni annidate, può essere più alto.

Interpretazione dell'Output di Esempio:

Consideriamo un esempio semplificato di un Seq Scan (Scansione Sequenziale) su una tabella grande:

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

Interpretazione:

• Seq Scan on users: Il database sta leggendo ogni singola riga nella tabella users.
• cost=0.00..15000.00: Il planner ha stimato il costo totale intorno a 15000 unità.
• rows=1000000: Il planner ha stimato che ci fossero 1 milione di righe nella tabella.
• actual time=0.020..150.500: Ha effettivamente impiegato 150.5 millisecondi per completare la scansione e il filtro.
• rows=950000: Ha effettivamente restituito 950.000 righe (dopo il filtraggio).
• loops=1: Questa scansione è stata eseguita una volta.
• Filter: (registration_date > '2023-01-01'): Questa è la condizione applicata per filtrare le righe.
• Rows Removed by Filter: 50000: 50.000 righe sono state scartate dal filtro.

Identificazione del collo di bottiglia: Non cercare solo il actual time più grande. Cerca anche un nodo che viene eseguito molte volte. Una scansione interna di un nested loop che impiega 0.2 ms può sembrare innocua finché loops=50000. In quel caso, il costo reale è approssimativamente il tempo per loop moltiplicato per il numero di loop.

Leggere dall'Interno verso l'Esterno

I piani di PostgreSQL sono alberi. Il nodo superiore restituisce il risultato finale al client, ma il lavoro di solito inizia più in profondità nel piano. Quando una query unisce orders, customers e order_items, la riga superiore potrebbe essere un Aggregate, ma il vero problema potrebbe essere una scansione o un join sottostante.

Di solito leggo un piano in questo ordine:

1. Inizia dai nodi di scansione più profondi e chiediti: PostgreSQL ha letto molte più righe di quelle restituite dalla query?
2. Confronta le rows stimate con le rows effettive.
3. Controlla se i nodi costosi hanno loops elevati.
4. Cerca nodi Sort, Hash o Materialize che traboccano su disco.
5. Usa BUFFERS per decidere se la query è per lo più lavoro CPU/cache o I/O su disco.

Ecco un esempio comune:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Se vedi una scansione sequenziale su milioni di righe di orders, poi un sort, poi un limit, il database sta facendo troppo lavoro prima di poter restituire le 20 righe richieste. Un indice pratico potrebbe essere:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

Dopo di che, un buon piano potrebbe usare l'indice per andare direttamente agli ordini più recenti per quel cliente e fermarsi dopo 20 righe. Il piano esatto dipende dalla dimensione della tabella, dalle statistiche, dalla versione di PostgreSQL e dalla distribuzione dei dati, ma il principio è stabile: abbina l'indice al pattern di filtro e ordinamento che usi effettivamente.

Nodi Comuni del Piano di Query e Strategie di Ottimizzazione

Comprendere i diversi tipi di nodi e come ottimizzarli è fondamentale per padroneggiare le prestazioni delle query.

1. Scansione Sequenziale (Seq Scan)

• Cos'è: Legge ogni riga nella tabella. Spesso inefficiente per tabelle grandi, specialmente quando si filtrano condizioni specifiche.
• Quando va bene: Per tabelle piccole, o quando devi recuperare una grande percentuale delle righe della tabella. Una scansione sequenziale non è automaticamente negativa.
• Ottimizzazione: Crea un indice sulle colonne di filtro selettive, ma verificalo con il piano. Se un predicato restituisce la maggior parte della tabella, PostgreSQL potrebbe correttamente continuare a usare una scansione sequenziale.

2. Scansione Indice (Index Scan)

• Cos'è: Usa un indice per trovare le righe che corrispondono alla clausola WHERE. PostgreSQL attraversa l'indice e poi recupera le righe corrispondenti dalla tabella.
• Ottimizzazione: Assicurati che l'indice corrisponda alla forma della query. Per un indice composto, l'ordine delle colonne è importante. Un indice su (tenant_id, created_at) aiuta una query che filtra per tenant_id e ordina per created_at; potrebbe non aiutare molto per una query che filtra solo per created_at.

3. Scansione Solo Indice (Index Only Scan)

• Cos'è: Un Index Scan ottimizzato dove tutti i dati richiesti dalla query sono disponibili direttamente all'interno dell'indice. PostgreSQL non ha bisogno di visitare l'heap della tabella.
• Quando è efficiente: Quando tutte le colonne selezionate sono disponibili dall'indice e la mappa di visibilità permette a PostgreSQL di evitare molti controlli sull'heap.
• Ottimizzazione: Considera un indice coprente con INCLUDE per percorsi read-intensive, ma non aggiungere ogni colonna "per ogni evenienza." Indici più grandi costano di più da mantenere in scrittura.

4. Operazioni di Join (Nested Loop, Hash Join, Merge Join)

• Nested Loop: Per ogni riga nella relazione esterna, PostgreSQL scansiona la relazione interna. Efficiente per relazioni esterne piccole o quando la relazione interna può essere rapidamente accessibile tramite un indice.
• Hash Join: Costruisce una tabella hash da una relazione (il lato di costruzione) e la sonda con righe dell'altra relazione (il lato di sonda). Efficiente per tabelle grandi dove gli indici non sono utili per la condizione di join.
• Merge Join: Richiede che entrambe le relazioni siano ordinate sulle chiavi di join. Unisce le liste ordinate. Efficiente per input grandi e già ordinati.
• Ottimizzazione:
  • Assicurati che esistano indici sulle colonne di join.
  • Rivedi se stime errate delle righe hanno causato una scelta di join sbagliata. PostgreSQL non supporta suggerimenti nativi per l'ottimizzatore nello stesso stile di alcuni database, quindi le soluzioni usuali sono statistiche migliori, indici migliori o una riscrittura della query.
  • Controlla EXPLAIN ANALYZE per conteggi di loops elevati o actual time alto sui nodi di join.

5. Ordinamento (Sort)

• Cos'è: Ordina le righe. Può essere computazionalmente costoso, specialmente su grandi set di dati.
• Ottimizzazione:
  • Aggiungi un indice il cui ordine delle colonne corrisponda al pattern ORDER BY quando la query è sufficientemente selettiva.
  • Riduci il numero di righe da ordinare aggiungendo clausole WHERE più restrittive.
  • Assicurati che work_mem sia configurato sufficientemente per permettere l'ordinamento in memoria piuttosto che su disco.

6. Aggregazioni (Aggregate)

• Cos'è: Esegue operazioni come COUNT(), SUM(), AVG(), GROUP BY.
• Ottimizzazione:
  • Assicurati che le clausole WHERE siano efficienti, riducendo il numero di righe prima dell'aggregazione.
  • Considera l'uso di viste materializzate per dati pre-aggregati se l'aggregazione è un'operazione frequente e lenta.
  • Indici sulle colonne usate nelle clausole GROUP BY.

Usare EXPLAIN ANALYZE con Opzioni

EXPLAIN ANALYZE ha diverse opzioni utili che possono fornire informazioni ancora più dettagliate.

VERBOSE

• Cosa fa: Mostra informazioni aggiuntive sul piano di query, come i nomi delle tabelle qualificati dallo schema e i nomi delle colonne di output.

EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

• Cosa fa: Include i costi stimati nell'output. Questo è il comportamento predefinito, ma puoi disattivarlo esplicitamente.

EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

• Cosa fa: Riporta informazioni sull'uso dei buffer (condivisi, temporanei e locali). Questo aiuta a identificare i colli di bottiglia di I/O.
  • shared hit: Blocchi trovati nella cache dei buffer condivisi di PostgreSQL.
  • shared read: Blocchi letti dal disco nei buffer condivisi.
  • temp read/written: Blocchi letti/scritti in file temporanei (spesso per sort o hash che superano work_mem).

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

• Cosa fa: Include il tempo di avvio effettivo e il tempo totale per ogni nodo. Questo è il comportamento predefinito per ANALYZE.

EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

Combinare Opzioni

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

Consigli Pratici e Migliori Pratiche

• Inizia con EXPLAIN ANALYZE: Usa sempre EXPLAIN ANALYZE per l'analisi delle prestazioni nel mondo reale. EXPLAIN da solo è insufficiente.
• Concentrati su actual time: Dai priorità all'ottimizzazione dei nodi con il actual time più alto.
• Confronta rows (stimate vs. effettive): Grandi discrepanze indicano che il planner di query di PostgreSQL potrebbe fare supposizioni inaccurate. Questo può spesso essere risolto aggiornando le statistiche della tabella usando ANALYZE <nome_tabella>; o creando indici appropriati.
• Usa BUFFERS: Analizza l'uso dei buffer per capire se la tua query è vincolata dall'I/O.
• Testa con dati realistici: Esegui EXPLAIN ANALYZE su un database che ha una quantità rappresentativa di dati e una distribuzione dei dati simile al tuo ambiente di produzione.
• Ottimizza per fasi: Non cercare di ottimizzare tutto in una volta. Affronta prima il collo di bottiglia più grande.
• Considera work_mem: Se vedi letture significative su disco per ordinamento o hashing (temp read/written in BUFFERS), aumentare work_mem (per sessione o globalmente) potrebbe aiutare, ma fai attenzione all'uso della memoria.
• Indici saggiamente: Crea solo indici che sono effettivamente usati e vantaggiosi. Troppi indici possono rallentare le scritture e consumare spazio su disco.
• Controlla la versione di PostgreSQL: Le versioni più recenti hanno spesso planner di query migliorati e nuove funzionalità che possono influenzare le prestazioni.

Un Passaggio Pratico di Ottimizzazione

Prendi questa query:

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Se il piano mostra una scansione sequenziale, un indice su email da solo potrebbe non aiutare perché la query applica lower(email). PostgreSQL non può sempre usare un indice semplice quando l'espressione nella query differisce dal valore indicizzato. Un'opzione migliore potrebbe essere un indice di espressione:

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

Poi riesegui:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Stai cercando meno righe scansionate, meno buffer letti e un tempo di esecuzione inferiore. Se il piano ancora non usa l'indice, controlla se la tabella è piccola, se le statistiche sono obsolete o se la query non è scritta come pensi che l'applicazione la invii.

Un altro caso comune è un join che sembra a posto in SQL ma esplode nel piano:

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

Indici utili potrebbero includere orders(created_at), order_items(order_id) e la chiave primaria su products(id). Ma se gli ultimi sette giorni includono la maggior parte della tabella orders, orders(created_at) potrebbe non essere la soluzione principale. Il piano ti dice se il vero problema è il filtro sulla data, l'esplosione del join o un indice mancante sulla tabella figlia.

Una buona ottimizzazione delle query PostgreSQL non è "aggiungi un indice finché il piano non cambia." È un ciclo: misura il piano reale, fai una modifica difendibile, misura di nuovo e mantieni la modifica solo se migliora il carico di lavoro che ti interessa realmente.