Индексы Postgres на пальцах: B-tree, GIN, BRIN и когда что брать

15 мая 2026 г. 19K

Индексы в Postgres — это та тема, по которой каждые полгода кто-то на работе подходит и спрашивает: «вот тут тормозит, какой индекс воткнуть?». Я решила собрать свой ответ в одном месте, чтобы можно было кидать ссылку. В нём только то, что я реально применяю в проде: B-tree, GIN, GIST, BRIN и Hash. Без академических подробностей.

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

Если ты не знаешь, какой индекс взять, бери B-tree. Это сбалансированное дерево, по которому быстро ищется по точному значению (=), по диапазону (<, >, BETWEEN) и по сортировке. Подходит для всего, что выглядит как «найди мне записи, у которых поле X равно или меньше Y».

CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX users_email_idx ON users(email);
CREATE INDEX orders_created_at_idx ON orders(created_at);

Составные B-tree-индексы

Очень полезная штука, про которую стабильно забывают. Если у тебя в запросе фильтр по двум-трём полям и сортировка — один правильный составной индекс работает быстрее, чем три отдельных. Порядок колонок важен.

CREATE INDEX posts_author_created_idx ON posts(author_id, created_at DESC);

Этот индекс обслуживает запросы типа «лента автора, последние посты»:

SELECT * FROM posts WHERE author_id = $1 ORDER BY created_at DESC LIMIT 20;

Правило, которое я повторяю себе на каждом review: в составном индексе колонки идут в порядке «равенство, потом неравенство, потом сортировка». Если первая колонка идёт в WHERE с диапазоном, индекс работает хуже, чем если бы она там сравнивалась на равенство.

Покрывающие индексы (INCLUDE)

Postgres умеет включать в индекс «не-индексные» колонки, которые не участвуют в условии, но возвращаются в SELECT. Это включает Index Only Scan и убирает поход в таблицу.

CREATE INDEX posts_author_created_idx2
ON posts(author_id, created_at DESC)
INCLUDE (title);

Использовать с умом: лишние колонки в индексе раздувают его и замедляют запись. Я добавляю INCLUDE только тогда, когда индекс реально становится Index Only Scan и это экономит существенное время.

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

GIN расшифровывается как Generalized Inverted Index. Идея простая: каждый «элемент» внутри значения колонки попадает в индекс отдельно. Сильнее всего пригождается на трёх типах данных.

tsvector — полнотекстовый поиск

ALTER TABLE posts ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX posts_search_gin_idx ON posts USING gin(search);

SELECT id, title FROM posts
WHERE search @@ websearch_to_tsquery('russian', $1)
ORDER BY ts_rank(search, websearch_to_tsquery('russian', $1)) DESC
LIMIT 20;

Это рабочий полнотекст из коробки. Для базового поиска с подсветкой и весами хватает с головой.

jsonb

CREATE INDEX events_payload_gin_idx ON events USING gin(payload jsonb_path_ops);

jsonb_path_ops — облегчённая версия класса операторов, она поддерживает только @>, но индекс получается ощутимо меньше и быстрее. Я почти всегда беру именно её.

SELECT * FROM events WHERE payload @> '{"type": "signup"}';

Массивы

CREATE INDEX posts_tags_gin_idx ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres', 'sql'];

Индекс работает по операторам @>, &&, <@. Очень удобно для тегов, ролей, разрешений.

pg_trgm для LIKE

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_name_trgm_idx ON users USING gin(name gin_trgm_ops);

SELECT * FROM users WHERE name ILIKE '%орлов%';

Это уже не вполне «полнотекст», но даёт быстрый ILIKE '%...%' — то, чего обычный B-tree не умеет вообще. Использую для пользовательских автоподсказок, где нужен поиск по подстроке без построения tsvector.

GIST — для гео и диапазонов

GIST — обобщённое дерево, которое умеет работать с операторами «пересекается», «содержит», «соседствует». Главные сценарии: PostGIS (геометрия) и интервальные типы.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX rooms_period_gist_idx
  ON bookings USING gist (room_id, daterange(start_date, end_date, '[)'));

SELECT * FROM bookings
WHERE room_id = 7
  AND daterange(start_date, end_date, '[)') && daterange('2026-06-01', '2026-06-10', '[)');

btree_gist добавляет поддержку обычных типов в GIST, что позволяет в одном индексе совмещать room_id и интервал. Без расширения пришлось бы делать GIST только по интервалу и фильтровать room_id отдельно.

BRIN — для очень больших, отсортированных по физическому порядку таблиц

BRIN (Block Range INdex) хранит min и max значений по диапазонам блоков, а не по отдельным строкам. Идеален, когда данные физически идут в том же порядке, что и колонка индексирования: например, time-series, логи, события.

CREATE INDEX events_created_at_brin_idx
  ON events USING brin(created_at)
  WITH (pages_per_range = 32);

Размер BRIN — десятые и сотые доли процента от таблицы. Поиск по диапазону работает быстро, потому что Postgres сразу пропускает огромные блоки. Минус — для случайного доступа BRIN бесполезен. И если данные не физически отсортированы, толку от индекса не будет.

Использую BRIN на таблицах от 100 миллионов строк, где B-tree становится тяжёлым по диску, а запросы — это всегда «дай мне последние сутки» или «дай мне диапазон неделя X».

Hash — отдельный кейс

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

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

Не путай с типом индекса. Это просто условие WHERE в самом DDL. Очень мощная штука для разреженных запросов.

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

Индекс хранит только записи активных пользователей и не раздувается за счёт удалённых. Запрос с тем же условием подхватит этот индекс автоматически. Часто экономит десятки процентов места.

Индексы по выражению

CREATE UNIQUE INDEX users_lower_email_idx
  ON users (lower(email));

SELECT * FROM users WHERE lower(email) = lower($1);

Если в приложении email сравнивается без учёта регистра, такой индекс — обязательный пункт. И не забывай, что чтобы индекс сработал, в WHERE должно быть ровно то же выражение.

Когда лучше не добавлять индекс

На таблице с активными INSERT/UPDATE каждый индекс — это работа на запись. Если запрос идёт раз в сутки и его выгоднее держать через материализацию или CTE, я предпочту не плодить индексы.

Для маленьких таблиц (до пары тысяч строк) Postgres всё равно сделает Seq Scan и будет прав: чтение всего файла ровно за один миг быстрее, чем поход через индекс.

Как понять, что индекс не используется

Открываем pg_stat_user_indexes:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Если в idx_scan ноль уже неделю, а индекс при этом большой — кандидат на удаление. Перед удалением полезно подождать пика нагрузки: иногда индексы используются только для редких отчётов.

Шпаргалка

Точные сравнения, диапазоны, сортировка — B-tree.
jsonb с @>, массивы, полнотекст, LIKE по подстроке — GIN.
Геометрия и интервалы с пересечениями — GIST.
Огромные таблицы с физически отсортированной колонкой — BRIN.
Условные индексы — WHERE в DDL.
Сравнения с функциями — индекс по выражению.

Главное правило, которое я держу в голове: индекс не обязан существовать сразу. Часто проще запустить запрос, посмотреть в EXPLAIN, что он делает Seq Scan, и только тогда добавить индекс под конкретный паттерн доступа. Чем меньше индексов — тем быстрее запись и тем проще жить с миграциями.