Bilang mga tagapangasiwa ng database, kailangan nating maging nahuhumaling sa mga tool at pamamaraan ng pagpapahusay ng pagganap ng database.
Sa PostgreSQL, mayroon kaming access sa utos na EXPLAIN ANALYZE na nagbibigay-daan sa aming pag-aralan ang execution plan at performance ng isang ibinigay na query sa database. Ang utos ay nagbabalik ng isang detalyadong impormasyon sa kung paano pinoproseso ng database engine ang query. Kabilang dito ang pagkakasunud-sunod ng mga operasyong isinagawa, tinantyang mga gastos sa query, timing ng pagpapatupad, at higit pa.
Pagkatapos ay maaari naming gamitin ang impormasyong ito upang matukoy ang mga query sa database pati na rin tukuyin at ayusin ang mga potensyal na bottleneck sa pagganap.
Tinatalakay ng tutorial na ito kung paano gamitin ang command na EXPLAIN ANALYZE sa PostgreSQL para tingnan at i-optimize ang performance ng query.
PostgreSQL EXPLAIN ANALYZE
Ang utos ay medyo prangka. Una, kailangan nating ihanda ang utos na EXPLAIN ANALYZE sa simula ng query na nais nating suriin.
Ang command syntax ay ang mga sumusunod:
Ipaliwanag ang PAGSUSURISa sandaling naisakatuparan mo ang utos, ibabalik ng PostgreSQL ang isang detalyadong output tungkol sa ibinigay na query.
Pag-unawa sa EXPLAIN ANALYZE Query Output
Gaya ng nabanggit, sa sandaling patakbuhin natin ang utos na EXPLAIN ANALYZE, bubuo ang PostgreSQL ng isang detalyadong ulat ng query plan at ang mga istatistika ng pagpapatupad.
Ang output ay binubuo ng isang hanay ng mga column na naglalaman ng kapaki-pakinabang na impormasyon. Ang mga resultang hanay ay tulad ng ipinapakita sa kani-kanilang kahulugan:
QUERY PLAN – Ipinapakita ng column na ito ang execution plan ng tinukoy na query. Ang execution plan ay tumutukoy sa isang sequence ng mga operasyon na ginagawa ng database engine para matagumpay na makumpleto ang query.
PLANO – Ang pangalawang column ay ang PLAN column. Naglalaman ito ng textual na representasyon ng bawat operasyon o hakbang sa execution plan. Muli, ang bawat operasyon ay naka-indent upang ipahiwatig ang hierarchy ng mga operasyon.
KABUUANG GASTOS – Ang kabuuang hanay ng gastos ay kumakatawan sa tinantyang kabuuang halaga ng query. Ang gastos ay tumutukoy sa isang kamag-anak na sukat na ginagamit ng tagaplano ng query ng database upang matukoy ang pinakamainam na plano sa pagpapatupad.
TOTOONG HANAY – Ipinapakita ng column na ito ang eksaktong bilang ng mga row na pinoproseso sa bawat hakbang sa pagsasagawa ng query.
AKTWAL NA ORAS – Ipinapakita ng column na ito ang aktwal na oras na kinuha ng bawat operasyon na kinabibilangan ng parehong oras ng pagpapatupad ng operasyon at ang oras na ginugol sa mga mapagkukunan.
PANAHON NG PAGPAPLANO – Ipinapakita ng column na ito ang oras na kailangan ng query planner para makabuo ng execution plan. Kabilang dito ang kabuuang oras ng pag-optimize ng query at pagbuo ng plano.
PANAHON NG PAGSASANAY – Ipinapakita ng column na ito ang kabuuang oras upang maisagawa ang query. Kasama rin dito ang oras na ginugol sa pagpaplano at oras ng pagpapatupad ng query.
Halimbawa ng PostgreSQL EXPLAIN ANALYZE
Tingnan natin ang ilang pangunahing halimbawa ng paggamit ng pahayag na EXPLAIN ANALYZE.
Halimbawa 1: Piliin ang Pahayag
Gamitin natin ang statement na EXPLAIN ANALYZE para ipakita ang execution ng isang simpleng select statement sa PostgreSQL.
Sa sandaling patakbuhin namin ang nakaraang pahayag, dapat kaming makakuha ng isang output tulad ng sumusunod:
QUERY PLAN------------------------------------------------- -----------------
Seq Scan sa wp_users (cost=0.00..10.38 rows=10 width=2256) (aktwal na oras=0.009..0.010 rows=7 loops=1)
Filter: (id > 3)
Mga Hilera na Inalis ng Filter: 3
Oras ng Pagpaplano: 0.995 ms
Oras ng Pagpapatupad: 0.021 ms
(5 row)
Sa kasong ito, makikita natin na ang seksyon ng Query plan ay nagpapahiwatig na ang query ay nagsasagawa ng sequential scan sa wp_users table. Ang linya ng filter ay tumutukoy sa kundisyon na ginagamit upang i-filter ang mga resultang row.
Pagkatapos ay makikita namin ang 'Mga Rows na Inalis ng Filter' na nagpapakita ng bilang ng mga row na inaalis ng kundisyon ng filter.
Sa wakas, ipinapakita ng oras ng pagpapatupad ang kabuuang oras ng pagpapatupad ng query. Sa kasong ito, ang query ay tumatagal ng 0.021ms.
Halimbawa 2: Pagsusuri ng isang Join
Kumuha tayo ng mas kumplikadong query na nagsasangkot ng pagsali sa SQL. Para dito, ginagamit namin ang Pagila sample database. Maaari mong i-download at i-install ang sample na database sa iyong makina para sa layunin ng pagpapakita.
Maaari kaming magpatakbo ng isang simpleng pagsali tulad ng ipinapakita sa sumusunod:
ipaliwanag analisa SELECT f.title, c.nameMULA sa pelikula f
SUMALI sa film_category fc SA f.film_id = fc.film_id
SUMALI sa kategorya c SA fc.category_id = c.category_id;
Sa sandaling patakbuhin natin ang ibinigay na query, dapat nating makita ang output tulad ng sumusunod:
I-explore natin ang sumusunod na query plan:
- Nested Loop – Ito ay nagpapahiwatig na ang pagsali ay gumagamit ng isang nested loop na diskarte sa pagsali.
- Hash Join – Ang operasyong ito ay sumasali sa film_category at sa mga talahanayan ng pelikula gamit ang algorithm ng Hash join. Ang operasyong ito ay may halagang 77.50 at tinatayang 1000 row. Gayunpaman, ang aktwal na oras na kinuha para sa operasyong ito ay 0.254 hanggang 0.439 millisecond, at kumukuha ito ng 1000 row.
- Hash Cond – Isinasaad nito na ang kondisyon ng pagsali ay gumagamit ng Hash na pagsali upang tumugma sa mga column ng film_id at sa mga column ng film_category sa mga talahanayan ng pelikula.
- Seq Scan sa film_category – Ang operasyong ito ay nagsasagawa ng sequential scan sa film_category table na may halagang 16.00 at tinatayang 1000 row. Ang aktwal na oras na kinuha para sa operasyong ito ay 0.008 hanggang 0.056 millisecond, at kumukuha ito ng 1000 row.
- Seq Scan sa pelikula – Nagsasagawa ang query ng sunud-sunod na pag-scan sa talahanayan ng pelikula na may resultang tinantyang at aktwal na mga gastos at mga hilera sa operasyong ito.
- Memoize – Ini-cache ng operasyong ito ang mga resulta ng pagsasama sa pagitan ng film_category at mga talahanayan ng pelikula para sa kasunod na paggamit.
- Cache Key – Ito ay nagpapahiwatig na ang cache key na ginagamit para sa memoization ay batay sa category_id column mula sa film_category.
- Mode ng Cache - Ipinapahiwatig nito na ang query ay gumagamit ng lohikal na mode ng cache.
- Mga Hit, Miss, Evictions, Overflows – Ang tatlong linya ay nagbibigay ng mga istatistika tungkol sa cache, bilang ng mga hit, miss, evictions, at overflows sa panahon ng pagpapatupad. Kasama rin sa block na ito ang paggamit ng memory sa panahon ng pagpapatupad ng query.
- Index Scan gamit ang category_pkey – Ipinapakita nito ang operasyon na nagsasagawa ng index scan sa talahanayan ng kategorya gamit ang primary key index.
- Index Cond – Ipinapakita nito na ang index scan ay batay sa kundisyon na tumutugma sa column ng category_id sa talahanayan ng kategorya.
- Oras ng Pagpaplano – Ipinapakita ng linyang ito ang oras na kinuha para sa pagpaplano ng query na 3.005 millisecond.
- Oras ng Pagpapatupad – Sa wakas, ipinapakita ng linyang ito ang kabuuang oras ng pagpapatupad ng query na 0.745 millisecond.
Ayan na! Isang detalyadong impormasyon tungkol sa pagsasagawa ng isang simpleng pagsali sa PostgreSQL.
Konklusyon
Natuklasan mo ang kapangyarihan at paggamit ng pahayag na EXPLAIN ANALYZE sa PostgreSQL. Ang pahayag na EXPLAIN ANALYZE ay isang mahusay na tool para sa pagsusuri at pag-optimize ng query. Gamitin ang tool na ito upang bumuo ng mahusay at hindi gaanong resource-intensive na mga query.