Wstęp

Pewnie znasz tę sytuację: na środowisku deweloperskim wszystko śmiga, zapytania wracają od razu, testy przechodzą, a potem wdrażasz na produkcję no i już tak do końca nie działa.

Tak to bywa, że możemy przewidzieć kilkanaście scenariuszy, a ten jeden, który nie przyjdzie nam do głowy, wyskoczy akurat na produkcji. W tym szybkim strzale chciałbym opisać jeden z takich przypadków, który mi się przytrafił z indeksami w PostgreSQL. Do dziś pamiętam, jak mnie wtedy zaskoczyło, że baza po prostu wybrała sobie inny indeks niż ten, który dla niej przygotowaliśmy.

Na devie wszystko działało

Mieliśmy w projekcie tabelę, na której było naprawdę sporo indeksów, jak dobrze pamiętam, coś koło 12-15. Każdy miał swoje uzasadnienie, bo musieliśmy robić na niej zapytania po wielu różnych kombinacjach pól, co wiązało się też z tym, że potrzebowaliśmy kilkunastu indeksów. Implementując rozwiązanie, na devie mamy raptem kilkadziesiąt rekordów, przy dobrej symulacji możemy pokusić się o dużo więcej, ale produkcja to jednak inna skala.

Na devie wszystko wyglądało ładnie. Zapytania korzystały z indeksów, które zakładaliśmy właśnie z myślą o nich, więc nikt nie miał powodu, żeby coś sprawdzać. Natomiast na produkcji PostgreSQL zaczął wybierać indeksy po swojemu. Do jednego zapytania pasowało kilka indeksów, a silnik uznawał, że inny jest wydajniejszy niż ten, który według nas powinien być użyty. Efekt był taki, że zapytania zrobiły się mało optymalne i zaczęliśmy mieć problemy z wydajnością.

Jak do tego doszliśmy i co pomogło

Usiedliśmy do bazy produkcyjnej i po kolei sprawdzaliśmy, co mogło pójść nie tak. Pierwszy krok to oczywiście znalezienie winowajcy. Z analizy bazy wychodziło nam potencjalnie kilku kandydatów, więc pierwsze, co robimy, to EXPLAIN ANALYZE na każdym z nich i patrzymy, jak się zachowują na produkcji. I tutaj od razu wychodzi, że użyty jest zły indeks. Patrzymy na indeksy na bazie i według wszystkiego, co wiemy, powinien być użyty inny, a mimo to PostgreSQL robi po swojemu.

Zobrazujmy to sobie na przykładzie:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'NEW';

Samo EXPLAIN pokazuje tylko plan, czyli to, co baza zamierza zrobić. Dopiero z ANALYZE zapytanie faktycznie się wykonuje i dostajesz realne czasy oraz liczbę wierszy. W uproszczeniu problem wygląda tak:

Index Scan using idx_orders_customer on orders
  Index Cond: (customer_id = 42)
  Filter: ((status)::text = 'NEW'::text)

Czyli baza bierze indeks tylko po customer_id, czyta wszystkie zamówienia klienta, a status sprawdza dopiero na końcu jako filtr. Jak klient ma dużo zamówień, to robi się z tego sporo niepotrzebnej roboty. A tak wygląda plan, kiedy baza weźmie indeks złożony (customer_id, status), czyli ten, który dla tego zapytania założyliśmy:

Index Scan using idx_orders_customer_status on orders
  Index Cond: ((customer_id = 42) AND ((status)::text = 'NEW'::text))

Tutaj oba warunki obsługuje indeks, więc baza czyta tylko to, co faktycznie potrzebne. W EXPLAIN ANALYZE warto jeszcze spojrzeć na rows. Baza pokazuje tam, ile wierszy się spodziewała i ile wróciło naprawdę. Jak te liczby mocno się rozjeżdżają, to znak, że źle oszacowała sytuację i na tej podstawie wybrała plan. I mała uwaga: ANALYZE naprawdę wykonuje zapytanie, więc jeśli sprawdzasz tak UPDATE czy DELETE na produkcji, to lepiej opakuj to w transakcję i zrób ROLLBACK.

A jak to naprawiliśmy? Okazało się, że dwa indeksy się na siebie nakładały. Najpierw usunęliśmy jeden z nich, ten, którego brak według nas najmniej bolał, i PostgreSQL od razu zaczął brać właściwy indeks. Tylko że ten usunięty dalej był potrzebny w innych zapytaniach. No to zamiast niego założyliśmy dwa mniejsze indeksy złożone, bardziej precyzyjne, czyli lepiej dopasowane do konkretnych zapytań. I dopiero wtedy wszystko zaczęło działać tak, jak powinno.

