PostgreSQL Query Profiler: jak dopasować plan i zapytanie

Wielu, którzy już korzystają wyjaśnić.tensor.ru - nasza usługa wizualizacji planu PostgreSQL może nie być świadoma jednej ze swoich supermocy - przekształcenia trudnego do odczytania fragmentu dziennika serwera...

... w pięknie zaprojektowane zapytanie ze wskazówkami kontekstowymi dla odpowiednich węzłów planu:

W tym transkrypcie drugiej części jego relacja na PGConf.Russia 2020 Opowiem Ci jak nam się to udało.

Zapis pierwszej części, poświęconej typowym problemom wydajności zapytań i ich rozwiązaniom, znajduje się w artykule „Przepisy na wadliwe zapytania SQL”.

Najpierw zacznijmy kolorować - i nie będziemy już kolorować planu, już go pokolorowaliśmy, już mamy to piękne i zrozumiałe, ale prośba.

Wydawało nam się, że żądanie wyciągnięte z logu z niesformatowanym „arkuszem” wygląda bardzo brzydko i przez to niewygodnie.


Zwłaszcza gdy programiści „wklejają” treść żądania w kodzie (jest to oczywiście antywzorzec, ale się zdarza) w jednej linii. Straszny!

Narysujmy to jakoś piękniej.


A jeśli uda nam się to pięknie narysować, czyli rozebrać i złożyć z powrotem treść prośby, wówczas będziemy mogli „dołączyć” do każdego przedmiotu tej prośby wskazówkę – co wydarzyło się w odpowiednim punkcie planu.

Drzewo składni zapytań

Aby to zrobić, żądanie musi zostać najpierw przeanalizowane.


Ponieważ mamy rdzeń systemu działa na NodeJS, to zrobiliśmy do tego moduł, możesz znajdź go na GitHubie. W rzeczywistości są to rozszerzone „powiązania” z wewnętrznymi elementami samego parsera PostgreSQL. Oznacza to, że gramatyka jest po prostu kompilowana binarnie i tworzone są do niej powiązania z poziomu NodeJS. Jako podstawę wzięliśmy moduły innych osób - nie ma tu żadnej wielkiej tajemnicy.

Treść żądania podajemy jako dane wejściowe do naszej funkcji - na wyjściu otrzymujemy przetworzone drzewo składni w postaci obiektu JSON.


Teraz możemy przejść przez to drzewo w przeciwnym kierunku i złożyć żądanie z żądanymi wcięciami, kolorami i formatowaniem. Nie, nie można tego dostosować, ale wydawało nam się, że byłoby to wygodne.

Mapowanie węzłów zapytania i planu

Zobaczmy teraz, jak możemy połączyć plan, który analizowaliśmy w pierwszym kroku, i zapytanie, które analizowaliśmy w drugim.

Weźmy prosty przykład – mamy zapytanie, które generuje CTE i odczytuje z niego dwukrotnie. On generuje taki plan.

CTE

Jeśli przyjrzysz się temu uważnie, aż do wersji 12 (lub zaczynając od niej ze słowem kluczowym MATERIALIZED) formacja CTE stanowi absolutną barierę dla planisty.


Oznacza to, że jeśli gdzieś w żądaniu zobaczymy generację CTE i węzeł gdzieś w planie CTE, wtedy te węzły zdecydowanie „walczą” ze sobą, możemy je od razu połączyć.

Problem z gwiazdką: CTE można zagnieżdżać.

Są bardzo słabo zagnieżdżone, a nawet te o tej samej nazwie. Na przykład możesz wejść do środka CTE A zrobić CTE Xi na tym samym poziomie wewnątrz CTE B Zrób to jeszcze raz CTE X:

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

Porównując, musisz to zrozumieć. Zrozumienie tego „na własne oczy” – nawet zobaczenie planu, a nawet treści prośby – jest bardzo trudne. Jeśli generowanie CTE jest złożone, zagnieżdżone, a żądania są duże, to jest ono całkowicie nieświadome.

UNION

Jeśli w zapytaniu mamy słowo kluczowe UNION [ALL] (operator łączenia dwóch próbek), to w planie odpowiada albo węzłowi Append, albo kilka Recursive Union.


To, co jest „ponad” powyżej UNION - to pierwszy potomek naszego węzła, który jest „poniżej” - drugi. Jeśli przez UNION mamy więc kilka bloków „sklejonych” na raz Append- nadal będzie tylko jeden węzeł, ale będzie miał nie dwa, ale wiele dzieci - w kolejności, odpowiednio:

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

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

Problem z gwiazdką: wewnętrzne generowanie próbkowania rekurencyjnego (WITH RECURSIVE) może być także więcej niż jeden UNION. Ale tylko ostatni blok po ostatnim jest zawsze rekurencyjny UNION. Wszystko powyżej jest jednym, ale różnym UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Trzeba też umieć „wystawiać” takie przykłady. Na tym przykładzie to widzimy UNION-w naszej prośbie były 3 segmenty. Odpowiednio jeden UNION odpowiada Append-węzeł, a do drugiego - Recursive Union.

Dane do odczytu i zapisu

Wszystko jest już ustalone, teraz wiemy, która część żądania odpowiada której części planu. A w tych dziełach łatwo i naturalnie znajdziemy te obiekty, które są „czytelne”.

Z punktu widzenia zapytania nie wiemy, czy jest to tabela, czy CTE, ale są one wyznaczane przez ten sam węzeł RangeVar. Pod względem „czytelności” jest to również dość ograniczony zestaw węzłów:

• 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]

Znamy strukturę planu i zapytania, znamy zgodność bloków, znamy nazwy obiektów – dokonujemy porównania jeden do jednego.


