W tym tekście wyjaśniam, jak czytać wynik bez zgadywania, na które liczby patrzeć najpierw i jak używać tego narzędzia bez ryzyka dla danych. Dorzucam też praktyczne różnice między PostgreSQL i MySQL, bo w praktyce to właśnie tam najczęściej pojawiają się nieporozumienia.
Najważniejsze informacje, które od razu porządkują temat
- To polecenie uruchamia zapytanie i dopisuje do planu rzeczywiste czasy, liczbę wierszy oraz liczbę wykonań poszczególnych kroków.
- Cost nie oznacza milisekund, tylko wewnętrzny koszt estymowany przez optymalizator.
- Najpierw porównuję rows z actual rows, potem patrzę na loops i dopiero na ogólny czas.
- W PostgreSQL `ANALYZE` w tym kontekście nie jest tym samym co osobne polecenie aktualizujące statystyki tabel.
- Przy zapytaniach modyfikujących dane warto użyć transakcji z `ROLLBACK`, jeśli chcesz tylko profilować plan.
- Wynik w MySQL i PostgreSQL jest podobny ideowo, ale format i szczegóły metryk są inne.
Co robi to polecenie i kiedy naprawdę pomaga
Ja traktuję to narzędzie jako diagnostykę wydajności, a nie zwykły opis zapytania. Zwykły `EXPLAIN` pokazuje, co planner zamierza zrobić, a wariant z analizą pokazuje jeszcze, co faktycznie się wydarzyło podczas wykonania.
To robi różnicę zwłaszcza wtedy, gdy zapytanie wygląda rozsądnie na papierze, ale w aplikacji działa wyraźnie wolniej, niż powinno. Wtedy widać czarno na białym, czy problemem jest skan całej tabeli, źle dobrany join, zbyt optymistyczna estymacja liczby wierszy, czy może kosztowny sort albo odczyt z dysku.
Warto też pamiętać o jednej pułapce: osobne polecenie `ANALYZE` służy do zbierania statystyk o tabelach, a nie do mierzenia czasu zapytania. To podobne słowo, ale zupełnie inna rola. Jeśli statystyki są stare, planner może się mylić, a wtedy nawet dobrze napisane SQL potrafi dostać zły plan. Gdy rozumiesz tę różnicę, łatwiej zdecydować, czy naprawiasz zapytanie, czy najpierw odświeżasz statystyki tabel.
W praktyce używam tego przy wolnych zapytaniach z aplikacji, przy audycie indeksów i przy sprawdzaniu, czy zmiana w schemacie faktycznie coś poprawiła. Następny krok to nauczyć się czytać wynik tak, żeby nie mylić estymacji z rzeczywistym wykonaniem.
Jak czytać wynik bez zgadywania
Najważniejsza zasada jest prosta: nie zaczynam od czasu całkowitego. Najpierw patrzę na różnicę między tym, co planner przewidział, a tym, co naprawdę zaszło. Dopiero potem sprawdzam, gdzie ten rozdźwięk rośnie najbardziej.
| Element wyniku | Co oznacza | Na co zwracam uwagę |
|---|---|---|
| cost | Szacunkowy koszt operacji w wewnętrznych jednostkach optymalizatora | Nie traktuję tego jak czasu. To tylko porównanie planów między sobą. |
| actual time | Rzeczywisty czas wykonania danego kroku | To są zwykle milisekundy. Tu widać, gdzie naprawdę ucieka czas. |
| rows | Liczba wierszy przewidziana przez planner | Porównuję to z wynikiem rzeczywistym, bo duża różnica zwykle oznacza problem ze statystykami albo selektywnością warunku. |
| loops | Ile razy dany krok został wykonany | Wysokie loops przy kosztownym kroku to częsty sygnał, że join działa nieefektywnie. |
| buffers | Odczyty i zapisy buforów podczas planowania i wykonywania | Pomaga rozstrzygnąć, czy problem wynika z I/O, a nie tylko z CPU. |
W PostgreSQL warto też pamiętać, że actual time jest czasem rzeczywistym, a loops pokazuje liczbę wykonań kroku. Jeśli jakiś węzeł uruchamia się wiele razy, jego czas w planie jest zwykle pokazany jako średnia na jedno wykonanie. To bardzo pomaga przy nested loopach, gdzie pojedynczy krok wygląda niewinnie, ale po pomnożeniu przez liczbę powtórzeń robi się kosztowny.
Ja zwykle porównuję trzy rzeczy naraz: estymację wierszy, rzeczywiste wiersze i liczbę iteracji. Gdy te liczby są blisko siebie, plan zwykle jest zdrowy. Gdy różnice są duże, szukam przyczyny w danych, statystykach albo indeksach. To prowadzi wprost do sygnałów, które najczęściej zdradzają źródło problemu.
Jakie sygnały najczęściej zdradzają problem
Nie każdy dziwny plan oznacza awarię. Czasem wolniej działa po prostu duży zestaw danych. Ale są powtarzalne wzorce, które w praktyce od razu przyciągają moją uwagę.
| Sygnał | Co zwykle oznacza | Co sprawdzić najpierw |
|---|---|---|
| Seq Scan na dużej tabeli | Brak użytecznego indeksu albo filtr ma niską selektywność | Warunek `WHERE`, istniejące indeksy i aktualność statystyk |
| Nested Loop z bardzo dużą liczbą loops | Jedna strona joinu jest wykonywana wielokrotnie | Czy join key jest indeksowany i czy kolejność joinów ma sens |
| Duża różnica między rows i rzeczywistą liczbą wierszy | Planner źle ocenił selektywność albo rozkład danych jest nierówny | Statystyki tabel, histogramy, skew danych |
| Sort Method z użyciem dysku | Sort nie mieści się w pamięci i spada na wolniejsze I/O | `work_mem`, rozmiar wyniku i to, czy sort jest w ogóle potrzebny |
| Wysokie odczyty buffers | Zapytanie jest ograniczane przez dostęp do dysku lub cache nie pomaga | Indeksy, lokalność danych i sposób łączenia tabel |
Jest jeszcze jedna rzecz, którą początkujący często czytają źle: sequential scan nie zawsze jest zły. Na małej tabeli pełny skan bywa po prostu najszybszy. Problem zaczyna się wtedy, gdy ten sam wzorzec pojawia się na dużej tabeli, a planner wciąż nie ma lepszego wyboru, bo brakuje indeksu albo statystyki są nieaktualne.
Po samym planie zwykle da się wskazać kierunek naprawy, ale zanim coś zmienisz, trzeba zadbać o bezpieczny sposób testu. Właśnie tu najłatwiej popełnić kosztowny błąd.
Jak uruchamiać to bez ryzyka dla danych
W PostgreSQL i MySQL polecenie profilujące plan nadal uruchamia zapytanie. Dla `SELECT` to zwykle nie jest problem, ale dla `UPDATE`, `DELETE`, `INSERT` albo `MERGE` trzeba uważać, bo zmiany mogą się wykonać naprawdę. Jeśli chcę tylko sprawdzić plan dla zapytania modyfikującego dane, używam transakcji i kończę ją `ROLLBACK`.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '180 days';
ROLLBACK;To podejście daje mi pełny obraz wykonania, ale bez trwałego zapisu zmian. Dodatkowo warto pamiętać, że sam pomiar wprowadza niewielki narzut, więc wynik może być trochę wolniejszy niż zwykłe uruchomienie. Nie traktuję więc jednej liczby jako absolutnej prawdy. Patrzę raczej na różnice między wariantami tego samego zapytania.
Jeśli potrzebuję wyniku czytelnego dla narzędzia albo skryptu, wybieram format maszynowy. W PostgreSQL przydaje się JSON, XML albo YAML, a w MySQL często najbardziej użyteczny jest format drzewiasty lub JSON. To już nie jest kwestia smaku, tylko tego, jak później analizujesz wynik: ręcznie, w edytorze, czy automatycznie w pipeline.
Gdy bezpieczeństwo i interpretacja są już pod kontrolą, najciekawsze staje się porównanie silników. I tu różnice są większe, niż wielu osobom się wydaje.
PostgreSQL i MySQL pokazują podobne rzeczy, ale w innym układzie
Idea jest wspólna: zobaczyć plan i porównać go z rzeczywistym wykonaniem. Różni się jednak składnia, format wyjścia i to, na jakie szczegóły patrzysz jako pierwsze. To ważne, bo ktoś przyzwyczajony do jednego silnika potrafi źle odczytać wynik drugiego.
| Aspekt | PostgreSQL | MySQL |
|---|---|---|
| Typowa składnia | `EXPLAIN (ANALYZE, BUFFERS) ...` | `EXPLAIN ANALYZE ...` |
| Domyślny układ wyniku | Najczęściej tekstowy, ale dostępne są też formaty maszynowe | Najczęściej układ drzewiasty, z możliwością JSON w wybranych przypadkach |
| Co pokazuje czas | Czas rzeczywisty kroków i osobno czas planowania | Czas rzeczywisty iteratorów, zwykle w milisekundach |
| Jak czytać koszt | Cost to jednostki estymowane, nie czas | Cost też jest estymacją, ale wynik jest prezentowany w innym stylu |
| Co jest szczególnie przydatne | Buffers, różnice między estymacją a realnym wykonaniem, czas planowania | Iterator tree, liczba pętli, czas pierwszego i ostatniego wiersza |
W praktyce nie chodzi o to, który silnik „lepiej” pokazuje plan, tylko który lepiej pasuje do twojego sposobu diagnozowania. Ja przy PostgreSQL najpierw sprawdzam różnice między rows i actual rows oraz odczyty z buforów, a w MySQL mocniej patrzę na strukturę iteratorów i to, gdzie konkretnie rośnie liczba pętli. W obu przypadkach cel jest ten sam: zrozumieć, dlaczego optymalizator wybrał właśnie taki przebieg zapytania.
Jeśli plan nadal nie daje odpowiedzi, trzeba zejść o poziom niżej i sprawdzić, czy problem nie leży poza samym zapytaniem. To najczęściej oszczędza najwięcej czasu.
Co robię, gdy plan nadal nie wyjaśnia problemu
Jeżeli metryki wyglądają sensownie, a zapytanie wciąż działa wolno, wracam do rzeczy bardziej „ziemskich”: statystyk, indeksów, pamięci i realnego rozmiaru danych. W praktyce najczęściej winne są cztery rzeczy.
- Nieaktualne statystyki tabel, przez które planner źle szacuje liczbę wierszy.
- Zły indeks albo indeks, który nie pasuje do kolejności warunków w zapytaniu.
- Zbyt mało pamięci roboczej, przez co sort lub hash ląduje na dysku.
- Różnica między środowiskiem testowym a produkcyjnym, zwłaszcza przy dużej nierówności danych.
Tu bardzo pomaga rozdzielenie dwóch pojęć: analiza planu to nie to samo co analiza danych tabeli. Jeśli statystyki są stare, uruchamiam osobne `ANALYZE` na tabelach, które się mocno zmieniły. Jeśli problemem jest aplikacja w Pythonie, sprawdzam też, ile czasu zajmuje pobranie rekordów po stronie klienta, bo samo `EXPLAIN ANALYZE` nie liczy pełnego kosztu sieci i renderowania wyniku dla użytkownika.
W takich sytuacjach lubię iść małymi krokami: najpierw jeden indeks, potem jedna zmiana warunku, potem ponowny pomiar na podobnym zestawie danych. To daje lepszą odpowiedź niż jednoczesne poprawianie pięciu rzeczy, po którym nie wiadomo, co faktycznie zadziałało.
Najkrótsza droga od planu do szybszego SQL
Jeśli miałbym sprowadzić całą metodę do jednego schematu, wyglądałoby to tak: najpierw sprawdź, czy planner trafnie ocenił rozmiar danych, potem zobacz, czy zapytanie nie powtarza zbyt wielu kosztownych kroków, a na końcu popraw tylko ten element, który naprawdę robi różnicę. W większości przypadków wystarczy to, żeby z wolnego zapytania zrobić przewidywalne i stabilne.
Największą wartość daje mi nie sam wynik, tylko sposób myślenia, który za nim stoi. Gdy czytam plan konsekwentnie, przestaję zgadywać i zaczynam diagnozować. A to w SQL zwykle oznacza krótszą drogę do konkretnej poprawy: indeksu, statystyki, zmiany joinu albo prostszego warunku, który lepiej pasuje do danych.
