Statystyki obiektów

Informacje podstawowe


Statystyki służą lepszemu planowaniu wykonania zapytań. Odnoszą się np. do wielkości tabel, zróżnicowania danych w kolumnach etc. Im lepiej oddają one rzeczywisty stan danych, tym plany wykonania będą wydajniejsze. Mogą one być zbierane ręcznie z użyciem polecenia ANALYZE, lub przez demona autovacuum.

Dane statystyczne możemy przejrzeć np. z użyciem widoku pg_stats:


select * from pg_stats where tablename='some_stuff';




Jeśli uruchamiasz powyższy przykład jako inny niż superużytkownik user bazodanowy, pamiętaj że zobaczysz w tym słowniku informacje o obiekcie tylko jeśli masz uprawnienia dostępu do tego obiektu.


Możemy tam znaleźć na przykład informacje o zróżnicowaniu danych w poszczególnych kolumnach, czy ilości wartości unikalnych. Ciekawe informacje znajdziemy również w słowniku pg_class:


select relname,reltuples,relpages from pg_class

where relname='przyklad_statystyki';




Z tego słownika dowiemy się ile jest wierszy w tabeli (reltuples), oraz jaką zajmują przestrzeń (relpages) wyrażoną w ilości bloków.


Odświeżanie statystyk


Statystyki możemy odświeżać ręcznie lub pozostawić to zadanie demonowi autovacuum. Aby odświeżyć statystyki ręcznie dla obiektu używamy komendy analyze:


analyze some_stuff;


Jeśli zechcemy sprawdzić kiedy dla jakiegoś obiektu zostały ostatnio odświeżone statystyki, bądź odnaleźć obiekty dla których statystyki dawno nie były odświeżane możemy zajrzeć do słownika pg_stat_all_tables. Poniżej przykładowy kod z użyciem którego po odświeżeniu statystyk przeprowadziłem jeszcze czyszczenie tabeli i sprawdzam kiedy ostatnio było przeprowadzone czyszczenie i analiza zarówno ręcznie jak i automatycznie:


vacuum some_stuff;

select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze

from pg_stat_all_tables where relname='some_stuff';




Proces autovacuum odświeża statystyki obiektów automatycznie. Robi to dla tych tabel, dla których ilość zmienionych wierszy przekroczy wartość określoną w parametrze autovacuum_analyze_threshold:


show autovacuum_analyze_threshold;




który jest domyślnie ustawiony na 50 wierszy. Parametr ten jest ustawiony dla całej bazy, jednak możemy zmienić jego wartość dla wybranych tabel indywidualnie:


ALTER TABLE zlecenia

SET (autovacuum_analyze_threshold=500);


A jakbyśmy chcieli sprawdzić indywidualne ustawienia dla tabeli, wystarczy zajrzeć do słownika pg_class:


select relname,reloptions from pg_class where relname='zlecenia';




Proces autovacuum uruchamia się sam co czas określony w autovacuum_naptime domyślnie ustawionym na minutę:


show autovacuum_naptime;




Czyli w skrócie – autovacuum uruchomi się automatycznie co czas określony w parametrze autovacuum_naptime i odświeży statystyki dla tych tabel w których zostanie zmienione więcej wierszy niż określone w parametrze autovacuum_analyze_threshold dla bazy lub indywidualnie dla obiektu.


Default_statistics_target i histogram_bounds


Im więcej mamy danych statystycznych i im bardziej są one precyzyjne, tym lepiej planowane są algorytmy wykonania zapytań. Z drugiej strony zbieranie takich danych to zużycie zasobów systemowych i czas potrzebny na ich przetworzenie. To jak dużo jest zbieranych danych statystycznych zależy od parametru default_statistics_target. Myślę że najlepiej będzie to przeanalizować na przykładzie. Stwórzmy przykładową tabelkę i załadujmy do niej dane:


create table przyklad_statystyki (

x integer

);


insert into przyklad_statystyki values (generate_series (1,1000000) );


