Wielu, którzy już korzystają — z naszej usługi wizualizacji planów PostgreSQL, prawdopodobnie nie zdaje sobie sprawy z jednej z jej supermocy — przekształcania trudno czytelnego fragmentu logu serwera…
… w pięknie sformatowane zapytanie z kontekstowymi podpowiedziami dla odpowiednich węzłów planu:
W tej dekodowanej drugiej części swojego opowiem, jak udało nam się to zrealizować.
Transkrypcję pierwszej części, poświęconej typowym problemom wydajności zapytań i ich rozwiązaniom, można znaleźć w artykule .
Zacznijmy od pokolorowania — i będziemy kolorować już nie plan, bo go już odmalowaliśmy, jest piękny i zrozumiały, a zapytanie.
Wydawało nam się, że taki nieformatowany „arkusz” wydobyty z logu wygląda bardzo nieestetycznie i dlatego — niewygodnie.
Szczególnie, gdy programiści w kodzie „kleją” ciało zapytania (to, oczywiście, antywzorzec, ale się zdarza) w jedną linię. Okropne!
Zróbmy to jakoś ładniej.
A jeśli będziemy mogli to narysować ładnie, to znaczy rozebrać i złożyć ciało zapytania, to potem będziemy mogli do każdego obiektu tego zapytania „przyczepić” podpowiedź — co się działo w odpowiednim punkcie planu.
Drzewo składniowe zapytania
Aby to zrobić, zapytanie najpierw należy rozebrać.
Ponieważ, mamy , stworzyliśmy do niego moduł, możecie . W rzeczywistości jest to rozszerzone „wiązania” do wnętrz parsowania samego PostgreSQL. To znaczy po prostu binarnie skompilowana gramatyka и do niej zrobiono wiązania z boku NodeJS. Wzięliśmy za podstawę cudze moduły — tutaj nie ma żadnej wielkiej tajemnicy.
Przekazujemy ciało zapytania do wejścia naszej funkcji — na wyjściu otrzymujemy rozebrane drzewo składniowe w postaci obiektu JSON.
Teraz po tym drzewie możemy przebiec się wstecz i złożyć zapytanie z tymi wcięciami, kolorowaniem, formatowaniem, które chcemy. Nie, to nie jest konfigurowalne, ale wydawało nam się, że tak będzie wygodnie.
Mapowanie węzłów zapytania i planu
Teraz zobaczmy, jak można połączyć plan, który rozebraliśmy w pierwszym kroku, i zapytanie, które rozebraliśmy w drugim.
We’ll take a simple example — we have a query that forms a CTE and reads from it twice. It generates such a plan.
CTE
If we take a closer look at it, up to version 12 (or starting from it with the keyword MATERIALIZED) creation .
So, if we see somewhere in the query a CTE generation and somewhere in the plan a node CTE, these nodes are clearly 'matched', we can combine them right away.
A 'starred' task: CTEs can be nested.
There can be very poorly nested, and even identically named. For example, inside CTE A you can create CTE X, and at the same level inside CTE B you can again create CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...When matching, you need to understand this. Understanding it 'visually' — even seeing the plan, even seeing the body of the query — is very hard. If your CTE generation is complex, nested, and the queries are large — then it's completely unconscious.
UNION
If our query has the keyword UNION [ALL] (the operator that combines two selections), then in the plan it corresponds to either a node Dodać, or some kind of Rekurencyjne Połączenie.
What is 'above' the UNION — is the first child of our node, and what is 'below' — is the second. If several blocks are 'glued' together, then UNION -the node will still only be one, but it will have many children — in the order they go, respectively: Dodać(...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3
Append
-> ... #1
-> ... #2
-> ... #3: inside the generation of a recursive selection (
A 'starred' task) there can also be more than oneWITH RECURSIVE. But always the last block after the last UNION. Everything above — is one, but another UNIONWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, here ends the generation of the starting state of recursion UNION ALL (...) -- #3, only this block is recursive and can access T ) ... UNION:
Such examples also need to be 'unfolded'. In this example, we see that -segments in our query were 3 pieces. Accordingly, one UNIONcorresponds to UNION -the node, and the other — DodaćRead-write data Rekurencyjne Połączenie.
Now that we've laid it all out, we know which piece of the query corresponds to which piece of the plan. And in these pieces, we can easily find the objects that are 'read'.
From the query's perspective, we don’t know — whether it's a table or a CTE, but they are both represented by the same node.
Nie wiemy z punktu widzenia zapytania, czy to jest tabela, czy CTE, ale oznaczają się tym samym węzłem. RangeVar. W planie „czytanym” to również dość ograniczony zbiór węzłów:
Skanowanie sekwencyjne na [tbl]Skanowanie przez Bitmap Heap na [tbl]Indeks [Tylko] Skan [Wstecz] korzystając z [idx] na [tbl]Skan CTE na [cte]Wstaw/Update/Usuń na [tbl]
Znamy strukturę planu i zapytania, znamy zgodność bloków, znamy nazwy obiektów — robimy jednoznaczne dopasowanie.
Znów zadanie ‚ze znakiem jakości’. Bierzemy zapytanie, wykonujemy, nie mamy żadnych aliasów — po prostu przeczytaliśmy dwa razy z tej samej CTE.
Patrzymy w plan — co się stało? Dlaczego pojawił się alias? Nie zamawialiśmy go. Skąd wziął się taki ‚numerowy’?
PostgreSQL sam go dodaje. Trzeba po prostu zrozumieć, że ten właśnie alias nie ma dla nas żadnego sensu w kontekście dopasowania z planem, został po prostu dodany. Nie będziemy na niego zwracać uwagi.
Drugi zadanie ‚ze znakiem jakości’: jeśli mamy odczyt z tabeli partycjonowanej, otrzymamy węzeł Dodać lub Scalaj Dodaj, który będzie składał się z dużej liczby ‚dzieci’, z których każde będzie jakimś Skaniemz sekcji tabeli: Skanowanie sekwencyjne, Skanowanie przez Bitmap Heap lub Indeksowe skanowanie. Jednak w każdym przypadku te ‚dzieci’ nie będą złożonymi zapytaniami — w ten sposób te węzły można odróżnić od Dodać w przypadku UNION.
Te węzły również rozumiemy, zbieramy „w jedną całość” i mówimy: "wszystko, co czytałeś z megatable — to tutaj i w dół po drzewie".
„Proste” węzły uzyskiwania danych
Skanowanie wartości w planie odpowiada WARTOŚCI w zapytaniu.
Wynik — to zapytanie bez Z FROM w stylu WYBIERZ 1. Lub gdy masz oczywiście fałszywe wyrażenie w WHERE-blokada (wtedy pojawia się atrybut Filtr jednorazowy):
EXPLAIN ANALYZE
WYBIERZ * Z pg_class GDZIE FAŁSZ; -- lub 0 = 1Wynik (koszt=0.00..0.00 wierszy=0 szerokość=230) (rzeczywisty czas=0.000..0.000 wierszy=0 pętle=1)
Filtr jednorazowy: fałsz
Skan funkcji ‚mapują się’ na równoważne SRF.
A tu zagnieżdżone zapytania są bardziej skomplikowane — niestety, nie zawsze zamieniają się w InitPlan/Plan Sub. Czasami zamieniają się w ... Join lub ... Anty Join, szczególnie gdy piszesz coś w stylu GDZIE NIE ISTNIEJE .... I tam nie zawsze udaje się połączyć — w tekście planu nie ma odpowiednich węzłów operatorów.
Znów zadanie ‚ze znakiem jakości’: kilka WARTOŚCI w zapytaniu. W takim przypadku i w planie otrzymasz kilka węzłów Skanowanie wartości.
Rozróżnienie ich jednego od drugiego pomoże „numerowy” sufiks — dodaje się go dokładnie w kolejności znajdowania odpowiadających WARTOŚCI-bloków w miarę postępu zapytania z góry na dół.
Obróbka danych
Jak na razie wszystko w naszym zapytaniu zostało omówione — został tylko Limit.
Ale tu wszystko jest proste — takie węzły jak Limit, Sortuj, Agregat, WindowAgg, Unikalny ‚mapują się’ jeden-do-jednego na odpowiadające operatory w zapytaniu, jeśli tam są. Nie ma tu żadnych „gwiazdek” ani trudności.
JOIN
Trudności pojawiają się, gdy próbujemy połączyć JOIN ze sobą. Nie zawsze jest to możliwe, ale można.
Z perspektywy parsera zapytania mamy węzeł DołączExpr, który ma dokładnie dwóch potomków - lewego i prawego. To, odpowiednio, to, co jest "nad" twoim JOIN i to, co jest "pod" nim w zapytaniu.
A z perspektywy planu to dwóch potomków jakiegoś * Pętla/* Dołącz-węzła. Nested Loop, Hash Anti Join,… — coś takiego.
Skorzystamy z prostej logiki: jeśli mamy tabelki A i B, które "łączą się" ze sobą w planie, to w zapytaniu mogły być umieszczone albo A-JOIN-B, albo B-JOIN-A. Spróbujemy je połączyć tak, spróbujemy połączyć odwrotnie, i tak aż takie pary się skończą.
Weźmy nasze drzewo składniowe, weźmy nasz plan, popatrzmy na nie… nie wygląda to!
Przerysujemy w postaci grafów — o, już coś zaczyna przypominać coś!
Zwróćmy uwagę, że mamy węzły, które mają jednocześnie dzieci B i C — nie obchodzi nas w jakiej kolejności. Połączymy je i odwrócimy obrazek węzła.
Popatrzmy jeszcze raz. Teraz mamy węzły z dziećmi A i parą (B + C) — połączmy je.
Świetnie! Okazuje się, że te dwa JOIN z zapytania z węzłami planu skutecznie połączyliśmy.
Niestety, ta zadanie nie zawsze jest rozwiązywane.
Na przykład, jeśli w zapytaniu A JOIN B JOIN C, a w planie najpierw połączyły się "skrajne" węzły A i C. A w zapytaniu nie ma takiego operatora, nie mamy nic do podświetlenia, nie ma do czego przypiąć podpowiedzi. To samo z "przecinkiem", kiedy piszesz A, B.
Ale w większości przypadków, prawie wszystkie węzły da się "rozwiązać" i uzyskać taki profil czasowy z lewej — dosłownie, jak w Google Chrome, kiedy analizujesz kod na JavaScript. Widzisz ile czasu zajmuje każda linia i każdy operator "wykonania".
Aby było ci wygodniej z tego korzystać, zrobiliśmy przechowywanie , w którym możesz zapisać i później znaleźć swoje plany wraz z powiązanymi zapytaniami lub podzielić się z kimś linkiem.
Jeśli jednak musisz po prostu przekształcić nieczytelne zapytanie w odpowiednią formę, skorzystaj z .
Źródło: habr.com
