Indeks GIN w PostgreSQL -

W PostgreSQL istnieje kilka rodzajów indeksów (m.in. Indeks GIN), które można używać do optymalizacji zapytań i przyspieszenia operacji na bazie danych. Oto kilka najważniejszych rodzajów indeksów w PostgreSQL:

B-tree Index: Jest to domyślny rodzaj indeksu w PostgreSQL. B-tree indeks jest stosowany do sortowania i porównywania danych, co pomaga przy zapytaniach z wyrażeniami WHERE i operacjach sortowania.
Hash Index: Hash indeksy są używane do przyspieszania zapytań, w których używany jest operator równości (=). Są szybkie, ale nie nadają się do zakresowych zapytań.
GiST (Generalized Search Tree) Index: Ten rodzaj indeksu jest stosowany, gdy potrzebujesz przeszukiwać dane w bardziej zaawansowany sposób, na przykład do wyszukiwania w danych GIS (Geographic Information System) lub w trakcie przeszukiwania tekstów.
GIN (Generalized Inverted Index) Index: GIN indeksy są używane do przyspieszenia zapytań pełnotekstowych, gdzie używane są operatory przeszukiwania tekstu, takie jak LIKE lub ILIKE.
SP-GiST (Space-partitioned Generalized Search Tree) Index: Te indeksy są stosowane do specjalnych przypadków, takie jak przeszukiwanie danych przestrzennych, hierarchicznych lub innych niestandardowych struktur danych.
BRIN (Block Range INdex) Index: BRIN indeksy są stosowane do dużych tabel, w których dane można podzielić na bloki i utworzyć indeks na podstawie minimalnych i maksymalnych wartości w tych blokach. Pomaga to przyspieszyć zakresowe zapytania.
SP-GiST (Space-partitioned Generalized Search Tree) Index: Ten rodzaj indeksu jest używany do specjalnych przypadków, takich jak przeszukiwanie danych przestrzennych, hierarchicznych lub innych niestandardowych struktur danych.
Custom Index: PostgreSQL umożliwia tworzenie niestandardowych indeksów dostosowanych do konkretnych potrzeb aplikacji.

Wybór odpowiedniego rodzaju indeksu zależy od rodzaju zapytań, które są wykonywane na bazie danych i struktury danych. Warto zrozumieć charakterystykę każdego rodzaju indeksu i dostosować go do potrzeb konkretnej aplikacji w celu uzyskania najlepszej wydajności.

Indeks GIN

Indeks GIN (Generalized Inverted Index) w PostgreSQL jest używany do przyspieszania zapytań pełnotekstowych oraz innych rodzajów zapytań, które wykorzystują operatory przeszukiwania tekstu, takie jak LIKE lub ILIKE. Indeks GIN jest szczególnie przydatny do obsługi zapytań, które dotyczą kolumn zawierających kolekcje, takie jak tablice lub zestawy (arrays i sets). Kilka kluczowych informacji o indeksach GIN:

Indeksy GIN są efektywne w przeszukiwaniu tekstu, a także innych rodzajów danych, które można przekształcić na listy tokenów lub elementów. Są stosowane do wykonywania zapytań przy użyciu operacji przeszukiwania, jak np. @@, które umożliwiają odnajdywanie pasujących fragmentów tekstu w kolumnach.
Indeksy GIN są często stosowane do kolumn zawierających tablice lub zestawy danych. Pozwalają na efektywne wyszukiwanie wartości w tych strukturach danych, co jest przydatne w przypadku wielu rodzajów aplikacji, takich jak systemy rekomendacji lub analizy treści.
Partial GIN Index – PostgreSQL obsługuje tzw. „częściowe” indeksy GIN, które pozwalają na indeksowanie tylko części zawartości kolumny. Dzięki temu można skoncentrować się na interesujących danych i ograniczyć rozmiar indeksu.
W przypadku przeszukiwania tekstu w PostgreSQL, można również używać trigramów w połączeniu z indeksem GIN. Trigramy to trzy kolejne litery w wyrazach lub fragmentach tekstu. Tworzenie trigramów i indeksowania ich za pomocą GIN może przyspieszyć przeszukiwanie tekstu w bazie danych.
Optymalizacja wydajności: GIN indeksy są projektowane w taki sposób, aby dostarczać szybkie wyniki zapytań, zwłaszcza w przypadku dużych zbiorów danych.

