PostgreSQL Profiler Su'aal: sida loo waafajiyo qorshaha iyo weydiinta

Qaar badan oo hore u isticmaalay sharax.tensor.ru - Adeegeena arajinta qorshaha PostgreSQL waxa laga yaabaa in aanu ka warqabin mid ka mid ah awoodihiisa sare - u rogaya qayb adag in la akhriyo ee log server-ka

.

Qoraalkan qaybtiisa labaad Warbixinta PGConf.Russia 2020 Waxaan kuu sheegi doonaa sida aan ugu suurtagashay in aan tan samayno.

Nuqulka qaybta koowaad, oo u heellan dhibaatooyinka waxqabadka su'aalaha caadiga ah iyo xalkooda, ayaa laga heli karaa maqaalka "Cuntada loogu talagalay su'aalaha SQL ee jirran".

Marka hore, aynu bilowno midabaynta - oo annagu mar dambe ma midabayn doono qorshaha, waxaan horay u midabnay, waxaan horay u haysannay qurux badan oo la fahmi karo, laakiin codsi.

Waxay nooga muuqatay in "sheet" aan qaabeyn oo kale ah codsiga laga soo jiiday log uu u muuqdo mid aad u fool xun oo sidaas darteed aan ku habboonayn.


Gaar ahaan marka horumariyayaashu "ku dhejiyaan" jirka codsiga ee code (tani waa, dabcan, antipattern, laakiin way dhacdaa) hal xariiq. Naxdin!

Aan u sawirno kan si ka sii qurux badan.


Oo haddii aan si qurux badan u sawiri karno, taas oo ah, kala furfuri iyo dib u soo celinta jirka codsiga, ka dibna waxaan "ku dhejin karnaa" tilmaam shay kasta oo codsigan ah - waxa dhacay barta u dhiganta qorshaha.

Waydii geedka syntax

Si tan loo sameeyo, codsiga waa in marka hore la kala saaro.


Sababtoo ah waxaan leenahay xudunta nidaamka waxay ku socotaa NodeJS, ka dibna waxaan u samaynay module ah, aad awoodid ka hel GitHub. Dhab ahaantii, kuwan waxaa lagu kordhiyey "ku xirnaanta" gudaha gudaha PostgreSQL parser laftiisa. Taasi waa, naxwaha si fudud ayaa loo soo ururiyey binary waxaana lagu xirayaa NodeJS. Waxaan u qaadannay cutubyada dadka kale saldhig ahaan - halkan ma jirto sir weyn.

Waxaan quudineynaa jirka codsiga si aan u galno shaqadeena - marka la soo saaro waxaan helnaa geed syntax ah oo la jarjaray oo qaab JSON ah.


Hadda waxaan ku dhex ordi karnaa geedkan jihada ka soo horjeeda oo aan ku ururin karno codsi leh godad, midabaynta, iyo qaabeynta aan rabno. Maya, tani maaha wax la beddeli karo, laakiin waxay noo muuqatay in tani ay ku habboon tahay.

Weydiinta khariidadeynta iyo noodhadhka qorshaynta

Haddaba bal aynu eegno sida aynu isugu dari karno qorshihii aynu ku lafo-gurnay talaabadii hore iyo waydiinta aynu ku lafo-guraynay ta labaad.

Aan soo qaadano tusaale fudud - waxaan haynaa su'aal soo saarta CTE oo laba jeer ka akhriday. Wuxuu abuuraa qorshe noocaas ah.

CTE

Haddii aad si taxadar leh u eegto, ilaa nooca 12 (ama ka bilow erayga muhiimka ah MATERIALIZED) samaynta CTE waxay caqabad buuxda u tahay qorshaysha.


Tani waxay ka dhigan tahay haddii aan aragno jiilka CTE meel codsiga iyo noode meel ka mid ah qorshaha CTE, Markaa qanjidhadan ayaa hubaal ah inay "dagaallamayaan" midba midka kale, waxaan isla markiiba isku dari karnaa.

Dhibaato la xiriirta calaamadCTE-yada waa la buul-buuli karaa

Waxaa jira kuwo buul leh oo aad u liidata, iyo xitaa kuwo isku magac ah. Tusaale ahaan, waxaad kartaa gudaha CTE A samee CTE X, iyo isla heerka gudaha CTE B mar kale samee CTE X:

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

Markaad is barbardhigto, waa inaad tan fahantaa. Fahamka tan "indhahaaga" - xitaa inaad aragto qorshaha, xitaa inaad aragto jidhka codsiga - aad bay u adag tahay. Haddii jiilkaaga CTE uu yahay mid adag, buul leh, oo ay jiraan codsiyo waaweyn, markaa gabi ahaanba waa miyir beelay.

