PostgreSQL индексы: B-tree, GIN, GiST — когда какой использовать

Вы создали таблицу, написали запрос, он работает — но медленно. Первое, что приходит в голову — «надо добавить индекс». Но какой именно? PostgreSQL предлагает несколько типов индексов, и выбор неправильного может не просто не помочь, а ухудшить ситуацию. Разберёмся, когда какой использовать.

B-tree — дефолт и его пределы

Когда вы пишете CREATE INDEX без указания типа, PostgreSQL создаёт B-tree. Это сбалансированное дерево, которое отлично работает для операций сравнения: =, <, >, BETWEEN, IS NULL.

-- Классический B-tree индекс
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Составной индекс (порядок колонок важен!)
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

B-tree покрывает 90% типичных задач. Он поддерживает сортировку, уникальность и работает с LIKE 'prefix%' (но не с LIKE '%suffix'). Однако у него есть ограничения:

• Не умеет эффективно искать внутри JSON-документов
• Не подходит для полнотекстового поиска по tsvector
• Бесполезен для геопространственных запросов
• Раздувается при частых UPDATE на индексированные колонки

GIN — для jsonb и полнотекстового поиска

GIN (Generalized Inverted Index) — инвертированный индекс. Он разбирает составное значение на элементы и строит маппинг «элемент → список строк». Идеален для:

• JSONB-колонок с операторами @>, ?, ?&, ?|
• Полнотекстового поиска (tsvector + @@)
• Массивов (@>, &&)

-- GIN для JSONB
CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

-- Теперь этот запрос летает:
SELECT * FROM events
WHERE payload @> '{"type": "purchase", "amount": 500}';

-- GIN для полнотекстового поиска
CREATE INDEX idx_articles_fts ON articles USING gin (search_vector);

SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('russian', 'postgresql индексы');

Нюанс: GIN медленнее обновляется, чем B-tree. Для таблиц с редкими записями и частыми чтениями — идеален. При массовых INSERT используйте fastupdate = off.

GiST — для геоданных и диапазонов

GiST (Generalized Search Tree) — обобщённое дерево поиска. Строит иерархию ограничивающих прямоугольников. Применяется для:

• Геопространственных данных (PostGIS: ST_Contains, ST_DWithin)
• Диапазонных типов (int4range, tsrange, daterange)
• Поиска ближайших соседей (оператор <->)

-- GiST для диапазонов дат (бронирования)
CREATE TABLE bookings (
  id serial PRIMARY KEY,
  room_id int,
  period tsrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- Найти пересекающиеся бронирования:
SELECT * FROM bookings
WHERE period && tsrange('2026-03-01', '2026-03-15');

-- PostGIS: кафе в радиусе 500м
CREATE INDEX idx_places_geom ON places USING gist (geom);
SELECT name FROM places
WHERE ST_DWithin(geom, ST_MakePoint(37.6173, 55.7558)::geography, 500);

GiST проигрывает GIN по скорости точного поиска, но выигрывает в задачах на пересечение и расстояние.

BRIN — для time-series и append-only таблиц

BRIN (Block Range Index) — самый компактный тип. Хранит min/max значения для блоков страниц. Работает только если данные физически упорядочены на диске.

-- BRIN для таблицы логов
CREATE INDEX idx_logs_ts ON logs USING brin (created_at)
  WITH (pages_per_range = 64);

-- Размер: ~1 МБ на 100 ГБ данных (vs ~2 ГБ для B-tree)

-- Проверить корреляцию (должна быть близка к 1.0):
SELECT correlation FROM pg_stats
WHERE tablename = 'logs' AND attname = 'created_at';

Если корреляция низкая (данные перемешаны) — BRIN бесполезен, используйте B-tree.

Практика: EXPLAIN ANALYZE до и после

Таблица заказов, 5 миллионов строк:

-- До индекса:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE status = 'pending' AND created_at > now() - interval '1 hour';

-- Seq Scan on orders  (cost=0.00..185432.00 rows=1250)
--   actual time=0.015..892.341 rows=1183 loops=1
--   Buffers: shared hit=12045 read=73387
-- Execution Time: 893.012 ms

-- Создаём частичный индекс:
CREATE INDEX idx_orders_pending_recent ON orders (created_at)
WHERE status = 'pending';

-- После:
-- Index Scan using idx_orders_pending_recent
--   actual time=0.031..2.148 rows=1183 loops=1
--   Buffers: shared hit=1192
-- Execution Time: 2.891 ms

С 893 мс до 2.9 мс — ускорение в 300 раз. Частичный индекс занимает меньше места и обновляется быстрее.

Подводные камни

• Больше индексов ≠ лучше. Каждый индекс замедляет INSERT/UPDATE/DELETE.
• Неиспользуемые индексы. Проверяйте pg_stat_user_indexes.idx_scan — если 0 за месяц, удаляйте.
• Bloat. B-tree раздувается при частых обновлениях. Делайте REINDEX CONCURRENTLY.
• Порядок колонок. Индекс (a, b) помогает запросам по a и (a, b), но не по одному b.
• INCLUDE. Covering index позволяет делать Index Only Scan без обращения к таблице.

Вывод

Выбор индекса — инженерное решение. Алгоритм:

1. Равенство и сравнение скаляров → B-tree
2. Поиск внутри JSON, массивов, полнотекст → GIN
3. Геоданные, диапазоны, ближайшие соседи → GiST
4. Append-only с коррелированными данными → BRIN

Всегда проверяйте через EXPLAIN (ANALYZE, BUFFERS). Смотрите на actual time, buffers и тип сканирования. Индекс, который не используется планировщиком — мёртвый груз.