Indeksy GIN są przydatne w przypadku zaawansowanych potrzeb przeszukiwania tekstu i pracy z kolumnami zawierającymi kolekcje danych. Przy projektowaniu i optymalizacji baz danych warto rozważyć ich użycie, szczególnie jeśli aplikacja musi wykonywać wiele zapytań na takie dane.

Budowa indeksu GIN

Index GIN (Generalized Inverted Index) w PostgreSQL jest budowany w sposób specyficzny dla rodzaju danych, które indeksujemy. Ogólnie rzecz biorąc, indeks GIN jest budowany na podstawie tokenów lub elementów w kolumnach, co umożliwia przyspieszenie zapytań przeszukiwania tekstu lub zapytań w kolumnach zawierających kolekcje, takie jak tablice lub zestawy. Oólny proces budowy indeksu GIN wyglada następująco:

Tokenizacja lub dekompozycja: Pierwszym krokiem w budowie indeksu GIN jest tokenizacja lub dekompozycja danych w kolumnie. W przypadku przeszukiwania tekstu, tekst jest dzielony na tokeny, czyli pojedyncze słowa lub frazy. W przypadku kolumn zawierających tablice lub zestawy, elementy są wyodrębniane.
Sortowanie i grupowanie: Tokeny lub elementy są sortowane i grupowane. To pozwala na efektywne przechowywanie i odnajdywanie pasujących elementów w indeksie.
Tworzenie struktury drzewa: GIN indeks jest zazwyczaj zbudowany w oparciu o strukturę drzewa, w której każdy węzeł reprezentuje pewne grupowanie tokenów lub elementów. Dzięki tej strukturze indeks może być szybko przeszukiwany.
Tworzenie list odwrotnej: Dla każdego tokena lub elementu tworzona jest lista odwrotna, w której zawarte są wskaźniki do rekordów w tabeli, w których ten token lub element występuje.
Przechowywanie list odwrotnych w indeksie: Listy odwrotne są przechowywane w indeksie GIN w odpowiednich węzłach drzewa. Dzięki temu, podczas przeszukiwania, baza danych może skoncentrować się tylko na interesujących listach odwrotnych, co przyspiesza dostęp do danych.
Optymalizacje: PostgreSQL wprowadza różne optymalizacje, takie jak kompresja list odwrotnych, aby zmniejszyć rozmiar indeksu i zwiększyć wydajność.

Wewnętrznie indeks GIN zawiera indeks drzewa B zbudowany na kluczach, gdzie każdy klucz jest elementem jednego lub większej liczby indeksowanych elementów (na przykład elementu tablicy) i gdzie każda krotka na stronie zawiera wskaźnik do:

B drzewa wskaźników sterty („posting tree”, tj. „drzewo wysyłania”) lub
prostą listę wskaźników sterty („posting list” tj.„lista wysyłania”), gdy lista jest wystarczająco mała, aby zmieścić się w pojedynczej krotce indeksu wraz z wartością klucza.

Wielokolumnowe indeksy GIN są implementowane poprzez zbudowanie pojedynczego B-drzewa na podstawie wartości złożonych (numer kolumny, wartość klucza). Wartości kluczy dla różnych kolumn mogą być różnych typów.

*Rys. Struktura indeksu GIN (Żródło: www.postgresql.org)*

Proces budowy indeksu GIN (Generalized Inverted Index) jest zazwyczaj automatycznie obsługiwany przez PostgreSQL, wtedy gdy określimy odpowiednie kolumny do zaindeksowania. Musimy pamietać, że budowa i utrzymanie indeksu GIN może być kosztowne pod względem zasobów, dlatego należy rozważyć jego użycie i tylko wtedy, gdy jest to konieczne do zoptymalizowania konkretnych zapytań w bazie danych.Aby zdefiniować indeks GIN (Generalized Inverted Index) w PostgreSQL, musimy użyć odpowiedniej składni SQL w instrukcji CREATE INDEX. Poniżej ogólny sposób definiowania indeksu GIN:

sql
Copy code
CREATE INDEX nazwa_indeksu
ON nazwa_tabeli
USING GIN (nazwa_kolumny);

Gdzie:

