Типы индексов PostgreSQL: B-tree, GIN, GiST, BRIN и когда их применять

Короткий ответ

Выбирайте индекс по оператору в WHERE, а не по типу колонки:

Есть ещё Hash (только равенство) и SP-GiST (несбалансированное разбиение, например для IP-адресов или точек), но четыре типа выше покрывают большинство реальных задач.

B-tree: индекс по умолчанию не случайно

B-tree хранит ключи отсортированными, поэтому отвечает и на «найти точно», и на «найти диапазон», а ещё может вернуть строки уже упорядоченными и избавить от сортировки.

• В составном индексе важен порядок колонок: (customer_id, created_at) обслуживает WHERE customer_id = ? ORDER BY created_at, но не фильтр только по created_at.
• LIKE 'abc%' использует B-tree только при collation C или с классом операторов text_pattern_ops. LIKE '%abc%' — никогда.
• Колонки с низкой селективностью (булев флаг) сами по себе редко выигрывают от B-tree.

GIN: для «что внутри»

GIN раскладывает каждое значение на элементы — ключи и значения jsonb, элементы массива, слова документа — и хранит список строк для каждого элемента.

CREATE INDEX ON orders USING gin (attributes jsonb_path_ops);
-- обслуживает: WHERE attributes @> '{"delivery": "express"}'

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
-- обслуживает: WHERE name ILIKE '%telefon%'

• jsonb_path_ops компактнее и быстрее для @>, но не поддерживает операторы проверки ключей вроде ?. Класс по умолчанию jsonb_ops поддерживает больше операторов.
• Чтение из GIN быстрое, запись дороже: одна строка обновляет много записей индекса. Pending list (fastupdate) смягчает это, но замедляет часть поисков, пока список не слит.

GiST: пересечения, геометрия и «ближайшие»

GiST — это каркас: каждый узел хранит предикат, покрывающий потомков (например, ограничивающий прямоугольник). Он отвечает на вопросы «что пересекается» и «что ближе всего».

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Такое ограничение исключения делает двойное бронирование комнаты невозможным на уровне базы. На GiST работают геоданные PostGIS и поиск ближайших соседей через ORDER BY location <-> point LIMIT 10. Для полнотекстового поиска GiST меньше и дешевле при обновлении, чем GIN, но обычно медленнее при поиске.

BRIN: крошечные индексы для огромных таблиц

BRIN хранит только минимум и максимум для каждого диапазона страниц таблицы. Если строки физически пишутся в порядке колонки — created_at в append-only таблице событий, — PostgreSQL пропускает все диапазоны, где подходящих строк быть не может.

CREATE INDEX ON events USING brin (created_at);

BRIN-индекс может быть на порядки меньше B-tree на той же колонке. Он бесполезен, если колонка не коррелирует с физическим порядком строк — например, после множества обновлений или со случайными UUID.

Частичные, функциональные и покрывающие индексы

• Частичный: индексируйте только те строки, которые запрашиваете. CREATE INDEX ON orders (created_at) WHERE status = 'pending'; остаётся маленьким, пока большинство заказов завершены.
• Функциональный: индекс по результату выражения. CREATE INDEX ON users (lower(email)); работает, только если запрос использует ровно lower(email).
• Покрывающий: CREATE INDEX ON orders (customer_id) INCLUDE (total); позволяет ответить из одного индекса (index-only scan), если visibility map актуальна — а это зависит от vacuum.

Как найти неиспользуемые индексы

Каждый индекс замедляет запись и занимает место. Проверьте статистику:

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

Прежде чем удалять индекс с idx_scan = 0:

• проверьте все реплики — статистика ведётся на каждом сервере отдельно;
• узнайте, когда статистику сбрасывали последний раз;
• не удаляйте индексы первичных ключей и уникальных ограничений;
• ищите дубликаты: индекс по (a) обычно лишний рядом с (a, b).

Всегда подтверждайте через EXPLAIN (ANALYZE, BUFFERS), что планировщик действительно использует новый индекс.