Wprowadzenie do analizy wydajności zapytań

Pozwól, że zacznę od prawdziwej historii. Wyobraź sobie, że pracujesz jako dev na dużej platformie sklepu internetowego. Wszystko śmiga idealnie, poza jednym szczegółem: raport miesięcznej sprzedaży ładuje się tak wolno, że twoi użytkownicy zdążą złożyć jeszcze jedno zamówienie, zanim się skończy. Ty się stresujesz, twój menadżer się stresuje, serwer PostgreSQL gotuje się jak czajnik i wszystko zaczyna pachnieć apokalipsą. Teraz wyobraź sobie, że mógłbyś jednym ruchem znaleźć przyczynę i ją naprawić.

Analiza wydajności zapytań to jak badanie lekarskie twojego PostgreSQL. Pomaga znaleźć „wąskie gardła” (bóle) i naprawić je, żeby poprawić doświadczenie użytkownika i oszczędzić zasoby systemowe.

Jak zapytania działają pod maską PostgreSQL?

Kiedy piszesz proste zapytanie typu:

SELECT * FROM products WHERE price > 100;

PostgreSQL nie rzuca się od razu po dane. Najpierw analizuje twoje zapytanie, wymyśla jak najlepiej je wykonać, i dopiero potem bierze się do roboty.

Główne etapy wykonywania zapytań:

1. Parsowanie. PostgreSQL sprawdza twoje zapytanie pod kątem błędów składniowych i zamienia je na wewnętrzną reprezentację.
2. Optymalizacja. Optymalizator zapytań rozważa kilka sposobów wykonania zapytania i wybiera najtańszy (czasowo i zasobowo).
3. Wykonanie. Serwer działa według wybranego planu, wyciągając dane.

Co to jest „wąskie gardło”?

„Wąskie gardło” to część zapytania, która spowalnia całą resztę. Może to być operacja, która niespodziewanie zjada najwięcej czasu lub zasobów. Na przykład, jeśli PostgreSQL zamiast szybkiego użycia indeksu robi pełny skan tabeli (Seq Scan), zapytanie zwalnia. Albo jeśli danych jest dużo więcej niż się spodziewałeś i serwer traci masę czasu na sortowanie, łączenie, filtrowanie.

Takie momenty to właśnie wąskie gardła — to je warto szukać i optymalizować w pierwszej kolejności.

Narzędzia do analizy wydajności zapytań

W PostgreSQL masz kilka mocnych narzędzi, które pomogą ci znaleźć problemy w zapytaniach:

1. EXPLAIN i EXPLAIN ANALYZE. Te komendy pokazują ci, jak PostgreSQL zamierza wykonać zapytanie, albo nawet je wykonują, żeby zmierzyć faktyczną wydajność.

• EXPLAIN: pokazuje plan wykonania zapytania bez faktycznego jego wykonania.
• EXPLAIN ANALYZE: wykonuje zapytanie i pokazuje prawdziwy plan wykonania z rzeczywistymi metrykami czasowymi.

Przykład użycia EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Wynik:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Tu widać, że zapytanie robi „Seq Scan” — pełny skan tabeli, co jest nieefektywne przy dużych tabelach.

2. pg_stat_statements. To dodatkowe rozszerzenie, które śledzi wykonywane zapytania. Pokazuje:

• Jakie zapytania są wykonywane na serwerze.
• Ile czasu zajmuje każde zapytanie.
• Ile wierszy zwraca zapytanie i ile zasobów zużywa.

Żeby włączyć pg_stat_statements, musisz:

1. Włączyć rozszerzenie:

CREATE EXTENSION pg_stat_statements;

2. Skonfigurować PostgreSQL: W pliku postgresql.conf dodaj:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Zrestartuj PostgreSQL.

Teraz możesz analizować zapytania:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

To pokaże 5 najbardziej „ciężkich” zapytań w kolejności malejącej całkowitego czasu wykonania.

3. Logowanie wolnych zapytań. Możesz ustawić PostgreSQL, żeby zapisywał zapytania, które trwają zbyt długo (np. ponad 1 sekundę), do pliku logu.

W tym celu w postgresql.conf ustaw:

log_min_duration_statement = 1000  # Czas w milisekundach (1 sekunda)

Teraz wolne zapytania będą zapisywane do logów PostgreSQL.

Podstawowe metryki do analizy wydajności

Kiedy analizujesz wydajność zapytań, zwracaj uwagę na te kluczowe metryki:

1. Czas wykonania. Główny wskaźnik czasu potrzebnego na wykonanie zapytania. Im szybciej, tym lepiej.
2. Liczba wierszy. Jeśli twoje zapytanie zwraca lub skanuje więcej wierszy niż się spodziewałeś, to może być problem.
3. Użycie indeksów. Jeśli zapytanie powinno używać indeksu, a zamiast tego robi sekwencyjny skan (Seq Scan), to sygnał do optymalizacji.
4. Bufory i operacje dyskowe. Zapytania, które mocno korzystają z dysku, są wolniejsze niż te, które używają danych z RAM-u.

Jak wykorzystać tę wiedzę w praktyce?

Przykład 1: Wolne zapytanie

Piszesz zapytanie, żeby wyciągnąć wszystkie produkty droższe niż 100:

SELECT * FROM products WHERE price > 100;

Zauważasz, że zapytanie działa za wolno. Używasz EXPLAIN i widzisz:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Problem: zapytanie robi pełny skan tabeli, bo nie ma indeksu na kolumnie price.

Rozwiązanie:

Stwórz indeks:

CREATE INDEX idx_price ON products(price);

Teraz zapytanie używa Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Przykład 2: Wykrywanie wolnych zapytań z pg_stat_statements

Za pomocą komendy:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Odnajdujesz zapytanie, które zużywa najwięcej czasu. Otwierasz je przez EXPLAIN ANALYZE, poprawiasz i działa szybciej.

Kiedy zaczniesz używać EXPLAIN, pg_stat_statements i innych narzędzi, twoje zapytania będą działać szybciej, a serwer PostgreSQL będzie chodził jak szwajcarski zegarek. W następnym wykładzie wejdziemy w szczegóły parametrów EXPLAIN, takich jak cost, rows i width, żebyś mógł czytać plany wykonania zapytania jak otwartą książkę.