Pokaże Wam jak samemu możemy się dowiedzieć na podstawie statystyk, ile wierszy powinno zwrócić (wziąć pod uwagę) nasze zapytanie.
Do podejrzenia statystyk wykorzystujemy widok pg_stats. Informacje te pochodzą z tabeli pg_statistics (katalog systemowy, dostępny tylko dla super-użytkownika). Więcej informacji na temat pg_statistics znajdziecie tutaj.
Informację, które możecie odczytać z pg_stats:
- schemaname - nazwa schematu, której znajduje się tabela
- tablename - nazwa tabeli
- attname - nazwa kolumny, dla której przestawiane są statystyki
- inherited - przyjmuje wartości boolowskie. Jeśli jest true w wierszach są kolumny dziedziczące
- null_frac - współczynnik ilości wierszy, które zawierają wartości NULL
- avg_width - średnia długość danych w wierszach podanych w bajtach
- n_distinct - jeśli wartość jest dodatnia, jest estymacją ilości wierszy o wartościach unikalnych. Jeśli jest wartością ujemną, jest liczbą unikalnych wartości podzielną przez ilość wierszy
- most_common_vals - lista najczęściej występujących wartości w kolumnie
- most_common_freqs - lista współczynników wystąpień wartości w tabeli most_common_vals
- histogram_bounds - lista wartości, które dzielą wszystkie wartości w kolumnie na równe grupy. Dane nie są podawane jeśli typ kolumny nie uwzględnia operatora '<' przy porównywaniu danych lub jeśli most_common_vals występują wszystkie możliwe wartości w kolumnie
- correlations - statystyczna korelacja uporządkowania wartości kolumn. Podobnie jak dla histogram_bounds tylko dla kolumn które uwzględniają operator '<', dana jest podawana
Od wersji 9.2 doszły dodatkowe informacje: most_common_elems (najczęściej występujące wartości nienullowe), most_common_elem_freqs (współczynnik wystąpień najczęściej występujących wartości nienullowych), elem_count_histogram (histogramu liczby rodzajów różnych nie-zerowych wartości elementów w obrębie wartości kolumny, a następnie przez średnią liczbę odrębnych elementów niezerowych).
Statystyki w praktyce
Przykład tabeli na której będziemy pracować:Table "public.event2" +--------+-----------------------------+-----------+ | Column | Type | Modifiers | +--------+-----------------------------+-----------+ | c1 | integer | not null | | c2 | character varying(20) | | | c3 | timestamp without time zone | | | c4 | integer | | | c5 | integer | | +--------+-----------------------------+-----------+
Nasza tabelka ma 500 wierszy. Jeśli jednak nie znany ilości wierszy, mamy dwa wyjścia: policzyć je funkcją count lub sprawdzić na tabeli pg_class:
127.0.0.1:7432 postgres@test # SELECT reltuples, relpages FROM pg_class WHERE relname = 'event2'; +-----------+----------+ | reltuples | relpages | +-----------+----------+ | 500 | 9 | +-----------+----------+ (1 row) Time: 0,442 ms
Statystyki dla kolumny c5:
127.0.0.1:7432 postgres@test # select * from pg_stats where tablename = 'event2' and attname = 'c5';
+-[ RECORD 1 ]------+---------------------------------------------------------------------------------------------------------------------+
| schemaname | public |
| tablename | event2 |
| attname | c5 |
| inherited | f |
| null_frac | 0 |
| avg_width | 4 |
| n_distinct | 20 |
| most_common_vals | {19,16,4,5,6,9,0,12,18,17,3,1,2,15,7,11,10,13,8,14} |
| most_common_freqs | {0.074,0.062,0.06,0.06,0.06,0.058,0.056,0.054,0.054,0.052,0.05,0.048,0.048,0.042,0.04,0.04,0.038,0.036,0.034,0.034} |
| histogram_bounds | NULL |
| correlation | 0.067399 |
+-------------------+---------------------------------------------------------------------------------------------------------------------+
Time: 7,469 ms
Chcemy określić ile wierszy pasuje do warunku c2 = 19. Bazując na danych ze statystyk wiemy, że w kolumnie mamy:
- 20 unikalnych wartości
- nie ma wartości NULL
- przykładowo wartość 19 występuje w 7.4% wszystkich wartości czyli dla 500 wszystkich wierszach, jest ich 37 ( => 500 * 7.4%)
127.0.0.1:7432 postgres@test # explain select * from event2 where c5 = 19; +---------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------+ | Seq Scan on event2 (cost=0.00..15.25 rows=37 width=40) | | Filter: (c5 = 19) | +---------------------------------------------------------+ (2 rows) Time: 0,356 ms
Podobnie mamy selektywność dla kolumny c2, o ile szukana wartość znajduje się w najczęsciej występujących wartościach:
127.0.0.1:7432 postgres@test # select * from pg_stats where tablename = 'event2' and attname = 'c2';
+-[ RECORD 1 ]------+---------------------------------+
| schemaname | public |
| tablename | event2 |
| attname | c2 |
| inherited | f |
| null_frac | 0 |
| avg_width | 6 |
| n_distinct | 5 |
| most_common_vals | {0074a,97a14,f87fa,dc358,85c58} |
| most_common_freqs | {0.336,0.336,0.208,0.066,0.054} |
| histogram_bounds | NULL |
| correlation | 0.0551021 |
+-------------------+---------------------------------+
Time: 1,243 ms
Jeśli jednak naszej wartości tam nie ma, selektywność obliczamy następująco:
(1 - sum(most_common_freqs))/(num_distinct / most_common_vals_nr)
A dla naszej tabeli/kolumny będzie to wynosiło:
(1 - (0.336+0.336+0.208+0.066+0.054)) / (5 / 5) = 0
Inny przykład gdy mamy tylko wartości unikalne. Statystyki dla kolumny c1:
127.0.0.1:7432 postgres@test # select * from pg_stats where tablename = 'event2' and attname = 'c1';
+-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| schemaname | public |
| tablename | event2 |
| attname | c1 |
| inherited | f |
| null_frac | 0 |
| avg_width | 4 |
| n_distinct | -1 |
| most_common_vals | NULL |
| most_common_freqs | NULL |
| histogram_bounds | {1,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225,230,235,240,245,250,255,260,265,270,275,280,285,290,295,300,305,310,315,320,325,330,335,340,345,350,355,360,365,370,375,380,385,390,395,400,405,410,415,420,425,430,435,440,445,450,455,460,465,470,475,480,485,490,495,500} |
| correlation | 0.998155 |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Time: 1,250 ms
Tym razem wzór selektywności na podstawie histogram_bounds wygląda następująco:
(full_his_bound_count + (value - his_bound[nr].min)/(his_bound[nr].max - his_bound[nr].min)) / num_his_bound
Gdzie:
- full_his_bround_count - ilość przedziałów, które w całości bierzemy pod uwagę przy obliczaniu selektywności.
- value - nasza wartość dla której obliczamy selektywność
- his_bound[nr] - przedział, w którym jest nasza wartość. Np dla wartości = 8 jest to przedział 2, pomiędzy 5 (min), a 10 (max).
- num_his_bound - ilość przedziałów w kolumnie histogram_bounds. W powyższym przykładzie wartości mamy od 1 do 500 co 5 dlatego 500/5 = 100
Obliczanie selektywności rekordów dla warunków:
- c1 < 8, selektywność = (1 + (8 - 5)/(10 - 5))/(100) = 8
- c1 < 28, selektywność = (5 + (28 - 25)/(30 - 25))/(100) = 28
Sprawdźmy nasze wyniki:
127.0.0.1:7432 postgres@test # explain select * from event2 where c1 < 28; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Index Scan using event2_pkey on event2 (cost=0.00..8.86 rows=28 width=40) | | Index Cond: (c1 < 28) | +----------------------------------------------------------------------------+ (2 rows) Time: 0,311 ms
Obliczanie selektywności dla dwóch kolumn jest bardzo podobne. Na początku wychodzimy od obliczania selektywności każdej kolumny z osobna, mnożymy je i w ten sposób otrzymujemy selektywność całego warunku.
Przykład dla zapytania:
select * from event2 where c2 = '0074a' and c4 = 30
selektywność = ((selektywność dla c2) * (selektywność dla c4) * (ilość wierszy)) = 0.336 * 0.02 = 0.006
ilość wierszy = 0.006 * 500 = 3
127.0.0.1:7432 postgres@test # explain select * from event2 where c2 = '0074a' and c4 = 30; +--------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------+ | Seq Scan on event2 (cost=0.00..16.50 rows=3 width=26) | | Filter: (((c2)::text = '0074a'::text) AND (c4 = 30)) | +--------------------------------------------------------+ (2 rows) Time: 0,456 ms
Odświeżanie informacji na temat statystyk
Dane w tabeli pg_statistics są aktualizowane na bieżąco oraz po wykonaniu poleceń: ANALYZE i VACCUM ANALYZE.Aby zmniejszyć/zwiększyć ilość danych statystycznych możemy zmienić wartość zmiennej globalnej konfiguracyjnej: default_statistics_target (domyślnie
przyjmuje wartość 100).
O innych przykładach obliczania selektywności dla zapytań odsyłam do dokumentacji tutaj.
Brak komentarzy:
Prześlij komentarz