UNION

Haddii aan ku hayno kelmad muhiim ah weydiinta UNION [ALL] (shaqaale ku biiraya laba muunado), ka dibna qorshaha waxa uu la mid yahay noodhka midkood Append, ama qaar Recursive Union.


Taas oo ah "korka" sare UNION - Tani waa farcankii ugu horreeyay ee noode, kaas oo "hoos" - labaad. Haddii loo maro UNION Waxaan leenahay dhowr baloog oo "ku dheggan" hal mar, ka dibna Append-waxaa weli jiri doona hal nood, laakiin ma yeelan doonto laba, laakiin carruur badan - siday u kala horreeyaan, siday u kala horreeyaan:

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

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

Dhibaato la xiriirta calaamadGudaha: jiilka muunada dib u curashada (WITH RECURSIVE) sidoo kale waxay noqon kartaa wax ka badan hal UNION. Laakiin kaliya block aadka u dambeeya ka dib kan ugu dambeeya had iyo jeer waa soo noqnoqda UNION. Wax kasta oo kor ku xusan waa mid, laakiin kala duwan UNION:

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

Waxaad sidoo kale u baahan tahay inaad awood u yeelatid inaad "ku dhejiso" tusaalooyinkaas. Tusaalahan waxaan ku aragnaa taas UNION-waxaa jiray 3 qaybood oo codsigayaga ah. Sidaas awgeed, mid UNION u dhiganta Append- node, iyo kan kale - Recursive Union.

Akhri-qor xogta

Wax walba waa la dhigay, hadda waxaan ognahay qaybta codsiga ee u dhiganta qaybta qorshaha. Qaybahan waxaan si fudud oo dabiici ah uga heli karnaa walxahaas "la akhriyi karo".

Marka laga eego dhinaca su'aasha, ma garanayno inuu miis yahay ama CTE, laakiin waxaa loo qoondeeyey isla nood. RangeVar. Iyo marka la eego "akhris", kani sidoo kale waa unugyo kooban oo xadidan:

• 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]

Waxaan ognahay qaabka qorshaha iyo weydiinta, waan ognahay waraaqaha waraaqaha, waxaan ognahay magacyada walxaha - waxaan samaynaa isbarbardhig hal-hal ah.


Mar labaad hawsha "oo leh calaamad". Codsiga waanu qaadanaa, fulinaa,ma lihin magacyo-waxa aanu laba jeer ka akhrinay isla CTE-da.


Waxaan eegnaa qorshaha - waa maxay dhibku? Maxaynu u yeelanay magac-yaqaan? Ma aanan dalban. Halkee ayuu ka helayaa "lambarka" noocaas ah?

PostgreSQL lafteeda ayaa ku dartay. Kaliya waxaad u baahan tahay inaad fahamto taas kaliya magac ahaan annaga, ujeeddooyinka isbarbardhigga qorshaha, wax macno ah ma samaynayso, si fudud ayaa halkan loogu daray. Yaynaan dheg jalaq u siin.

Midka labaad hawsha "oo leh calaamad": haddii aan wax ka akhrineyno miis qaybsan, markaa waxaan heli doonnaa noode Append ama Merge Append, kuwaas oo ka koobnaan doona tiro badan oo "carruur ah", oo mid kasta oo ka mid ah uu noqon doono si uun Scan'oo ka socda miiska-qaybta: Seq Scan, Bitmap Heap Scan ama Index Scan. Laakiin, si kastaba ha ahaatee, kuwan "carruurta" ma noqon doonaan su'aalo adag - tani waa sida qanjidhadan loo kala saari karaa Append at UNION.


Waxaan sidoo kale fahmeynaa guntinnada noocaas ah, ku soo ururi "hal tusin" oo waxaad dhahdaa: "wax kasta oo aad ka akhrido megatable waa halkan iyo hoos geedka".

Xogta "fudud" ee helaysa noodhka

Values Scan u dhiganta qorshaha VALUES codsiga.