Dlaczego PostgreSQL wybrał nie ten indeks?

Szczerze? Dokładnie już nie pamiętam, co było źródłem problemu w naszym przypadku. Natomiast dziś wiem trochę więcej o tym, jak PostgreSQL podejmuje taką decyzję, i to sporo wyjaśnia.

Baza nie wie, który indeks Ty uważasz za właściwy. Ona dla każdego zapytania szacuje koszt kilku możliwych planów na podstawie statystyk tabeli, czyli między innymi tego, ile jest wierszy i jak rozkładają się wartości w kolumnach. I bierze ten plan, który według tych szacunków wychodzi najtaniej. Na devie przy kilkudziesięciu rekordach każdy plan jest szybki, więc tam problemu po prostu nie zobaczysz. Natomiast przy milionach rekordów jeden błędny szacunek potrafi zrobić z szybkiego zapytania bardzo wolne.

A teraz dołóż do tego kilka indeksów, które pasują do tego samego zapytania, na przykład (customer_id) i (customer_id, status). Baza ma kilku podobnych kandydatów i wystarczy niewielka pomyłka w szacunkach, żeby wybrała nie ten. Do tego PostgreSQL domyślnie zakłada, że kolumny są od siebie niezależne. Więc jeśli w Twoich danych dwie kolumny są mocno ze sobą powiązane, to baza źle policzy, ile wierszy zwróci warunek na obu naraz.

Jest jeszcze jedna rzecz, o której warto wiedzieć, jeśli piszesz w Javie. JDBC, a więc też Hibernate czy Spring Data JPA, wysyła zapytania jako prepared statements. Po kilku wykonaniach tego samego zapytania sterownik PostgreSQL przygotowuje je po stronie serwera i baza może wtedy przejść na jeden ogólny plan, który nie patrzy na konkretne wartości parametrów. I teraz, jeśli dane są nierówno rozłożone, na przykład jeden klient ma miliony zamówień, a reszta po kilka, to taki plan może być okej dla większości wywołań, ale bardzo słaby dla tych kilku. Objaw jest dość charakterystyczny: wklejasz zapytanie ręcznie do konsoli i śmiga, a to samo wywołane z aplikacji się muli. Żeby to sprawdzić, możesz na próbę ustawić plan_cache_mode na force_custom_plan i zobaczyć, czy problem znika.

Gdybym dziś trafił na taką sytuację, to oprócz EXPLAIN ANALYZE sprawdziłbym jeszcze, które indeksy są w ogóle używane:

SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

Jeśli indeks ma idx_scan na poziomie zera albo blisko zera, to jest dobry kandydat do usunięcia. Oczywiście pod warunkiem, że statystyki nie były niedawno resetowane i że patrzysz na bazę, na którą faktycznie idzie ruch. A jeśli problemem są powiązane kolumny, to PostgreSQL pozwala dodać dla nich rozszerzone statystyki:

CREATE STATISTICS orders_status_type (dependencies) ON status, type FROM orders;
ANALYZE orders;

Co warto zapamiętać

Planowanie indeksów jest naprawdę ważne. Dodając nowy indeks, warto spojrzeć na całość, czyli na pozostałe indeksy, które już są na tabeli, i oszacować, czy przy danym zapytaniu baza nie weźmie przypadkiem jakiegoś innego. Każdy kolejny indeks to po prostu kolejna opcja, przy której PostgreSQL może się pomylić.

Oczywiście indeksy trzeba też tworzyć z rozwagą. Odczyt robi się szybszy, ale każdy indeks wpływa na INSERT i UPDATE, bo baza musi go aktualizować razem z tabelą. No i warto pamiętać, żeby nie zakładać indeksów na zapas, „bo może się kiedyś przydadzą”. Jeśli chcesz zobaczyć, jak to wygląda od strony samego indeksu, czyli co przyspiesza, a za co płacisz, opisałem to we wpisie Co to jest indeks w bazach danych i jak wpływa na wydajność?

Podsumowanie

W tym krótkim wpisie chciałem pokazać, że nawet jeśli coś dobrze zaplanujesz i na devie wszystko działa, to po wdrożeniu na produkcję zawsze może coś pójść nie tak.

Dlatego jeśli wiesz, że pracujesz na kluczowych tabelach, na których zapytania wykonują się na produkcji dość często, a do tego są one dość duże, to warto dobrze to przemyśleć już na etapie implementacji. Dzięki temu unikniesz nieprzyjemnych sytuacji po wdrożeniu.