🥇PostgreSQL Query Profiler: carane cocog rencana lan pitakon

Akeh sing wis nggunakake nerangake.tensor.ru - layanan visualisasi rencana PostgreSQL kita bisa uga ora ngerti babagan salah sawijining superpower - ngowahi potongan log server sing angel diwaca ...

... dadi pitakon sing dirancang kanthi apik kanthi petunjuk kontekstual kanggo simpul rencana sing cocog:

Ing transkrip iki saka bagean kapindho kang laporan ing PGConf.Russia 2020 Aku bakal pitutur marang kowe carane kita bisa nindakake iki.

Transkrip saka bagean pisanan, darmabakti kanggo masalah kinerja query khas lan solusi, bisa ditemokake ing artikel "Resep kanggo pitakon SQL sing lara".

Pisanan, ayo miwiti pewarnaan - lan ora bakal diwarnai maneh rencana kasebut, wis diwarnai, wis apik lan bisa dingerteni, nanging panjaluk.

Kayane karo "lembar" sing ora diformat kaya ngono, panjaluk sing ditarik saka log katon ala banget lan mulane ora trep.

Utamané nalika pangembang "lem" awak panjalukan ing kode (iki, mesthi, antipattern, nanging mengkono) ing siji baris. nggegirisi!

Ayo nggambar iki kanthi luwih apik.

Lan yen kita bisa nggambar iki apik, yaiku, mbongkar lan sijine maneh awak panjalukan, banjur kita bisa "masang" Petunjuk kanggo saben obyek panjalukan iki - apa kedaden ing titik sing cocog ing rencana.

Pohon sintaksis pitakon

Kanggo nindakake iki, panjalukan kudu diurai dhisik.

Amarga kita duwe inti saka sistem mlaku ing NodeJS, banjur kita digawe modul kanggo, sampeyan bisa temokake ing GitHub. Nyatane, iki minangka "pengikat" lengkap menyang internal parser PostgreSQL dhewe. Tegese, tata basa mung dikompilasi binar lan ikatan digawe saka NodeJS. Kita njupuk modul wong liya minangka basis - ora ana rahasia gedhe ing kene.

We feed awak panjalukan minangka input kanggo fungsi kita - ing output kita njaluk wit sintaks parsed ing wangun obyek JSON.

Saiki kita bisa mbukak liwat wit iki ing arah ngelawan lan ngumpulake panjalukan karo indents, werna, lan format sing kita arep. Ora, iki ora bisa dikustomisasi, nanging kayane kita bakal trep.

Pemetaan pitakon lan simpul rencana

Saiki ayo ndeleng kepiye cara nggabungake rencana sing dianalisis ing langkah pertama lan pitakon sing dianalisis ing langkah kapindho.

Coba conto sing prasaja - kita duwe pitakon sing ngasilake CTE lan diwaca kaping pindho. Dheweke nggawe rencana kasebut.

CTE

Yen dideleng kanthi teliti, nganti versi 12 (utawa diwiwiti saka tembung kunci MATERIALIZED) tatanan CTE minangka penghalang mutlak kanggo perencana.

Iki tegese yen kita ndeleng generasi CTE nang endi wae ing request lan simpul nang endi wae ing rencana CTE, banjur kelenjar iki mesthi "perang" karo siji liyane, kita bisa langsung gabungke.

Masalah karo tanda bintang: CTEs bisa nested.

Ana sing nested banget, lan malah sing jeneng padha. Contone, sampeyan bisa nang CTE A nggawe CTE X, lan ing tingkat padha nang CTE B gawe maneh CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Nalika mbandhingake, sampeyan kudu ngerti iki. Ngerteni iki "kanthi mripatmu" - malah ndeleng rencana, malah ndeleng awak panjaluk - angel banget. Yen generasi CTE sampeyan rumit, bersarang, lan panjaluke gedhe, mula ora sadar.

UNION

Yen kita duwe tembung kunci ing pitakon UNION [ALL] (operator nggabungake rong conto), banjur ing rencana kasebut cocog karo simpul Append, utawa sawetara Recursive Union.

Sing "ndhuwur" ing ndhuwur UNION - iki turunan pisanan saka simpul kita, kang "ing ngisor" - kaloro. Yen liwat UNION kita duwe sawetara pamblokiran "terpaku" bebarengan, banjur Append-Isih ana siji simpul, nanging ora bakal duwe loro, nanging akeh anak - ing urutan padha pindhah, mungguh:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Masalah karo tanda bintang: generasi sampling rekursif ing njero (WITH RECURSIVE) uga bisa luwih saka siji UNION. Nanging mung blok paling pungkasan sawise sing pungkasan tansah rekursif UNION. Kabeh ing ndhuwur iku siji, nanging beda UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Sampeyan uga kudu bisa "kelet" conto kasebut. Ing conto iki kita weruh sing UNION- ana 3 bagean ing panyuwunan kita. Mangkono, siji UNION соответствует Append-node, lan liyane- Recursive Union.

Data diwaca-tulis

Kabeh wis ditata, saiki kita ngerti bagean panjaluk sing cocog karo rencana kasebut. Lan ing potongan kasebut kita bisa kanthi gampang lan alami nemokake obyek sing "bisa diwaca".

Saka sudut pandang pitakon, kita ora ngerti apa tabel utawa CTE, nanging padha ditetepake dening simpul sing padha. RangeVar. Lan babagan "keterbacaan", iki uga minangka set simpul sing cukup winates:

Seq Scan on [tbl]
Bitmap Heap Scan on [tbl]
Index [Only] Scan [Backward] using [idx] on [tbl]
CTE Scan on [cte]
Insert/Update/Delete on [tbl]

Kita ngerti struktur rencana lan pitakon, kita ngerti korespondensi pamblokiran, kita ngerti jeneng obyek - kita nggawe perbandingan siji-kanggo-siji.

