🥇PostgreSQL Query Profiler: kako uskladiti plan i poizvedbu

Mnogi, ki že uporabljajo explain.tensor.ru — našo storitev vizualizacije načrtov PostgreSQL, morda ne vedo za eno od njenih super sposobnosti — pretvoriti težko berljiv del strežniškega dnevnika…

… v lepo oblikovan poizvedbo s kontekstualnimi namigi o ustreznih vozliščih načrta:

V tem razčlemberu drugega dela svojega poročevalnega dogodka na PGConf.Russia 2020 bom opisal, kako nam je to uspelo.

S transkripcijo prvega dela, posvečenega običajnim težavam z zmogljivostjo poizvedb in njihovim rešenjem, se lahko seznanite v članku „Recepti za bolne SQL-poizvedbe“.

Najprej se bomo lotili barvanja — in barvali bomo že ne načrt, ker smo ga že pobarvali, je za nas že lep in razumljiv, temveč poizvedbo.

Zdi se nam, da tako neformatirani 'lista', izvlečen iz dnevnika, izgleda zelo grdo in zato — nepraktično.

Še posebej, ko razvijalci v kodi 'lepijo' telo poizvedbe (to je seveda antipattern, a se dogaja) v eno vrstico. Groza!

Poglejmo, kako to narisati bolj privlačno.

In če bomo to lahko narisali lepo, to pomeni, da bomo razčlenili in ponovno sestavili telo poizvedbe, potem bomo lahko 'pripeli' namige k vsakemu objektu te poizvedbe — kaj se je dogajalo v ustrezni točki načrta.

Sintaktično drevo poizvedbe

Da bi to dosegli, je treba poizvedbo najprej razdeliti.

Ker sistem jedro deluje na NodeJS, smo zanj naredili modul, ki ga lahko najdete na GitHubu. V resnici gre za podaljšane 'bindinge' na notranjosti samega parserja PostgreSQL. To je preprosto binarno kompilirana slovnica in k njej so narejeni bindingi s strani NodeJS. Temeljili smo na drugih modulih — tukaj ni večjih skrivnosti.

Nahranimo telo poizvedbe v našo funkcijo — na izhodu dobimo razčlenjeno sintaktično drevo v obliki JSON-objekta.

Zdaj lahko po tem drevesu tečemo v obratni smeri in sestavimo poizvedbo s tistimi zamiki, barvanjem in oblikovanjem, ki ga želimo. Ne, to ni nastavljivo, toda zdelo se nam je, da bo tako udobno.

Ujemanje vozlišč poizvedbe in načrta

Zdaj poglejmo, kako lahko združimo načrt, ki smo ga razčlenili v prvem koraku, in poizvedbo, ki smo jo razčlenili v drugem.

Vzemimo preprost primer — imamo poizvedbo, ki oblikuje CTE in z njega dvakrat bere. Ustvari takšen načrt.

CTE

Če se natančno ozrete nanj, da do 12. različice (ali od nje naprej s ključnimi besedami MATERIALIZED) oblikovanje CTE je neizpodbitna ovira za načrtovalnika..

Kar pomeni, da če nekje v poizvedbi vidimo generacijo CTE in nekje v načrtu vozlišče CTE, potem se ti vozliči nedvoumno 'združujejo', jih lahko takoj združimo.

Naloga 'z zvezdico': CTE so lahko odprti.

Nekateri so lahko zelo slabo odprti, celo z istim imenom. Na primer, lahko znotraj CTE A naredite CTE X, in na isti ravni znotraj CTE B naredite znova CTE X:

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

Pri primerjavi to morate razumeti. Razumevanje 's očmi' — celo ko vidite načrt, celo ko vidite telo poizvedbe — je zelo težko. Če je vaša generacija CTE zapletena, vgrajena, so poizvedbe velike — takrat je to še težje razumeti.

UNION

Če imamo v poizvedbi ključno besedo UNION [ALL] (operater za povezovanje dveh izbirk), ji v načrtu ustreza bodisi vozlišče Dodaj, bodisi kakšno Rekurzivna Unija.

To, kar je 'zgoraj' nad UNION — to je prvi potomček našega vozlišča, kar je 'spodaj' — drugi. Če imamo UNION več blokov 'lepilo' hkrati, potem Dodaj-vozlišče bo vseeno samo eno, otroci pa ne bosta le dva, temveč veliko — v vrstnem redu, kot sledijo:

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

Dodajanje
  -> ... #1
  -> ... #2
  -> ... #3

Naloga 'z zvezdico': znotraj generacije rekurzivne izbire (WITH RECURSIVE) je lahko tudi več kot ena UNION. A vedno je rekurzivni samo zadnji blok po zadnjem UNION. Kar je višje — to je eno, ampak drugo UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, tukaj se konča generacija začetnega stanja rekurzije
UNION ALL
  (...) -- #3, samo ta blok je rekurziven in lahko vsebuje sklic na T
)
...

Tudi takšne primere je treba znati 'razpustiti'. V tem primeru vidimo, da UNION-segmentov v naši poizvedbi je bilo 3. Zato je enemu UNION ustreza Dodaj-vozlišče, drugemu — Rekurzivna Unija.

