Statystyki są nieaktualne - kiedy uruchomić ANALYZE

PostgreSQL

Statystyki są nieaktualne - kiedy uruchomić ANALYZE

W skrócie

To jeden z najbardziej podstępnych problemów wydajnościowych w PostgreSQL, bo nic się nie psuje w sposób oczywisty - zapytanie po prostu zaczyna być wolne, choć dane i indeksy są na miejscu. Winowajcą bywają nieaktualne statystyki, na których opiera się planer zapytań. Gdy jego obraz danych rozjeżdża się z rzeczywistością, wybiera plany, które kiedyś były dobre, a teraz są fatalne. Pokażemy, kiedy trzeba odświeżyć statystyki ręcznie i jak sprawić, żeby baza robiła to na czas sama.

Jak to wygląda w praktyce

Klasyczny scenariusz: wgrywasz nocą duży import - kilka milionów wierszy do tabeli - i rano zapytania, które wczoraj wracały w ułamku sekundy, nagle mielą się sekundami. Plan wykonania pokazuje skan sekwencyjny zamiast użycia indeksu albo nieoptymalną kolejność łączenia tabel. Inny objaw pojawia się po masowym DELETE albo po dużej aktualizacji - planer szacuje, że pasuje na przykład dziesięć wierszy, a w rzeczywistości jest ich dziesięć tysięcy (albo odwrotnie), i przez to dobiera zły algorytm złączenia. Świeżo utworzona i od razu wypełniona tabela też potrafi zaskoczyć fatalnym planem, bo nie zdążył jej jeszcze dotknąć żaden ANALYZE.

Dlaczego tak się dzieje

Planer PostgreSQL nie zgaduje po omacku - dla każdego zapytania szacuje, ile wierszy zwrócą poszczególne kroki, i na tej podstawie wybiera najtańszy plan: czy użyć indeksu, czy przejść całą tabelę, w jakiej kolejności łączyć tabele. Te szacunki opierają się na statystykach zbieranych przez ANALYZE - między innymi na liczbie wierszy, liczbie wartości unikalnych w kolumnach i rozkładzie najczęstszych wartości. Jeśli dane mocno się zmieniły, a statystyki są stare, planer liczy na nieaktualnym obrazie i podejmuje decyzje oderwane od rzeczywistości. Autovacuum uruchamia ANALYZE automatycznie, ale dopiero po przekroczeniu progu zmienionych wierszy, więc tuż po dużym imporcie albo masowej zmianie statystyki bywają jeszcze stare. Świeżo załadowana tabela może w ogóle nie mieć statystyk, dopóki autoanalyze się nią nie zajmie - i wtedy planer działa na wartościach domyślnych, które rzadko pasują.

Jak to rozwiązać krok po kroku

1. Po dużym imporcie albo masowej zmianie od razu odśwież statystyki dotkniętej tabeli: ANALYZE zamowienia;. To szybka operacja, która nie blokuje normalnej pracy jak ciężkie sprzątanie.
2. Jeśli przy okazji chcesz posprzątać martwe wiersze po dużym DELETE lub UPDATE, połącz oba kroki: VACUUM ANALYZE zamowienia;.
3. Dla pewnej kolumny, po której często filtrujesz i której rozkład jest nierówny, zwiększ dokładność statystyk: ALTER TABLE zamowienia ALTER COLUMN status SET STATISTICS 500;, a potem ponów ANALYZE tej tabeli.
4. Gdy problem się powtarza na gorącej tabeli, spraw, by autovacuum analizował ją częściej: ALTER TABLE zamowienia SET (autovacuum_analyze_scale_factor = 0.02);, żeby ANALYZE ruszał już przy niewielkim odsetku zmian.
5. Do jednorazowego odświeżenia całej bazy po dużej migracji użyj narzędzia z powłoki: vacuumdb --analyze-only --all. Odświeży statystyki bez ciężkiego sprzątania.
6. Jeśli mimo aktualnych statystyk planer źle szacuje przy skorelowanych kolumnach, rozważ statystyki rozszerzone: CREATE STATISTICS ... ON kol_a, kol_b FROM tabela; i ponów ANALYZE - pomaga, gdy wartości w dwóch kolumnach są od siebie zależne.

Jak sprawdzić, że zadziałało

Najpewniejszy dowód daje EXPLAIN ANALYZE na problematycznym zapytaniu. Porównaj w nim liczbę wierszy szacowaną przez planer z liczbą rzeczywiście zwróconą - po dobrym ANALYZE te dwie wartości powinny być zbliżone, a plan powinien wrócić do sensownego wyboru (na przykład użycia indeksu). Datę ostatniego odświeżenia statystyk sprawdzisz w widoku pg_stat_user_tables w kolumnach last_analyze oraz last_autoanalyze - po ręcznym ANALYZE zobaczysz tam świeży znacznik czasu. Warto też potwierdzić realny efekt na czasie wykonania: to samo zapytanie po odświeżeniu statystyk powinno wrócić do dawnej, krótkiej odpowiedzi zamiast mielić sekundami.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5