Indeksy B-tree vs GIN vs GiST: praktyczny przewodnik po PostgreSQL

Co naprawdę wybierasz, gdy wybierasz indeks

Większość problemów z indeksami w PostgreSQL zaczyna się tak samo: widok listy jest szybki przy 1 000 wierszy, a potem zwalnia przy 1 000 000. Albo pole wyszukiwania, które działało w testach, zamienia się w sekundową pauzę w produkcji. Gdy tak się dzieje, kuszące jest pytanie „Który indeks jest najlepszy?”. Lepsze pytanie brzmi: „Co ten ekran każe bazie danych robić?”

Ta sama tabela może potrzebować różnych typów indeksów, bo różne ekrany czytają ją w różny sposób. Jeden widok filtruje po pojedynczym statusie i sortuje po created_at. Inny robi pełnotekstowe wyszukiwanie. Jeszcze inny sprawdza, czy pole JSON zawiera określony klucz. Kolejny znajduje elementy blisko punktu na mapie. To różne wzorce dostępu, więc jeden typ indeksu nie wygra wszędzie.

To właśnie wybierasz, decydując się na indeks: jak aplikacja uzyskuje dostęp do danych. Czy głównie robisz dokładne dopasowania, zakresy i sortowanie? Czy przeszukujesz dokumenty lub tablice? Czy pytasz „co jest blisko tej lokalizacji” czy „co pokrywa się z tym zakresem”? Odpowiedź wskazuje, czy B-tree, GIN, czy GiST będzie najlepiej pasować.

B-tree, GIN i GiST prostym językiem

Wybór indeksu to mniej kwestia typu kolumny, a bardziej tego, co twoje zapytania z tą kolumną robią. PostgreSQL wybiera indeksy na podstawie operatorów takich jak =, <, @> czy @@, a nie na podstawie tego, czy kolumna jest „text” czy „json”. Dlatego to samo pole może wymagać różnych indeksów na różnych ekranach.

B-tree: szybki przy uporządkowanych wyszukiwaniach

B-tree to domyślny i najczęstszy wybór. Sprawdza się, gdy filtrujesz po dokładnej wartości, po zakresie lub potrzebujesz wyników w określonym porządku.

Typowy przykład to panel administracyjny filtrowany po statusie i sortowany po created_at. Indeks B-tree na (status, created_at) może pomóc zarówno filtrze, jak i sortowaniu. B-tree to też standard do unikalności (unique constraints).

GIN: szybki, gdy każdy wiersz zawiera wiele przeszukiwalnych kluczy

GIN jest zaprojektowany do pytań „czy ten wiersz zawiera ten termin/wartość?”, gdzie jeden wiersz może odpowiadać wielu kluczom. Typowe przykłady to pełnotekstowe wyszukiwanie (dokument zawiera słowa) oraz zawartość JSONB/tablic (JSON zawiera klucz/wartość).

Wyobraź sobie rekord klienta z obiektem JSONB preferences i ekran, który filtruje użytkowników, gdzie preferences zawiera { "newsletter": true }. To zapytanie typowe dla GIN.

GiST: elastyczny dla zakresów, geo i podobieństwa

GiST to ogólne ramy używane przez typy danych, które nie pasują do prostego porządku. Naturalnie pasuje do zakresów (nakładanie się, zawieranie), zapytań geometrycznych i geograficznych (blisko, w obrębie) oraz niektórych wyszukiwań po podobieństwie.

Gdy decydujesz między B-tree, GIN i GiST, zacznij od zapisania operatorów, których używają twoje najbardziej ruchliwe ekrany. Po tym właściwy indeks zwykle staje się jasny.

Tabela decyzyjna dla typowych ekranów (filtry, wyszukiwanie, JSON, geo)

Większość aplikacji potrzebuje tylko kilku wzorców indeksów. Sztuka polega na dopasowaniu zachowania ekranu do operatorów, których używają twoje zapytania.

Dwa indeksy mogą współistnieć, gdy końpoinsty różnią się. Na przykład lista administracyjna może potrzebować B-tree na (tenant_id, created_at) dla szybkiego sortowania, podczas gdy strona wyszukiwania potrzebuje GIN do @@. Zatrzymaj oba tylko wtedy, gdy oba wzorce zapytań są powszechne.

