Типы индексов в 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), проверкой IS NULL / IS NOT NULL и — с оговоркой — поиском по префиксу (LIKE 'prefix%'). Оговорка важная: обычный индекс ускорит такой LIKE только в базе с «сишной» сортировкой (C). В базе с русской локалью, а это почти всегда так, порядок символов другой, и для префиксного поиска нужен отдельный индекс с классом операторов text_pattern_ops. А UNIQUE-ограничения и первичные ключи PostgreSQL всегда строит на B-tree.

У индекса с text_pattern_ops есть обратная сторона: значения в нём разложены по кодам символов, а не по правилам языка. Префиксный LIKE и точное = он закрывает, а обычный ORDER BY last_name и условия вида last_name > 'Иванов' мимо него — база отсортирует и отберёт перебором. Поэтому на практике либо держат на колонке два индекса, обычный и префиксный, либо объявляют саму колонку с сортировкой C и обходятся одним.

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

живой пример

SELECT id, status, created_at
FROM orders
WHERE created_at >= DATE '2026-08-10' AND created_at < DATE '2026-08-20'
ORDER BY created_at;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

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

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

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

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

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

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

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

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

-- Поиск по 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 — буфер отложенных изменений: новые записи складываются в список ожидания, а в само дерево попадают позже. Вставки от этого ускоряются, но цену платит не тот, кто её вызвал: список разбирается на той вставке, которая переполнила лимит gin_pending_list_limit, и именно она внезапно занимает секунды вместо миллисекунд. Плюс чтение обязано просматривать список ожидания в дополнение к индексу, пока он не разобран. Отсюда правило: в интерактивном пути, где важен предсказуемый хвост задержек, fastupdate выключают (WITH (fastupdate = off)), в пакетной загрузке — оставляют.

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

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 EXTENSION IF NOT EXISTS postgis;
CREATE TABLE shop (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location geometry(Point, 4326) NOT NULL
);

CREATE INDEX ix_shop_location ON shop USING gist (location);
SELECT * FROM shop ORDER BY location <-> ST_SetSRID(ST_Point(37.6, 55.7), 4326) LIMIT 10;

Второй пример — уже не чистый PostgreSQL: тип geometry, функция ST_Point и оператор расстояния <-> приходят с расширением PostGIS, на голой базе таких слов нет. И система координат у точки в запросе должна совпадать с системой координат колонки, иначе база остановит запрос на несовпадении — отсюда ST_SetSRID. Подробности — в статье про PostGIS.

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.
• Точечный поиск (=) он почти не ускоряет: весь диапазон в 128 страниц всё равно придётся прочитать.

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

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

BRIN настраивается, и без настройки его выводы звучат как приговор. Главная ручка — pages_per_range (по умолчанию 128): сколько страниц таблицы описывает одна запись индекса. Меньше значение — индекс точнее и крупнее, больше — компактнее и грубее; на таблице, где данные хорошо упорядочены, уменьшение до 32 часто даёт заметно меньше лишних чтений.

CREATE INDEX ix_events_occurred_brin ON events USING brin (occurred_at) WITH (pages_per_range = 32);

Вторая ручка — сводка по новым страницам. По умолчанию свежие страницы попадают в индекс не сразу: их подытоживает autovacuum. Параметр autosummarize = on включает это агрессивнее, а руками сводку добирают вызовом brin_summarize_new_values('ix_events_occurred_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);

В обычном приложении встречается крайне редко; если встретите, то в аналитике трафика, где ищут по префиксу адреса или подсети.

Смешанные индексы: btree_gin и btree_gist

Отдельная задача — когда в условии рядом стоят обычная колонка и что-то из мира GIN или GiST: «заказы этого продавца, у которых в атрибутах есть ключ gift» или «брони этой комнаты, пересекающиеся с периодом». Два отдельных индекса база объединит битовой картой, но это дороже одного составного, а составной обычным способом не собрать: B-дерево не умеет индексировать jsonb, GIN не умеет обычное сравнение.

Расширения btree_gin и btree_gist закрывают ровно этот разрыв — они добавляют поддержку обычных типов в GIN и GiST, после чего колонки можно смешивать в одном индексе:

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX ix_products_seller_attrs ON products USING gin (seller_id, attributes jsonb_path_ops);

btree_gist нужен ещё и для EXCLUDE-ограничений вида «одна комната, пересекающиеся периоды»: сравнение комнаты по равенству берётся как раз оттуда. Про сам EXCLUDE — в статье про массивы и диапазоны.

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

Задача	Тип индекса
=, <, >, BETWEEN обычной колонки	B-tree
ORDER BY	B-tree
LIKE 'prefix%'	B-tree + text_pattern_ops
Foreign key	B-tree
UNIQUE ограничение	B-tree
JSONB @>, ?	GIN
Поиск по элементу массива	GIN
Полнотекстовый поиск (частые чтения)	GIN
Полнотекстовый поиск (частые записи)	GiST
Геометрия PostGIS, поиск ближайших	GiST
Диапазонные типы (tstzrange, int4range)	GiST
EXCLUDE для непересечений	GiST + btree_gist
LIKE '%substring%'	GIN + pg_trgm
Журнал с возрастающим timestamp	BRIN
IP-префиксы	SP-GiST

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

Обычный B-tree не умеет LIKE '%слово%'. Чтобы понять почему, посмотрите на поиск по началу строки — с ним дерево справляется:

живой пример

SELECT 'LIKE ''K%''' AS how, last_name
FROM customer
WHERE last_name LIKE 'K%'
UNION ALL
SELECT 'диапазон K..L', last_name
FROM customer
WHERE last_name >= 'K' AND last_name < 'L';

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Обе половины вернут одни и те же фамилии: LIKE 'K%' — это и есть диапазон от K до L, а диапазон дерево проходит за один спуск. У подстроки диапазона нет: «анов» может начаться в любом месте значения, спускаться не к чему. Эту задачу решает расширение 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 '%иван%';

-- и нечёткий поиск: оператор %, а не функция similarity()
SELECT * FROM customer WHERE full_name % 'иванв';

Здесь легко ошибиться. Условие similarity(full_name, 'иванв') > 0.4 выглядит понятнее, но индекс его не ускорит: база не умеет заглядывать внутрь вызова функции и честно посчитает похожесть для каждой строки таблицы. Индекс работает через оператор %, а порог похожести для него задаётся отдельно — параметром pg_trgm.similarity_threshold (по умолчанию 0,3).

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

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

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

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

Запрос попадёт в такой индекс, только если планировщик выведет условие индекса из условия запроса. Проще всего повторить его дословно:

живой пример

SELECT id, status, total_amount
FROM orders
WHERE customer_id = 'cus-01'
  AND status IN ('PENDING_PAYMENT', 'PAID', 'SHIPPED')
ORDER BY created_at;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

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

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

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

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

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

Глубже: индекс на живой таблице: CONCURRENTLY и REINDEXрасширенное

Все примеры выше создают индекс командой CREATE INDEX, и на стенде это верно. На боевой таблице обычный CREATE INDEX берёт блокировку SHARE: чтение продолжается, а любая запись в таблицу ждёт, пока индекс не будет построен. На таблице заказов в сто миллионов строк это десятки минут, в которые сервис не может создать ни одного заказа.

CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);
DROP INDEX CONCURRENTLY ix_orders_old;
REINDEX INDEX CONCURRENTLY ix_orders_customer;

CONCURRENTLY строит индекс в два прохода, не мешая записи. Цена: дольше, нельзя внутри транзакции (Liquibase и Flyway для такой миграции требуют выполнения вне транзакции), а если построение упало, в базе остаётся невалидный индекс, который занимает место и не используется; его видно в \d пометкой INVALID, и его удаляют и строят заново. REINDEX CONCURRENTLY (PostgreSQL 12+) перестраивает раздутый индекс тем же способом.

Практический порядок для миграции с индексом: CONCURRENTLY, вне транзакции, с statement_timeout побольше, а после проверка pg_index.indisvalid. И заранее ответ на вопрос «а нужен ли индекс вообще»: каждый индекс замедляет вставку и обновление, и таблица с двенадцатью индексами пишется заметно медленнее, чем с тремя.

Цену индекса полезно представлять числами. Он занимает место — обычно от 10 до 30 % размера таблицы на одну колонку, а GIN по jsonb бывает и больше самой таблицы. Он замедляет запись: каждая вставка обновляет таблицу и все её индексы, поэтому INSERT в таблицу с восемью индексами делает девять записей вместо одной. И он раздувается: UPDATE в PostgreSQL создаёт новую версию строки, а с ней новые записи во всех индексах, где участвует изменённая колонка; старые записи остаются мусором, пока их не уберёт автоматическая уборка. Индекс, который раздулся сильно (проверяется сравнением его размера с оценкой «сколько должно быть»), перестраивают REINDEX CONCURRENTLY.

Отсюда и правило про количество: «сколько индексов много» зависит от отношения чтения к записи, но на таблице с активной записью больше пяти-шести — повод пересмотреть. Какие из них не нужны, база скажет сама: счётчики обращений лежат в pg_stat_user_indexes, и idx_scan = 0 за пару недель работы означает, что индекс никто не использует. Подробнее про это — в статье про селективность и статистику.

Коротко

• B-tree — выбор по умолчанию: сравнения, сортировка, диапазоны, UNIQUE, внешние ключи.
• GIN — JSONB, массивы, полнотекстовый поиск. Быстрое чтение, медленная запись.
• GiST — диапазоны, геометрия, EXCLUDE, kNN. Быстрая запись, медленнее чтение.
• BRIN — журналы и метрики с возрастающей меткой времени: килобайты индекса на гигабайты данных, но только при физической упорядоченности.
• Hash и SP-GiST — узкие случаи (точное равенство; IP и URL-префиксы), в обычном приложении не нужны.
• pg_trgm + GIN решает LIKE '%подстрока%' и нечёткий поиск, а частичный индекс — таблицу, где в запросы попадает меньшинство строк.
• На боевой таблице индекс создают и удаляют с CONCURRENTLY, вне транзакции; упавшее построение оставляет INVALID-индекс, его сносят и строят заново.
• Индекс стоит места (10–30 % таблицы, GIN по jsonb — больше), замедляет запись пропорционально их числу и раздувается от обновлений; лишние ищут по idx_scan = 0.
• BRIN настраивают: pages_per_range, autosummarize и brin_summarize_new_values после массовой загрузки; fastupdate у GIN переносит цену на случайную вставку.
• btree_gin и btree_gist позволяют собрать один индекс из обычной колонки и jsonb или диапазона — иначе пришлось бы держать два и надеяться на битовую карту.

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

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