Branje-pisanje podatkov.

Vse, razložili smo, zdaj vemo, kateri del poizvedbe ustreza kateremu delu načrta. In v teh delih lahko zlahka najdemo tiste objekte, ki so 'brani'.

Z vidika poizvedbe ne vemo — ali je to tabela ali CTE, vendar se označujejo z istim vozliščem. ObsegSpremenljivke. In terms of "readability" — this is also a rather limited set of nodes:

Sekvencijski sken na [tbl]
Bitmapski pregled skladišta na [tbl]
Indeks [Samo] Skeniranje [Nazad] uporaba [idx] na [tbl]
CTE Skeniranje na [cte]
Vstavi/Posodobi/Delete na [tbl]

We know the structure of the plan and the request, we know the correspondence of the blocks, we know the names of the objects — we make a clear mapping.

Again, the task "with a star". We take the request, execute it, there are no aliases — we simply read from the same CTE twice.

We look at the plan — what's the problem? Why did an alias appear? We didn't order it. Where did this "numbered" one come from?

PostgreSQL adds it itself. We just need to understand that such an alias doesn't make any sense for our mapping purposes related to the plan, it's just added here. Let's not pay attention to it.

Drugi the task "with a star": if we are reading from a partitioned table, we will get a node Dodaj ili Združitev Dodajanje, which will consist of a large number of "children", each of which will be some kind of Scan‘ from the section table: Sekvencijski sken, Bitmapski pregled skladišta ili Index Scan. But, in any case, these "children" will not be complex requests — this is how you can distinguish such nodes from Dodaj when UNION.

We also understand such nodes, collect them "into one pile" and say: "everything you read from megatable — this is here and down the tree".

"Simple" nodes for data retrieval

Pregled vrednosti in the plan correspond to VALUES in the request.

Rezultat — this is a request without FROM like SELECT 1. Or when you have a deliberately false expression in WHERE-block (then the attribute arises One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- or 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

Funkcija skeniranja "map" to identically named SRF.

However, with nested queries, it gets more complicated — unfortunately, they don't always turn into ZagonitiPlan/Področni načrt. Sometimes they turn into ... Join ili ... Anti Join, especially when you write something like WHERE NOT EXISTS .... And there mixing doesn't always work — the text of the plan doesn't have operators corresponding to the plan's nodes.

Again, the task "with a star": several VALUES in the request. In this case, in the plan, you will get several nodes Pregled vrednosti.

Distinguishing one from another will be helped by "numbered" suffixes — it's added precisely in the order of finding the corresponding VALUES-blocks along the query from top to bottom.

Obdelava podatkov

It seems we've figured everything in our request — only left Limit.

But here everything is simple — such nodes like Limit, Razvrsti, Strukturirati, WindowAgg, Unique "map" one-to-one to corresponding operators in the request if they are there. There are no "stars" or complexities here.

JOIN

Difficulties arise when we want to combine JOIN with each other. This is not always possible, but it can be done.

Z vidika parsera poizvedbi, imamo čvor JoinExpr, koji ima točno dva potomka - lijevi i desni. To je, redom, ono što je 'iznad' vašeg JOIN-a i ono što 'ispod' njega u poizvedbi piše.

A s vidika plana to su dva potomka nekog * Zanka/* Pridružite se-čvora. Ugniježdena petlja, Hash Anti Join,… — nešto slično tome.

Iskoristimo jednostavnu logiku: ako imamo tablice A i B koje se 'spajaju' međusobno u planu, onda su u poizvedbi mogle biti postavljene ili A-JOIN-B, ali B-JOIN-A. Pokušajmo ih tako spojiti, pokušajmo ih spojiti obrnuto, i tako dok takvi parovi ne ispare.

Uzmimo naše sintaktičko stablo, uzmimo naš plan, pogledajmo ih… ne izgleda!

Preoblikujmo u oblik grafova — oh, već nešto sliči nečemu!

Obratimo pažnju da imamo čvorove koji istovremeno imaju djecu B i C — nije važno u kojem redu. Spojit ćemo ih i preokrenuti sliku čvora.

Pogledajmo još jednom. Sada imamo čvorove s djecom A i parom (B + C) — spojimo i njih.

Odlično! Izgleda da smo ta dva JOIN iz poizvedbe s čvorovima plana uspješno spojili.

Nažalost, ovaj zadatak se ne može uvijek riješiti.

Na primjer, ako u poizvedbi A JOIN B JOIN C, a u planu su se prvi spojili 'ekstremni' čvorovi A i C. A u poizvedbi nema takvog operatora, nemamo ništa za istaknuti, nema na što povezati savjet. Ista stvar je s 'zarezom' kada pišete A, B.

Ali, u većini slučajeva, gotovo svi čvorovi mogu se 'razvezati' i dobiti ovakav profiliranje lijevo po vremenu — doslovno, kao u Google Chrome-u, kada analizirate JavaScript kod. Vidite koliko je vremena svaka linija i svaki operator 'izvršen'.

A da vam sve to bude lakše koristiti, napravili smo pohranu arhiva, gdje možete pohraniti i kasnije pronaći svoje planove zajedno s povezanim poizvedbama ili s nekim podijeliti vezu.