Jeśli nie jesteś pewien, patrz najpierw na operator. Indeksy pomagają, gdy baza może pominąć dużą część tabeli.

Filtry i sortowanie: tu B-tree zwykle wygrywa

Dla większości codziennych ekranów B-tree to nudny wybór, który działa. Jeśli twoje zapytanie wygląda jak „wybierz wiersze, gdzie kolumna równa się wartości, może posortuj je, potem pokaż pierwszą stronę”, B-tree zwykle jest pierwszą rzeczą do wypróbowania.

Filtry równości to klasyczny przypadek. Kolumny jak status, user_id, account_id, type czy tenant_id pojawiają się non-stop w dashboardach i panelach admina. Indeks B-tree może przeskoczyć prosto do pasujących wartości.

Filtry zakresowe też dobrze pasują do B-tree. Gdy filtrujesz po czasie lub zakresach liczbowych, uporządkowana struktura pomaga: created_at >= ..., price BETWEEN ..., id > .... Jeśli UI ma „Ostatnie 7 dni” lub „50–100 zł”, B-tree robi dokładnie to, czego potrzebujesz.

Sortowanie i paginacja to miejsce, gdzie B-tree może oszczędzić najwięcej pracy. Gdy porządek indeksu odpowiada ORDER BY, PostgreSQL często zwróci wiersze już posortowane zamiast sortować dużą liczbę w pamięci.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Indeksy kompozytowe mają prostą regułę: PostgreSQL efektywnie używa tylko wiodącej części indeksu. Myśl „od lewej do prawej”. Z (user_id, status, created_at) zapytania filtrujące po user_id (i opcjonalnie status) skorzystają. Zapytanie filtrujące tylko po status zwykle nie skorzysta.

Indeksy częściowe to duża korzyść, gdy ekran interesuje się tylko fragmentem danych. Typowe fragmenty to „tylko aktywne wiersze”, „nie soft-deleted” lub „niedawna aktywność”. Powodują, że indeks jest mniejszy i szybszy.

Kolumny o wysokiej kardynalności i koszt dodatkowych indeksów

Kolumny o wysokiej kardynalności mają dużo unikalnych wartości, jak user_id, order_id, email czy created_at z dokładnością do sekundy. Indeksy zwykle tu błyszczą, bo filtr może szybko zawęzić wyniki do niewielkiego wycinka tabeli.

Kolumny niskiej kardynalności to przeciwieństwo: boole i małe enumy jak is_active, status IN ('open','closed') czy plan IN ('free','pro'). Indeks na takie kolumny często zawodzi, bo każda wartość pasuje do dużej części wierszy. PostgreSQL może wtedy wybrać sekwencyjne skanowanie, bo korzystanie z indeksu i tak wymaga czytania wielu stron tabeli.

Innym subtelnym kosztem jest pobieranie wierszy. Nawet jeśli indeks szybko znajduje identyfikatory, baza może wciąż musieć wejść do tabeli po pozostałe kolumny. Jeśli zapytanie potrzebuje tylko kilku pól, indeks pokrywający (covering index) może pomóc, ale też powiększa indeks i podnosi jego koszt utrzymania.

Każdy dodatkowy indeks ma cenę przy zapisie. Insert musi zapisać do każdego indeksu. Aktualizacje zmieniające indeksowane kolumny muszą zaktualizować również indeksy. Dodawanie indeksów „na wszelki wypadek” może spowolnić całą aplikację, nie tylko jeden ekran.

Praktyczne wskazówki:

• Zacznij od 1–2 głównych indeksów na ruchliwą tabelę, opartych na realnych filtrach i sortowaniach.
• Faworyzuj kolumny o wysokiej kardynalności używane w WHERE i ORDER BY.
• Ostrożnie indeksuj boole i małe enumy, chyba że łączą się z inną selektywną kolumną.
• Dodaj nowy indeks tylko wtedy, gdy potrafisz wskazać dokładne zapytanie, które on przyspieszy.

Przykład: lista ticketów filtrowana po assignee_id (wysoka kardynalność) zyska na indeksie, podczas gdy samo is_archived = false często nie.

Ekrany wyszukiwania: pełnotekstowe, prefiksy i „zawiera”

