Podstawowe koncepcje planu wykonania zapytania: cost, rows, width

Kiedy piszesz zapytanie SQL, PostgreSQL nie zaczyna go wykonywać od razu. Najpierw odpala swój "mózg" — optymalizator zapytań, który tworzy plan wykonania. Ten plan to jak trasa na mapie: PostgreSQL oblicza, jakie akcje i w jakiej kolejności trzeba wykonać, żeby skutecznie dostać dane.

Optymalizator zapytań ocenia wszystkie możliwe ścieżki wykonania twojego zapytania: sekwencyjne skanowanie tabeli, użycie indeksów, filtrowanie, sortowanie itd. Próbuje znaleźć najtańszy (jeśli chodzi o zasoby) sposób na wykonanie twojego zapytania. Czyli szuka kompromisu między czasem wykonania a zasobami serwera.

Kluczowe parametry planu wykonania

No to przechodzimy do prawdziwej "jazdy" — rozkładania parametrów, które PostgreSQL pokazuje ci po wykonaniu komendy EXPLAIN. Na początek weźmy prosty przykład:

EXPLAIN
SELECT * FROM students WHERE age > 20;

Dostaniesz coś w tym stylu:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=72)
  Filter: (age > 20)

Rozłóżmy te tajemnicze słowa i liczby.

1. cost (koszt wykonania)

cost — to szacowanie, ile zasobów potrzeba do wykonania zapytania. Ten parametr składa się z dwóch części:

• Startup Cost: koszt rozpoczęcia operacji (np. przygotowanie indeksu).
• Total Cost: całkowity koszt wykonania całej operacji.

Przykład:

cost=0.00..35.00

• 0.00 — to Startup Cost.
• 35.00 — to Total Cost.

Im niższe wartości cost, tym bardziej PostgreSQL lubi ten plan. Ale pamiętaj, że cost to wartość względna. Nie jest liczona w sekundach czy milisekundach, raczej pokazuje wewnętrzną ocenę PostgreSQL.

2. rows (przewidywana liczba wierszy)

rows pokazuje, ile wierszy PostgreSQL spodziewa się zwrócić lub przetworzyć na tym etapie wykonania zapytania. W naszym przykładzie:

rows=7

To znaczy, że PostgreSQL zakłada, że filtr age > 20 zwróci 7 wierszy. Te dane pochodzą ze statystyk, które PostgreSQL zbiera o tabeli. Jeśli statystyki są stare, prognoza może być nietrafiona. To może prowadzić do mniej optymalnego planu.

3. width (szerokość wiersza w bajtach)

width — to średni rozmiar każdego wiersza zwracanego na tym etapie, liczony w bajtach. W naszym przykładzie:

width=72

To znaczy, że każdy zwrócony wiersz zajmuje średnio 72 bajty. width uwzględnia rozmiar danych w kolumnach i wszelkie dodatkowe narzuty, jak identyfikatory wierszy czy dane techniczne.

To trochę jak z ładowaniem aplikacji. Jeśli jej "waga" (czyli width) jest duża, potrzebujesz więcej czasu na załadowanie, nawet jeśli masz szybki internet (czyli cost).

Przykład analizy planu wykonania

Spójrzmy na prawdziwy przykład. Załóżmy, że mamy tabelę students:

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

I wykonujemy takie zapytanie:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Wynik może wyglądać tak:

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan: PostgreSQL robi sekwencyjne skanowanie tabeli students. Czyli przechodzi przez każdy wiersz.
• cost=0.00..42.50: Koszt wykonania operacji. Startup Cost to 0.00, a całkowity koszt — 42.50.
• rows=3: PostgreSQL spodziewa się, że filtr age > 20 AND major = 'CS' zwróci 3 wiersze.
• width=164: Każdy wiersz zajmuje średnio 164 bajty.

Teraz już wiesz, jak PostgreSQL podejmuje decyzje i możesz wyłapywać słabe punkty w zapytaniach. Na przykład, jeśli widzisz wysoki cost, to może oznaczać, że zapytanie jest zbyt ciężkie. Albo jeśli widzisz dużo wierszy w rows, warto przemyśleć swój filtr.

Jak działa cost w praktyce?

Dodajmy indeks na kolumnę age:

CREATE INDEX idx_age ON students(age);

Teraz powtórzmy nasze zapytanie:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Wynik może się zmienić:

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Co się zmieniło?

• Zamiast Seq Scan mamy teraz Bitmap Heap Scan: PostgreSQL najpierw szuka pasujących wierszy w indeksie idx_age, a potem pobiera je z tabeli.
• cost mocno spadł: teraz Startup Cost to 4.37, a Total Cost — 20.50.
• Operacja stała się wydajniejsza dzięki indeksowi.

Wizualizacja: różnica między Seq Scan a Index Scan

Mała tabelka porównawcza, żeby było bardziej obrazowo:

Pułapki i typowe błędy

Kiedy korzystasz z parametrów planu wykonania, bądź gotowy na niespodzianki. Na przykład, nie zawsze niski cost oznacza lepsze wykonanie. Jeśli statystyki bazy są stare (np. po masowej aktualizacji tabeli), plan może być niedokładny. Odświeżaj statystyki komendą ANALYZE. Więcej o tym w następnej lekcji.

Upewnij się, że używasz indeksów tam, gdzie trzeba. Ale nie przesadzaj z indeksami: zajmują miejsce i spowalniają operacje zapisu.