Yawancin waɗanda ke amfani da su - Sabis na gani na shirinmu na PostgreSQL maiyuwa ba zai san ɗayan manyan ƙarfinsa ba - yana juya yanki mai wuyar karantawa na log ɗin sabar...
... a cikin kyakkyawan tsari da aka ƙera tare da alamu na mahallin mahallin madaidaicin kudurorin shirin:
A cikin wannan rubutu na kashi na biyu na sa Zan gaya muku yadda muka yi nasarar yin wannan.
Ana iya samun kwafin ɓangaren farko, wanda aka keɓe ga matsalolin aikin tambaya na yau da kullun da mafitarsu, a cikin labarin .
Da farko, bari mu fara canza launi - kuma ba za mu ƙara canza launin shirin ba, mun riga mun canza shi, mun riga mun sami shi mai kyau da fahimta, amma buƙatun.
Ya zama kamar a gare mu cewa tare da irin wannan "zanen takarda" wanda ba a tsara shi ba buƙatar da aka jawo daga log ɗin ya yi muni sosai kuma saboda haka bai dace ba.
Musamman lokacin da masu haɓakawa suka "manne" jikin buƙatun a cikin lambar (wannan shine, ba shakka, antipattern, amma yana faruwa) a cikin layi ɗaya. M!
Bari mu zana wannan ko ta yaya da kyau.
Kuma idan za mu iya zana wannan da kyau, wato, tarwatsa da kuma mayar da baya tare da jikin bukatar, sa'an nan za mu iya "haɗa" wani ambato ga kowane abu na wannan bukatar - abin da ya faru a daidai batu a cikin shirin.
Tambaya itace itace
Don yin wannan, buƙatun dole ne a fara warwarewa.
Domin muna da , to, mun yi masa module, za ku iya . A zahiri, waɗannan an tsawaita “dauri” zuwa cikin abubuwan da ke cikin PostgreSQL parser kanta. Wato, nahawu kawai an haɗa shi da binary kuma ana ɗaure shi daga NodeJS. Mun dauki tsarin wasu mutane a matsayin tushe - babu wani babban sirri a nan.
Muna ciyar da jikin buƙatun azaman shigarwa zuwa aikinmu - a wurin fitarwa muna samun bishiyar syntax da aka fake a cikin nau'in abun JSON.
Yanzu za mu iya gudu ta cikin wannan bishiyar ta wata hanya dabam kuma mu tara buƙatu tare da indents, canza launi, da tsara abin da muke so. A'a, wannan ba abu ne wanda za'a iya daidaita shi ba, amma yana kama da mu cewa wannan zai dace.
Taswirar taswira da nodes na shirin
Yanzu bari mu ga yadda za mu hada shirin da muka yi nazari a mataki na farko da tambayar da muka yi nazari a ta biyu.
Bari mu ɗauki misali mai sauƙi - muna da tambaya da ke haifar da CTE kuma ta karanta daga gare ta sau biyu. Yana haifar da irin wannan shirin.
CTE
Idan ka dube shi a hankali, har zuwa sigar 12 (ko farawa daga gare ta tare da maɓalli MATERIALIZED) samuwar .
Wannan yana nufin cewa idan muka ga wani ƙarni na CTE a wani wuri a cikin buƙatun da kumburi a wani wuri a cikin shirin CTE, to waɗannan nodes tabbas suna "yaki" da juna, za mu iya hada su nan da nan.
Matsala tare da alamar alama: Ana iya shigar da CTEs.
Akwai masu gida mara kyau, har ma da masu suna iri ɗaya. Misali, zaku iya ciki CTE A yi CTE X, kuma a daidai matakin ciki CTE B sake yi CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Lokacin kwatanta, dole ne ku fahimci wannan. Fahimtar wannan "da idanunku" - ko da ganin shirin, ko da ganin jikin buƙatar - yana da wuyar gaske. Idan ƙarni na CTE ɗin ku yana da rikitarwa, gida, kuma buƙatun suna da girma, to gaba ɗaya ba su san komai ba.
UNION
Idan muna da keyword a cikin tambaya UNION [ALL] (mai aiki na haɗuwa da samfurori guda biyu), sannan a cikin shirin ya dace da ko dai kumburi Append, ko wasu Recursive Union.
Abin da yake "sama" a sama UNION - wannan shine zuriyar farko na kumburinmu, wanda shine "a ƙasa" - na biyu. Idan ta hanyar UNION muna da tubalan da yawa “manne” lokaci guda, sannan Append- har yanzu za a sami kumburi ɗaya kawai, amma ba zai sami biyu ba, amma yara da yawa - a cikin tsari da suka bi, bi da bi:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Matsala tare da alamar alama: ciki recursive sampling tsara (WITH RECURSIVE) kuma yana iya zama fiye da ɗaya UNION. Amma kawai toshe na ƙarshe bayan na ƙarshe koyaushe yana maimaituwa UNION. Duk abin da ke sama ɗaya ne, amma daban UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Hakanan kuna buƙatar samun damar “fitar” irin waɗannan misalan. A cikin wannan misali mun ga haka UNION- akwai sassa 3 a cikin buƙatarmu. Saboda haka, daya UNION соответствует Append- node, da kuma sauran - Recursive Union.
Karanta-rubutun bayanai
An tsara komai, yanzu mun san wane yanki na buƙatar ya dace da wane yanki na shirin. Kuma a cikin waɗannan ɓangarorin za mu iya samun sauƙi da ta halitta waɗanda abubuwan da suke "ana iya karantawa".
Ta fuskar tambaya, ba mu san ko tebur ne ko CTE ba, amma an tsara su ta kulli ɗaya. RangeVar. Kuma dangane da “karantuwa”, wannan ma ƙayyadaddun ƙayyadaddun tsarin nodes ne:
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]
Mun san tsarin tsarin da tambaya, mun san daidaitattun tubalan, mun san sunayen abubuwan - muna yin kwatancen daya-da-daya.
Sake aiki "tare da alamar alama". Muna ɗaukar buƙatar, aiwatar da shi, ba mu da wasu laƙabi - muna karanta shi sau biyu daga CTE ɗaya.
Muna kallon shirin - menene matsalar? Me ya sa muke da laƙabi? Ba mu yi oda ba. A ina yake samun irin wannan "lambar"?
PostgreSQL yana ƙara da kanta. Kuna buƙatar fahimtar hakan kawai kawai irin wannan laƙabi a gare mu, don dalilai na kwatanta da shirin, ba shi da ma'ana, kawai an ƙara shi a nan. Kada mu kula shi.
Na biyu aiki "tare da alamar alama": idan muna karantawa daga teburin da aka raba, to za mu sami kumburi Append ko Merge Append, wanda zai ƙunshi babban adadin "yara", kuma kowannensu zai kasance ko ta yaya Scan' om daga sashin tebur: Seq Scan, Bitmap Heap Scan ko Index Scan. Amma, a kowane hali, waɗannan "yara" ba za su kasance masu rikitarwa ba - wannan shine yadda za'a iya bambanta wadannan nodes daga. Append a UNION.
Muna kuma fahimtar irin waɗannan kullin, tattara su "a cikin tudu ɗaya" kuma mu ce: "duk abin da kuka karanta daga megatable yana nan da ƙasa bishiyar".
"Sauƙaƙe" bayanan karɓar nodes
Values Scan yayi daidai a cikin shirin VALUES a cikin bukatar.
Result nema ne ba tare da FROM kamar SELECT 1. Ko kuma lokacin da kake da gangan maganganun ƙarya a ciki WHERE-block (sannan sifa ta bayyana 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 “taswira” zuwa SRFs na suna iri ɗaya.
Amma tare da nest queries duk abin da ya fi rikitarwa - da rashin alheri, ba ko da yaushe juya zuwa InitPlan/SubPlan. Wani lokaci sukan juya zuwa ... Join ko ... Anti Join, musamman idan ka rubuta wani abu kamar WHERE NOT EXISTS .... Kuma a nan ba koyaushe yana yiwuwa a haɗa su ba - a cikin rubutun shirin babu masu aiki da suka dace da nodes na shirin.
Sake aiki "tare da alamar alama": wasu VALUES a cikin bukatar. A wannan yanayin kuma a cikin shirin za ku sami nodes da yawa Values Scan.
Suffixes "Lambobi" za su taimaka wajen bambanta su da juna - an ƙara su daidai a cikin tsari wanda aka samo daidaitattun. VALUES- blocks tare da bukatar daga sama zuwa kasa.
Sarrafa bayanai
Da alama an daidaita duk abin da ke cikin buƙatarmu - abin da ya rage shi ne Limit.
Amma a nan duk abin da yake da sauki - irin nodes kamar Limit, Sort, Aggregate, WindowAgg, Unique “Taswira” daya-da-daya zuwa ga masu aiki masu dacewa a cikin buƙatun, idan suna nan. Babu “taurari” ko matsaloli a nan.
JIIN
Wahaloli suna tasowa lokacin da muke son haɗuwa JOIN tsakanin su. Wannan ba koyaushe yana yiwuwa ba, amma yana yiwuwa.
Daga mahangar mai binciken tambaya, muna da kumburi JoinExpr, wanda ke da yara biyu daidai - hagu da dama. Wannan, saboda haka, shine abin da ke "sama" JOIN ɗin ku da abin da aka rubuta "a ƙasa" a cikin buƙatar.
Kuma a mahangar shirin, wadannan zuriyar wasu ne guda biyu * Loop/* Join- kumburi. Nested Loop, Hash Anti Join,... - wani abu kamar haka.
Bari mu yi amfani da sauki dabaru: idan muna da tebur A da B cewa "haɗuwa" juna a cikin shirin, sa'an nan a cikin bukatar za a iya located ko dai. A-JOIN-B, ko B-JOIN-A. Mu yi kokari mu hada ta wannan hanya, mu yi kokarin hada wata hanya, da sauransu har sai mun kare daga irin wadannan nau’ukan.
Mu dauki bishiyar mu ta syntax, mu dauki shirinmu, mu dube su... ba kama ba!
Bari mu sake zana shi a cikin nau'i na jadawali - oh, ya riga ya yi kama da wani abu!
Mu lura cewa muna da nodes da suke da yara B da C lokaci guda - ba mu damu da wane tsari ba. Bari mu haɗa su kuma mu juya hoton kumburin.
Mu sake dubawa. Yanzu muna da nodes tare da yara A da nau'i-nau'i (B + C) - masu dacewa da su kuma.
Mai girma! Sai ya zama cewa mu ne wadannan biyu JOIN daga buƙatar tare da nodes ɗin shirin an yi nasarar haɗa su.
Kaico, wannan matsalar ba koyaushe ake warware ta ba.
Misali, idan a cikin bukata A JOIN B JOIN C, kuma a cikin shirin, da farko, an haɗa nodes "na waje" A da C. Amma babu irin wannan ma'aikacin a cikin buƙatun, ba mu da wani abu da za mu haskaka, babu abin da za a haɗa alamar. Haka yake da “comma” lokacin da kake rubutu A, B.
Amma, a mafi yawan lokuta, kusan duk nodes za a iya "kunce" kuma za ka iya samun irin wannan profiling a hannun hagu a lokaci - a zahiri, kamar a Google Chrome lokacin da ka bincika JavaScript code. Kuna iya ganin tsawon lokacin da kowane layi da kowane bayani suka ɗauki don " aiwatarwa."
Kuma don sanya shi mafi dacewa a gare ku don amfani da duk wannan, mun sanya ajiya , inda zaku iya ajiyewa kuma daga baya nemo tsare-tsaren ku tare da buƙatun da ke da alaƙa ko raba hanyar haɗin gwiwa tare da wani.
Idan kawai kuna buƙatar kawo tambayar da ba za a iya karantawa a cikin isasshen tsari ba, yi amfani da ita .
source: www.habr.com