Pola wyszukiwania wyglądają prosto, ale użytkownicy oczekują wiele: wiele słów, różne formy słów i sensowne rankowanie. W PostgreSQL to zwykle pełnotekstowe wyszukiwanie: przechowujesz tsvector (przetworzony tekst) i pytasz tsquery (to, co wpisał użytkownik, rozbite na termy).

Dla pełnotekstowego wyszukiwania GIN to powszechny domyślny wybór, bo szybko odpowiada na pytanie „czy ten dokument zawiera te termy?” dla wielu wierszy. Kosztem są cięższe operacje zapisu: insert i update kosztują więcej.

GiST też może działać dla pełnotekstowego. Często jest mniejszy i tańszy przy aktualizacjach, ale zwykle wolniejszy przy odczytach niż GIN. Jeśli twoje dane zmieniają się bardzo często (np. tabele typu event), to balans odczyt/zapis ma znaczenie.

Prefiksowe wyszukiwanie to nie pełnotekst

Prefiksowe wyszukiwanie oznacza „zaczyna się od”, np. wyszukiwanie klientów po prefiksie emaila. To nie jest to, do czego służy pełnotekstowe wyszukiwanie. Dla wzorców prefiksowych B-tree może pomóc (często przy odpowiedniej klasie operatorów), bo pasuje do porządku łańcuchów.

Dla wyszukiwań „zawiera” jak ILIKE '%error%' B-tree zwykle nie pomoże. Wtedy przydaje się indeks trigramowy lub inna metoda wyszukiwania.

Gdy użytkownicy chcą filtrów plus wyszukiwania tekstowego

Większość realnych ekranów łączy wyszukiwanie z filtrami: status, assignee, zakres dat, tenant itd. Praktyczne ustawienie to:

• GIN (czasem GiST) na kolumnie tsvector.
• B-tree dla najbardziej selektywnych filtrów (np. account_id, status, created_at).
• Zasada „trzymaj minimalnie”, bo zbyt wiele indeksów spowalnia zapisy.

Przykład: ekran ticketów, który wyszukuje „refund delayed” i filtruje status = 'open' oraz konkretne account_id. Pełnotekstowe wyszukiwanie daje trafne wiersze, a B-tree pomaga szybko zawęzić do właściwego konta i statusu.

Pola JSONB: wybór między GIN a docelowymi B-tree

JSONB daje dużą elastyczność, ale może prowadzić do wolnych zapytań, jeśli traktujesz go jak zwykłą kolumnę. Główna decyzja jest prosta: czy szukasz „gdziekolwiek w tym JSON-ie”, czy filtrujesz po kilku konkretnych ścieżkach?

Dla zapytań containment jak metadata @> '{"plan":"pro"}' zwykle wybierzesz GIN. Jest zbudowany do pytania „czy ten dokument zawiera ten kształt?” i wspiera też sprawdzenia istnienia klucza, jak ?, ?| i ?&.

Jeśli aplikacja głównie filtruje po jednej lub dwóch ścieżkach JSON, docelowy indeks wyrażeniowy B-tree bywa szybszy i mniejszy. Pomaga też, gdy potrzebujesz sortowania lub porównań numerycznych na wyciągniętych wartościach.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Dobre reguły:

• Użyj GIN, gdy użytkownicy przeszukują wiele kluczy, tagów lub zagnieżdżonych struktur.
• Użyj B-tree na wyrażeniu, gdy użytkownicy często filtrują po konkretnych ścieżkach.
• Indeksuj to, co pojawia się na realnych ekranach, nie wszystko.
• Jeśli wydajność zależy od kilku kluczy JSON, rozważ promowanie ich do normalnych kolumn.

Przykład: ekran supportu może filtrować tickety po metadata->>'priority' i sortować po created_at. Zindeksuj ścieżkę JSON priority i zwykłą kolumnę created_at. Pomiń szeroki GIN, chyba że użytkownicy też przeszukują tagi lub zagnieżdżone atrybuty.

Zapytania geo i zakresowe: tu GiST bywa najlepszy

Ekrany geo i zakresowe to miejsce, gdzie GiST często staje się oczywistym wyborem. GiST jest zaprojektowany do pytania „czy to się nakłada, zawiera lub jest blisko czegoś?”, a nie „czy ta wartość równa się tamtej?”.