Zmienimy teraz parametr default_statistics_target na czas trwania sejsi na wartość 10. Jej domyślna wartość to 100. Następnie przeanalizujmy tabelkę . Przyjrzyj się kolumnie histogram_bounds w tym i następnym przykładzie.


set default_statistics_target=10;

analyze przyklad_statystyki;




Teraz zmienimy wartość parametru na 3 i ponownie przenalizujemy tabelę:


set default_statistics_target=3;

analyze przyklad_statystyki;

select tablename,histogram_bounds from pg_stats where tablename='przyklad_statystyki';



W kolumnie histogram_bounds znajdziemy informacje na temat dystrybucji danych w kolumnie. W tabeli przyklad_statystyki mamy tylko jedną kolumnę, o nazwie X tak więc nie wyświetlałem jej. Pamiętaj że histogram_bounds nie odnosi się do tabeli a do kolumn! Porównaj ilość wpisów w tym i poprzednim przykładzie. W kolumnie X mamy wartości w zakresie 1-1000000. Informacje z histogram_bounds oddają mniej więcej rozłożenie tych danych. Im więcej danych mamy w histogram_bounds, tym lepiej może być szacowana np. selektywność przy wybieraniu wierszy z użyciem warunków w WHERE.

Parametr default_statistics_target przyjmuje wartości w zakresie 1-1000. Jak widzisz w powyższych przykładach wartość ta odnosi się do ilości histogramów dla poszczególnych kolumn. Parametr ten możesz zmieniać dla sesji, albo dla całej bazy danych. Domyślne ustawienie (100) jest zwykle wystarczające, choć zależy głównie od wielkości tabeli i rozłożenia wystąpień wartości. Wyobraź sobie że masz tabelę w które przechowujesz informację o obywatelach Polski i kodzie pocztowym do jakiego są przypisani. Gdyby do każdego kodu pocztowego była przypisana taka sama liczba obywateli, mało precyzyjny histogram (mający mało wartości) byłby wystarczający, ponieważ selektywność dla każdego kodu pocztowego byłaby zbliżona. Wystarczyłaby nam zasadniczo nawet średnia selektywność. Ponieważ rozłożenie jest całkiem inne w rzeczywistości, może dojść do sytuacji takiej – dla 90% kodów pocztowych średnio w jednym jest np. 200 obywateli (wartość wyjęta z kapelusza – zupełnie nie mam pojęcia czy jest właściwa, przyjąłem taką na potrzeby przykładu) a 10% po 30000 (np. w centrach dużych miast). Jeśli mielibyśmy histogram mało precyzyjny – np. ustawilibyśmy wartość 5 dla default_statistics_target mogłoby się okazać że histogram_bounds wygląda tak: 203,240,189,196,233,210. Na takim histogramie bazowałoby obliczenie selektywności przy wyborze wierszy. Lepiej byłoby, żeby PostgreSQL wiedział o istnieniu takich anomaliów w całym zakresie jak kody pocztowe pod którymi mieszka 30000 obywateli, ponieważ wtedy plan wykonania mógłby wyglądać całkiem inaczej.

Podsumowując – jeśli mamy duże zróżnicowanie zagęszczenia wartości w kolumnie, a jednocześnie dana kolumna często występuje w warunkach where – warto rozważyć wyższą precyzję statystyk dla niej. Możemy ją ustawić indywidualnie dla każdej z kolumn – niezależnie od odgórnego ustawienia default_statistics_target. Aby ustawić pożądany poziom statystyk dla wybranej kolumny tabeli wydajemy polecenie:


ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny SET STATISTICS x;


Aby przywrócić pierwotną domyślną wartość wydajemy polecenie:


ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny SET STATISTICS -1;


Wartość -1 ustawia domyślną wartość określaną przez parametr default_statistics_target dla jego aktualnego ustawienia. Oczywiście nie możemy zapomnieć o ponownym uruchomieniu polecenia ANALYZE!



Ten artykuł jest elementem poniższych kursów: