Ca administratori de baze de date, trebuie să fim obsedați de instrumentele și metodele de îmbunătățire a performanței bazei de date.
În PostgreSQL, avem acces la comanda EXPLAIN ANALYZE care ne permite să analizăm planul de execuție și performanța unei anumite interogări de bază de date. Comanda returnează informații detaliate despre modul în care motorul bazei de date procesează interogarea. Aceasta include secvența operațiunilor efectuate, costurile estimate de interogare, timpul de execuție și multe altele.
Putem apoi folosi aceste informații pentru a identifica interogările bazei de date, precum și pentru a identifica și remedia potențialele blocaje de performanță.
Acest tutorial discută cum să utilizați comanda EXPLAIN ANALYZE în PostgreSQL pentru a vizualiza și optimiza performanța interogării.
PostgreSQL EXPLICA ANALIZA
Comanda este destul de simplă. Mai întâi, trebuie să punem înainte comanda EXPLAIN ANALYZE la începutul interogării pe care dorim să o analizăm.
Sintaxa comenzii este următoarea:
EXPLICAȚI ANALIZAOdată ce executați comanda, PostgreSQL returnează o ieșire detaliată despre interogarea furnizată.
Înțelegerea rezultatului interogării EXPLAIN ANALYZE
După cum am menționat, odată ce rulăm comanda EXPLAIN ANALYZE, PostgreSQL generează un raport detaliat al planului de interogare și al statisticilor de execuție.
Ieșirea este compusă dintr-un set de coloane care conțin informații utile. Coloanele rezultate sunt așa cum se arată cu semnificația lor respectivă:
PLAN DE INTEROGARE – Această coloană afișează planul de execuție al interogării specificate. Planul de execuție se referă la o secvență de operații pe care motorul bazei de date le efectuează pentru a finaliza cu succes interogarea.
PLAN – A doua coloană este coloana PLAN. Acesta conține o reprezentare textuală a fiecărei operațiuni sau pas din planul de execuție. Din nou, fiecare operație este indentată pentru a indica ierarhia operațiunilor.
COST TOTAL – Coloana cost total reprezintă costul total estimat al interogării. Costul se referă la o măsură relativă pe care planificatorul de interogări de bază de date o folosește pentru a determina planul optim de execuție.
RANDURI REALE – Această coloană arată numărul exact de rânduri care sunt procesate la fiecare pas din execuția interogării.
TIMP ACTUAL – Această coloană arată timpul efectiv luat de fiecare operațiune care include atât timpul de execuție al operației, cât și timpul alocat resurselor.
TIMP DE PLANIFICARE – Această coloană arată timpul necesar planificatorului de interogări pentru a genera un plan de execuție. Aceasta include timpul total de optimizare a interogării și generarea planului.
TIMPUL DE EXECUȚIE – Această coloană arată timpul total pentru executarea interogării. Aceasta include, de asemenea, timpul petrecut cu planificarea și timpul de execuție a interogărilor.
PostgreSQL EXPLICA ANALIZA Exemplu
Să ne uităm la câteva exemple de bază de utilizare a instrucțiunii EXPLAIN ANALYZE.
Exemplul 1: Selectați Declarație
Să folosim instrucțiunea EXPLAIN ANALYZE pentru a arăta execuția unei instrucțiuni select simple în PostgreSQL.
Odată ce rulăm instrucțiunea anterioară, ar trebui să obținem o ieșire după cum urmează:
PLAN DE INTEROGARE-------------------------------------------------- -----------------
Seq Scan on wp_users (cost=0,00..10,38 rânduri=10 lățime=2256) (timp real=0,009..0,010 rânduri=7 bucle=1)
Filtru: (id > 3)
Rânduri eliminate de filtru: 3
Timp de planificare: 0,995 ms
Timp de execuție: 0,021 ms
(5 rânduri)
În acest caz, putem vedea că secțiunea Plan de interogare indică faptul că interogarea efectuează o scanare secvențială pe tabelul wp_users. Linia de filtrare denotă condiția care este utilizată pentru a filtra rândurile rezultate.
Vedem apoi „Rândurile eliminate de filtru” care arată numărul de rânduri care sunt eliminate de condiția de filtru.
În cele din urmă, timpul de execuție arată timpul total de execuție al interogării. În acest caz, interogarea durează 0,021 ms.
Exemplul 2: Analizarea unei asocieri
Să luăm o interogare mai complexă care implică o îmbinare SQL. Pentru aceasta, folosim baza de date eșantion Pagila. Puteți descărca și instala baza de date eșantion pe mașina dvs. în scop demonstrativ.
Putem rula o alăturare simplă, așa cum se arată în următoarele:
explica analiza SELECT f.titlu, c.numeDIN filmul f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN categoria c ON fc.category_id = c.category_id;
Odată ce rulăm interogarea dată, ar trebui să vedem rezultatul după cum urmează:
Să explorăm următorul plan de interogare:
- Bucla imbricată – Aceasta indică faptul că îmbinarea folosește o strategie de îmbinare a buclei imbricate.
- Hash Join – Această operație unește tabelele film_category și film folosind un algoritm Hash join. Această operațiune are un cost de 77,50 și este estimat la 1000 de rânduri. Cu toate acestea, timpul real necesar pentru această operație este de 0,254 până la 0,439 milisecunde și preia 1000 de rânduri.
- Hash Cond – Aceasta indică faptul că condiția de îmbinare utilizează o îmbinare Hash pentru a potrivi coloanele film_id și coloanele film_category din tabelele film.
- Seq Scan on film_category – Această operație realizează o scanare secvențială pe tabelul film_category cu un cost de 16,00 și 1000 de rânduri estimate. Timpul efectiv necesar pentru această operație este de 0,008 până la 0,056 milisecunde și preia 1000 de rânduri.
- Seq Scan on film – Interogarea efectuează o scanare secvențială pe tabelul de film cu costurile și rândurile estimate și reale rezultate în această operație.
- Memoize – Această operație memorează în cache rezultatele îmbinării dintre film_category și tabelele de film pentru utilizare ulterioară.
- Cheia cache – Aceasta indică faptul că cheia cache care este utilizată pentru memorare se bazează pe coloana categorie_id din film_category.
- Cache Mode – Aceasta indică faptul că interogarea folosește modul logic cache.
- Hits, Misses, Evictions, Overflows – Cele trei linii oferă statistici despre memoria cache, numărul de accesări, rateuri, evacuări și depășiri în timpul execuției. Acest bloc include, de asemenea, utilizarea memoriei în timpul executării interogării.
- Scanare index folosind category_pkey – Aceasta arată operația care efectuează o scanare index pe tabelul de categorii folosind indexul cheii primare.
- Index Cond – Aceasta arată că scanarea indexului se bazează pe condiția care se potrivește cu coloana categorie_id din tabelul de categorii.
- Timp de planificare – Această linie arată timpul necesar pentru planificarea interogărilor, care este de 3,005 milisecunde.
- Timp de execuție – În cele din urmă, această linie arată timpul total de execuție al interogării, care este de 0,745 milisecunde.
Iată-l! O informație detaliată despre execuția unei simple join în PostgreSQL.
Concluzie
Ați descoperit puterea și utilizarea instrucțiunii EXPLAIN ANALYZE în PostgreSQL. Declarația EXPLAIN ANALYZE este un instrument puternic pentru analiza și optimizarea interogărilor. Utilizați acest instrument pentru a crea interogări eficiente și care necesită mai puține resurse.