Типы индексов: B-tree, Hash, GiN, GiST, BRIN

Индекс — отдельная структура данных, которая помогает СУБД быстро находить строки, не просматривая всю таблицу; у PostgreSQL таких структур несколько, и под разные задачи нужны разные.

Индекс — вспомогательная структура, которая хранит значения столбца (или выражения) в удобном для поиска виде вместе со ссылками на строки таблицы. Тип индекса определяет, какие запросы он умеет ускорять.

Без индекса любой поиск по условию — это полный перебор таблицы (последовательное сканирование). На тысяче строк это незаметно, на десятках миллионов — секунды ожидания. Индекс превращает перебор в адресный доступ: вместо «прочитать всё и отфильтровать» СУБД сразу прыгает к нужным строкам. Но универсального индекса нет: B-tree отлично ищет по диапазону чисел, но бесполезен для запроса «у каких документов в JSONB есть ключ tags». PostgreSQL предлагает целое семейство типов, и этот урок — карта, какой из них под что брать.

Зачем это на практике

Неправильный тип индекса — это либо индекс, которым планировщик никогда не воспользуется (потраченное место и замедление записи впустую), либо отсутствие ускорения там, где оно критично. Запрос с фильтром по массиву, по JSONB-полю или полнотекстовый поиск B-tree-индекс не ускорит вообще — нужен GIN. Поиск ближайших точек на карте — это GiST. А на таблице логов в сотни гигабайт обычный B-tree займёт десятки гигабайт, тогда как BRIN решит ту же задачу, заняв единицы мегабайт. Выбор типа — это первое архитектурное решение об индексировании, и оно сильно влияет и на скорость, и на размер базы.

B-tree: универсальный выбор по умолчанию

Когда вы пишете CREATE INDEX без указания типа, PostgreSQL создаёт B-tree. Это сбалансированное дерево, в котором значения хранятся в отсортированном порядке. Оно поддерживает операторы сравнения =, <, <=, >, >= и BETWEEN, а ещё помогает с ORDER BY и LIKE 'префикс%'.

-- B-tree создаётся по умолчанию; обе строки эквивалентны
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_email ON users USING btree (email);

B-tree подходит для подавляющего большинства колонок: первичные и внешние ключи, даты, числа, короткие строки. Логика простая: если запрос фильтрует столбец операторами сравнения или сортирует по нему — нужен B-tree. Покажем на исполнимом примере саму идею ускорения. SQLite в песочнице тоже строит B-tree, так что эффект тот же.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, age INTEGER);
INSERT INTO users (email, age) VALUES
  ('[email protected]', 31), ('[email protected]', 24),
  ('[email protected]', 42), ('[email protected]', 19), ('[email protected]', 35);

CREATE INDEX idx_users_age ON users (age);

-- запрос по диапазону: B-tree находит строки, не читая всю таблицу
SELECT email, age FROM users WHERE age BETWEEN 25 AND 40 ORDER BY age;

Вывод:

[email protected]|31
[email protected]|35

На пяти строках выигрыша нет, но на миллионах именно B-tree по age отделяет мгновенный ответ от полного сканирования.

GIN: массивы, JSONB и полнотекст

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

Берите GIN, когда условие звучит как «содержит»: массив содержит значение, JSONB содержит ключ или пару, документ содержит слово.

-- массив тегов: ищем статьи, у которых в tags есть 'sql'
CREATE INDEX idx_articles_tags ON articles USING gin (tags);
SELECT id FROM articles WHERE tags @> ARRAY['sql'];

-- JSONB: ищем заказы, где в data есть ключ/значение
CREATE INDEX idx_orders_data ON orders USING gin (data);
SELECT id FROM orders WHERE data @> '{"status": "paid"}';

-- полнотекстовый поиск по статье
CREATE INDEX idx_docs_fts ON docs USING gin (to_tsvector('russian', body));
SELECT id FROM docs WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'индекс');

Операторы @> (содержит) и @@ (совпадение полнотекста) — то, ради чего GIN существует. B-tree по столбцу tags такие запросы не ускорит вообще: он умеет сравнивать массивы целиком, но не искать вхождение элемента.

GiST: геометрия, диапазоны и близость

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

-- геоданные (расширение PostGIS): найти объекты рядом с точкой
CREATE INDEX idx_places_geom ON places USING gist (geom);

