Indeksowanie tablic: tworzenie indeksów GIN i BTREE

Wyobraź sobie, że masz tabelę z milionami rekordów, a jedna z kolumn przechowuje tablice. Na przykład mamy tabelę products i każdy produkt może należeć do kilku kategorii:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Tablica stringów do przechowywania kategorii produktu
);

Załóżmy, że chcesz znaleźć wszystkie produkty należące do kategorii electronics. Proste użycie operatora @> do wyszukiwania może skutkować pełnym skanowaniem całej tabeli:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Pełne skanowanie (Seq Scan) — to wolne. Zwłaszcza jeśli tabela jest ogromna. Indeksy ratują sytuację, zamieniając to na szybsze wyszukiwanie.

Typy indeksów dla tablic

PostgreSQL wspiera dwa główne typy indeksów, które możesz użyć dla tablic:

1. GIN (Generalized Inverted Index) — idealny do szybkiego wyszukiwania elementów w tablicy albo sprawdzania przecięć.
2. BTREE (Binary Tree) — dobry do innych operacji, np. do dokładnego porównywania tablic.

Przyjrzyjmy się każdemu z nich bliżej.

1. Indeks GIN: prędkość światła

GIN (Generalized Inverted Index) — to indeks, który świetnie sprawdza się przy operatorach takich jak:

• @> (tablica zawiera element lub inną tablicę),
• <@ (tablica jest zawarta w innej tablicy),
• && (tablice mają wspólne elementy).

Tak możesz stworzyć indeks GIN dla naszej kolumny categories:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Po utworzeniu indeksu zapytania będą działać zauważalnie szybciej. Na przykład zapytanie:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

będzie korzystać z twojego indeksu GIN.

Fun fact: Indeks GIN działa na zasadzie odwróconej listy — przechowuje, które elementy (np. stringi) są w których rekordach. To trochę jak indeks w książce, gdzie możesz znaleźć temat po numerze strony. Fajne, nie?

2. Indeks BTREE: kiedy liczy się kolejność

BTREE (Binary Tree) — to standardowy indeks używany w większości baz danych. Nadaje się do operacji wymagających dokładnego porównania tablic, np.:

• Sprawdzanie równości tablicy =,
• Porównywanie tablic według kolejności elementów (>, <).

Stworzenie indeksu BTREE dla tablicy wygląda tak:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Przykład zapytania, które może użyć indeksu BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Pamiętaj jednak, że indeksy BTREE nie nadają się do operatorów typu @> czy <@. Do nich lepiej użyć GIN.

Przykłady użycia indeksów

Teraz połączmy teorię z praktyką i ogarnijmy kilka przykładów.

1. Wyszukiwanie przecięcia tablic

Załóżmy, że chcesz znaleźć wszystkie produkty powiązane z kategoriami electronics i smartphones, używając operatora && (przecięcie tablic):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Do tego idealnie nada się indeks GIN, który już wcześniej stworzyłeś:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Z takim indeksem zapytanie wykona się dużo szybciej dzięki odwróconej liście.

2. Porównanie tablic na równość

Jeśli musisz znaleźć produkty, które należą tylko do kategorii electronics i gadgets (w tej kolejności), to tutaj lepiej sprawdzi się indeks BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Stwórz odpowiedni indeks:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Wydajność indeksów

Indeksy przyspieszają zapytania, ale mają też swoje minusy. Na przykład:

• Tworzenie indeksu zajmuje czas i zasoby. Jeśli masz bardzo dużą tabelę, budowanie indeksu może trochę potrwać.
• Aktualizacja tabeli. Za każdym razem, gdy dodajesz nowe rekordy lub aktualizujesz dane, indeksy też się aktualizują. To może spowolnić operacje INSERT i UPDATE.

Jednak w większości przypadków zysk z szybkich zapytań jest większy niż te koszty.

Jak wybrać: GIN czy BTREE?

Oto mała tabelka, która pomoże ci wybrać odpowiedni indeks do zadania: