Concetti base del piano di esecuzione: cost, rows, width

Quando scrivi una query SQL, PostgreSQL non la esegue subito. Prima attiva il suo "cervello" — ottimizzatore di query, che crea il piano di esecuzione. Questo piano è come un percorso su una mappa: PostgreSQL calcola quali azioni e in che ordine deve fare per ottenere i dati con successo.

L’ottimizzatore valuta tutte le possibili strade per eseguire la tua query: scansione sequenziale della tabella, uso degli indici, filtraggio, ordinamento ecc. Cerca di trovare il modo meno costoso (in termini di risorse) per eseguire la query. In pratica, cerca un compromesso tra il tempo di esecuzione e le risorse del server.

Parametri chiave del piano di esecuzione

Ok, ora passiamo al "succulento" — l’analisi dei parametri che PostgreSQL ti mostra dopo il comando EXPLAIN. Partiamo da un esempio semplice:

EXPLAIN
SELECT * FROM students WHERE age > 20;

Otterrai qualcosa del genere:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=72)
  Filter: (age > 20)

Vediamo cosa significano queste parole e numeri misteriosi.

1. cost (costo di esecuzione)

cost — è una stima di quante risorse serviranno per eseguire la query. Questo parametro ha due parti:

• Startup Cost: costo per iniziare l’operazione (tipo preparare un indice).
• Total Cost: costo totale per completare tutta l’operazione.

Esempio:

cost=0.00..35.00

• 0.00 — è lo Startup Cost.
• 35.00 — è il Total Cost.

Più basso è il valore di cost, più PostgreSQL preferisce quel piano. Però, ricorda che cost è un valore relativo. Non è in secondi o millisecondi, ma riflette una stima interna di PostgreSQL.

2. rows (numero stimato di righe)

rows mostra quante righe PostgreSQL si aspetta di restituire o processare in questa fase della query. Nel nostro esempio:

rows=7

Vuol dire che PostgreSQL pensa che il filtro age > 20 restituirà 7 righe. Questi dati vengono dalle statistiche che PostgreSQL raccoglie sulla tabella. Se le statistiche sono vecchie, la previsione può essere sbagliata. Questo può portare a un piano meno ottimale.

3. width (larghezza della riga in byte)

width — è la dimensione media di ogni riga restituita in questa fase, in byte. Nel nostro esempio:

width=72

Vuol dire che ogni riga restituita occupa in media 72 byte. width tiene conto della dimensione dei dati nelle colonne e di eventuali overhead, tipo identificatori di riga o info di servizio.

È un po’ come scaricare un’app. Se il suo "peso" (cioè width) è grande, ci metterai di più a scaricarla, anche se hai una connessione veloce (cioè cost).

Esempio di analisi di un piano di esecuzione

Vediamo un esempio reale. Supponiamo di avere una tabella students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    major VARCHAR(50)
);

E facciamo questa query:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Il risultato potrebbe essere così:

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan: PostgreSQL fa una scansione sequenziale della tabella students. Quindi passa su ogni riga.
• cost=0.00..42.50: Costo dell’operazione. Startup Cost è 0.00, il costo totale è 42.50.
• rows=3: PostgreSQL si aspetta che il filtro age > 20 AND major = 'CS' restituisca 3 righe.
• width=164: Ogni riga occupa in media 164 byte.

Ora hai capito come PostgreSQL prende le sue decisioni e puoi trovare i punti deboli nelle query. Per esempio, se vedi un cost alto, può voler dire che la query è pesante. Oppure, se vedi tante righe in rows, forse devi rivedere il filtro.

Come funziona cost nella pratica?

Aggiungiamo un indice sulla colonna age:

CREATE INDEX idx_age ON students(age);

Ora rifacciamo la query:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Il risultato può cambiare:

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Cosa è cambiato?

• Invece di Seq Scan ora c’è Bitmap Heap Scan: PostgreSQL prima trova le righe giuste nell’indice idx_age, poi le prende dalla tabella.
• cost è sceso di molto: ora lo Startup Cost è 4.37, il Total Cost è 20.50.
• L’operazione è diventata più efficiente grazie all’indice.

Visualizzazione: differenza tra Seq Scan e Index Scan

Ecco una tabellina di confronto, così è più chiaro:

Trappole e errori tipici

Quando usi i parametri del piano di esecuzione, preparati a qualche sorpresa. Per esempio, non sempre un cost basso vuol dire che la query va meglio. Se le statistiche del database sono vecchie (tipo dopo un aggiornamento massiccio della tabella), il piano può essere impreciso. Aggiorna le statistiche con il comando ANALYZE. Ne parliamo meglio nella prossima lezione.

Assicurati di usare gli indici dove servono. Ma non esagerare: occupano spazio e rallentano le operazioni di scrittura.