Indeksy w PostgreSQL: B-Tree, Hash, GIN, GiST i BRIN

Każdy z tych typów indeksów działa inaczej i ma swoje mocne oraz słabe strony. Zrozumienie, który indeks wybrać do konkretnego przypadku, potrafi skrócić czas zapytania z sekund do milisekund — albo odwrotnie, jeśli wybór jest zły.

W tym artykule poznasz najważniejsze rodzaje indeksów w PostgreSQL, ich zastosowania, różnice w wydajności i przykłady użycia z EXPLAIN. To nie teoria — zobaczysz konkretne przykłady z jsonb, tablicami, tekstem i danymi przestrzennymi, które faktycznie robią różnicę w realnych projektach.

Przegląd typów indeksów

B-Tree to domyślny indeks w PostgreSQL. Obsługuje równość, zakresy, sortowanie i unikalność. Działa najlepiej przy wysokiej selektywności i porządku zgodnym z zapytaniami (np. ORDER BY). Wspiera wielokolumnowość z zasadą lewego prefiksu i operator classes (np. text_pattern_ops dla LIKE z prefiksem). To najczęstszy wybór, gdy nie ma specjalnych wymagań.

Indeks Hash służy do porównań równościowych. Od PostgreSQL 10 jest trwały (WAL) i replikuje się. Daje sens przy bardzo prostych warunkach = na krótkich kluczach, ale zwykle indeks B-Tree PostgreSQL jest równie dobry lub lepszy, bo wspiera więcej operatorów i porządkowanie. GIN to odwrócony indeks dla kolekcji i dokumentów (ARRAY, jsonb, tsvector). Świetny do zapytań “zawiera” i pełnotekstowych. Ma wolniejsze inserty/updated niż B-Tree przez koszt aktualizacji wielu wpisów. GiST to elastyczna struktura drzewa dla danych z „pojęciem odległości” i zakresów (np. geometria, ranges). Umożliwia KNN (ORDER BY <->). BRIN przechowuje streszczenia zakresów bloków, jest bardzo mały i szybki w budowie. Działa najlepiej na bardzo dużych tabelach, gdy kolumna rośnie wraz z wstawieniami (naturalna korelacja).

W praktyce łączymy to z indeksami częściowymi, na wyrażenia (np. lower(email)), z kolumnami dołączonymi (INCLUDE) dla indeksów pokrywających. Jeśli zaczynasz z PostgreSQL, zajrzyj do: PostgreSQL: wprowadzenie, instalacja i pierwsza baza danych.

Kiedy używać B-Tree, a kiedy GIN

Wybór wynika z operatorów i selectivity. B-Tree używasz przy =, <, >, BETWEEN, sortowaniu i joinach po kluczu. Dla LIKE 'prefiks%' stosuj B-Tree z text_pattern_ops/varchar_pattern_ops. GIN wybierasz dla jsonb @>, ?|, ?&, arrays @>, @<@, && oraz pełnotekstowych @@. GIN skaluje się dobrze dla zapytań „zawiera”, ale ma wyższy koszt zapisu. Patrz na cardinality i selectivity: jeśli warunek wybiera mało wierszy, indeks zadziała; gdy dużo, planner może wybrać skan sekwencyjny.

Przykład porównawczy:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
-- B-Tree do równości i sortowania
CREATE INDEX ON users (email);
SELECT * FROM users WHERE email = 'a@b.com';
SELECT * FROM users WHERE created_at BETWEEN now()-interval '7 days' AND now()
ORDER BY created_at DESC LIMIT 50;

-- GIN do JSONB i tablic
CREATE INDEX CONCURRENTLY users_prefs_gin ON users USING gin (prefs jsonb_path_ops);
SELECT id FROM users WHERE prefs @> '{"lang":"pl"}';

-- B-Tree dla LIKE z prefiksem
CREATE INDEX users_name_btree ON users (name text_pattern_ops);
SELECT * FROM users WHERE name LIKE 'Jan%';

Dodatkowe przykłady i pułapki omawiam szerzej tu: Indeksy w SQL: teoria i praktyka.

Indeksy pełnotekstowe i przestrzenne

Pełnotekstowe wyszukiwanie PostgreSQL opiera się na tsvector/tsquery i zwykle używa GIN. Budujemy kolumnę tsvector (najlepiej generowaną) i indeksujemy:

SQL
Kopiuj
1
2
3
4
ALTER TABLE articles
  ADD COLUMN doc tsvector GENERATED ALWAYS AS (to_tsvector('polish', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
CREATE INDEX articles_doc_gin ON articles USING gin (doc);
SELECT id FROM articles WHERE doc @@ plainto_tsquery('polish', 'postgresql indeksy');

Dla indeksów przestrzennych używa się PostGIS. Domyślnie indeks GiST PostgreSQL na geometrii/geografii umożliwia szybkie ST_Intersects i KNN:

SQL
Kopiuj
1
2
3
4
5
6
-- wymagane rozszerzenie PostGIS
CREATE INDEX cities_geom_gist ON cities USING gist (geom);
SELECT name FROM cities
WHERE ST_DWithin(geom, ST_SetSRID(ST_Point(21.0122,52.2297), 4326), 0.1)
ORDER BY geom <-> ST_SetSRID(ST_Point(21.0122,52.2297), 4326)  -- KNN
LIMIT 10;

W przypadku bardzo dużych, przyrastających tabel rozważ BRIN do filtrów po czasie lub ID, a przestrzennie i tak trzymaj GiST dla operacji geometrycznych. W kontekście dużych zbiorów danych warto też znać: Partycjonowanie i sharding w PostgreSQL: podstawy.

Wpływ indeksów na wydajność zapytań

Indeksy w PostgreSQL skracają dostęp do danych (Index Scan/Bitmap Heap Scan), ale spowalniają INSERT/UPDATE/DELETE i zwiększają koszty VACUUM/maintenance. Planner wybiera plan na podstawie statystyk (ANALYZE), estymując selectivity i koszt planu zapytania. Zbyt niska selektywność skutkuje skanem sekwencyjnym. Bitmapy pomagają, gdy warunek zwraca umiarkowany procent wierszy.

Korelacja danych ma znaczenie. Przy wysokiej korelacji kolumny z fizycznym ułożeniem stron (np. rosnący timestamp) BRIN bywa znakomity i bardzo mały. Dla zapytań silnie ograniczających warto użyć indeksów pokrywających (INCLUDE), by uniknąć odczytu tabeli. Rozważ też CLUSTER lub pg_repack dla poprawy lokalności danych. Więcej o planach i kosztach: Optymalizacja zapytań SQL i analiza planów wykonania.

Analiza planu z EXPLAIN

EXPLAIN analiza planu pokazuje wybór skanu, koszty i selektywność. Używaj EXPLAIN (ANALYZE, BUFFERS) do porównywania indeksów. Przykład B-Tree vs GIN:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
-- B-Tree: equality/range
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'a@b.com';

-- GIN na JSONB: containment
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE prefs @> '{"lang":"pl","beta":true}';

-- BRIN: duża tabela, filtr po czasie
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 day';

Zwracaj uwagę na rodzaj skanu (Index Scan, Bitmap Heap Scan, Seq Scan), szacowane vs rzeczywiste wiersze, liczbę recheck przy GIN/GiST oraz na odczyty BUFFERS. Rozbieżności estymacji zwykle oznaczają przestarzałe statystyki lub złą selektywność operatorów.

Tworzenie i utrzymanie indeksów

Twórz indeksy bez przestojów używając CONCURRENTLY. Aktualizuj statystyki i kontroluj rozrost indeksów. Reaguj na bloat, gdy rośnie relacja:pg_stat_user_indexes.idx_scan jest niska, a rozmiar indeksu duży.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Bez blokady zapisu (wolniejsze, ale online)
CREATE INDEX CONCURRENTLY orders_created_btree ON orders (created_at);

-- Indeks częściowy ogranicza rozmiar
CREATE INDEX orders_open_partial ON orders (status) WHERE status = 'open';

-- Indeks wyrażenia i pokrywający
CREATE INDEX users_lower_email_inc ON users ((lower(email))) INCLUDE (id);

-- BRIN na rosnącym timestamp
CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 128);

-- Hash (tylko równość; rozważ B-Tree najpierw)
CREATE INDEX sessions_token_hash ON sessions USING hash (token);

-- Utrzymanie
VACUUM (ANALYZE) users;
REINDEX INDEX CONCURRENTLY users_lower_email_inc;
-- Monitoring użycia
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

Regularnie uruchamiaj ANALYZE, kontroluj autovacuum, ustaw maintenance_work_mem pod REINDEX. Usuwaj nieużywane indeksy. W przypadku częstych masowych modyfikacji rozważ czasowe wyłączenie mniej krytycznych indeksów i ponowne utworzenie. Dzięki temu indeks GIN PostgreSQL, indeks GiST PostgreSQL, indeks BRIN PostgreSQL i indeks Hash PostgreSQL pozostaną efektywne w realnych obciążeniach.