maneh tugas "karo tanda bintang". Kita njupuk panjaluk kasebut, nglakokake, kita ora duwe alias - kita mung maca kaping pindho saka CTE sing padha.

Kita ndeleng rencana - apa masalahe? Napa kita duwe alias? Kita ora pesen. Ngendi dheweke entuk "nomer nomer" kaya ngono?

PostgreSQL nambahake dhewe. Sampeyan mung kudu ngerti mung alias kuwi kanggo kita, kanggo tujuan comparison karo rencana, iku ora nggawe raos sembarang, iku mung ditambahake kene. Ayo ora nggatekake dheweke.

Kapindho tugas "karo tanda bintang": yen kita maca saka tabel partisi, banjur kita bakal entuk simpul Append utawa Merge Append, kang bakal kalebu nomer akeh "anak", lan saben kang bakal piye wae Scan'om saka tabel-bagean: Seq Scan, Bitmap Heap Scan utawa Index Scan. Nanging, ing kasus apa wae, "bocah-bocah" iki ora bakal dadi pitakon rumit - iki carane simpul kasebut bisa dibedakake saka Append ing UNION.

Kita uga ngerti knot kasebut, ngumpulake "ing siji tumpukan" lan ujar: "kabeh sing diwaca saka megatable ana ing ngisor wit".

"Prasaja" node panampa data

Values Scan cocog ing rencana VALUES ing panyuwunan.

Result punika panyuwunan tanpa FROM kaya SELECT 1. Utawa nalika sampeyan duwe ekspresi sengaja palsu ing WHERE-block (banjur atribut katon One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan "peta" menyang SRFs jeneng sing padha.

Nanging kanthi pitakon bersarang kabeh luwih rumit - sayangé, ora mesthi dadi InitPlan/SubPlan. Kadhangkala padha dadi ... Join utawa ... Anti Join, utamané nalika sampeyan nulis kaya WHERE NOT EXISTS .... Lan ing kene ora bisa digabungake - ing teks rencana ora ana operator sing cocog karo simpul rencana kasebut.

maneh tugas "karo tanda bintang": sawetara VALUES ing panyuwunan. Ing kasus iki lan ing rencana sampeyan bakal entuk sawetara simpul Values Scan.

Suffixes "nomer" bakal mbantu mbedakake siji liyane - ditambahake persis ing urutan sing ditemokake. VALUES- pamblokiran bebarengan request saka ndhuwur kanggo ngisor.

Ngolah data

Kayane kabeh ing panyuwunan kita wis diurutake - sing isih ana Limit.

Nanging ing kene kabeh prasaja - simpul kayata Limit, Sort, Aggregate, WindowAgg, Unique "map" siji-kanggo-siji kanggo operator cocog ing request, yen padha ana. Ora ana "lintang" utawa kangelan ing kene.

NGGABUNGA

Kesulitan muncul nalika kita pengin gabungke JOIN antarane awake dhewe. Iki ora tansah bisa, nanging bisa.

Saka sudut pandang parser pitakon, kita duwe simpul JoinExpr, sing persis anak loro - kiwa lan tengen. Iki, miturut, apa "ing ndhuwur" JOIN lan apa sing ditulis "ing ngisor" ing panjalukan.

Lan saka titik tampilan rencana, iki loro turunane sawetara * Loop/* Join- simpul. Nested Loop, Hash Anti Join,... - kaya ngono.

Ayo nggunakake logika prasaja: yen kita duwe tabel A lan B sing "gabung" saben liyane ing rencana, banjur ing panyuwunan padha bisa ditemokake A-JOIN-B, utawa B-JOIN-A. Coba gabungke cara iki, coba gabungke kanthi cara liya, lan sateruse nganti entek pasangan kasebut.

Ayo njupuk wit sintaks kita, njupuk rencana kita, deleng wong-wong mau ... ora padha!

Ayo digambar maneh ing wangun grafik - oh, wis katon kaya!

Wigati dicathet yen kita duwe kelenjar sing bebarengan duwe anak B lan C - kita ora peduli ing urutan apa. Ayo gabungke lan nguripake gambar simpul kasebut.

Ayo dideleng maneh. Saiki kita duwe kelenjar karo anak A lan pasangan (B + C) - kompatibel karo wong-wong mau.

apik tenan! Pranyata kita loro iki JOIN saka panjalukan karo simpul rencana kasil digabungake.

Sayange, masalah iki ora tansah ditanggulangi.

Contone, yen ing panjalukan A JOIN B JOIN C, lan ing rencana, pisanan kabeh, simpul "njaba" A lan C padha disambungake. Nanging ora ana operator kuwi ing panjalukan, kita ora duwe apa-apa kanggo nyorot, apa-apa kanggo masang pitunjuk kanggo. Semono uga karo "koma" nalika nulis A, B.

Nanging, ing umume kasus, meh kabeh simpul bisa "diikat" lan sampeyan bisa entuk profil kaya iki ing sisih kiwa ing wektu - secara harfiah, kaya ing Google Chrome nalika sampeyan nganalisa kode JavaScript. Sampeyan bisa ndeleng suwene saben baris lan saben pratelan njupuk "eksekusi."

Lan supaya luwih trep kanggo sampeyan nggunakake kabeh iki, kita wis nggawe panyimpenan arsip, ngendi sampeyan bisa nyimpen lan mengko golek plans bebarengan karo panjalukan sing gegandhengan utawa nuduhake link karo wong.

Yen sampeyan mung kudu nggawa pitakon sing ora bisa diwaca menyang formulir sing nyukupi, gunakake "normalizer" kita.

Source: www.habr.com

PostgreSQL Query Profiler: carane cocog rencana lan pitakon