Optymalizacja zapytań na podstawie analizy planu: EXPLAIN ANALYZE

To jest ten moment prawdy: zapytania SQL to nie tylko linijki kodu, ale prawdziwy dialog z bazą danych. Jeśli szepczesz jej czułe "SELECT *", baza może cię dobrze zrozumieć i wykonać polecenie bez szemrania. Ale jeśli wrzucisz jej nieuporządkowany SQL-owy romans, baza może się zamyślić... a potem zacznie zamulać.

Optymalizacja zapytań to sztuka mówienia do bazy w jasny i zwięzły sposób. Gdy zapytanie jest napisane konkretnie i efektywnie, wykonuje się szybko, nie obciąża systemu i nie przeszkadza innym procesom. Ale źle napisane zapytanie może spowolnić całą bazę: zacznie zżerać więcej CPU i RAM-u, dysk będzie mielił niepotrzebne odczyty i zapisy, a nawet aplikacje korzystające z bazy zaczną lagować.

EXPLAIN ANALYZE pomaga wyłapać takie problematyczne miejsca i zrozumieć, gdzie zapytanie dostaje zadyszki. To jak diagnostyka — bez niej ciężko leczyć wydajność.

Typowe problemy w zapytaniach i jak je wykryć

Czas poznać podejrzanych o spowalnianie wydajności. Do tego uzbroimy się w komendę EXPLAIN ANALYZE.

Problem 1: Skanowanie sekwencyjne (Seq Scan)

Seq Scan (skanowanie sekwencyjne) — to sytuacja, gdy PostgreSQL szuka danych, przeglądając każdy wiersz tabeli. Spoko, jeśli tabela jest mała, ale na dużych tabelach taki numer to katorga.

Jak sprawdzić, czy używany jest Seq Scan? Po prostu odpal analizę przez EXPLAIN ANALYZE. Przykład:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

Wynik może wyglądać tak (zwróć uwagę na Seq Scan):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Jak rozwiązać problem?

Stwórz indeks na student_id, jeśli jeszcze go nie ma:

CREATE INDEX idx_student_id ON students(student_id);

Po tym odpal ponownie EXPLAIN ANALYZE. Powinieneś zobaczyć Index Scan zamiast Seq Scan.

Problem 2: niska selektywność warunków

Selektywność — to ile wierszy trzeba przetworzyć, żeby znaleźć to, czego szukasz. Jeśli twój filtr obejmuje prawie całą tabelę, to indeks cię nie uratuje.

Przykład zapytania z niską selektywnością:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Jeśli w tabeli 90% studentów studiuje Computer Science, zapytanie może użyć Seq Scan, nawet jeśli jest indeks na program.

Jak poprawić zapytanie?

1. Przemyśl logikę zapytania: może warto doprecyzować filtr, dodając dodatkowe warunki.
2. Upewnij się, że statystyki tabeli są aktualne (to pomaga PostgreSQL dobrze ocenić selektywność):

ANALYZE students;

3. Jeśli zapytanie niepotrzebnie używa indeksu zamiast skanowania sekwencyjnego, spróbuj wymusić na PostgreSQL użycie indeksu:

SET enable_seqscan = OFF;

Problem 3: zbędne operacje sortowania

Sortowanie (Sort) potrafi być kosztowne, zwłaszcza jeśli dane nie mieszczą się w RAM-ie. Typowym przypadkiem wymagającym sortowania jest ORDER BY.

Przykład problemu:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Możesz zobaczyć coś takiego:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Jak przyspieszyć sortowanie? Jeśli często sortujesz po danej kolumnie, możesz stworzyć indeks:

CREATE INDEX idx_last_name ON students(last_name);

Teraz PostgreSQL może użyć indeksu, żeby pobrać dane już posortowane, bez dodatkowego sortowania.

Problem 4: Brak ograniczeń (LIMIT)

Kiedy pobierasz dane przez SELECT bez ograniczenia liczby zwracanych wierszy, zapytanie może przetworzyć całą tabelę, nawet jeśli potrzebujesz tylko pierwszy wiersz.

Jak to wygląda:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Jeśli baza ma milion wierszy, a filtr gpa > 3.5 zwraca 80% tabeli, możesz się nieźle naczekać.

Jeśli potrzebujesz tylko 10 najlepszych studentów, użyj LIMIT:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Dodatkowo razem z LIMIT możesz użyć OFFSET do paginacji.

Zarządzanie parametrami wykonania: SET

Komenda SET w PostgreSQL służy do zmiany parametrów pracy sesji lub pojedynczego zapytania. To coś jak tymczasowa konfiguracja, która wpływa na zachowanie bazy tylko w ramach bieżącego połączenia.

Mówiąc prościej, SET — to sposób na sterowanie "nastrojem" PostgreSQL na żywo, bez grzebania w globalnych ustawieniach.

Gdzie to się przydaje?

• Zmiana języka lub formatu daty przed odpaleniem raportu.
• Zwiększenie pamięci na jedno ciężkie zapytanie.
• Wyłączenie logowania podczas masowego ładowania danych.
• Tymczasowa zmiana ścieżki wyszukiwania schematów (search_path).
• Zarządzanie bezpieczeństwem (np. tymczasowe obniżenie uprawnień użytkownika).

Ogólna składnia

SET parametr = wartość;

Żeby sprawdzić aktualną wartość parametru, użyj:

SHOW parametr;

Żeby przywrócić wartość domyślną:

RESET parametr;

Przykład kompleksowej optymalizacji

Załóżmy, że mamy zadanie: znaleźć ostatnich 10 studentów z najwyższą średnią (GPA), którzy studiują Computer Science. Oto oryginalne zapytanie:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Analiza zapytania: Najpierw odpal EXPLAIN ANALYZE:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Jeśli widzisz skanowanie sekwencyjne i sortowanie, to sygnał do optymalizacji.

2. Indeks po filtrze i sortowaniu:

   Stwórz indeks złożony, obejmujący obie kolumny:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Sprawdzenie poprawy:

   Odpal ponownie EXPLAIN ANALYZE. Teraz zapytanie powinno użyć tego indeksu, unikając sortowania i skanowania sekwencyjnego.

Metodologia optymalizacji zapytań

1. Zacznij od analizy obecnego planu wykonania. Użyj EXPLAIN ANALYZE, żeby wyłapać problematyczne operacje.

2. Zidentyfikuj wąskie gardła. Znajdź węzły planu, które zajmują najwięcej czasu lub wymagają dużo zasobów.

3. Załóż indeksy. Sprawdź, które kolumny biorą udział w filtracji i sortowaniu, i stwórz odpowiednie indeksy.

4. Minimalizuj ilość danych. Używaj LIMIT, OFFSET oraz precyzyjnych warunków filtrujących.

5. Aktualizuj statystyki. Odpal ANALYZE, żeby PostgreSQL miał świeże info o rozkładzie danych.

6. Testuj zmiany. Po optymalizacji ponownie odpal EXPLAIN ANALYZE, żeby upewnić się, że wydajność faktycznie wzrosła.

Co dalej?

Właśnie przeszedłeś ekspresowy kurs optymalizacji zapytań. Gratulacje! Im więcej będziesz eksperymentować z EXPLAIN ANALYZE, tym lepiej zrozumiesz, jak działa PostgreSQL pod maską. I pamiętaj: żadne magiczne indeksy cię nie uratują, jeśli zapytanie jest zbyt skomplikowane albo napisane niejasno. SQL, jak każdy język, lubi przejrzystość.