Ponownie zadanie „z gwiazdką”. Przyjmujemy żądanie, wykonujemy je, nie mamy żadnych aliasów - po prostu czytamy je dwa razy z tego samego CTE.


Patrzymy na plan – w czym problem? Dlaczego mieliśmy pseudonim? Nie zamówiliśmy tego. Skąd on bierze taki „numer liczbowy”?

PostgreSQL dodaje to sam. Musisz to po prostu zrozumieć właśnie taki pseudonim dla nas, dla porównania z planem, nie ma to żadnego sensu, jest po prostu tutaj dodane. Nie zwracajmy na niego uwagi.

Drugi zadanie „z gwiazdką”: jeśli czytamy z podzielonej tabeli, otrzymamy węzeł Append lub Merge Append, który będzie się składał z dużej liczby „dzieci”, a każde z nich będzie w jakiś sposób Scan'om z sekcji tabeli: Seq Scan, Bitmap Heap Scan lub Index Scan. Ale w każdym razie te „dzieci” nie będą złożonymi zapytaniami - w ten sposób można odróżnić te węzły Append w UNION.


My też rozumiemy takie węzły, zbieramy je „w jeden stos” i mówimy: „wszystko, co czytasz z megatable, jest tutaj i na dole drzewa".

„Proste” węzły odbierające dane

Values Scan odpowiada planowi VALUES w żądaniu.

Result jest prośbą bez FROM tak jak SELECT 1. Lub gdy celowo użyjesz fałszywego wyrażenia WHERE-block (wtedy pojawia się atrybut 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

Function Scan „mapę” do SRF o tej samej nazwie.

Ale w przypadku zapytań zagnieżdżonych wszystko jest bardziej skomplikowane - niestety nie zawsze się to zamienia InitPlan/SubPlan. Czasami zamieniają się w ... Join lub ... Anti Joinzwłaszcza gdy piszesz coś w stylu WHERE NOT EXISTS .... I tutaj nie zawsze można je połączyć - w tekście planu nie ma operatorów odpowiadających węzłom planu.

Ponownie zadanie „z gwiazdką”: Niektóre VALUES w żądaniu. W tym przypadku i w planie otrzymasz kilka węzłów Values Scan.


W odróżnieniu od siebie pomogą przyrostki „numerowane” - dodawane są dokładnie w takiej kolejności, w jakiej znajdują się odpowiadające im przyrostki VALUES-bloki wzdłuż żądania od góry do dołu.

Przetwarzanie danych

Wygląda na to, że wszystko w naszej prośbie zostało załatwione – pozostało tylko tyle Limit.


Ale tutaj wszystko jest proste - takie węzły jak Limit, Sort, Aggregate, WindowAgg, Unique „mapuj” jeden do jednego do odpowiednich operatorów w żądaniu, jeśli istnieją. Nie ma tu żadnych „gwiazd” ani trudności.

DOŁĄCZ

Trudności pojawiają się, gdy chcemy się połączyć JOIN pomiędzy nimi. Nie zawsze jest to możliwe, ale jest to możliwe.


Z punktu widzenia analizatora zapytań mamy węzeł JoinExpr, który ma dokładnie dwójkę dzieci - lewego i prawego. Odpowiednio to jest to, co znajduje się „nad” Twoim DOŁĄCZENIEM i to, co jest napisane „pod” nim w żądaniu.

A z punktu widzenia planu są to dwaj potomkowie niektórych * Loop/* Join-węzeł. Nested Loop, Hash Anti Join,... - coś w tym stylu.

Użyjmy prostej logiki: jeśli mamy tabele A i B, które „łączą się” w planie, to w żądaniu mogą się one znajdować albo A-JOIN-BLub B-JOIN-A. Spróbujmy połączyć w ten sposób, spróbujmy połączyć w drugą stronę i tak dalej, aż skończą nam się takie pary.

Weźmy nasze drzewo składni, weźmy nasz plan, spójrzmy na nie... nie są podobne!


Narysujmy to jeszcze raz w formie wykresów – o, już coś na to wskazuje!


Zauważmy, że mamy węzły, które jednocześnie mają dzieci B i C – nie obchodzi nas, w jakiej kolejności. Połączmy je i odwróćmy obraz węzła.


Spójrzmy jeszcze raz. Teraz mamy węzły z dziećmi A i parami (B + C) - także z nimi kompatybilne.


Świetnie! Okazuje się, że jesteśmy tą dwójką JOIN z żądania z węzłami planu zostały pomyślnie połączone.

Niestety, ten problem nie zawsze jest rozwiązany.


Na przykład, jeśli w żądaniu A JOIN B JOIN C, a na planie przede wszystkim połączono „zewnętrzne” węzły A i C. Ale w żądaniu nie ma takiego operatora, nie mamy nic do podkreślenia, nie ma do czego przyczepić podpowiedzi. To samo dotyczy „przecinka” podczas pisania A, B.

Ale w większości przypadków prawie wszystkie węzły można „rozwiązać” i z czasem można uzyskać tego rodzaju profilowanie po lewej stronie - dosłownie, jak w Google Chrome podczas analizy kodu JavaScript. Możesz zobaczyć, ile czasu zajęło wykonanie każdej linii i każdej instrukcji.


Aby ułatwić Ci korzystanie z tego wszystkiego, stworzyliśmy miejsce do przechowywania archiwum, gdzie możesz zapisać i później znaleźć swoje plany wraz z powiązanymi prośbami lub udostępnić komuś link.

Jeśli potrzebujesz po prostu sprowadzić nieczytelne zapytanie do odpowiedniej formy, użyj nasz „normalizator”.

Źródło: www.habr.com