Uno degli strumenti che rendono Postgres prevalentemente forte in termini di prestazioni è il suo comando EXPLAIN che fornisce dettagli sul piano di esecuzione di una query SQL.
Cos'è il comando EXPLAIN?
Il comando EXPLAIN visualizza il piano di esecuzione che il pianificatore PostgreSQL genera per una data istruzione SQL.
Ciò include informazioni sui costi stimati per l'esecuzione di ogni fase della query. Esaminando questi costi, possiamo capire perché una query viene eseguita lentamente e come ottimizzarla.
PostgreSQL spiega il costo
Utilizziamo principalmente il comando EXPLAIN per recuperare le informazioni su una determinata query. Il comando restituisce alcune informazioni sulla query.
Prendiamo ad esempio la query mostrata di seguito:
SELEZIONA f.titolo, c.nome
DAL film f
ISCRIVITI a film_category fc ON f.film_id = fc.film_id
UNISCITI alla categoria c ON fc.category_id = c.category_id;
Se eseguiamo il comando spieghi sul precedente join semplice:
SPIEGARE SELEZIONA f.titolo, c.nomeDAL film f
ISCRIVITI a film_category fc ON f.film_id = fc.film_id
UNISCITI alla categoria c ON fc.category_id = c.category_id;
Dovremmo ottenere l'output come mostrato di seguito:
Potresti notare che per ogni passaggio della query, PostgreSQL restituisce il costo stimato di tale query.
- startup_cost - Mostra il costo stimato per inizializzare l'operazione prima che possa iniziare a produrre le righe.
- total_cost – Il costo totale per recuperare tutte le righe.
- Righe: determina il numero stimato di righe che vengono restituite dalla query.
- Larghezza: determina il numero medio di byte di tutte le righe restituite dall'operazione.
I costi delle query in PostgreSQL sono indicati in unità arbitrarie determinate dai parametri di costo impostati nella configurazione del server.
La chiave di questi parametri è seq_page_cost che imposta il costo del recupero di una pagina su disco non presente nella cache.
Se sei su pgAdmin, puoi utilizzare la funzione 'spiega analizza' per ottenere un diagramma più leggibile e ben presentato per il comando spieghi. Per esempio:
È quindi possibile fare clic su ogni passaggio per visualizzare le statistiche e i costi stimati.
Ottimizzazione delle query in base al costo
Quando si ottimizzano le query, è essenziale comprendere che il costo inferiore di solito significa un'esecuzione più rapida. Pertanto, puoi modificare le tue query a un costo inferiore.
Ecco alcuni fattori da considerare:
Usa gli indici delle tabelle – Gli indici riducono significativamente il costo delle query basate sulla ricerca mantenendo una struttura dei dati che consente una ricerca più rapida.
Usa le funzioni e gli operatori con saggezza – La stima dei costi per funzioni e operatori non è sempre accurata, in quanto il costo effettivo può dipendere fortemente dai dati specifici. Pertanto, limitare al minimo l'uso di funzioni e operatori complessi.
Conclusione
Abbiamo esplorato il concetto di costi in un comando PostgreSQL ANALYZE. Abbiamo discusso del significato dell'output del comando e di come utilizzare l'output del costo per analizzare il modo più efficiente per eseguire una determinata query.