Indeksowanie danych JSONB: użycie indeksów GIN i BTREE

Indeksowanie w PostgreSQL to sposób na szybkie wyszukiwanie danych w bazie. Gdyby dane w twojej tabeli były książkami, to indeksowanie to taki katalog w bibliotece, który pozwala szybko znaleźć potrzebną książkę po tytule albo autorze. Z JSONB jest trochę bardziej pod górkę, bo dane są przechowywane w strukturze, a nie w zwykłych wierszach i kolumnach.

Kiedy dane JSONB zaczynają puchnąć do rozmiarów "książki o Harrym Potterze, tylko bez obrazków", szukanie w tej strukturze może być powolne. Na przykład, jeśli chcesz znaleźć wszystkie zamówienia, gdzie konkretny klucz "status" ma wartość "delivered", PostgreSQL musi przejść przez wszystkie rekordy, żeby to sprawdzić. Brzmi jak robota, której nie chciałbyś robić ręcznie, prawda?

No i tu na scenę wchodzą indeksy GIN i BTREE — nasi bohaterowie, którzy ratują nas przed długim czekaniem!

Typy indeksów dla JSONB

GIN (Generalized Inverted Index)

Indeks GIN został stworzony specjalnie do pracy ze strukturami, takimi jak tablice i obiekty, więc jest idealny dla JSONB. Pozwala indeksować nie cały obiekt, ale poszczególne klucze i wartości w środku. To znaczy, że dzięki GIN możesz szybko znaleźć rekordy zawierające konkretne klucze, wartości albo ich kombinacje.

Wyobraź sobie kolumnę JSONB z danymi:

{"name": "Alice", "age": 25, "city": "Berlin"}

Indeks GIN tworzy wewnętrzną strukturę, gdzie klucze "name", "age" i "city" są powiązane z ich wartościami. Więc kiedy szukasz "name": "Alice", PostgreSQL już wie, gdzie tego szukać — nie przeszukuje całej tabeli.

BTREE

Indeks BTREE to bardziej klasyczne podejście. Tworzy uporządkowaną strukturę, która pozwala szybko znaleźć dane po konkretnych wartościach. W przypadku JSONB BTREE możesz użyć, jeśli szukasz dokładnego dopasowania danych albo masz stały klucz (np. chcesz porównać cały obiekt JSONB).

Jeśli twoja kolumna zawiera obiekty JSONB, takie jak:

{"name": "Bob", "age": 30}

Indeks BTREE może się przydać, jeśli szukasz rekordów, gdzie cały obiekt jest dokładnie taki sam.

{"name": "Bob", "age": 30}

Tworzenie indeksu dla JSONB

Najpierw zobaczmy, jak stworzyć indeks GIN. Wszystko, czego potrzebujesz, to magiczna komenda CREATE INDEX. Tak to wygląda:

-- Tworzymy indeks GIN dla kolumny JSONB
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

Gdzie:

• idx_jsonb_data — nazwa indeksu.
• orders — nazwa tabeli.
• data — kolumna z danymi JSONB.

Po utworzeniu tego indeksu zapytania, które szukają kluczy lub wartości w JSONB, będą działać szybciej.

Załóżmy, że mamy tabelę orders z kolumną data, która zawiera JSONB:

Zapytanie bez indeksu:

-- Znajdź wszystkie zamówienia ze statusem "delivered"
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

Jeśli tabela jest duża, to zapytanie może trwać długo. Ale z indeksem GIN pójdzie znacznie szybciej.

Jak stworzyć indeks BTREE

Żeby stworzyć indeks BTREE, musisz trochę zmienić podejście. W większości przypadków, żeby użyć BTREE z JSONB, trzeba wskazać, że chcesz indeksować nie cały obiekt, ale jego część. Przykład:

-- Tworzymy indeks BTREE dla konkretnego klucza
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

Zwróć uwagę na (data->>'total'). To wyciąga wartość klucza total z obiektu JSONB i właśnie ta wartość jest indeksowana. Teraz, jeśli szukasz zamówień, gdzie total = 100, PostgreSQL użyje tego indeksu.

Przykład użycia na tych samych danych:

Zapytanie:

-- Znajdź wszystkie zamówienia, gdzie total = 100
SELECT * FROM orders WHERE data->>'total' = '100';

Z indeksem BTREE dla data->>'total', to zapytanie będzie działać dużo szybciej.

Porównanie GIN i BTREE

Jeśli masz złożone struktury JSONB i często używasz operatorów takich jak @> albo ?, wybierz GIN. Jeśli szukasz konkretnych wartości lub kluczy, które są stałe, BTREE może być lepszym wyborem.

Pułapki i typowe błędy przy indeksowaniu JSONB

Praca z indeksowaniem JSONB może być potężna, ale jest kilka pułapek, o których warto pamiętać.

1. Brak indeksu tam, gdzie jest potrzebny. Jeśli często używasz danych JSONB w filtrach (WHERE), ale nie stworzyłeś indeksu, zapytania będą wolne.
2. Nadmierne indeksowanie. Jeśli tworzysz indeksy dla każdego możliwego klucza JSONB, to może spowolnić inserty i aktualizacje.
3. Zły wybór typu indeksu. Jeśli twoje zapytania są złożone i używają operatorów jak @> albo ?, ale stworzyłeś indeks BTREE, nie zobaczysz poprawy wydajności.
4. Brak wiedzy o ścieżkach. Jeśli ciągle odwołujesz się do zagnieżdżonych wartości, ale nie stworzyłeś indeksu dla konkretnej ścieżki (np. data->>'some_key'), twoje zapytanie i tak będzie wolne.

Podsumowanie: kiedy używać którego indeksu

• Używaj GIN, jeśli masz tablice albo złożone obiekty, gdzie często szukasz po kluczach i wartościach.
• Używaj BTREE, jeśli szukasz dokładnego dopasowania albo często odwołujesz się do konkretnych kluczy.