nazwa_indeksu to nazwa, którą chcesz przypisać indeksowi.
nazwa_tabeli to nazwa tabeli, na której chcesz utworzyć indeks.
nazwa_kolumny to nazwa kolumny, którą chcesz zaindeksować.

Przykład 1:

Definiowanie indeksu GIN na kolumnie description w tabeli films:

sql
Copy code
CREATE INDEX gin_index
ON films
USING GIN (description);

Po utworzeniu takiego indeksu, PostgreSQL będzie w stanie efektywnie obsługiwać zapytania przeszukiwania tekstu lub zapytania dotyczące kolumny description w tabeli films.

Warto również dodać, że PostgreSQL obsługuje różne opcje konfiguracyjne dla indeksów GINk. Konfiguracje te pozwalają na dostosowanie działania indeksu do konkretnych potrzeb aplikacji. Na przykład, można zmieniać sposób tokenizacji tekstu lub konfigurować optymalizacje kompresji indeksu. Opcje te można uwzględnić w definicji indeksu.

GIN a FTS w PostgreSQL

Jak już wspomniałem PostgreSQL wykorzystuje indeks GIN do przyspieszenia operacji wyszukiwania pełnotekstowego. Indeks ten jest budowany na podstawie wektorów tekstu i pozwala na efektywne znajdowanie dokumentów zawierających konkretne słowa, frazy lub faktycznie tokeny. Indeks GIN jest stosowany w kolumnach zawierających tekst do przyspieszenia operacji @@ (dopasowania tekstu) i innych operacji wyszukiwania pełnotekstowego.PostgreSQL dostarcza wiele wbudowanych operatorów, które umożliwiają przeprowadzanie różnych rodzajów operacji pełnotekstowego wyszukiwania, takich jak:

@@: Operacja dopasowania tekstu, która znajduje dokładne dopasowania w tekście.
&: Operacja logicznego AND w zapytaniach pełnotekstowych.
|: Operacja logicznego OR w zapytaniach pełnotekstowych.
!: Operator negacji, który wyklucza wyniki zawierające określone słowo.

Korzystając z w/w operatorów możemy używać klauzul WHERE zawierających wyrażenia pełnotekstowe w zapytaniach SQL w celu przeszukiwania tekstu w bazie danych. Na przykład, można użyć klauzuli WHERE z operatorem @@ do odnalezienia pasujących dokumentów.PostgreSQL oferuje zaawansowane mechanizmy tokenizacji i stemmingu, co pozwala na wydajne przeszukiwanie tekstu w różnych językach. W technologii FTS tokenizacja polega na dzieleniu tekstu na tokeny (słowa lub frazy), a stemming na redukcji słów do ich formy podstawowej. PostgreSQL obsługuje różne języki i konfiguracje dla tych operacji.Dwa kluczowe elementy związane z obsługą tekstu i pełnotekstowym wyszukiwaniem w PostgreSQL to tsquery i to_tsvector. Mają one związek z indeksem GIN. Oto ich podstawowe znaczenia i związek z indeksem GIN:

tsquery

tsquery jest to specjalny typ danych w PostgreSQL, który reprezentuje zapytanie pełnotekstowe. Pozwala na definiowanie zapytań, które wyszukują konkretne frazy lub słowa w tekście. Przykładowo, tsquery może być używane do określenia, że chcemy znaleźć wszystkie dokumenty zawierające zarówno „kot” i „pies”. Przykład tsquery może wyglądać tak: 'kot & pie’. To zapytanie można przekazać jako argument w celu wyszukiwania w kolumnach zawierających dane tekstowe.

Zapytanie tsquery jest definiowane jako łańcuch znaków zawierający operatory i operandy. Operatory to znaki takie jak & (AND), | (OR), ! (NOT), służące do łączenia operacji na operandach. Operandy to konkretne słowa, frazy lub tokeny, które chcemy znaleźć w tekście.

Z tsquery możemy używać operatorów logicznych, aby łączyć operandy w zapytaniach. Na przykład, & oznacza operację AND, | oznacza operację OR, a ! oznacza operację NOT. Możesz również używać nawiasów w celu kontrolowania kolejności operacji.

Przykłady zapytań tsquery:

’kot & pies’ – Znajdź dokumenty zawierające zarówno słowo „kot”, jak i „pies”.’kot | pies’ – Znajdź dokumenty zawierające „kot” lub „pies”.’kot & !pies’ – Znajdź dokumenty zawierające „kot”, ale nie „pies”.’kot & (pies | mysz)’ – Znajdź dokumenty zawierające „kot” i jednocześnie „pies” lub „mysz”.

Po zdefiniowaniu zapytania tsquery, można użyć go w zapytaniach SQL w klauzuli WHERE, aby przeszukać kolumny tekstowe w bazie danych. PostgreSQL użyje takiego zapytania, aby znaleźć pasujące dokumenty w bazie.

to_tsvector

to_tsvector to funkcja w PostgreSQL, która służy do konwertowania tekstu na postać wektorową, która jest bardziej efektywna w przeszukiwaniu tekstu. Funkcja to_tsvector jest często wykorzystywana w połączeniu z indeksem GIN (Generalized Inverted Index), aby umożliwić efektywne przeszukiwanie tekstu w bazie danych. Funkcja to_tsvector konwertuje tekst na strukturę danych, która może być indeksowana i przeszukiwana w bardziej wydajny sposób.Działanie funkcji to_tsvector:

Tokenizacja tekstu: Pierwszym krokiem w działaniu to_tsvector jest tokenizacja tekstu. Tokenizacja polega na podziale tekstu na pojedyncze tokeny, czyli słowa, frazy lub fragmenty tekstu. Na przykład, zdanie „Szybki pies biegnie” może zostać podzielone na tokeny: „Szybki”, „pies”, „biegnie”.
Przekształcanie tokenów: Następnie, tokeny są przekształcane. W przypadku języków naturalnych, przekształcanie może obejmować operacje takie jak redukcja słów do formy podstawowej (stemming), usuwanie znaków diakrytycznych, zmiana na małe litery itp. Przekształcone tokeny tworzą wektor tekstu.
Tworzenie wektora tekstu: Po przekształceniu tokenów, funkcja to_tsvector tworzy wektor tekstu. Wektor ten zawiera informacje o tokenach, ich pozycji i częstotliwości występowania w tekście. Ten wektor jest bardziej efektywny do przeszukiwania tekstu niż sam tekst w postaci surowego łańcucha znaków.
Przechowywanie w indeksie GIN: W przypadku przeszukiwania tekstu, stworzony wektor tekstu jest przechowywany w indeksie GIN. Indeks GIN pozwala na efektywne przeszukiwanie i odnajdywanie dokumentów zawierających pasujący tekst.

Działanie to_tsvector pozwala na wydajne przeszukiwanie tekstu w bazie danych, ponieważ zamiast przeszukiwać surowy tekst, bazuje na wektorach tekstu, które są odpowiednio przekształcone i zoptymalizowane pod kątem przeszukiwania. W połączeniu z tsquery i indeksem GIN, umożliwia to skomplikowane operacje pełnotekstowego wyszukiwania w bazie PostgreSQL.W skrócie mozna powiedzieć że, to_tsvector konwertuje tekst na formę, którą można zindeksować, a tsquery jest używane do definiowania zapytań w celu odnalezienia pasujących wyników w tekście. Obie te funkcje są ściśle związane z indeksem GIN, który służy do optymalizacji przeszukiwania tekstowego w PostgreSQL.

Zastosowanie GIN do przeszukiwania typów wektorowych

W PostgreSQL, indeks GIN (Generalized Inverted Index) jest często stosowany w kontekście kolumn zawierających tablice (arrays). Umożliwia to efektywne przeszukiwanie danych wewnątrz tych tablic. Aby używać indeksu GIN z tablicami, wykonujemy następujące kroki:

Przed użyciem indeksu GIN z tablicami, należy go utworzyć. Możemy to zrobić, korzystając z instrukcji CREATE INDEX i wskazując odpowiednią kolumnę zawierającą tablice. Ogólny format:

sql
Copy code
CREATE INDEX nazwa_indeksu
ON nazwa_tabeli
USING GIN (nazwa_kolumny);

Gdzie:

nazwa_indeksu to nazwa indeksu.
nazwa_tabeli to nazwa tabeli, w której znajduje się kolumna z tablicami.
nazwa_kolumny to nazwa kolumny z tablicami, którą chcesz zaindeksować.

Przykład 1.

Tworzenie indeksu GIN na kolumnie tags w tabeli posts:

sql
Copy code
CREATE INDEX nazwa_indeksu
ON nazwa_tabeli
USING GIN (nazwa_kolumny);

Po utworzeniu indeksu GIN możemy przeszukiwać tablice w kolumnie z użyciem zapytań SQL.

Przykład 2:

Zapytanie, które znajduje wszystkie wpisy, które zawierają tagi „kot”:

sql
Copy code
SELECT * FROM wiadomosci
WHERE tags @> ARRAY['kot'];

Operator @> jest używany do porównywania tablic. Działa z indeksem GIN, co pozwala na efektywne odnajdywanie pasujących wpisów.

PostgreSQL automatycznie korzysta z indeksu GIN w przypadku zapytań, które korzystają z operatorów tablicowych, takich jak @> czy &&. W zapytaniach możemy również użyć innych operacji tablicowych, takich jak = ANY lub = ALL.

W niektórych przypadkach należy rozważyć użycie częściowego indeksu GIN, który zawiera tylko wybrane elementy z tablic. To pozwoli zmniejszyć rozmiar indeksu i zoptymalizować przeszukiwanie w konkretnych przypadkach.Indeks GIN z tablicami jest szczególnie przydatny w sytuacjach, gdzie wystepują kolumny zawierające zestawy danych, takie jak tagi, kategorie, lista identyfikatorów itp. Pozwala to na szybkie i efektywne przeszukiwanie danych wewnątrz tych tablic, co jest przydatne w wielu aplikacjach.

Własne indeksy GIN

Nie zawsze jednak typ danych na którym operujemy posiada gotowa obsługę, która pozwala w sensowny sposób użyć indeksu GIN. W PostgreSQL istnieje możliwość tworzenia własnych indeksów GIN (Generalized Inverted Index) dostosowanych do konkretnych potrzeb i typów danych.Tworzenie własnych indeksów GIN jest przydatne w przypadku, gdy bazowe mechanizmy PostgreSQL nie spełniają dokładnie wymagań Twojej aplikacji. Można wówczas tworzyć niestandardowe indeksy GIN, aby zoptymalizować przeszukiwanie danych w oparciu o swoje własne reguły i formaty danych. Proces tworzenia niestandardowego indeksu GIN:

Ustalenie formatu danych: Identyfikacja, jakie dane chcemy indeksować i w jakim formacie się one znajdują. Można przekształcić te dane na format, który będzie bardziej odpowiedni do przeszukiwania, np. na listę tokenów lub elementów.
Tworzenie własnej funkcji przekształcającej: Piszemy niestandardową funkcję w języku PL/pgSQL, PL/Python lub innym obsługiwanym przez PostgreSQL, która przekształca dane na odpowiedni format. Funkcja ta będzie wywoływana podczas tworzenia indeksu GIN.
Definicja indeksu GIN: Używamy instrukcji CREATE INDEX w SQL, aby zdefiniować własny indeks GIN. Wskazujemy nazwę funkcji przekształcającej jako część definicji indeksu. Przykładowa instrukcja tworzenia niestandardowego indeksu GIN mogłaby wyglądać tak:

sql
Copy code
CREATE INDEX nazwa_indeksu
ON nazwa_tabeli
USING GIN (nazwa_kolumny gin_przekształcająca_funkcja);

Utworzenie indeksu: Po zdefiniowaniu indeksu możemy go utworzyć, wykonując odpowiednią instrukcję SQL.
Użycie niestandardowego indeksu: Następnie możesz używać swojego niestandardowego indeksu GIN do przyspieszania zapytań na danych, które zostały przekształcone przez Twoją funkcję.

Warto pamiętać, że tworzenie niestandardowych indeksów GIN wymaga zaawansowanej wiedzy na temat PostgreSQL i dobrej znajomości struktury danych, które chcemy indeksować. Tworzenie własnych indeksów GIN może znacząco wpłynąć na wydajność przeszukiwania danych w Twojej bazie, ale wymaga ostrożności i testowania, aby upewnić się, że spełniają one Twoje oczekiwania.

Po więcej informacji zapraszamy na szkolenia.

Dokumentacja PostgreSQL