Dane geo zwykle to punkty (lokalizacja sklepu), linie (trasa) lub poligony (obszar dostawy). Typowe ekrany to „sklepy blisko mnie”, „prace w promieniu 10 km”, „pokaż elementy wewnątrz tego obszaru mapy” lub „czy ten adres jest w naszym obszarze obsługi?”. Indeks GiST (najczęściej przez PostGIS geometry/geography) przyspiesza operatory przestrzenne, dzięki czemu baza może pominąć większość wierszy zamiast sprawdzać każdy kształt.

Zakresy są podobne. PostgreSQL ma typy range jak daterange czy int4range, a typowe pytanie to nakładanie się: „czy to rezerwacja koliduje z istniejącą?” lub „pokaż subskrypcje aktywne w tym tygodniu”. GiST efektywnie wspiera operatory nakładania i zawierania, dlatego jest powszechny w kalendarzach i systemach dostępności.

B-tree nadal ma znaczenie na ekranach geo: wiele stron najpierw filtruje po tenant, status lub czasie, potem stosuje warunek przestrzenny i sortuje. GiST obsłuży część przestrzenną, ale B-tree pomoże przy selektywnych filtrach i sortowaniu.

Jak wybierać indeks krok po kroku

Wybór indeksu to głównie kwestia operatora, a nie nazwy kolumny. Ta sama kolumna może wymagać różnych indeksów w zależności od tego, czy używasz =, >, LIKE 'prefix%', pełnotekstowego, containment JSON, czy odległości geo.

Przeczytaj zapytanie jak listę kontrolną: WHERE decyduje, które wiersze kwalifikują się, JOIN jak tabele się łączą, ORDER BY decyduje o porządku wyników, a LIMIT ile wierszy naprawdę potrzebujesz. Najlepszy indeks to często ten, który pomaga znaleźć pierwsze 20 wierszy szybko.

Prosty proces, który działa dla większości ekranów:

