🥇PostgreSQL Query Profiler: si të përputhni planin dhe pyetjen

Shumë prej atyre që tashmë e përdorin explain.tensor.ru — shërbimin tonë të vizualizimit të planeve PostgreSQL, ndoshta nuk e dinë një nga superfuqitë e tij — të kthente një copë logu serveri me lexueshmëri të ulët…

… në një kërkesë të bukur të strukturuar me sugjerime kontekstuale për nyjet përkatëse të planit:

Në këtë shpërthim të pjesës së dytë të raportit tim në PGConf.Russia 2020 do të tregoj se si arritëm ta bëjmë këtë.

Me transkriptin e pjesës së parë, e cila merret me problemet e zakonshme të performancës së kërkesave dhe zgjidhjet e tyre, mund të njiheni në artikullin «Recetat për kërkesat SQL që sëmuren».

Së pari, do të merremi me ngjyrimin — dhe do ta ngjyrosim tani jo planin, sepse atë e kemi bërë tashmë të bukur dhe kuptimplotë, por kërkesën.

Na dukej që një «pllakë» e paformatizuar e nxjerrë nga logu e kërkesës dukej shumë e shëmtuar dhe për këtë arsye — e pakëndshme.

Sidomos kur zhvilluesit në kod «ngjisin» trupin e kërkesës (sigurisht, kjo është një antipattern, por ndodh) në një rresht. Të tmerrshme!

Le të vizatojmë këtë në një mënyrë më të bukur.

Dhe nëse mund ta vizatojmë bukur, dmth. të analizojmë dhe të rindërtojmë trupin e kërkesës, atëherë më vonë mund t'i lidhim çdo objekti të kësaj kërkese me një sugjerim — çfarë ndodhi në pikën përkatëse të planit.

Pema sintaksore e kërkesës

Për ta bërë këtë, së pari duhet të analizohet kërkesa.

Duke pasur parasysh se në qendër të sistemit punon NodeJS, ne e bëmë një modulik për të, mund ta gjeni në GitHub. Në të vërtetë, kjo është një lidhje e zgjeruar me brendësitë e parserit të vet PostgreSQL. Pra, thjesht është një gramatikë e kompilarizuar në mënyrë binare dhe lidhje të bëra nga ana e NodeJS. Ne morëm si bazë module të huaja — këtu nuk ka asnjë sekret të madh.

I japim trupin e kërkesës si hyrje në funksionin tonë — dhe në dalje marrim pemën sintaksore të analizuar në formën e një objekti JSON.

Tani përmes kësaj peme mund të kalojmë në anën tjetër dhe të rindërtojmë kërkesën me ato tërheqje, ngjyrosje dhe formatizim që duam. Jo, kjo nuk është e konfiguruar, por na duket që pikërisht kështu do të ishte e përshtatshme.

Përputhja e nyjeve të kërkesës dhe planit

Tani do të shohim se si mund të kombinojmë planin, të cilin e analizuam në hapin e parë, dhe kërkesën, të cilën e analizuam në të dytin.

Le të marrim një shembull të thjeshtë - kemi një kërkesë që formon CTE dhe e lexon dy herë nga ajo. Ajo gjeneron një plan të tillë.

CTE

Nëse e shikojmë me kujdes, deri në versionin 12 (ose duke filluar nga ajo me fjalën kyçe MATERIALIZED) formimi CTE është një barrierë e patjetërsueshme për planifikuesin.

Pra, nëse shohim diku në kërkesë gjenerimin e CTE dhe diku në plan një nyje CTE, atëherë këto nyje pa dyshim janë "të lidhura", mund t'i bashkojmë menjëherë.

Detyra "me yll": CTE mund të jenë të ndërlikuara.

Mund të jenë shumë keq të ndërlikuara, dhe madje me emra të njëjtë. Për shembull, ju mund të keni brenda CTE A të bëni CTE X, dhe në të njëjtin nivel brenda CTE B të bëni përsëri CTE X:

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

Kur përputheni, duhet ta kuptoni këtë. Të kuptoni këtë "me sy" - madje duke parë planin, madje duke parë trupin e kërkesës - është shumë e vështirë. Nëse keni një gjenerim të CTE të ndërlikuar, të ndërlikuar, kërkesat janë të mëdha - atëherë nuk e kuptoni fare.

