Plan wykonania zapytania i jego analiza

Analiza węzłów

Wiemy już w jaki sposób możemy wyświetlić plan wykonania zapytania, zajmiemy się więc teraz ich analizą. Na poniższej ilustracji oznaczyłem najważniejsze elementy literami:

A : Ta sekcja określa rodzaj skanu oraz obiekt na którym jest wykonywany. W tym przypadku jest to skan sekwencyjny na tabeli some_stuff.

B : W tej części znajdziesz dwie wartości kosztu wykonania zapytania. Pierwsza to koszt początkowy określający koszt pobrania pierwszego wiersza. Zaskakująca może być wartość 0. Skan sekwencyjny zaczyna pobierać wiersze od razu, nie potrzebuje żadnych przygotować. Stąd taka wartość. Dodałem do zapytania sortowanie. Plan tego zapytania jest wyświetlony na kolejnej ilustracji. W tym przypadku pierwszym węzłem jest sortowanie, które musi zostać wykonane zanim wiersze zaczną być przekazywane do aplikacji klienckiej. Zauważ że w tym przypadku koszt skanu sekwencyjnego pozostał taki sam, jednak jego rozpoczęcie wymaga wcześniejszego posortowania danych – tutaj koszt początkowy jest znacznie wyższy. Drugą wartością w podawanym koszcie jest kosztem pełnego wykonania węzła, a więc w tym przypadku odczytania całej tabeli.

C : To oszacowana liczba wierszy do wyświetlenia. Jeśli ta wartość bardzo się różni od rzeczywistej liczby wierszy w tabeli – powinien to być dla nas znak, że statystyki są nieaktualne i należy je odświeżyć.

D : Oszacowana liczba bajtów jaką średnio zajmuje jeden rekord. Oszacujmy więc wielkość tabeli na podstawie tych danych. Mamy (160000 wierszy * 8 bajtów każdy)/1024/1024 = 1,22 MB.

E : Tutaj mamy rzeczywiste dane obrazujące koszt wykonania i ilość wierszy jaka faktycznie została przetworzona w wyniku wykonania zapytania. Actual time – dwie wartości kosztu - tym razem rzeczywistego. Rows – faktyczna liczba przetworzonych wierszy.

F : Jeśli wartość parametru loops jest większa niż 1, oznacza to że dany węzeł był wykonywany więcej niż raz. Może tak się zdarzyć np. przy operacji łączenia tabel. Należy pamiętać, że wartość parametrów actual time i rows odnosi się do pojedynczego wykonania pętli. Jeśli ilość wykonań jest większa niż 1, należy te wartości pomnożyć przez ilość wykonań aby uzyskać faktyczny koszt i ilość wierszy przetworzonych w ramach danego węzła.

Skan po indeksie

Jeśli istnieje indeks który można byłoby wykorzystać w realizacji zapytania, najprawdopodobniej zostanie on wykorzystany zamiast skanu sekwencyjnego po tabeli. Na potrzeby przykładu stworzyłem indeks „turbo_burbo” którego dane są posortowane w sposób zgodny z wymogami zapytania.

create index turbo_burbo on some_stuff(x); explain analyze select * from some_stuff order by x;

Sortowanie

Na poniższej ilustracji przedstawiony jest plan wykonania zapytania wymagający posortowania danych. W tym jednak przypadku nie mam indeksu którego można by użyć i dane trzeba będzie posortować „na żywo”. Zaznaczony fragment jest bardzo ważny z punktu widzenia wydajności. Operacja sortowania może być wykonana z użyciem pamięci operacyjnej lub dysku. To w jaki sposób zostanie wykonane sortowanie zależy od ustawienia parametru work_mem. Jeśli całe sortowanie nie jest w stanie zostać wykonane w pamięci określonej przez ten parametr, zostanie wykonane z użyciem dysku tak jak to widzimy na poniższym przykładzie. External sort to właśnie sortowanie z użyciem dysku. Wielkość WORK_MEM mam ustawioną domyślnie na 4MB, a jak widzimy na potrzeby sortowania zostało wykorzystane mniej niż 3MB na dysku. Dlaczego więc nie zostało to posortowane w pamięci? Sortowanie z użyciem dysku potrzebuje mniej miejsca ponieważ jest wykonywane innym algorytmem. Na drugiej z poniższych ilustracji widzimy sortowanie po zwiększeniu parametru WORK_MEM do 64 MB.

explain analyze select * from some_stuff order by y;

Po zmianie wielkości pamięci używanej m.in. do sortowania, zmienił się też plan wykonania zapytania. Tym razem sortowanie jest wykonywane z użyciem algorytmu QUICKSORT – czyli na poziomie pamięci operacyjnej. Widzimy teraz, że na potrzeby sortowania w pamięci zostało wykorzystane nie 3 a prawie 14MB. Dlatego właśnie wcześniej planer zapytania zadecydował o wykorzystaniu zewnętrznego sortowania z użyciem dysku – sortowanie w pamięci wymagało jak widać znacznie więcej przestrzeni niż mieliśmy dostępne w work_mem.

set work_mem='64MB'; explain analyze select * from some_stuff order by y;

Warto też zwrócić uwagę na czas wykonania zapytania

Przykład z sortowaniem i zmianą parametru work_mem jest raczej czysto informacyjny - zmiana parametrów nawet w ramach sesji to nie taka prosta sprawa - szczególnie na produkcji