Analiza wydajności funkcji i procedur: użycie EXPLAIN ANALYZE

EXPLAIN ANALYZE pomaga zrozumieć, jak PostgreSQL "myśli", kiedy wykonuje twoje zapytanie:

• Jakie kroki są wykonywane podczas przetwarzania danych.
• Ile czasu zajmuje wykonanie każdego kroku.
• Dlaczego konkretne zapytanie działa wolno — czy to przez pełny przegląd tabeli (ang. Seq Scan) czy pominięty indeks.

Komenda EXPLAIN ANALYZE faktycznie wykonuje zapytanie i pokazuje, jak PostgreSQL optymalizuje wykonanie. Wyobraź sobie, że rozbierasz zegarek, żeby ogarnąć, jak działa jego mechanizm. To samo robi EXPLAIN ANALYZE, tylko z twoimi zapytaniami SQL.

Składnia EXPLAIN ANALYZE

Zacznijmy od prostego przykładu. Oto podstawowa forma komendy:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

To zapytanie wykona komendę SELECT i pokaże, jak PostgreSQL przetwarza dane.

Wynik EXPLAIN ANALYZE to drzewo wykonania zapytania. Każdy poziom drzewa opisuje krok, który PostgreSQL wykonuje:

• Operation Type — typ operacji (np. Seq Scan, Index Scan).
• Cost — jak bardzo PostgreSQL uważa, że ta operacja jest kosztowna.
• Rows — ile wierszy się spodziewa i ile faktycznie wyszło w wyniku.
• Time — ile czasu zajęła operacja.

Przykład wyniku:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Zwróć uwagę na Seq Scan on students. To znaczy, że PostgreSQL przegląda WSZYSTKIE wiersze tabeli students. Jeśli tabela jest duża, to może być BARDZO WOLNE.

Przykłady użycia EXPLAIN ANALYZE

Przejdźmy przez kilka praktycznych przykładów, gdzie nauczysz się wykrywać i naprawiać problemy w zapytaniach.

Przykład 1: pełny przegląd tabeli

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Wynik:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Problem tutaj jest taki, że PostgreSQL robi Seq Scan, czyli przegląda wszystkie wiersze tabeli. Jeśli w tabeli są miliony wierszy, to będzie wąskie gardło wydajności.

Rozwiązanie: stwórz indeks na kolumnie age.

CREATE INDEX idx_students_age ON students(age);

Teraz wykonaj to samo zapytanie:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Wynik:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Widzimy Index Scan zamiast Seq Scan. Super, teraz zapytanie śmiga!

Przykład 2: złożone zapytanie z JOIN

Załóżmy, że mamy dwie tabele: students i courses. Chcemy poznać imiona studentów i nazwy kursów, na które są zapisani.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Wynik może wyglądać mniej więcej tak:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Jak widzisz, PostgreSQL ma wszystko pod kontrolą: używa indeksów na tabelach enrollments i courses, a wykonanie jest szybkie. Ale jeśli któregoś indeksu zabraknie, możesz zobaczyć Seq Scan, co spowolni wykonanie.

Optymalizacja wydajności funkcji

Załóżmy teraz, że mamy funkcję, która zwraca listę studentów starszych niż określony wiek:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Możemy analizować wydajność tej funkcji za pomocą EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Przyspieszanie działania funkcji

Jeśli wykonanie funkcji trwa długo, możliwe, że problemem jest pełny przegląd tabeli. Żeby to naprawić:

1. Upewnij się, że kolumna używana w filtrach (age) jest zindeksowana.
2. Sprawdź liczbę wierszy w tabeli i pomyśl o partycjonowaniu, jeśli danych jest za dużo.

Wąskie gardła i jak je naprawić

1. Pełny przegląd tabel (Seq Scan). Używaj indeksów, żeby przyspieszyć wyszukiwanie wierszy. Ale pamiętaj, zbyt dużo indeksów też może spowolnić wstawianie danych.

2. Duża liczba wierszy w wyniku. Jeśli zapytanie zwraca miliony wierszy, pomyśl o dodaniu filtrów (WHERE, LIMIT) albo paginacji (OFFSET).

3. "Kosztowne" operacje. Niektóre operacje, jak sortowanie, agregacja czy łączenie dużych tabel, mogą zużywać sporo zasobów. Używaj indeksów albo dziel zapytania na kilka etapów.