Типы индексов в PostgreSQL — B-tree, GIN, GiST, BRIN

← назад к разделу

Без индекса база читает каждую строку таблицы при каждом запросе. На тысяче строк это незаметно. На миллионе — запрос выполняется секунды вместо миллисекунд. На десятках миллионов — минуты.

Индекс — это отдельная структура данных, которую PostgreSQL строит и обновляет рядом с таблицей. Она позволяет найти нужные строки, не читая всё подряд.

PostgreSQL поддерживает шесть видов индексов. Каждый устроен по-своему и хорошо работает на своём классе задач. Правильный выбор иногда ускоряет запрос в сотни раз.

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

Когда пишут просто CREATE INDEX, PostgreSQL создаёт B-tree. Это сбалансированное дерево, где каждый узел содержит диапазон значений. База спускается по дереву от корня к нужному листу — за O(log N) шагов, а не за O(N).

CREATE INDEX ix_orders_created_at ON orders (created_at);
-- то же самое с явным указанием типа:
CREATE INDEX ix_orders_created_at ON orders USING btree (created_at);

B-tree работает с операторами сравнения (=, <, <=, >, >=), диапазонами (BETWEEN, IN), сортировкой (ORDER BY), паттерном с фиксированным префиксом (LIKE 'prefix%') и проверкой IS NULL / IS NOT NULL. Именно он используется для UNIQUE ограничений и первичных ключей.

Для подавляющего большинства обычных таблиц B-tree — единственный нужный тип.

Hash — почти никогда не нужен

Hash-индекс хранит хэши значений и умеет только проверять точное равенство (=). Логично предположить, что он быстрее B-tree там, где нужен только =. На практике:

• B-tree на точном равенстве работает сопоставимо по скорости.
• Hash не поддерживает сортировку, диапазоны, составные запросы.
• До PostgreSQL 10 hash-индекс не записывался в журнал WAL и мог испортиться при сбое.

-- редкий случай применения
CREATE INDEX ix_account_email_hash ON account USING hash (email);

Выбирайте hash только при наличии измеренного подтверждения, что он быстрее B-tree в вашем конкретном случае.

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

GIN расшифровывается как Generalized Inverted Index — обобщённый инвертированный индекс. Принцип такой же, как у поискового движка: для каждого «токена» (ключ JSONB, элемент массива, слово) хранится список строк, где он встречается.

-- Поиск по JSONB
CREATE INDEX ix_event_payload ON event_log USING gin (payload jsonb_path_ops);

-- Поиск по массиву тегов
CREATE INDEX ix_article_tags ON article USING gin (tags);

-- Полнотекстовый поиск
CREATE INDEX ix_post_search ON post USING gin (to_tsvector('russian', body));

Характерные свойства GIN:

• Чтение быстрое — найти строки с нужным ключом или словом за один просмотр.
• Запись медленнее, чем у B-tree: изменение одной строки может затронуть много записей в индексе.
• Есть параметр fastupdate — буфер отложенных изменений, который ускоряет вставки ценой небольшой задержки при чтении.

Для JSONB-колонок существуют два варианта оператора: стандартный (jsonb_ops) и компактный (jsonb_path_ops). Компактный поддерживает только оператор вхождения @>, зато занимает меньше места и работает быстрее — выбирайте его, если не нужны операторы ? и ?|.

GiST — диапазоны, геометрия, исключения

GiST (Generalized Search Tree) — обобщённое дерево поиска. Он предназначен для типов данных, у которых нет линейного порядка: геометрические фигуры, временные диапазоны, IP-сети.

