PostgreSQL має кілька видів індексів, кожен під свій тип запитів:
- B-tree (за замовчуванням) - рівність, діапазони (
<,>,BETWEEN), сортування,LIKE 'початок%'. Покриває більшість випадків. - Hash - лише рівність (
=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний. - GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви,
jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент». - GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS,
tsrange, exclusion constraints, триграми з пошуком найближчих). - SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
- BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).
CREATE INDEX orders_user_idx ON orders (user_id); -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags); -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period); -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);
Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).
Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.
