Czym jest EXPLAIN i jak dzięki niemu analizować zapytania

Wyobraź sobie, że tworzysz apkę i nagle jedno z twoich zapytań staje się najdroższe w całej bazie danych. Zaczynasz zauważać błędy i lagi w działaniu aplikacji. Właśnie wtedy na scenę wchodzi EXPLAIN, który pomaga ogarnąć, gdzie wszystko się sypie. Optymalizacja zapytań na podstawie analizy EXPLAIN pozwala ci zaoszczędzić zasoby, zyskać czas i poprawić wrażenia użytkowników z twojej apki.

EXPLAIN — to twój sposób na zajrzenie do wnętrza PostgreSQL i zobaczenie, jak dokładnie baza danych zamierza wykonać zapytanie. Pokazuje, czy zostanie użyty index, czy wszystko skończy się na pełnym skanowaniu tabeli, jakie kroki wykona optymalizator, w jakiej kolejności i jak duże będą pośrednie wyniki.

Innymi słowy, EXPLAIN pozwala ogarnąć, czego się spodziewać po wykonaniu zapytania: czy jest „ciężkie”, ile wierszy ma być przetworzonych i jakie zasoby będą użyte. To niezastąpione narzędzie, gdy zapytanie nagle zaczyna mulić, a ty musisz dowiedzieć się — dlaczego.

EXPLAIN — to jak latarka w ciemności: z nią widać, co się dzieje pod maską i gdzie dokładnie wszystko idzie nie tak.

Składnia komendy EXPLAIN

Zobaczmy podstawową składnię komendy EXPLAIN:

EXPLAIN twoje_zapytanie_SQL;

Przykład zapytania:

EXPLAIN SELECT * FROM students WHERE age > 20;

Kiedy odpalasz tę komendę, PostgreSQL nie wykona zapytania. Zamiast tego pokaże ci plan wykonania. To jak szkic przed budową — warto zobaczyć, co będzie zrobione, zanim coś popsujesz.

Przykład wyniku:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Ten wynik może wyglądać strasznie, ale spokojnie — zaraz rozkminimy główne elementy.

Analiza podstawowego planu wykonania

Rozłóżmy na czynniki ten wynik:

1. Seq Scan on students — to znaczy, że PostgreSQL przeskanuje całą tabelę students (sekwencyjne skanowanie). To nie zawsze źle, ale przy dużych tabelach Seq Scan może być wolny.

2. cost=0.00..35.00 — to szacowany koszt wykonania operacji:

   • Startup Cost: początkowy koszt operacji (tu 0.00).
   • Total Cost: całkowity koszt do zakończenia operacji (tutaj 35.00).

3. rows=7 — PostgreSQL zakłada, że warunek age > 20 zwróci 7 wierszy. To się nazywa "kardynalność" (cardinality). Jeśli widzisz dziwne szacunki, może to oznaczać, że statystyki twojej tabeli są nieaktualne.

4. width=37 — to średni rozmiar jednego wiersza w bajtach.

5. Filter: (age > 20) — pokazuje, że PostgreSQL zastosuje filtr, sprawdzając każdy wiersz.

Tak więc wynik EXPLAIN daje ci wgląd w strategie i założenia PostgreSQL. Możesz użyć tych informacji do optymalizacji.

Opcje komendy EXPLAIN

Chociaż podstawowy wynik EXPLAIN już jest przydatny, możesz go zmodyfikować za pomocą tych opcji:

ANALYZE

Z tą opcją PostgreSQL nie tylko pokaże plan wykonania, ale też wykona zapytanie i poda faktyczne dane. Przykład:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Pozwoli to porównać założenia PostgreSQL z rzeczywistym wykonaniem, żeby zobaczyć, na ile się pokrywają.

VERBOSE

Pokazuje dodatkowe szczegóły, przydatne do głębszej analizy. Przykład:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Pokazuje użycie buforów pamięci podczas wykonania zapytania. Używa się razem z ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Jeśli chcesz ukryć lub pokazać info o kosztach (cost), użyj tej opcji:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

Wynik planu może być pokazany w innych formatach, np. JSON lub XML. Przykład:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Przykład użycia EXPLAIN

Załóżmy, że masz bazę university z tabelą students. Chcesz znaleźć wszystkich studentów starszych niż 20 lat:

EXPLAIN SELECT * FROM students WHERE age > 20;

Wynik może wyglądać tak:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Jak już wspominaliśmy, to sekwencyjne skanowanie Seq Scan, które może być nieefektywne przy dużych tabelach.

Teraz stwórzmy index na kolumnie age i zobaczmy, czy plan się zmieni:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Wynik:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Teraz PostgreSQL używa skanowania po indeksie (Index Scan), co zwykle jest szybsze, szczególnie przy dużych tabelach.

Typowe pytania i błędy

Dlaczego moje zapytanie działa wolno, mimo że jest index?

Może zapytanie zwraca za dużo wierszy, przez co użycie indexu nie daje zysku. Index może być kiepski albo nieaktualny.

Co jeśli wynik EXPLAIN jest trudny do zrozumienia?

Zacznij od prostych zapytań i analizuj węzły planu wykonania po jednej operacji.

Jak sprawdzić, czy statystyki tabeli są nieaktualne?

Wykonaj komendę ANALYZE students

Kiedy używać EXPLAIN bez ANALYZE?

Gdy chcesz zobaczyć plan bez faktycznego wykonania (np. dla zapytań, które modyfikują dane).