1. Zapisz dokładne operatory, których używa ekran (np. status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Zacznij od indeksu, który pasuje do najbardziej selektywnego filtra lub domyślnego sortu. Jeśli ekran sortuje po created_at DESC, zacznij od tego.
3. Dodaj indeks kompozytowy tylko, gdy widzisz te same filtry razem często. Umieść kolumny o równości najpierw, potem zakresy, potem klucz sortujący.
4. Użyj indeksu częściowego, gdy zawsze filtrujesz do podzbioru (np. status = 'open'). Użyj indeksu wyrażeniowego, gdy zapytujesz wartość wyliczoną (np. lower(email) dla wyszukiwań nieczułych na wielkość liter).
5. Waliduj z EXPLAIN ANALYZE. Zachowaj indeks tylko jeśli znacząco skraca czas wykonania i ilość przeczytanych wierszy.

Konkretne przykłady: ekran supportu może filtrować tickety po status i sortować po newest. B-tree na (status, created_at DESC) to silny pierwszy wybór. Jeśli ten sam ekran filtruje też po fladze JSONB meta @> '{"vip": true}', to inny operator zwykle wymaga oddzielnego indeksu skoncentrowanego na JSON.

Typowe błędy, które marnują czas (i spowalniają zapisy)

Częsty sposób na rozczarowanie to wybranie „właściwego” typu indeksu dla niewłaściwego operatora. PostgreSQL potrafi użyć indeksu tylko wtedy, gdy zapytanie pasuje do tego, co indeks ma rozwiązać. Jeśli aplikacja używa ILIKE '%term%', zwykły B-tree na tej kolumnie będzie nieużyty i wciąż zeskanujesz tabelę.

Inna pułapka to budowanie gigantycznych indeksów wielokolumnowych „na wszelki wypadek”. Wyglądają bezpiecznie, ale są drogie w utrzymaniu i często nie pasują do realnych wzorców zapytań. Jeśli lewe kolumny nie są używane w filtrze, reszta indeksu może nie pomagać.

Łatwo też przesadzić z indeksowaniem kolumn niskiej selektywności. B-tree na booleanie lub małym enumie może być prawie bezużyteczny, chyba że zrobisz go częściowym odpowiadającym temu, co faktycznie filtrujesz.

JSONB ma swoje pułapki. Szeroki indeks GIN może być świetny dla elastycznych filtrów, ale wiele przypadków będzie szybszych z wyrażeniowym indeksem na wyciągniętej wartości. Jeśli ekran zawsze filtruje po payload->>'customer_id', indeksowanie tego wyrażenia jest często mniejsze i szybsze niż indeksowanie całego dokumentu.

Wreszcie, każdy dodatkowy indeks obciąża zapisy. Na tabelach często aktualizowanych (tickety, zamówienia) każdy insert i update musi zaktualizować każdy indeks.

Zanim dodasz indeks, zatrzymaj się i sprawdź:

• Czy indeks odpowiada dokładnemu operatorowi, którego używa zapytanie?
• Czy możesz zastąpić szeroki indeks wielokolumnowy jednym lub dwoma skoncentrowanymi?
• Czy powinien to być indeks częściowy, by uniknąć hałasu niskiej selektywności?
• Dla JSONB: czy wyrażeniowy indeks nie pasuje lepiej?
• Czy tabela jest dość write-heavy, że koszt indeksu przewyższa zysk z odczytu?

Szybkie kontrole przed dodaniem (lub zachowaniem) indeksu

Zanim utworzysz nowy indeks, bądź konkretny co do tego, co aplikacja naprawdę robi. Indeks „miły do posiadania” często zamienia się w wolniejsze zapisy i więcej miejsca z małym zyskiem.

Zacznij od trzech najważniejszych ekranów (lub endpointów API) i zapisz ich dokładny kształt zapytań: filtry, porządek sortowania i co wpisuje użytkownik. Wiele „problemów z indeksami” to w rzeczywistości „niejasne zapytania”, zwłaszcza gdy ludzie debatują B-tree vs GIN vs GiST bez zapisania operatorów.

Prosta lista kontrolna:

• Wybierz 3 realne ekrany i wypisz dokładne wzorce WHERE i ORDER BY (włącznie z kierunkiem i obsługą NULL).
• Potwierdź typ operatora: równość (=), zakres (>, BETWEEN), prefiks, zawiera, nakładanie się, czy odległość.
• Wybierz jeden indeks na typowy wzorzec ekranu, przetestuj go i zachowaj tylko te, które mierzalnie zmniejszają czas lub liczbę odczytów.
• Jeśli tabela jest write-heavy, bądź rygorystyczny: dodatkowe indeksy mnożą koszt zapisu i zwiększają presję VACUUM.
• Sprawdź ponownie po zmianach funkcji. Nowy filtr, nowy domyślny sort lub przejście z „starts with” na „contains” może uczynić stary indeks nieistotnym.

Przykład: dashboard dodaje domyślny sort last_activity DESC. Jeśli masz tylko indeks na status, filtr może być szybki, ale sort teraz wymusi dodatkową pracę.

Przykład: mapowanie realnych ekranów do właściwego indeksu

Tabela decyzyjna pomaga tylko wtedy, gdy potrafisz ją odwzorować na rzeczywiste ekrany, które dostarczasz. Oto trzy typowe ekrany i indeksy, które zwykle do nich pasują.

Praktyczny sposób zastosowania zaczyna się od UI:

• Wypisz każdy kontroler, który zawęża wyniki (filtry).
• Zapisz domyślny porządek sortowania.
• Bądź konkretny co do zachowania wyszukiwania (pełnotekst vs starts-with vs contains).
• Wyróżnij „specjalne” pola (JSONB, geo, zakresy).

Następne kroki: włącz indeksowanie do procesu tworzenia

Dobre indeksy podążają za ekranami: filtrami, które ludzie klikają, porządkiem, którego oczekują, i polem wyszukiwania, z którego faktycznie korzystają. Traktuj indeksowanie jako nawyk podczas developmentu, a unikniesz większości niespodzianek wydajnościowych później.

Uczyń to powtarzalnym: zidentyfikuj 1–3 zapytania, które ekran wykonuje, dodaj najmniejszy indeks pasujący do nich, przetestuj na realistycznych danych, a potem usuń to, co nie daje efektu.

Jeśli budujesz narzędzie wewnętrzne lub portal klienta, planuj indeksy wcześnie, bo te aplikacje często rosną przez dodawanie kolejnych filtrów i list. Jeśli budujesz z AppMaster (appmaster.io), warto traktować konfigurację filtrów i sortowania każdego ekranu jako konkretne wzorce zapytań i dodawać tylko te indeksy, które pasują do rzeczywistych kliknięć.