🥇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. А в плане «читается» — это тоже достаточно ограниченный набор узлов:

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]

Структуру плана и запроса мы знаем, соответствие блоков знаем, имена объектов знаем — делаем однозначное сопоставление.

Опять-таки задача «со звездочкой». Берем запрос, выполняем, у нас никаких алиасов нет — мы просто два раза из одной CTE почитали.

Смотрим в план — что за беда? Почему у нас алиас вылез? Мы его не заказывали. Откуда он такой «номерной»?

PostgreSQL сам его добавляет. Надо просто понимать, что именно такой алиас для нас для целей сопоставления с планом никакого смысла не несет, он просто здесь добавлен. Не будем на него обращать внимания.

I dyti задача «со звездочкой»: если у нас идет чтение из секционированной таблицы, то мы получим узел Shto или Bashkosh Bashkëngjit, который будет состоять из большого количества «детей», и каждый из которых будет каким-то Scan‘ом из таблицы-секции: Skene Sekuenciale, Skanim i Shkëmbit Bitmap или Index Scan. Но, в любом случае, эти «дети» будут не сложными запросами — так эти узлы и можно отличать от Shto при UNION.

Такие узлы мы тоже понимаем, собираем «в одну кучку» и говорим: "все, что ты читал из megatable — это вот тут и вниз по дереву".

«Простые» узлы получения данных

Skemë Vlerash в плане соответствует VALUES в запросе.

Rezultati — это запрос без FROM вроде SELECT 1. Или когда у вас заведомо ложное выражение в KU-блоке (тогда возникает атрибут 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

Skandimi i Funksionit «мапятся» на одноименные SRF.

А вот с вложенными запросами все сложнее — к сожалению, они не всегда превращаются в PlaniFill/SubPlan. Иногда они превращаются в ... Join или ... Anti Join, особенно когда вы пишете что-то вроде WHERE NOT EXISTS .... И вот там совмещать не всегда получается — в тексте плана соответствующих узлам плана операторов нет.

Опять-таки задача «со звездочкой»: несколько VALUES в запросе. В этом случае и в плане вы получите несколько узлов Skemë Vlerash.

Отличить их один от другого помогут «номерные» суффиксы — он добавляется именно в порядке нахождения соответствующих VALUES-блоков по ходу запроса сверху вниз.

Обработка данных

Вроде все в нашем запросе разобрали — остался только Limit.

Но тут все просто — такие узлы как Limit, Rregullo, Shkallëzimi, WindowAgg, Unike «мапятся» один-в-один на соответствующие операторы в запросе, если они там есть. Тут никаких «звездочек» и сложностей нет.

JOIN

Сложности возникают, когда мы хотим совместить JOIN между собой. Это сделать не всегда, но можно.

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ë.