-- диапазоны времени: найти бронирования, пересекающие интервал
CREATE INDEX idx_booking_period ON bookings USING gist (period);
SELECT id FROM bookings WHERE period && tsrange('2026-06-01', '2026-06-07');

Классический случай GiST — поиск ближайших объектов (ORDER BY geom <-> point(...)) и проверка пересечения диапазонов оператором &&. Для геоданных и интервальных типов это незаменимо.

BRIN: огромные естественно отсортированные таблицы

BRIN (Block Range Index, индекс по диапазонам блоков) — крошечный индекс для очень больших таблиц, где данные физически лежат примерно по порядку. Вместо ссылки на каждую строку BRIN хранит для каждого блока страниц лишь сводку: минимальное и максимальное значение в этом блоке. Поэтому он занимает в сотни раз меньше места, чем B-tree.

-- таблица логов на сотни миллионов строк, растёт по времени
CREATE INDEX idx_events_ts ON events USING brin (created_at);

-- запрос по диапазону дат: BRIN отсекает блоки, чьи min/max вне диапазона
SELECT count(*) FROM events
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-08';

BRIN блистает, когда есть корреляция между физическим порядком строк и значением столбца: события пишутся по возрастанию created_at, поэтому соседние блоки содержат близкие даты. Если же данные перемешаны (например, случайный UUID), BRIN бесполезен — у каждого блока диапазон min/max окажется «от и до», и отсечь ничего не получится.

Hash: только равенство

Hash-индекс хранит хеши значений и поддерживает единственный оператор — =. Он не помогает ни сортировке, ни диапазонам, ни LIKE. Теоретически на точном поиске он может быть чуть компактнее и быстрее B-tree, но на практике выигрыш мал, а B-tree умеет и равенство тоже. Поэтому Hash берут редко и осознанно — когда столбец участвует исключительно в проверках на равенство и значения длинные.

-- оправдано только если по token всегда ищут строго через =
CREATE INDEX idx_sessions_token ON sessions USING hash (token);
SELECT user_id FROM sessions WHERE token = 'a1b2c3d4e5';

Как это работает под капотом

Все индексы PostgreSQL — это отдельные файлы на диске, физически не связанные с порядком строк в таблице (исключение по смыслу — BRIN, который как раз опирается на этот порядок). Поиск по B-tree — это спуск от корня к листу по отсортированным ключам за число шагов, пропорциональное логарифму размера таблицы: вот почему даже на миллиардах строк хватает считанных обращений к диску. GIN внутри хранит для каждого «элемента» (слова, ключа JSONB) упорядоченный список идентификаторов строк, и запрос «содержит X» — это просто чтение готового списка. BRIN не указывает на строки вовсе: по сводкам блоков он быстро отсекает заведомо ненужные диапазоны страниц, а внутри оставшихся уже идёт обычное чтение. Платой за любой индекс служит замедление записи: каждый INSERT, UPDATE и DELETE должен обновить ещё и все индексы таблицы, плюс они занимают место на диске.

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

• B-tree на массив или JSONB. Запрос «содержит элемент» он не ускорит. Для операторов @>, ?, @@ нужен GIN, иначе планировщик честно сделает полное сканирование.
• BRIN на перемешанные данные. Без корреляции между физическим порядком и значением сводки блоков ничего не отсекают — индекс есть, толку ноль.
• Hash «для скорости». В большинстве случаев B-tree не хуже и универсальнее; отдельный Hash почти никогда не оправдан.
• Индексы на всё подряд. Лишний индекс замедляет каждую запись и съедает диск. Индексируйте столбцы, которые реально встречаются в WHERE, JOIN и ORDER BY.

Итоги

• B-tree — выбор по умолчанию: равенство, диапазоны (<, >, BETWEEN), сортировка, LIKE 'префикс%'.
• GIN — для «составных» значений: массивы, JSONB, полнотекст; операторы «содержит» @> и @@.
• GiST — для геометрии, диапазонов и поиска близости: пересечение &&, ближайшие объекты.
• BRIN — крошечный индекс для огромных таблиц с естественным порядком (логи по времени).
• Hash — только оператор =; на практике почти всегда проигрывает B-tree и берётся редко.
• Любой индекс ускоряет чтение, но замедляет запись и занимает место — индексируйте осознанно.