Shumë prej atyre që tashmë po përdorin - Shërbimi ynë i vizualizimit të planit PostgreSQL mund të mos jetë i vetëdijshëm për një nga superfuqitë e tij - duke kthyer një pjesë të vështirë për t'u lexuar të regjistrit të serverit...
... në një pyetje të dizajnuar bukur me sugjerime kontekstuale për nyjet përkatëse të planit:
Në këtë transkript të pjesës së dytë të tij Unë do t'ju tregoj se si ia dolëm ta bënim këtë.
Transkripti i pjesës së parë, kushtuar problemeve tipike të performancës së pyetjeve dhe zgjidhjeve të tyre, mund të gjendet në artikull .
Së pari, le të fillojmë të ngjyrosim - dhe ne nuk do ta ngjyrosim më planin, e kemi ngjyrosur tashmë, e kemi tashmë të bukur dhe të kuptueshëm, por një kërkesë.
Na u duk se me një "fletë" kaq të paformatuar, kërkesa e nxjerrë nga regjistri duket shumë e shëmtuar dhe për këtë arsye e papërshtatshme.
Sidomos kur zhvilluesit "ngjisin" trupin e kërkesës në kod (ky është, natyrisht, një antimodel, por ndodh) në një rresht. E tmerrshme!
Le ta vizatojmë këtë disi më bukur.
Dhe nëse mund ta vizatojmë bukur këtë, domethënë të çmontojmë dhe bashkojmë përsëri trupin e kërkesës, atëherë mund t'i "bashkëngjisim" një aluzion secilit objekt të kësaj kërkese - çfarë ndodhi në pikën përkatëse në plan.
Pema e sintaksës së pyetjeve
Për ta bërë këtë, së pari duhet analizuar kërkesa.
Sepse ne kemi , atëherë ne bëmë një modul për të, ju mundeni . Në fakt, këto janë "lidhje" të zgjeruara me pjesët e brendshme të vetë analizuesit PostgreSQL. Kjo do të thotë, gramatika është thjesht e përpiluar binar dhe lidhen me të nga NodeJS. Ne morëm si bazë modulet e njerëzve të tjerë - nuk ka asnjë sekret të madh këtu.
Ne ushqejmë trupin e kërkesës si hyrje në funksionin tonë - në dalje marrim një pemë sintaksore të analizuar në formën e një objekti JSON.
Tani mund të kalojmë nëpër këtë pemë në drejtim të kundërt dhe të mbledhim një kërkesë me dhëmbëzimet, ngjyrosjen dhe formatimin që duam. Jo, kjo nuk është e personalizueshme, por na dukej se kjo do të ishte e përshtatshme.
Kërkesat e hartës dhe nyjet e planit
Tani le të shohim se si mund të kombinojmë planin që analizuam në hapin e parë dhe pyetjen që analizuam në të dytin.
Le të marrim një shembull të thjeshtë - ne kemi një pyetje që gjeneron një CTE dhe lexon prej tij dy herë. Ai krijon një plan të tillë.
CTE
Nëse e shikoni me kujdes, deri në versionin 12 (ose duke u nisur prej tij me fjalën kyçe MATERIALIZED) formimi .
Kjo do të thotë që nëse shohim një gjenerim CTE diku në kërkesë dhe një nyje diku në plan CTE, atëherë këto nyje patjetër "luftojnë" me njëra-tjetrën, ne mund t'i kombinojmë menjëherë.
Problem me një yll: CTE-të mund të vendosen me fole.
Ka fole shumë të dobëta, madje edhe me të njëjtin emër. Për shembull, mundeni brenda CTE A bëj CTE X, dhe në të njëjtin nivel brenda CTE B bëjë atë përsëri CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Kur krahasoni, duhet ta kuptoni këtë. Të kuptosh këtë "me sytë e tu" - edhe të shohësh planin, madje të shohësh trupin e kërkesës - është shumë e vështirë. Nëse gjenerata juaj CTE është komplekse, e ndërthurur dhe kërkesat janë të mëdha, atëherë është plotësisht i pavetëdijshëm.
BASHKIMI
Nëse kemi një fjalë kyçe në pyetje UNION [ALL] (operatori i bashkimit të dy mostrave), atëherë në plan korrespondon ose me një nyje Append, ose disa Recursive Union.
Ajo që është "sipër" më lart UNION - ky është pasardhësi i parë i nyjës sonë, i cili është "poshtë" - i dyti. Nëse përmes UNION ne kemi disa blloqe "të ngjitura" menjëherë, atëherë Append- do të ketë ende vetëm një nyje, por do të ketë jo dy, por shumë fëmijë - sipas radhës që shkojnë, përkatësisht:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Problem me një yll: brenda gjenerimit të mostrave rekursive (WITH RECURSIVE) gjithashtu mund të jetë më shumë se një UNION. Por vetëm blloku i fundit pas atij të fundit është gjithmonë rekurziv UNION. Gjithçka e mësipërme është një, por e ndryshme UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Ju gjithashtu duhet të jeni në gjendje të "shfaqni" shembuj të tillë. Në këtë shembull shohim se UNION-Ka pasur 3 segmente në kërkesën tonë. Prandaj, një UNION korrespondon me Append- nyja, dhe tek tjetra - Recursive Union.
Lexo-shkruaj të dhëna
Gjithçka është parashtruar, tani e dimë se cila pjesë e kërkesës korrespondon me cilën pjesë të planit. Dhe në këto pjesë ne mund të gjejmë lehtësisht dhe natyrshëm ato objekte që janë "të lexueshme".
Nga pikëpamja e pyetjes, ne nuk e dimë nëse është një tabelë apo një CTE, por ato përcaktohen nga e njëjta nyje RangeVar. Dhe për sa i përket "lexueshmërisë", ky është gjithashtu një grup mjaft i kufizuar nyjesh:
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]
Ne e dimë strukturën e planit dhe pyetjes, ne e dimë korrespondencën e blloqeve, ne dimë emrat e objekteve - bëjmë një krahasim një-për-një.
Përsëri detyra "me një yll". Ne e marrim kërkesën, e ekzekutojmë, nuk kemi pseudonim - thjesht e lexojmë dy herë nga e njëjta CTE.
Ne shikojmë planin - cili është problemi? Pse kishim pseudonim? Ne nuk e porositëm. Ku e merr ai një "numër" të tillë?
PostgreSQL e shton atë vetë. Ju vetëm duhet ta kuptoni këtë vetëm një pseudonim i tillë për ne, për qëllime krahasimi me planin, nuk ka kuptim, thjesht shtohet këtu. Le të mos i kushtojmë vëmendje atij.
Dytë detyra "me një yll": nëse lexojmë nga një tabelë e ndarë, atëherë do të marrim një nyje Append ose Merge Append, i cili do të përbëhet nga një numër i madh "fëmijësh", dhe secila prej të cilëve do të jetë disi ScanNga seksioni i tabelës: Seq Scan, Bitmap Heap Scan ose Index Scan. Por, në çdo rast, këta "fëmijë" nuk do të jenë pyetje komplekse - kështu mund të dallohen këto nyje nga Append при UNION.
Ne gjithashtu kuptojmë nyje të tilla, i mbledhim ato "në një grumbull" dhe themi: "gjithçka që lexoni nga megatable është këtu dhe poshtë pemës".
Nyje "të thjeshta" të marrjes së të dhënave
Values Scan korrespondon në plan VALUES në kërkesë.
Result është një kërkesë pa FROM si SELECT 1. Ose kur keni një shprehje qëllimisht të rreme WHERE-block (pastaj shfaqet atributi 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 "hartë" për SRF-të me të njëjtin emër.
Por me pyetjet e mbivendosura gjithçka është më e ndërlikuar - për fat të keq, ato jo gjithmonë shndërrohen në InitPlan/SubPlan. Ndonjëherë ato shndërrohen në ... Join ose ... Anti Join, sidomos kur shkruani diçka të tillë WHERE NOT EXISTS .... Dhe këtu nuk është gjithmonë e mundur t'i kombinoni ato - në tekstin e planit nuk ka operatorë që korrespondojnë me nyjet e planit.
Përsëri detyra "me një yll": disa VALUES në kërkesë. Në këtë rast dhe në plan do të merrni disa nyje Values Scan.
Prapashtesat "të numëruara" do të ndihmojnë për t'i dalluar ato nga njëra-tjetra - ato shtohen saktësisht në rendin në të cilin gjenden ato përkatëse. VALUES-bllokon përgjatë kërkesës nga lart poshtë.
Përpunimin e të dhënave
Duket sikur gjithçka në kërkesën tonë është rregulluar - gjithçka që ka mbetur është Limit.
Por këtu gjithçka është e thjeshtë - nyje të tilla si Limit, Sort, Aggregate, WindowAgg, Unique "hartë" një për një me operatorët përkatës në kërkesë, nëse ata janë atje. Këtu nuk ka "yje" apo vështirësi.
BASHKOHU
Vështirësitë lindin kur duam të kombinojmë JOIN mes tyre. Kjo nuk është gjithmonë e mundur, por është e mundur.
Nga këndvështrimi i analizuesit të pyetjeve, ne kemi një nyje JoinExpr, e cila ka saktësisht dy fëmijë - majtas dhe djathtas. Kjo, në përputhje me rrethanat, është ajo që është "sipër" JOIN tuaj dhe ajo që shkruhet "më poshtë" në kërkesë.
Dhe nga pikëpamja e planit, këta janë dy pasardhës të disave * Loop/* Join-nyje. Nested Loop, Hash Anti Join,... - diçka e tillë.
Le të përdorim logjikën e thjeshtë: nëse kemi tabela A dhe B që "bashkohen" me njëra-tjetrën në plan, atëherë në kërkesë ato mund të vendosen ose A-JOIN-BOse B-JOIN-A. Le të përpiqemi të kombinojmë në këtë mënyrë, le të përpiqemi të kombinojmë anasjelltas, e kështu me radhë derisa të na mbarojnë çifte të tilla.
Le të marrim pemën tonë sintaksore, të marrim planin tonë, t'i shikojmë ato ... jo të ngjashme!
Le ta rivizatojmë në formën e grafikëve - oh, tashmë duket si diçka!
Le të theksojmë se ne kemi nyje që njëkohësisht kanë fëmijë B dhe C - nuk na intereson në çfarë rendi. Le t'i kombinojmë ato dhe ta kthejmë figurën e nyjës.
Le të shohim përsëri. Tani kemi nyje me fëmijët A dhe çifte (B + C) - të pajtueshme edhe me ta.
E shkëlqyeshme! Rezulton se ne jemi këta të dy JOIN nga kërkesa me nyjet e planit u kombinuan me sukses.
Mjerisht, ky problem nuk zgjidhet gjithmonë.
Për shembull, nëse në një kërkesë A JOIN B JOIN C, dhe në plan, para së gjithash, u lidhën nyjet "e jashtme" A dhe C. Por nuk ka asnjë operator të tillë në kërkesë, nuk kemi asgjë për të theksuar, asgjë për t'i bashkangjitur një aluzion. Është e njëjta gjë me "presjen" kur shkruan A, B.
Por, në shumicën e rasteve, pothuajse të gjitha nyjet mund të "zgjidhen" dhe ju mund ta merrni këtë lloj profilizimi në të majtë me kohë - fjalë për fjalë, si në Google Chrome kur analizoni kodin JavaScript. Ju mund të shihni se sa kohë iu desh çdo rreshti dhe çdo deklarate për të "ekzekutuar".
Dhe për ta bërë më të përshtatshëm për ju përdorimin e gjithë kësaj, ne kemi bërë ruajtjen , ku mund të ruani dhe më vonë të gjeni planet tuaja së bashku me kërkesat përkatëse ose të ndani lidhjen me dikë.
Nëse thjesht duhet të sillni një pyetje të palexueshme në një formë adekuate, përdorni .
Burimi: www.habr.com