DK Damir Khaibulin
🇷🇺 RU Russo 🇬🇧 EN Inglese 🇩🇪 DE Tedesco 🇫🇷 FR Francese 🇮🇹 IT Italiano 🇪🇸 ES Spagnolo
Parliamo
← Torna agli articoli
Performance e database

Ottimizzazione delle query: EXPLAIN ed EXPLAIN ANALYZE in PostgreSQL + altri strumenti di analisi

Una guida pratica all'analisi delle query PostgreSQL: EXPLAIN ed EXPLAIN ANALYZE, più pg_stat_statements, auto_explain, pgBadger, Laravel Telescope e Percona PMM con esempi reali.

Parliamone ↗ Leggi l'articolo
Lingue disponibili
EN RU DE FR IT ES
✦
Articolo in evidenza ↗

Ottimizzazione delle query: EXPLAIN ed EXPLAIN ANALYZE in PostgreSQL + altri strumenti di analisi

📊 Cosa sono EXPLAIN ed EXPLAIN ANALYZE?

EXPLAIN è un comando in PostgreSQL (e in altri database SQL) che mostra il piano di esecuzione di una query senza eseguirla effettivamente. Risponde alla domanda: "Come intende il DBMS eseguire questa query?"

EXPLAIN ANALYZE è una versione più potente che esegue effettivamente la query e mostra metriche di esecuzione reali: tempo, numero di righe, indici utilizzati, ecc.

🎯 Perché ci servono?

Nell'era dei big data e delle applicazioni ad alto carico, comprendere le prestazioni del database è una competenza critica. Questi strumenti aiutano a:

  • Identificare i colli di bottiglia nelle prestazioni
  • Ottimizzare gli indici e la struttura del database
  • Comprendere il comportamento dell'ottimizzatore di query
  • Ridurre il carico sul server del database

📈 Esempio di utilizzo in Laravel con PostgreSQL

// EXPLAIN semplice $explain = DB::select('EXPLAIN SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explain); // EXPLAIN ANALYZE $explainAnalyze = DB::select('EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explainAnalyze); // Usare macro per comodità DB::macro('explain', function ($query, $bindings = []) { return DB::select('EXPLAIN ' . $query, $bindings); }); DB::macro('explainAnalyze', function ($query, $bindings = []) { return DB::select('EXPLAIN ANALYZE ' . $query, $bindings); }); // Esempio di utilizzo della macro $users = DB::explain('SELECT * FROM users WHERE active = true');

🔍 Analisi dei risultati di EXPLAIN

Un output tipico include:

Seq Scan on users (cost=0.00..15.00 rows=500 width=44) Filter: (email = 'user@example.com'::text)
  • Seq Scan — scansione sequenziale (può essere lenta)
  • Index Scan — scansione tramite indice (più veloce)
  • cost — costo stimato (più basso è meglio)
  • rows — numero di righe atteso

🛠 Altri strumenti di analisi per PostgreSQL

1. pg_stat_statements — Monitoraggio dell'esecuzione delle query

-- Abilitare l'estensione CREATE EXTENSION pg_stat_statements; -- Le query più costose SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2. auto_explain — Analisi automatica delle query lente

-- Abilitare in postgresql.conf shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '100ms' -- registra le query >100ms

3. pgBadger — Analizzatore di log PostgreSQL

# Installazione e utilizzo pgbadger /var/log/postgresql/postgresql-*.log -o report.html

4. Laravel Telescope — Diagnostica integrata in Laravel

// Abilitare il monitoraggio delle query in config/telescope.php 'watchers' => [ QueryWatcher::class => [ 'enabled' => env('TELESCOPE_QUERY_WATCHER', true), 'slow' => 100, // query lente >100ms ], ]

5. Percona Monitoring and Management (PMM) — Monitoraggio completo dei database

Piattaforma open-source per il monitoraggio e la gestione delle prestazioni di MySQL, MongoDB e PostgreSQL.

6. EXPLAIN (FORMAT JSON) — Analisi dettagliata delle query

-- Ottenere un piano di esecuzione dettagliato in formato JSON EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM users;