Result waa codsi aan lahayn FROM nooc ka mid ah SELECT 1. Ama marka aad si ula kac ah u leedahay hadal been abuur ah WHERE-block (ka dibna sifada ayaa soo baxaysa 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 "maab" ku socda SRF-yada isla magaca ah.

Laakiin su'aalaha buul-ku-jirka ah wax walba waa ka sii dhib badan yihiin - nasiib darro, mar walba ma noqdaan InitPlan/SubPlan. Mararka qaarkood waxay isu beddelaan ... Join ama ... Anti Join, gaar ahaan marka aad wax u qorto sida WHERE NOT EXISTS .... Oo halkan mar walba suurtagal maaha in la isku daro - qoraalka qorshaha ma jiraan hawlwadeeno u dhigma qanjidhada qorshaha.

Mar labaad hawsha "oo leh calaamad": qaar VALUES codsiga. Xaaladdan oo kale iyo qorshaha waxaad heli doontaa dhowr nood Values Scan.


Erayada "lambareeyay" waxay kaa caawin doonaan in la kala saaro midba midka kale - waxaa lagu daraa sida saxda ah ee loo helo kuwa u dhigma. VALUES- xannibaadyada codsiga kor ilaa hoos.

Habaynta xogta

Waxay u muuqataa in wax kasta oo codsigeena ku jiray la xaliyay - waxa hadhay oo dhan waa Limit.


Laakiin halkan wax walba waa sahlan yihiin - noodhka sida Limit, Sort, Aggregate, WindowAgg, Unique "khariidad" mid-ka-mid ah hawlwadeenada u dhigma ee codsiga, haddii ay jiraan. Ma jiraan "xiddigo" ama dhibaatooyin halkan.

JOIN

Dhibaatooyinku waxay soo baxaan markaan rabno inaan isku darno JOIN dhexdooda. Tani mar walba suurtagal maaha, laakiin waa suurtagal.


Marka laga eego dhinaca falanqaynta weydiinta, waxaanu leenahay noode JoinExpr, oo leh laba carruur ah - bidix iyo midig. Tani, si waafaqsan, waa waxa "korka" ku biiristaada iyo waxa ku qoran "hoose" ee codsiga.

Marka laga eego dhinaca qorshahana, waa laba farac oo qaar ka mid ah * Loop/* Join-node. Nested Loop, Hash Anti Join,... - wax la mid ah.

Aynu isticmaalno macquul fudud: haddii aan haysano jaantusyada A iyo B ee "ku biira" midba midka kale ee qorshaha, markaa codsiga waxay ku jiri karaan midkood. A-JOIN-B, ama B-JOIN-A. Aynu isku dayno in aynu sidan isku darsano, aynu isku dayno in aynu dhanka kale isku darsano, iyo wixii la mid ah inta aynu ka dhammaanayno lammaanaha noocaas ah.

Aan soo qaadano geedkeena syntax, qaadano qorshahayaga, eegno iyaga... ma aha mid la mid ah!


Aynu dib u sawirno qaabka garaafyada - oh, waxay mar hore u egtahay wax!


Aynu ogaano inaynu leenahay nodes isku mar wada dhaleen carruur B iyo C - dan kama lihin siday u kala horreeyaan. Aynu isku darno oo aan ka rogno sawirka noodhka.


Aan mar kale eegno. Hadda waxaan haynaa noodo leh carruur A iyo lammaane (B + C) - iyagana ku habboon.


Wayn! Waxaa soo baxday inaan nahay labadan JOIN laga bilaabo codsiga leh noodhadhka qorshaha ayaa si guul leh la isugu daray.

Hoogay, dhibkan had iyo jeer lama xaliyo.


Tusaale ahaan, haddii codsi A JOIN B JOIN C, iyo qorshaha, ugu horreyntii, qanjidhada "outer" A iyo C ayaa ku xiran. Laakiin ma jiro hawlwadeen noocaas ah codsiga, ma hayno wax aan muujinno, ma jiraan wax aan ku lifaaqno tilmaame. Waxay la mid tahay "comma" markaad wax qorayso A, B.

Laakiin, inta badan kiisaska, ku dhawaad ​​dhammaan qanjidhada waa la "furan karaa" oo waxaad heli kartaa noocaan oo kale ah dhinaca bidix waqtiga - macno ahaan, sida Google Chrome marka aad falanqeyso code JavaScript. Waxaad arki kartaa inta xariiq kasta iyo bayaan kastaa ay qaateen in la fuliyo.


Oo si ay kuugu fududaato inaad waxaas oo dhan isticmaasho, waxaanu samaynay kaydinta archive, halkaas oo aad ku kaydsan karto oo hadhow aad ka heli karto qorshayaashaada oo ay la socdaan codsiyada la xidhiidha ama qof la wadaagto xidhiidhka.

Haddii aad u baahan tahay oo kaliya inaad keento weydiimo aan la akhriyi karin foom kugu filan, isticmaal noo "caadiye".

Source: www.habr.com