Użycie EXPLAIN ANALYZE do mierzenia faktycznego czasu wykonania zapytań

Jeśli komenda EXPLAIN pozwala ci zajrzeć w kryształową kulę i zobaczyć, jak PostgreSQL “planuje” wykonać zapytanie, to EXPLAIN ANALYZE zamienia cię w prawdziwego detektywa, który sprawdza, co się naprawdę wydarzyło.

Kluczowe różnice między EXPLAIN a EXPLAIN ANALYZE:

EXPLAIN – to teoria, pokazująca, jak PostgreSQL planuje wykonanie zapytania. Widzisz przewidywane wartości, takie jak liczba wierszy (rows) i koszt wykonania (cost).

EXPLAIN ANALYZE – to praktyka. PostgreSQL naprawdę wykonuje zapytanie i pokazuje:

• Faktyczną liczbę przetworzonych wierszy na każdym etapie.
• Faktyczny czas wykonania każdej operacji.
• Porównanie z założeniami planu (rows i cost).

Przykład: jeśli twoje zapytanie zakłada przetworzenie 100 wierszy, a faktycznie przetwarza 10 000 wierszy, EXPLAIN ANALYZE od razu wyciągnie ten nieprzyjemny fakt na światło dzienne!

Podstawowa składnia i użycie

Tak jak EXPLAIN, EXPLAIN ANALYZE jest banalnie prosty w użyciu. Po prostu dodaj słowo ANALYZE do swojej komendy EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Oto co zrobi PostgreSQL:

• Wykona zapytanie.
• Zarejestruje każdą operację w planie wykonania, razem z faktycznymi danymi.
• Zwróci pełny opis procesu wykonania zapytania.

Jakie dane daje EXPLAIN ANALYZE?

Faktyczny czas wykonania operacji:

• Actual Start Time: kiedy operacja się zaczęła.
• Actual End Time: kiedy operacja się skończyła.

Całkowita liczba przetworzonych wierszy:

To pomaga ocenić, na ile trafne były założenia planu (wartości rows).

Informacje o buforach:

Jak były używane bufory dyskowe i pamięciowe.

Przykład użycia EXPLAIN ANALYZE

Sprawdźmy konkretny przykład. Mamy tabelę students, która zawiera dane o studentach:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Wykonajmy zapytanie, żeby wyciągnąć studentów starszych niż 20 lat:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Przykładowy wynik:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Rozkładamy wynik na czynniki pierwsze:

• Seq Scan – mówi, że PostgreSQL robi sekwencyjne skanowanie tabeli.
• cost=0.00..14.00 – to przewidywany koszt operacji.
• rows=2 – PostgreSQL spodziewa się, że zapytanie zwróci 2 wiersze (i ma rację!).
• actual time=0.025..0.026 – faktyczny czas wykonania operacji (w milisekundach).
• Rows Removed by Filter: 2 – dwa wiersze zostały odfiltrowane, bo nie spełniały warunku WHERE.

Porównanie teorii i praktyki

Tu jest magia EXPLAIN ANALYZE: pokazuje, jak naprawdę zostało wykonane zapytanie i pozwala porównać to z teoretycznym planem wykonania.

Sprawdźmy bardziej złożony przykład.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Przykładowy wynik:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Co widzimy:

1. PostgreSQL wykonał zapytanie w 0.057 milisekundy.
2. Tylko jeden wiersz (rows=1) spełnia warunki WHERE.
3. Trzy wiersze zostały odfiltrowane (Rows Removed by Filter: 3).

Podsumowanie

Użycie EXPLAIN ANALYZE pozwala ci znaleźć wąskie gardła i zrozumieć, jak optymalizować zapytania. Na przykład:

• Jeśli Seq Scan jest zbyt "ciężki", może czas dodać indeks.
• Jeśli założenia PostgreSQL mocno odbiegają od rzeczywistych danych, trzeba sprawdzić statystyki tabel (ANALYZE) albo strukturę indeksów.