Uso di EXPLAIN ANALYZE per misurare il tempo reale di esecuzione delle query

Se il comando EXPLAIN ti permette di guardare nella sfera di cristallo e vedere come PostgreSQL “pianifica” di eseguire una query, allora EXPLAIN ANALYZE ti trasforma in un vero detective che scopre cosa è successo davvero.

Differenze chiave tra EXPLAIN e EXPLAIN ANALYZE:

EXPLAIN – è la teoria, mostra come PostgreSQL pianifica di eseguire la query. Vedi valori stimati come il numero di righe (rows) e il costo di esecuzione (cost).

EXPLAIN ANALYZE – è la pratica. PostgreSQL esegue davvero la query e mostra:

• Il numero reale di righe processate in ogni step.
• Il tempo reale di esecuzione di ogni operazione.
• Il confronto con le ipotesi del piano (rows e cost).

Esempio: se la tua query dovrebbe processare 100 righe, ma in realtà ne processa 10 000, EXPLAIN ANALYZE ti svela subito questo fatto poco elegante!

Sintassi base e utilizzo

Come per EXPLAIN, EXPLAIN ANALYZE è facilissimo da usare. Basta aggiungere la parola ANALYZE al tuo comando EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ecco cosa fa PostgreSQL:

• Esegue la query.
• Registra ogni operazione nel piano di esecuzione, inclusi i dati reali.
• Restituisce una descrizione completa del processo di esecuzione della query.

Quali dati fornisce EXPLAIN ANALYZE?

Tempo reale di esecuzione delle operazioni:

• Actual Start Time: quando l’operazione è iniziata.
• Actual End Time: quando l’operazione è finita.

Numero totale di righe processate:

Questo aiuta a valutare quanto sono accurate le ipotesi del piano (valori rows).

Info sui buffer:

Come sono stati usati i buffer su disco e in memoria.

Esempio di utilizzo di EXPLAIN ANALYZE

Diamo un’occhiata a un esempio concreto. Abbiamo una tabella students che contiene dati sugli studenti:

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

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Eseguiamo una query per estrarre gli studenti con più di 20 anni:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Esempio di risultato:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Analizziamo il risultato:

• Seq Scan – indica che PostgreSQL sta facendo una scansione sequenziale della tabella.
• cost=0.00..14.00 – è il costo stimato dell’operazione.
• rows=2 – PostgreSQL si aspetta che la query restituisca 2 righe (e ci ha azzeccato!).
• actual time=0.025..0.026 – tempo reale di esecuzione dell’operazione (in millisecondi).
• Rows Removed by Filter: 2 – due righe sono state filtrate perché non rispettavano la condizione WHERE.

Confronto tra teoria e pratica

Ecco dov’è la magia di EXPLAIN ANALYZE: ti mostra come la query è stata davvero eseguita e ti permette di confrontarla con il piano teorico di esecuzione.

Diamo un’occhiata a un esempio più complesso.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Esempio di risultato:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Cosa vediamo:

1. PostgreSQL ha eseguito la query in 0.057 millisecondi.
2. Solo una riga (rows=1) soddisfa le condizioni WHERE.
3. Tre righe sono state filtrate (Rows Removed by Filter: 3).

Riepilogo

Usare EXPLAIN ANALYZE ti permette di trovare i colli di bottiglia e capire come ottimizzare le query. Per esempio:

• Se Seq Scan è troppo “pesante”, forse è il momento di aggiungere un indice.
• Se le ipotesi di PostgreSQL sono molto diverse dai dati reali, controlla le statistiche delle tabelle (ANALYZE) o la struttura degli indici.

2

Compito

SQL SELF, livello 41, lezione 3

Bloccato

Analisi dell'esecuzione di una query con filtraggio e ordinamento

Analisi dell'esecuzione di una query con filtraggio e ordinamento

Apri