Analiza wolnych zapytań z pg_stat_statements

Kiedy pracujesz nad prawdziwymi projektami, z twoją aplikacją może jednocześnie korzystać tysiące użytkowników. Wysyłają zapytania do bazy, dodają dane, czytają je, aktualizują... I nagle zauważasz, że twój serwer zaczyna "jęczeć". To znak, że twoje zapytania są dalekie od ideału. Czasem zapytanie, które "na papierze" wyglądało spoko, w praktyce może być katastrofą dla wydajności. I tu na scenę wchodzi pg_stat_statements.

pg_stat_statements pozwala ci:

1. Śledzić wolne zapytania.
2. Sprawdzać, ile razy dane zapytania były wykonywane.
3. Dowiedzieć się, ile czasu one zajmowały.
4. Zobaczyć średni czas wykonania zapytania.
5. Nie popełnić fatalnego błędu i nie przepisać całej aplikacji!

Poznawanie struktury pg_stat_statements

Po aktywacji rozszerzenia w twojej bazie pojawia się specjalny widok pg_stat_statements. Tu trzymane są wszystkie dane o wykonanych zapytaniach. Najpierw ogarnijmy, co tam jest:

SELECT * FROM pg_stat_statements LIMIT 1;

Wynik może wyglądać tak (wersja uproszczona):

Krótkie wyjaśnienia:

• query — samo zapytanie SQL.
• calls — ile razy to zapytanie było wykonane.
• total_time — ile łącznie czasu zajęło to zapytanie.
• rows — liczba wierszy, które zwróciło zapytanie.
• shared_blks_read — liczba przeczytanych bloków (sięgamy do dysku, jeśli nie korzystasz z cache).

Analiza wyników

Teraz, gdy pg_stat_statements jest włączone, zobaczmy, jak znaleźć wolne zapytania.

Najwolniejsze zapytania

Żeby wyłapać, które zapytania zabierają najwięcej czasu, możesz użyć takiego zapytania:

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

Tu:

• mean_time — to średni czas wykonania jednego zapytania (total_time / calls).
• ORDER BY total_time DESC — sortujemy po łącznym czasie wykonania.

Często wykonywane zapytania

Czasem problemem nie są wolne zapytania, tylko te, które są odpalane zbyt często. Na przykład:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Optymalizacja zapytań

1. Używaj indeksowania

Jeśli widzisz, że zapytania po konkretnych kolumnach są wolne, sprawdź, czy masz indeks na tych kolumnach. Załóżmy, że masz tabelę students z dużą ilością wierszy i często odwołujesz się do pola last_name. Warto zrobić indeks:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Przepisz zapytanie

Załóżmy, że widzisz, że zapytanie typu SELECT * FROM orders WHERE amount > 1000 trwa za długo. Najpewniej zamiast "wszystko o wszystkim" trzeba wybierać tylko potrzebne kolumny:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Czyszczenie statystyk

Czasem, żeby zobaczyć tylko nowe wyniki (np. po optymalizacji), trzeba wyczyścić dane w pg_stat_statements. Robi się to komendą:

SELECT pg_stat_statements_reset();

Działa jak przycisk "Resetuj" w twoim kalkulatorze. Po wykonaniu statystyki będą zbierane od nowa.

Szukanie problematycznych zapytań

Wyobraź sobie, że jesteś adminem bazy dla uniwerku i studenci masowo narzekają, że ich panel ładuje się wieki. Postanawiasz sprawdzić pg_stat_statements:

Krok 1: Szukanie najwolniejszych zapytań

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

Widzisz, że zapytanie typu SELECT * FROM students WHERE status = 'active' trwa 30 sekund. O ja... Trzeba działać!

Krok 2: Sprawdzenie indeksowania Analizując tabelę students, ogarniasz, że kolumna status nie ma indeksu. Naprawiasz to:

CREATE INDEX idx_students_status ON students (status);

Krok 3: Sprawdzenie efektu Po optymalizacji znowu sprawdzasz pg_stat_statements i widzisz, że zapytanie leci w 0.5 sekundy. Wygrana!

Częste błędy przy użyciu pg_stat_statements

Czasem admini popełniają błędy przy analizie zapytań:

1. Nieaktywowane rozszerzenie. Jeśli zapomnisz dodać pg_stat_statements do shared_preload_libraries, statystyki po prostu się nie zbiorą.
2. Ignorowanie indeksowania. Nawet jeśli zapytania są wolne, problem może rozwiązać dodanie odpowiednich indeksów.
3. Brak resetu statystyk. Jeśli nie wykonasz pg_stat_statements_reset(), stare dane przeszkadzają w analizie bieżącej sytuacji.

Korzystanie z pg_stat_statements w twojej pracy to jak GPS dla bazy danych: dokładnie mówi ci, gdzie utknąłeś w "korku" i nawet podpowiada, jak objechać. Dobrze ustawiając to narzędzie, możesz mega poprawić wydajność swoich baz danych.