Jak czytać plan zapytania (EXPLAIN ANALYZE)

PostgreSQL

Jak czytać plan zapytania (EXPLAIN ANALYZE)

W skrócie

EXPLAIN ANALYZE to najważniejsze narzędzie diagnostyczne w PostgreSQL, ale tylko wtedy, gdy umiemy odczytać jego wynik. Rozkładamy plan zapytania na czynniki pierwsze i pokazujemy, gdzie szukać problemu.

Jak to wygląda w praktyce

Uruchamiamy EXPLAIN ANALYZE na wolnym zapytaniu i dostajemy kilkanaście wciętych linii pełnych nazw operacji, kosztów, liczb wierszy i czasów. Na pierwszy rzut oka nic z tego nie wynika - nie wiadomo, czy 12 tysięcy w nawiasie to dużo, czy mało, ani która z linii odpowiada za większość czasu. Często ludzie patrzą tylko na pierwszą liczbę kosztu na górze i na tym poprzestają, albo mylą koszt szacowany z realnym czasem. Efekt jest taki, że plan, który miał wskazać problem, sam staje się zagadką. A wystarczy znać kilka zasad czytania, żeby ten wynik zaczął mówić wprost, gdzie leży wąskie gardło.

Dlaczego tak się dzieje

Plan zapytania to drzewo węzłów, gdzie każdy węzeł to jedna operacja: odczyt tabeli, użycie indeksu, złączenie, sortowanie, agregacja. Węzły zagnieżdżone (bardziej wcięte) wykonują się jako pierwsze i przekazują wyniki wyżej - dlatego plan czyta się od najgłębiej wciętych linii ku górze. Każdy węzeł ma dwie warstwy liczb. EXPLAIN bez wykonania pokazuje szacunki planera w formacie cost=start..total rows=N width=B - to prognozy w umownych jednostkach, nie sekundy. Dopiero EXPLAIN ANALYZE naprawdę wykonuje zapytanie i dokłada rzeczywiste pomiary: actual time=start..total rows=N loops=P. Najcenniejsza w diagnostyce jest różnica między rows szacowanym a rows rzeczywistym - duża rozbieżność znaczy, że planer źle ocenił dane i mógł wybrać zły plan. To dlatego czasu i decyzji nie da się ocenić z samego EXPLAIN - trzeba go wykonać.

Jak to rozwiązać krok po kroku

1. Zawsze uruchamiamy pełną wersję: EXPLAIN (ANALYZE, BUFFERS) <zapytanie>;. ANALYZE daje rzeczywiste czasy, a BUFFERS pokazuje, ile stron czytano z pamięci podręcznej, ile z dysku i czy powstały pliki tymczasowe.
2. Czytamy plan od środka na zewnątrz - od najbardziej wciętych węzłów. To one dostarczają dane węzłom nad nimi. Węzeł na samej górze to ostatni krok zwracający wynik.
3. Dla każdego węzła patrzymy na actual time i mnożymy górną wartość przez loops - węzeł wykonany w pętli 1000 razy realnie kosztuje tysiąckrotność swojego pojedynczego czasu. Tak znajdujemy węzeł, który zjada najwięcej.
4. Porównujemy rows szacowane (w cost=...) z rows rzeczywistym (w actual...). Jeśli planer spodziewał się 10 wierszy, a przyszło 500 tysięcy, to sygnał do ANALYZE tabeli albo do poprawy statystyk - zły szacunek prowadzi do złego planu.
5. Rozpoznajemy typy węzłów odczytu. Seq Scan czyta całą tabelę - to bywa poprawne dla małych tabel, ale przy selektywnym WHERE na dużej tabeli oznacza brak indeksu. Index Scan i Index Only Scan to celny odczyt po indeksie. Bitmap Heap Scan to kompromis dla warunków zwracających wiele wierszy.
6. Sprawdzamy węzły złączeń i sortowań. Nested Loop jest tani przy małej liczbie wierszy, ale zabójczy przy dużej. Hash Join i Merge Join lepiej znoszą duże zbiory. Przy Sort patrzymy na Sort Method - external merge Disk oznacza sortowanie na dysku i kandydata do podniesienia work_mem.
7. Na koniec bierzemy najkosztowniejszy węzeł i dopasowujemy działanie: indeks pod Seq Scan, ANALYZE przy złej estymacji, więcej pamięci przy sortowaniu na dysku, przepisanie zapytania przy kosztownym Nested Loop.

Jak sprawdzić, że zadziałało

Powtarzamy EXPLAIN (ANALYZE, BUFFERS) po zmianie i porównujemy dwa plany obok siebie. Dobry znak to zniknięcie kosztownego Seq Scan na rzecz Index Scan, zbliżenie się liczby wierszy szacowanej do rzeczywistej oraz spadek łącznego actual time w węźle-winowajcy i na szczycie planu. Jeśli w BUFFERS zniknęły odczyty z dysku (read) na rzecz trafień w pamięć (hit) albo przestały powstawać pliki tymczasowe, to twardy dowód poprawy. Warto sprawdzić plan na realnych, produkcyjnych wartościach parametrów, bo dla różnych danych planer potrafi wybrać różne ścieżki - to, że jeden przypadek przyspieszył, nie zawsze znaczy, że przyspieszyły wszystkie.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5