Abaningi asebevele besebenzisa - Isevisi yethu yokubuka uhlelo lwe-PostgreSQL ingase ingaqapheli enye yamandla ayo amakhulu - ukuguqula ucezu olufundeka kanzima lwelogi yeseva...
... embuzweni oklanywe kahle onamacebo komongo wamanodi epulani ahambisanayo:
Kulo mbhalo wengxenye yesibili yakhe Ngizokutshela ukuthi sikwazile kanjani ukwenza lokhu.
Okulotshiweyo kwengxenye yokuqala, okunikezelwe ezinkingeni zokusebenza kombuzo ojwayelekile nezisombululo zazo, kungatholakala esihlokweni. .
Okokuqala, ake siqale ukufaka umbala - futhi ngeke sisabala ipulani, sesivele siyifake umbala, sesivele sinayo enhle futhi eqondakalayo, kodwa isicelo.
Kwabonakala kithi ukuthi “ngeshidi” elingafomethiwe elinjalo isicelo esikhishwe kulogi sibukeka sisibi kakhulu futhi ngenxa yalokho singathandeki.
Ikakhulukazi lapho abathuthukisi "benamathisela" umzimba wesicelo kukhodi (lokhu, yiqiniso, i-antipattern, kodwa kwenzeka) emgqeni owodwa. Kubi!
Ake sikudwebe kahle lokhu ngandlela thize.
Futhi uma singakwazi ukudweba lokhu kahle, okungukuthi, ukuhlakaza futhi sibuyisele ndawonye umzimba wesicelo, khona-ke singase "sinamathisele" iseluleko entweni ngayinye yalesi sicelo - okwenzekile endaweni ehambisanayo ohlelweni.
Umbuzo we-syntax tree
Ukuze wenze lokhu, isicelo kufanele siqale sicutshungulwe.
Ngoba sinakho , bese sikwenzela imojuli, ungakwazi . Eqinisweni, lezi "zibophezelo" ezinwetshiwe kwabangaphakathi be-PostgreSQL yokuhlaziya ngokwayo. Okusho ukuthi, uhlelo lolimi luhlanganiswe nje kanambambili futhi izibopho zenziwe kulo kusuka ku-NodeJS. Sithathe amamojula abanye abantu njengesisekelo - akukho mfihlo enkulu lapha.
Sondla umzimba wesicelo njengokufakwayo kumsebenzi wethu - kokukhiphayo sithola isihlahla se-syntax esihlukanisiwe esisesimweni sento ye-JSON.
Manje sesingagijima kulesi sihlahla siye kolunye uhlangothi futhi sihlanganise isicelo ngama-indent, umbala, nokufometha esikufunayo. Cha, lokhu akwenzeki ngokwezifiso, kodwa kubonakala ngathi lokhu kuzoba lula.
Umbuzo wokwenza imephu namanodi wokuhlela
Manje ake sibone ukuthi singaluhlanganisa kanjani uhlelo esiluhlaziyile esinyathelweni sokuqala kanye nombuzo esiwuhlaziye kwesesibili.
Ake sithathe isibonelo esilula - sinombuzo okhiqiza i-CTE futhi sifunde kuwo kabili. Wenza icebo elinjalo.
CTE
Uma uyibheka ngokucophelela, kuze kufike kunguqulo 12 (noma ukuqala kuyo ngegama elingukhiye MATERIALIZED) ukwakheka .
Lokhu kusho ukuthi uma sibona isizukulwane se-CTE endaweni ethile esicelweni kanye ne-node endaweni ethile ohlelweni CTE, khona-ke lawa ma-node ngokuqinisekile "alwa" nomunye nomunye, singawahlanganisa ngokushesha.
Inkinga ngenkanyezi: Ama-CTE angafakwa esidlekeni.
Kunezidleke ezimbi kakhulu, ngisho namagama afanayo. Ngokwesibonelo, ungakwazi ngaphakathi CTE A yenza CTE X, futhi ezingeni elifanayo ngaphakathi CTE B kwenze futhi CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Uma uqhathanisa, kufanele ukuqonde lokhu. Ukuqonda lokhu "ngamehlo akho" - ngisho nokubona uhlelo, ngisho nokubona umzimba wesicelo - kunzima kakhulu. Uma isizukulwane sakho se-CTE siyinkimbinkimbi, sifakwe esidlekeni, futhi izicelo zinkulu, khona-ke asazi lutho.
INYUNYANA
Uma sinegama elingukhiye embuzweni UNION [ALL] (i-opharetha yokuhlanganisa amasampula amabili), bese kuhlelo ihambisana nanoma iyiphi i-node Append, noma okunye Recursive Union.
Lokho "ngaphezulu" ngenhla UNION - lena inzalo yokuqala ye-node yethu, "engezansi" - eyesibili. Uma ngokusebenzisa UNION sinamabhlogo amaningana "anamathiselwe" ngesikhathi esisodwa, ke Append-kusazoba ne-node eyodwa kuphela, kodwa ngeke ibe nambili, kodwa izingane eziningi - ngokulandelana kwazo, ngokulandelana:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Inkinga ngenkanyezi: ngaphakathi kwesizukulwane samasampula esiphindaphindayo (WITH RECURSIVE) futhi ingaba ngaphezu kokukodwa UNION. Kodwa ibhulokhi yokugcina kuphela ngemva kweyokugcina ehlala iphindaphinda UNION. Konke okungenhla kukodwa, kodwa kuhlukile UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Udinga futhi ukwazi "ukunamathela" izibonelo ezinjalo. Kulesi sibonelo siyakubona lokho UNION-bekukhona izingxenye ezi-3 esicelweni sethu. Ngokuvumelana nalokho, eyodwa UNION соответствует Append-node, futhi komunye - Recursive Union.
Funda-bhala idatha
Konke kubekiwe, manje sesiyazi ukuthi iyiphi ingxenye yesicelo ehambisana nesiqephu sohlelo. Futhi kulezi zingcezu singathola kalula futhi ngokwemvelo lezo zinto "ezifundekayo".
Ngokombono wombuzo, asazi noma itafula noma i-CTE, kodwa aqokwe ngenodi efanayo. RangeVar. Futhi mayelana "nokufundeka", lokhu futhi kuyisethi elinganiselwe yamanodi:
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]
Siyazi ukwakheka kohlelo kanye nombuzo, siyakwazi ukuxhumana kwamabhulokhi, sazi amagama ezinto - senza ukuqhathanisa okukodwa kuya kokukodwa.
Futhi umsebenzi "ngenkanyezi". Sithatha isicelo, sisenze, asinazo iziteketiso - sisanda kusifunda kabili ku-CTE efanayo.
Sibheka uhlelo - yini inkinga? Kungani saba nesibizo? Asiyi-odanga. Uyithathaphi “inombolo yenombolo”?
I-PostgreSQL iyengeza yona. Udinga nje ukukuqonda lokho isibizo esinjalo nje kithi, ngezinhloso zokuqhathanisa nohlelo, akwenzi mqondo, kumane kwengezwe lapha. Asimnake.
Owesibili umsebenzi "ngenkanyezi": uma sifunda etafuleni elihlukanisiwe, khona-ke sizothola i-node Append noma Merge Append, okuzoba nenani elikhulu "lezingane", futhi ngayinye yazo izoba ngandlela-thile Scan'om kusukela esigabeni-sethebula: Seq Scan, Bitmap Heap Scan noma Index Scan. Kodwa, kunoma yikuphi, lezi "zingane" ngeke zibe imibuzo eyinkimbinkimbi - yile ndlela la ma-node angahlukaniswa ngayo. Append at UNION.
Siyawaqonda futhi amafindo anjalo, siwaqoqe “ngenqwaba eyodwa” bese sithi: “yonke into oyifunda ku-megatable ilapha futhi phansi esihlahleni".
"Elula" amanodi okuthola idatha
Values Scan kuhambisana nohlelo VALUES esicelweni.
Result isicelo ngaphandle FROM njenge SELECT 1. Noma uma unenkulumo engamanga ngamabomu WHERE-block (bese kuvela imfanelo One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 "imephu" eya kuma-SRF egama elifanayo.
Kodwa ngemibuzo efakwe esidlekeni yonke into iyinkimbinkimbi kakhulu - ngeshwa, ayishintshi njalo InitPlan/SubPlan. Kwesinye isikhathi ziphenduka zibe ... Join noma ... Anti Join, ikakhulukazi uma ubhala into efana nale WHERE NOT EXISTS .... Futhi lapha akunakwenzeka ngaso sonke isikhathi ukuwahlanganisa - embhalweni wepulani awekho ama-opharetha ahambisana nama-node ohlelo.
Futhi umsebenzi "ngenkanyezi": abanye VALUES esicelweni. Kulokhu futhi ohlelweni uzothola ama-node amaningana Values Scan.
Izijobelelo "ezinezinombolo" zizosiza ukuhlukanisa komunye nomunye - zengezwe ngendlela efanayo ngendlela okutholakala ngayo okuhambisanayo. VALUES-blocks kanye nesicelo kusuka phezulu kuya phansi.
Ukucubungula idatha
Kubonakala sengathi yonke into esicelweni sethu ilungisiwe - osekusele nje Limit.
Kodwa lapha konke kulula - ama-node anjalo Limit, Sort, Aggregate, WindowAgg, Unique "imephu" eyodwa-to-one kuma-opharetha ahambisanayo esicelweni, uma bekhona. Azikho “izinkanyezi” noma ubunzima lapha.
JOIN
Kuvela ubunzima uma sifuna ukuhlanganisa JOIN phakathi kwabo. Lokhu akwenzeki ngaso sonke isikhathi, kodwa kungenzeka.
Ngokombono womhlahleli wombuzo, sinenodi JoinExpr, enezingane ezimbili ncamashi - kwesokunxele nakwesokudla. Lokhu, ngokufanelekile, yilokho “okungaphezu” kwakho UKUJOYINA kanye nalokho okubhalwe “ngezansi” kukho esicelweni.
Futhi ngokombono wohlelo, laba bayinzalo yabanye ababili * Loop/* Join-indawo. Nested Loop, Hash Anti Join,... - into enjalo.
Masisebenzise ingqondo elula: uma sinamathebula A no-B “ahlanganisa” elinye nelinye ohlelweni, esicelweni angatholakala noma A-JOIN-B, noma B-JOIN-A. Ake sizame ukuhlanganisa ngale ndlela, ake sizame ukuhlanganisa enye indlela, njalonjalo kuze kube yilapho siphelelwa ngamapheya anjalo.
Ake sithathe isihlahla sethu se-syntax, sithathe uhlelo lwethu, sibheke ... azifani!
Masiyidwebe kabusha ngendlela yamagrafu - oh, isivele ibukeka njengento ethile!
Masiqaphele ukuthi sinama-node ngesikhathi esisodwa anezingane B no-C - asinandaba nokuthi imuphi umyalelo. Masizihlanganise futhi siphendule isithombe se-node.
Ake sibheke futhi. Manje sesinamanodi anezingane A namapheya (B + C) - ahambisana nawo futhi.
Kuhle! Kuvele ukuthi yithi laba ababili JOIN esicelweni esinamanodi ohlelo ahlanganiswe ngempumelelo.
Maye, le nkinga ayixazululeki ngaso sonke isikhathi.
Isibonelo, uma kusicelo A JOIN B JOIN C, futhi ohlelweni, okokuqala, ama-node "angaphandle" A no-C axhunyiwe. Kodwa akukho opharetha onjalo esicelweni, akukho lutho esilugqamisayo, akukho lutho olunamathisela i-hint. Kuyafana nange "comma" uma ubhala A, B.
Kodwa, ezimweni eziningi, cishe wonke ama-node "angathululwa" futhi ungathola lolu hlobo lwephrofayela ngakwesokunxele ngesikhathi - ngokoqobo, njengaku-Google Chrome lapho uhlaziya ikhodi ye-JavaScript. Ungabona ukuthi umugqa ngamunye nesitatimende ngasinye kuthathe isikhathi eside kangakanani "ukusebenzisa."
Futhi ukwenza kube lula kuwe ukuthi usebenzise konke lokhu, senze isitoreji , lapho ungagcina khona futhi uthole kamuva izinhlelo zakho kanye nezicelo ezihlobene noma wabelane ngesixhumanisi nomunye umuntu.
Uma udinga nje ukuletha umbuzo ongafundeki efomini elanele, sebenzisa .
Source: www.habr.com