Indeksy PostgreSQl są kluczem do optymalizacji obciążenia bazy danych i skrócenia czasu wykonywania zapytania.
Rola indeksów bazy danych jest podobna do indeksu na końcu książki lub indeksu (katalogu) książek zgromadzonych w bibliotece. Indeks bazy danych przechowuje informacje o tym, gdzie w danej tabeli znajduje się wiersz danych, dzięki czemu baza danych nie musi skanować całej tabeli w poszukiwaniu informacji. Tak jak poszukiwanie książki w bibliotece bez wykorzystania indeksu wiązać się będzie z koniecznością sięgnięcia do wszystkich woluminów zgromadzonych na wielu półkach, tak nasza baza danych bez indeksu musi wykonywać pełne skanowanie tabeli za każdym razem, gdy potrzebuje znaleźć wiersz lub zbiór wierszy. Pełne skanowanie tabeli jest to proces, w którym baza danych po prostu przegląda tabelę wiersz po wierszu, szukając żądanego wiersza lub wierszy. W przypadku dużych tabel może to być powolne.
Indeks zawiera „adresy wierszy”, których baza danych może użyć, aby szybciej znaleźć to, czego szuka. Baza danych podczas wykonywania zapytania, najpierw przechodzi do indeksu, a następnie wykorzystując informacje w nim zawarte szybko identyfikuje odpowiedni wiersz i przystępuje do pobrania żądanych danych.
Istotnym jest również, aby wspomnieć, że o przydatności danego indeksu decyduje jego Klucz. Klucz indeksu to kolumna lub grupa kolumn, do których indeks jest stosowany. Indeks może przyspieszyć zapytanie tylko wtedy, gdy jego klucz zawiera jedną lub więcej kolumn użytych w predykacie zapytania (tj. w jego klauzuli WHERE). Odwołując się do przykładu z biblioteką. W bibliotece, jeśli znasz tylko tytuł książki, to ułożenie wszystkich książek wyłącznie według nazwiska autora nie pomoże zlokalizować poszukiwanego dzieła.
Inaczej mówiąc klucz indeksu mówi nam jakiej kolumny lub grupy kolumn dotyczy dany indeks.
W artykule omówinie ważniejsze indeksy w Postgresie.
Typy indeksów w PostgreSQL:
- B-tree
- z haszowaniem (hash)
- GIN
- GiST
- SP-GIST
- BRIN
Poszczególne indeksy używają innych algorytmów, które są najlepiej dopasowane do różnych typów zapytań. Domyślnie polecenie CREATE INDEX tworzy indeksy B-Tree, które jest indeksem najbardziej uniwersalnym, pasującym do większości typowych sytuacji.
Prosty przykład użycia CREATE INDEX:
CREATE INDEX nazwa_indexu ON nazwa_tabeli [USING typ_indeksu]
(
nazwa_kolumny [ASC | DESC] [NULLS {FIRST | LAST }],
...
);Indeks PostgreSQL – B-Tree
Indeksy B-Tree to specyficzny rodzaj indeksu bazy danych, porządkujący określone zakresy wartości kluczy w postaci struktury drzewiastej. B-tree oznacza „drzewo zrównoważone”. Indeks B-drzewa porządkuje wiersze zgodnie z wartościami klucza (pamiętaj, że kluczem jest kolumna lub kolumny) i dzieli uporządkowaną listę na zakresy. Zakresy te są zorganizowane w strukturę drzewa, w której węzeł główny definiuje szeroki zestaw zakresów, a każdy poziom poniżej definiuje coraz węższy zakres.
Rozważmy ponownie nasz przykład biblioteczny i wyobraźmy sobie, że książki w bibliotece ułożone są według nazwiska autora. Załóżmy teraz, że szukasz książki autorstwa Henryka Sienkiewicza, czyli wartością klucza będzie słowo „Sienkiewicz”. W budynku biblioteki widzisz rzędy regałów, przy czym każdy rząd oznaczony jest początkowymi literami nazwisk autorów, na przykład A-F, dalej G-O, potem P-Z. Mamy tutaj wiec do czynienia z Zakresami wartości kluczy (np. Zakres P-Z). Gdy przechodzimy do konkretnego regału, widzimy iż każda półka posiada tabliczkę z mniejszym zakresem nazwisk autorów, np. PA-PZ (nazwiska zaczynające się od liter PA… do PZ…) , potem SA-SZ i tak dalej. Mamy więc tutaj do czynienia z podzakresami większych zakresów. Dalej z kolei książki są na każdej półce ułożone alfabetycznie wg. nazwisk autorów, dzięki czemu możesz szybko znaleźć książkę Sienkiewicza.
Wróćmy ponownie do indeksu B-Tree. Co do zasady organizacja takiego indeksu odzwierciedla opisany wyżej przykład dotyczący biblioteki. Oczywiście w wypadku indeksu bazodanowego mamy do czynienia z większą ilością zakresów, jednak idea bazowa pozostaje ta sama. Każdy węzeł w B-drzewie nazywany jest blokiem. Bloki na każdym poziomie drzewa z wyjątkiem ostatniego nazywane są blokami gałęzi, a te na ostatnim poziomie nazywane są blokami liści. Wpisy w blokach gałęzi składają się z identyfikatora zakresu i wskaźnika do bloku na następnym poziomie drzewa. Każdy wpis w bloku liścia reprezentuje wiersz i składa się z wartości klucza (np. „Sienkiewicz”) i identyfikatora wiersza, którego baza danych używa do uzyskania wszystkich innych danych zawartych w tym wierszu.
B-Tree – podczas wykonywania polecenia CREATE INDEX ten typ indexu jest tworzony domyślnie. Wirtualnie wszystkie bazy danych będą miały indeksy B-Tree. B-Tree zapewnia równowagę, utrzymuje podobną ilość danych w każdym „odgałęzieniu”. Dlatego też liczba poziomów, które muszą zostać przemierzone aby odnaleźć właściwe wiersze jest zawsze w podobnym zakresie. B-Trees mogą działać na wszystkich typach danych, a także mogą być używane do pobierania wartości NULL.
CREATE UNIQUE INDEX title_idx ON films (title);Indeks PostgreSQL – oparty o haszowanie
Indeksy oparte o haszowanie (mieszanie) bazują na znanej w dziedzinie programowania koncepcji tablic (map) mieszających (Hash Map). Mapa mieszająca jest strukturą danych, która stanowi formę tablicy asocjacyjnej, czyli strukturę, która może mapować klucze na wartości. Tablica mieszająca wykorzystuje funkcję mieszającą do obliczenia indeksu, zwanego również kodem mieszającym, w tablicy segmentów lub przedziałów, z których można znaleźć żądaną wartość. Podczas wyszukiwania elementu w tablicy klucz jest haszowany, a wynikowy hash wskazuje, gdzie przechowywana jest odpowiednia wartość. Innymi słowy haszowanie polega na pobraniu fragmentu informacji (ciągu) i przekształceniu go w adres lub wskaźnik do docelowych danych.
Indeksy haszujące pozwalają na szybkie wyszukiwanie danych przechowywanych w tabelach, szczególnie gdy istnieje wiele danych wejściowych o podobnych wartościach lub duplikatach. Co więcej w rzeczywistości klucz indeksu nie przechowuje wartości a jej skrót, co tym bardziej przyspiesza przeszukiwanie w samej tabeli kluczy. Niezależnie od szczegółów implementacyjnych trzeba pamiętać iż w PostgreSQL indeksy oparte o haszowanie uchodzą za zdecydowanie najszybsze.
Indeksy oparte o haszowanie przed PostgreSQL 10 były wykorzystywane właściwie jedynie w przypadku porównywania wartości (operator =). Od Postgres 10 indeksy haszowane są zapisywane do logów oraz poddawane replikacji.Przykład utworzenia indeksu z haszowaniem:
CREATE INDEX index_name ON table_name USING hash (column);Indeks PostgreSQL – GIN
W informatyce indeks odwrócony jest indeksem bazy danych przechowującym mapowanie treści, takich jak słowa lub liczby, do ich lokalizacji w tabeli (czy dokumencie). W PostgreSQL indeksy odwrócone zawierają wpis dla każdego słowa (leksemu) wraz z listą pasujących lokalizacji, w których dane słowo wystąpiło. „Odwrócony” odnosi się do sposobu, w jaki jest zbudowana wewnętrzna struktura indeksu, która stanowi obejmujące całą tabelę drzewo wszystkich wartości kolumn, w którym pojedynczy wiersz może być reprezentowany w wielu miejscach w drzewie. Dla porównania, indeks B-drzewa na ogół ma jedną lokalizację, w której wpis indeksu wskazuje na określony wiersz.
Indeksy odwrotne GIN (Generalized Inverted Indexes) pozwalają na obsługę wartości zawierających więcej niż jeden klucz czyli są przydatne gdy mówimy o indeksowaniu wartości tablicy. Podczas gdy indeksy B-Tree są zoptymalizowane pod kątem sytuacji, gdy wiersz ma raczej pojedynczą wartość klucza. Przede wszystkim jednak celem indeksu odwróconego jest umożliwienie szybkiego wyszukiwania pełnotekstowego kosztem zwiększonego przetwarzania podczas dodawania dokumentu do bazy danych. Przykład utworzenia indeksu GIN:
CREATE INDEX index_name ON table_name USING GIN (to_tsvector('english', column));Indeks PostgreSQL – GiST
Indeksy GiST (Generalized Inverted Search Tree) podobnie jak GIN są indeksami inwertowanymi, jak się więc łatwo domyślić ich zastosowanie jest w istocie podobne. Umożliwiają one budowanie ogólnych zrównoważonych struktur drzewiastych. Mogą być używane do operacji wykraczających poza porównania równości i zakresów. Służą do indeksowania typów danych geometrycznych, a także wyszukiwania pełnotekstowego.
Indeks GIST posiada cechy indeksu B-Tree, bowiem jest indeksem o strukturze drzewiastej. Istnieje między nimi jednak zasadnicza różnica. Indeks B-Tree jest ściśle powiązany z logicznymi operatorami porównania, to jest wartości kluczy na których operuje indeks mogą wchodzić w relację „> (większy)”, „< (mniejszy)” lub „= (równy)”.
Nie zawsze dane gromadzone w bazie mogą wchodzić w typowe relacje logiczne czy matematyczne. Czasami operujemy na danych, gdzie takie relacje nie mają sensu. Dzieje się tak na przykład dla danych przestrzennych, tekstowych, obrazów, zawartości XML itp.
Wówczas właśnie z pomocą przychodzi nam indeksu GiST. Pozwala on zdefiniować regułę dystrybucji danych dowolnego typu w zrównoważonym drzewie oraz metodę wykorzystania tej reprezentacji w celu uzyskania dostępu przez jakiegoś operatora. Na przykład, indeks GiST może „zmieścić” drzewo R dla danych przestrzennych z obsługą operatorów pozycji względnej (umieszczonych po lewej, po prawej stronie, zawiera itp.) lub drzewo RD dla zbiorów z obsługą operatorów przecięcia lub włączenia.
Pełne omówienie struktury indeksu GIST znajdziecie na jednym z naszych szkoleń 🙂
Indeks GIST tworzymy w sposób następujący:
CREATE INDEX index_name ON table_name USING GIST(column);Indeks PostgreSQL – SP GiST
Indeks SP-GiST jest nazywany uogólnionym odwróconym drzewem wyszukiwania partycjonowanym w przestrzeni. Indeks taki obsługuje podzielone na partycje drzewo wyszukiwania. Indeks SP-GiST jest najbardziej przydatny w partycjonowanych środowiskach klastrowych.
Głównym celem GiST jest możliwość indeksowania zapytań, których po prostu nie można indeksować za pomocą Indeksów B-Tree. Tak więc GiST jest przydatny, gdy mamy do wykonania zapytania, które nie są indeksowalne w btree. Indeksowana kolumna może mieć typ tsvector lub tsquery.
Indeksy SP-GIST używane są między innymi w zapytaniach przestrzennych, n przez dodatek PostGIS.
Poniżej składnia i przykład indeksu SP-GiST:
CREATE INDEX index_name ON table_name USING SPGiST (column);Indeks PostgreSQL- BRIN
BRIN (Block Range Index) czyli indeks zakresu bloków jest dedykowany głównie bardzo dużym tabelom w których indeksujemy przede wszystkim kolumny mają naturalną korelację z ich fizyczną lokalizacją w tabeli.
Najprościej opisać jest zasadę funkcjonowania indeksu BRIN poprzez porównanie do B-Tree. Otóż Indeksy B-drzewa zawierają wpisy dla każdego wiersza w tabeli, powielając dane w indeksowanych kolumnach. Umożliwia to superszybkie skanowanie indeksu, ale może powodować marnowanie miejsca na dysku. Indeks B-drzewa przechowuje również dane (węzły leaf) w posortowanej kolejności, co pozwala na bardzo szybkie wyszukiwanie pojedynczych wierszy. Przechowuje również pełną lokalizację każdego wiersza w tabeli, co może wymagać dużo pracy podczas uruchamiania zapytań zwracających wiele wierszy.
Z kolei w indeksie BRIN PostgreSQL przechowuje maksymalne i minimalne wartości wybranej kolumny dla każdej strony przechowywanych danych o rozmiarze 8k, a następnie przechowuje te informacje (numer strony oraz minimalne i maksymalne wartości kolumny) w indeksie BRIN. Tak więc indeks BRIN zawiera tylko minimalne i maksymalne wartości kolumny w danej grupie stron bazy danych. Dlatego powodem używania indeksu BRIN jest to, że jest to bardzo prosty sposób na optymalizację objętości indeksu w porównaniu z indeksem B-drzewa.
Jak już wspomniano, BRIN jest przeznaczony do obsługi bardzo dużych tabel, w przypadku których prędkość wyszukiwania w oparciu o ten indeks jest większa niż w oparciu o indeks B-Tree. Warto przy tym pamiętać, iż dla bardzo dużych tabel indeks nie będzie praktyczny jeśli nie użyjemy partycjonowania horyzontalnego.
Poniżej składnia i przykład indeksu BRIN:
CREATE INDEX index_name ON table_name USING BRIN(column);Indeksy a Planner zapytań
Planner Postgresa może z pewnych względów nie korzystać z indexu. W większości wypadków, Planner wybierze właściwie czy korzystać z indexu czy też pozostać przy skanowaniu sekwencyjnym. Liczba wierszy pobranych z tabeli może się różnić w zależności od konkretnych wartości pobieranych przez zapytanie.
Indeksy w PostgreSQL
Indeksy częściowe
Indeks częściowy obejmuje tylko podzbiór danych tabeli. Jest to indeks z klauzulą WHERE. Ideą jest zwiększenie wydajności indeksu poprzez zmniejszenie jego rozmiaru. Mniejszy indeks zajmuje mniej miejsca, jest łatwiejszy w utrzymaniu i szybszy do skanowania.
Indeksy na wyrażeniach
Zwykle tworzy się indeks, który odwołuje się do jednej lub więcej kolumn tabeli. Możesz jednak również utworzyć indeks na podstawie wyrażenia, które obejmuje kolumny tabeli. Taki indeks nazywa się indeksem wyrażenia lub indeksem funkcjonalnym.
Indeksy na wyrażeniach są przydatne w przypadku zapytań, które pasują do pewnych funkcji lub modyfikacji danych. Postgres umożliwia indeksowanie wyniku takiej funkcji.
SELECT * FROM rezerwacja WHERE lower(col1) = ’value’;
przydatny może być indeks:
CREATE INDEX rezerwacja_lower_col1_idx
ON rezerwacja (lower(col1));Indeksy Unikalne
Unikalny indeks gwarantuje, że w tabeli nie będzie więcej niż jeden wiersz o tej samej wartości. Tworzenie unikalnych indeksów jest korzystne z dwóch powodów: integralności danych i wydajności. Wyszukiwania na unikalnym indeksie są na ogół bardzo szybkie.
Indeks ten wymusza unikalność wartości w jednej lub wielu kolumnach.
Po zdefiniowaniu unikalnego indeksu dla kolumny, kolumna nie może przechowywać wielu wierszy o tych samych wartościach. Jeśli zdefiniujesz indeks unikalny dla dwóch lub więcej kolumn, połączone wartości w tych kolumnach nie mogą być duplikowane w wielu wierszach. PostgreSQL nie traktuje wartości NULL jako wartości unikalnej, dlatego można mieć wiele wartości NULL w kolumnie o unikalnym indeksie.
Przykład utworzenia Indeksu Unikalnego:
CREATE UNIQUE INDEX index_name
ON table_name(column_name, [...]);Indeksy wielokolumnowe
Możesz utworzyć indeks na więcej niż jednej kolumnie tabeli. Taki Indeks nazywamy indeksem wielokolumnowym, indeksem złożonym, indeksem połączonym.
Domyślnie indeks wielokolumnowy może mieć maksymalnie 32 kolumny w tabeli, można to jednak zmienić. Należy również pamiętać, że tylko typy indeksów B-Tree, GIST, GIN i BRIN obsługują indeksy wielokolumnowe.
Przykład utworzenia indeksu wielokolumnowego:
CREATE INDEX index_name
ON table_name(a,b,c,...);Po więcej informacji zapraszamy na szkolenia!!!