-- Constraint EXCLUDE: никаких пересечений в бронировании комнаты
CREATE EXTENSION btree_gist;
CREATE TABLE booking (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- Ближайшие точки на карте (kNN)
CREATE INDEX ix_shop_location ON shop USING gist (location);
SELECT * FROM shop ORDER BY location <-> ST_Point(37.6, 55.7) LIMIT 10;

GiST поддерживает поиск ближайших соседей (ORDER BY x <-> point) и ограничение EXCLUDE — гарантию непересечения записей. Например, нельзя забронировать одну комнату на пересекающиеся периоды.

По сравнению с GIN: GiST пишет быстрее, читает медленнее, занимает меньше места.

Когда GIN, а когда GiST

Оба подходят для полнотекстового поиска, но ведут себя по-разному:

• GIN — быстрое чтение, медленная запись, большой размер. Выбирайте, когда таблица читается чаще, чем пишется.
• GiST — быстрая запись, медленнее чтение, компактный. Выбирайте при частых обновлениях.

Для JSONB и массивов GIN обычно предпочтительнее. GiST берут тогда, когда нужны специфичные возможности: kNN, EXCLUDE, геометрия.

BRIN — для огромных таблиц с журналами

BRIN (Block Range Index) — индекс для таблиц, где строки физически расположены в порядке возрастания какого-то значения. Типичный пример — таблица событий с полем occurred_at.

Идея: вместо того чтобы индексировать каждую строку, BRIN запоминает минимальное и максимальное значение для каждого диапазона страниц (по умолчанию — 128 страниц). При запросе «события за последний час» база отбрасывает все диапазоны, у которых максимум раньше нужного времени.

CREATE INDEX ix_event_log_at_brin ON event_log USING brin (occurred_at);

Главное достоинство BRIN — размер: несколько десятков килобайт на таблицу в гигабайты. При этом диапазонные запросы работают быстро.

Ограничения:

• Работает только при физической упорядоченности. Если строки вставляются вразнобой, BRIN не поможет. При необходимости упорядочить — использовать CLUSTER.
• Точечный поиск (=) BRIN не ускоряет.

Подходит для: журналов и событий с автоматически возрастающей меткой времени, метрик, архивных разделов таблиц.

Не подходит для: таблиц с обновлениями, точечного поиска.

SP-GiST — редкий случай

Space-Partitioned GiST разбивает пространство значений на неравномерные части — подходит для IP-адресов и URL с общими префиксами.

CREATE INDEX ix_request_url ON request USING spgist (url);
CREATE INDEX ix_visit_ip ON visit USING spgist (ip);

В обычном приложении встречается крайне редко.

Таблица выбора

pg_trgm для поиска по подстроке

Обычный B-tree не умеет LIKE '%слово%' — он не знает, с чего начинается подстрока. Расширение pg_trgm решает эту проблему.

Триграмма — это тройка последовательных символов. Строка «иванов» разбивается на триграммы «ива», «ван», «ано», «нов». GIN-индекс хранит, в каких строках встречается каждая триграмма. При поиске LIKE '%анов%' база находит строки, содержащие нужные триграммы.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- ускоряет поиск по подстроке
SELECT * FROM customer WHERE full_name ILIKE '%иван%';

-- и нечёткий поиск
SELECT * FROM customer WHERE similarity(full_name, 'иванв') > 0.4;

Частичный индекс

Частичный (partial) индекс индексирует не всю таблицу, а только строки, подходящие под условие WHERE. Это позволяет сделать индекс значительно меньше и быстрее.

Типичная ситуация: таблица заказов, где 90% строк имеют статус COMPLETED, а запросы в основном работают с активными заказами.

-- Индексируем только активные заказы
CREATE INDEX ix_orders_active_customer
    ON orders (customer_id)
    WHERE status IN ('NEW', 'PAID', 'SHIPPED');

-- запрос использует индекс только если содержит то же условие
SELECT * FROM orders
WHERE customer_id = ?
  AND status IN ('NEW', 'PAID', 'SHIPPED');

Частичный индекс применим к любому типу. Размер меньше, запись быстрее — обновление строки не затрагивает индекс, если строка не подпадает под условие.

Частые ошибки

B-tree на JSONB. PostgreSQL не откажет, но такой индекс помогает только при = на всём документе целиком — что почти никогда не нужно. Для запросов по полям внутри документа (@>, ?) нужен GIN.

B-tree на массиве. Та же история: B-tree не понимает «есть ли элемент X в массиве». Нужен GIN.

LIKE '%X%' без pg_trgm. Запрос будет работать через полный перебор. Нужен GIN-индекс с оператором gin_trgm_ops.

BRIN на таблице с обновлениями. Если строки вставляются не по возрастанию индексируемого поля, BRIN теряет эффективность. Используйте B-tree.

Полный индекс там, где нужен частичный. Если 90% строк никогда не попадают в запросы по этому полю, полный индекс тратит место и замедляет запись без пользы.

Коротко

• B-tree — выбор по умолчанию для сравнений, сортировки, диапазонов, UNIQUE и внешних ключей.
• Hash — умеет только =, на практике не лучше B-tree; брать только при измеренном преимуществе.
• GIN — JSONB, массивы, полнотекстовый поиск. Быстрое чтение, медленная запись.
• GiST — диапазоны, геометрия, EXCLUDE, kNN. Быстрая запись, медленнее чтение.
• BRIN — журналы и метрики с возрастающим timestamp. Размер в килобайтах на гигабайты данных.
• SP-GiST — IP-адреса и URL-префиксы. Встречается редко.
• pg_trgm + GIN решает LIKE '%substring%' и нечёткий поиск.
• Частичный индекс — добавьте WHERE, если большинство строк таблицы никогда не попадают в запросы.

Что почитать дальше

• Составные индексы и порядок колонок — как работает правило левого префикса.
• Как выбрать индекс: селективность и EXPLAIN — когда индекс помогает, а когда нет.
• JSONB в PostgreSQL — подробнее о GIN и операторах для документов.
• Полнотекстовый поиск в PostgreSQL — tsvector, tsquery, ранжирование.