UNION

Nëse në kërkesën tonë ka fjalën kyçe UNION [ALL] (operatori i bashkimit të dy përzgjedhjeve), atëherë në plan i korrespondon ose një nyje Shto, ose ndonjë Bashkimi Rekursiv.

Ajo që "lartë" mbi UNION -është pasardhësi i parë i nyjës tonë, ajo që "poshtë" - e dyta. Nëse përmes UNION kemi "ngjitur" disa blloqe menjëherë, atëherë Shto-nyja do të jetë vetëm një, por fëmijët e saj do të jenë shumë - në përkatësi si shkojnë, përkatësisht:

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

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

Detyra "me yll": brenda gjenerimit të zgjedhjes rekurzive (WITH RECURSIVE) gjithashtu mund të ketë më shumë se një UNION. Por gjithmonë rekurziv është vetëm blloku më i fundit pas UNION. Gjithçka që është lart - është një, por tjetër UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, këtu përfundon gjenerimi i gjendjes fillestare të rekurzionit
UNION ALL
  (...) -- #3, vetëm ky bllok është rekurziv dhe mund të përmbajë një referencë ndaj T
)
...

Shembuj të tillë gjithashtu duhet të dini si "të ndahen". Këtu, në këtë shembull, ne e shohim se UNION-se segmenteve në kërkesën tonë ishin 3. Pra, një UNION përputhet me Shto-nyjë, dhe një tjetër - Bashkimi Rekursiv.

Leximi-shkrimi i të dhënave

Tani, ne e kemi ndarë, tani e dimë se cila pjesë e kërkesës i përket cilës pjesë të planit. Dhe në këto pjesë ne mund të gjejmë lehtësisht dhe pa ndonjë vështirësi ato objekte që "lexohen".

Nga pikëpamja e kërkesës ne nuk e dimë - nëse është një tabelë apo CTE, por ato shënohen me të njëjtën nyje Shtrirja e Var. Ndërsa në planin "lexohet" - është gjithashtu një grup mjaft i kufizuar nyjesh:

Skene Sekuenciale në [tbl]
Skanim i Shkëmbit Bitmap në [tbl]
Indeksi [Vetëm] Skano [Mbrapsht] duke përdorur [idx] mbi [tbl]
Skano CTE në [cte]
Shtoni/Përditëso/Delete në [tbl]

Struktura e planit dhe e kërkesës e dimë, përputhjen e blloqeve e dimë, emrat e objekteve i dimë - bëjmë një përputhje të qartë.

Sërish, detyra "me yll". Marrim kërkesën, e realizojmë, nuk kemi asnjë alias - thjesht të dyja herët kemi lexuar nga një CTE.

Shikojmë në plan - çfarë ndodhi? Pse na doli një alias? Nuk e kishim kërkuar. Si doli ai "me numër"?

PostgreSQL e shton vetë atë. Duhet thjesht të kuptojmë se pikërisht ky alias nuk ka asnjë kuptim për ne për qëllime përputhjeje me planin, ai është thjesht këtu për t'u shtuar. Të mos i kushtojmë vëmendje.

I dyti detyra "me yll": nëse kemi lexim nga një tabelë të ndarë, atëherë do të kemi një nyje Shto ose Bashkosh Bashkëngjit, e cila do të përbëhet nga një numër i madh "fëmijësh" dhe secili prej tyre do të jetë një lloj Scan'i tabelës-seksion: Skene Sekuenciale, Skanim i Shkëmbit Bitmap ose Index Scan. Por, në çdo rast, këta "fëmijë" do të jenë kërkesa jo të komplikuara - kështu që këto nyje mund të dallohen nga Shto në UNION.

Këto nyje gjithashtu i kuptojmë, i grumbullojmë "në një grup" dhe themi: "çdo gjë që ke lexuar nga megatable - është këtu dhe poshtë në pemë.".

"Nyjet "e thjeshta" të marrjes së të dhënave

Skemë Vlerash në plan përputhen VLERAT në kërkesë.

Rezultati - kjo është një kërkesë pa FROM siç duket SELECT 1. Ose kur keni një shprehje të gabuar në KU-bllok (atëherë lind atributi One-Time Filter):

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