🚀 Esempi pratici di ottimizzazione in Laravel

Ottimizzazione del problema N+1

// MALE: N+1 query $users = User::all(); foreach ($users as $user) { echo $user->posts->count(); // Query separata per ogni utente } // BENE: eager loading $users = User::with('posts')->get(); foreach ($users as $user) { echo $user->posts->count(); // Tutti i dati già caricati }

Analisi di query complesse con utilizzo degli indici

// Creare un indice Schema::table('orders', function (Blueprint $table) { $table->index(['user_id', 'created_at']); }); // Analizzare la query $analysis = DB::explainAnalyze(" SELECT users.name, COUNT(orders.id) as order_count FROM users JOIN orders ON users.id = orders.user_id WHERE orders.created_at > NOW() - INTERVAL '30 days' GROUP BY users.id HAVING COUNT(orders.id) > 5 ");

📊 Guida al confronto degli strumenti

Esploriamo le principali differenze tra gli strumenti di analisi delle prestazioni del database:

EXPLAIN mostra il piano di esecuzione teorico senza eseguire la query. È perfetto per l'analisi preliminare delle query quando vuoi capire come PostgreSQL intende eseguire la tua istruzione SQL. Usalo durante lo sviluppo delle query per individuare potenziali problemi di prestazioni in anticipo.

EXPLAIN ANALYZE va oltre, eseguendo effettivamente la query e fornendo metriche di prestazioni reali. Questo strumento rivela tempi di esecuzione reali, conteggi di righe e utilizzo delle risorse. È essenziale per identificare i veri colli di bottiglia in ambienti simili alla produzione, ma dovrebbe essere usato con cautela sui sistemi di produzione a causa dell'esecuzione della query.

pg_stat_statements offre statistiche complete su tutte le query eseguite nel tuo database. Traccia la frequenza di esecuzione, il tempo totale e il tempo medio di esecuzione di tutte le query. È inestimabile per identificare le query più costose nella tua applicazione nel tempo.

auto_explain registra automaticamente le query lente in base a soglie configurabili. Una volta configurato, lavora silenziosamente in background, catturando i piani di esecuzione per le query che superano la soglia di durata definita. Perfetto per il monitoraggio continuo delle prestazioni senza intervento manuale.

Laravel Telescope fornisce il monitoraggio delle query a livello di applicazione all'interno dell'ecosistema Laravel. Mostra le query nel contesto della tua applicazione, incluse le relazioni Eloquent, le richieste HTTP e l'elaborazione dei job. Ideale per ambienti di sviluppo e staging.

pgBadger analizza i file di log di PostgreSQL per generare report HTML dettagliati sulle prestazioni del database. Aiuta a identificare pattern, query lente e problemi di connessione su periodi prolungati.

Percona Monitoring and Management offre monitoraggio di livello enterprise con dashboard di visualizzazione, sistemi di avviso e analisi delle query su più tecnologie di database.

💡 Consigli pratici

  1. Inizia con EXPLAIN per un'analisi rapida
  2. Usa EXPLAIN ANALYZE per dati precisi
  3. Crea indici basati sui risultati dell'analisi
  4. Monitora regolarmente — le prestazioni cambiano con la crescita dei dati
  5. Testa con volumi di dati realistici
  6. Combina gli strumenti per un'analisi completa
  7. Configura avvisi per il degrado delle prestazioni

🔗 Conclusione

Comprendere gli strumenti di analisi delle query non è un lusso ma una necessità per gli sviluppatori moderni. Nell'ecosistema Laravel abbiamo strumenti potenti sia a livello di database (PostgreSQL) che a livello di framework (Telescope).

Punti chiave da ricordare:

  • Analizza sempre le query prima del deployment
  • Gli indici risolvono l'80% dei problemi di prestazioni
  • Il monitoraggio regolare previene il degrado delle prestazioni
  • Combina più strumenti per una visibilità completa
  • Testa le prestazioni in condizioni realistiche
Tecnologie e argomenti

Tag dell'articolo

Nessun articolo corrisponde a questi filtri.

Hai un progetto o un'idea da discutere?

Parliamone ↗