Rezultati (kostot=0.00..0.00 rreshta=0 gjerësi=230) (koha aktuale=0.000..0.000 rreshta=0 loops=1)
  One-Time Filter: false

Skandimi i Funksionit "mapohen" në SRF të njëjtë.

Por me kërkesat e thelluara është më e komplikuar - fatkeqësisht, ato nuk transformohen gjithmonë në PlaniFill/SubPlan. Ndonjëherë ata transformohen në ... Join ose ... Anti Join, veçanërisht kur shkruani diçka si WHERE NOT EXISTS .... Dhe aty kombinimi nuk është gjithmonë i mundur - në tekstin e planit operatoret përkatës nuk ekzistojnë.

Sërish, detyra "me yll": disa VLERAT në kërkesë. Në këtë rast dhe në plan do të merrni disa nyje Skemë Vlerash.

Të dallohet njëri nga tjetri do të ndihmojnë sufikset "numerik" - ato shtohen saktësisht në rendin e gjetjes së përputhjeve VLERAT-blloqeve përgjatë kërkesës nga lartë poshtë.

Përpunimi i të dhënave

Duket se kemi shkruar gjithçka në kërkesën tonë - mbeti vetëm Limit.

Por këtu është thjesht - këto nyje si Limit, Rregullo, Shkallëzimi, WindowAgg, Unike "mapohen" një-në-një me operatoret përkatës në kërkesë, nëse ato janë atje. Nuk ka "ylli" dhe vështirësi këtu.

JOIN

Vështirësitë lindin, kur duam të përshtatim JOIN ndërmjet tyre. Kjo nuk është gjithmonë e mundur, por është e mundur.

Nga pikëpamja e parser-it të pyetjeve, kemi një nyje JoinExpr, e cila ka pikërisht dy pasardhës — të majtë dhe të djathtë. Kjo, përkatësisht, është ajo që është "në" JOIN tuaj dhe ajo që është "nën" të në pyetje.

Dhe nga pikëpamja e planit, janë dy pasardhës të ndonjë * Rreth/* Bashkohu-nyje. Nested Loop, Hash Anti Join,… — diçka e tillë.

Le të përfitojmë nga logjika e thjeshtë: nëse kemi tabela A dhe B, të cilat "bashkohen" mes tyre në plan, atëherë në pyetje ato mund të jenë vendosur ose A-JOIN-B, ose B-JOIN-A. Le të provojmë t'i kombinojmë kështu, le të provojmë t'i kombinojmë atëherë, dhe kështu deri sa nuk mbarojnë këto çifte.

Le të marrim pemën tonë sintaktike, le të marrim planin tonë, le të shohim mbi ta… nuk duket!

Të rifreskojmë si grafë — o, tani tashmë po duket si diçka!

Le të vëmë re se kemi nyje, të cilat njëkohësisht kanë fëmijë B dhe C — nuk ka rëndësi në cilin rend. Le të bashkojmë dhe të kthejmë imazhin e nyjës.

Le të shohim përsëri. Tani kemi nyje me fëmijë A dhe çiftin (B + C) — le të bashkojmë edhe ato.

Përkryer! Kështu del se këto dy JOIN nga pyetja me nyjet e planit i kemi kombinuar me sukses.

Fatkeqësisht, kjo detyrë nuk zgjidhet gjithmonë.

Për shembull, nëse në pyetje A JOIN B JOIN C, dhe në plan në radhë të parë janë bashkuar nyjet "ekstreme" A dhe C. Dhe në pyetje s'ka një operator të tillë, nuk kemi asgjë për të nxjerrë në pah, asgjë për të lidhur me sugjerimin. E njëjta gjë ndodh me "virgjëreshën", kur shkruani A, B.

Por, në shumicën e rasteve, gati të gjitha nyjet mund të "zhbllokohen" dhe të marrim një profilizim të tillë majtas sipas kohës — pikërisht si në Google Chrome, kur analizoni kodin në JavaScript. Shihni sa kohë ka kaluar çdo rresht dhe çdo operator "u ekzekutua".

Dhe për ta bërë më të lehtë për ju të përdorni të gjitha këto, ne kemi bërë ruajtjen arkiv, ku mund të ruani dhe më pas të gjeni planet tuaja së bashku me pyetjet e asociuara ose të ndani një lidhje me dikë.