B-tree vs GIN vs GiST: практическое руководство по индексам PostgreSQL

Что вы на самом деле выбираете при выборе индекса

Большинство проблем с индексами в PostgreSQL начинается одинаково: список кажется быстрым при 1 000 строк, а при 1 000 000 тормозит. Или поле поиска, которое работало в тестах, превращается в паузу на секунду в продакшне. В такой ситуации легко спросить: «Какой индекс лучше?» Но более правильный вопрос: «Что именно этот экран просит базу данных сделать?»

Одна и та же таблица может требовать разных типов индексов, потому что разные экраны читают её по-разному. Один вид фильтрует по одному статусу и сортирует по created_at. Другой делает полнотекстовый поиск. Третий проверяет, содержит ли JSON-поле ключ. Четвёртый ищет объекты рядом с точкой на карте. Это разные паттерны доступа — поэтому один тип индекса не победит во всех случаях.

Именно это вы выбираете при выборе индекса: как приложение обращается к данным. Вы в основном делаете точные совпадения, диапазоны и сортировку? Ищете внутри документов или массивов? Спрашиваете «что рядом с этой точкой» или «что пересекает этот диапазон»? Ответ определяет, подходит ли B-tree, GIN или GiST.

B-tree, GIN и GiST простым языком

Выбор индекса больше зависит не от типа колонки, а от того, что ваши запросы с ней делают. PostgreSQL выбирает индексы по операторам вроде =, <, @>, или @@, а не по тому, «text» ли это или «json». Вот почему одно и то же поле может требовать разных индексов на разных экранах.

B-tree: быстро для упорядоченных поисков

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

Типичный пример — админский список, отфильтрованный по status и отсортированный по created_at. B-tree-индекс на (status, created_at) поможет и фильтру, и сортировке. B-tree также часто используют для обеспечения уникальности (unique constraints).

GIN: быстро, когда каждая строка содержит много искомых ключей

GIN создан для вопросов вида «содержит ли эта строка этот термин/значение?», когда одна строка может соответствовать множеству ключей. Частые примеры — полнотекстовый поиск (документ содержит слова) и проверка принадлежности в JSONB/массиве (JSON содержит ключ/значение).

Представьте запись клиента с объектом JSONB preferences, и экран, который фильтрует пользователей, где preferences содержит {\"newsletter\": true}. Это стиль запроса для GIN.

GiST: гибкий для диапазонов, гео и похожести

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

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

Таблица решений для распространённых экранов (фильтры, поиск, JSON, гео)

Большинству приложений нужны лишь несколько типовых шаблонов индексов. Суть в том, чтобы сопоставить поведение экрана с операторами, которые используются в запросах.

Два индекса могут сосуществовать, если конечные точки разные. Например, админский список может требовать B-tree на (tenant_id, created_at) для быстрой сортировки, в то время как страница поиска нуждается в GIN для @@. Сохраняйте оба только если оба шаблона запросов встречаются часто.

Если вы не уверены, сначала посмотрите на оператор. Индексы помогают, когда база данных может пропустить большие участки таблицы.

Фильтры и сортировка: где обычно побеждает B-tree

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

Фильтры по равенству — классический случай. Колонки вроде status, user_id, account_id, type или tenant_id часто встречаются в дэшбордах и админках. B-tree может быстро найти соответствующие значения.

Диапазоны по времени или числам тоже хорошо ложатся на B-tree. Упорядоченная структура помогает при created_at >= ..., price BETWEEN ..., id > .... Если в UI есть «последние 7 дней» или «от 50 до 100$», B-tree делает именно то, что нужно.

Сортировка и пагинация — где B-tree может сэкономить больше всего. Если порядок индекса совпадает с ORDER BY, PostgreSQL часто может вернуть строки уже отсортированными, вместо сортировки большого набора в памяти.

-- Общий экран: "Мои открытые тикеты, сначала новые"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Правило для составных индексов простое: PostgreSQL эффективно использует только лидирующую часть индекса. Думайте «слева направо». С индексом (user_id, status, created_at), запросы, фильтрующие по user_id (и опционально status) выигрывают. Запрос, фильтрующий только по status, обычно не получит пользы.

Частичные индексы — сильное улучшение, когда экран заботится только о срезе данных. Часто это «только активные», «не soft-deleted» или «недавняя активность». Они держат индекс меньшим и быстрее.

Колонки с высокой кардинальностью и стоимость дополнительных индексов

Колонки с высокой кардинальностью имеют много уникальных значений, например user_id, order_id, email или created_at с точностью до секунды. Здесь индексы обычно выигрывают, потому что фильтр быстро сужает результаты до малого числа строк.

Колонки с низкой кардинальностью — противоположность: булевы и маленькие enum, например is_active, status IN ('open','closed') или plan IN ('free','pro'). Индекс на таких полях часто разочаровывает: каждое значение соответствует большой части таблицы, и PostgreSQL может корректно выбрать последовательное сканирование, поскольку обращение через индекс всё равно требует чтения многих страниц таблицы.

Ещё один тонкий издержек — извлечение строк. Даже если индекс быстро находит matching IDs, базе всё равно может понадобиться посетить основную таблицу за остальными колонками. Если запросу нужно только несколько полей, покрывающий индекс (covering index) может помочь, но он делает индекс большим и дороже в поддержке.

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

Практические рекомендации:

• Начинайте с 1–2 рабочих индексов на каждую загруженную таблицу, ориентируясь на реальные фильтры и сортировки.
• Отдавайте предпочтение колонкам с высокой кардинальностью, используемым в WHERE и ORDER BY.
• Будьте осторожны с индексированием булевых и маленьких enum, если они не комбинируются с другой селективной колонкой.
• Добавляйте индекс только после того, как точно назовёте конкретный запрос, который он ускорит.

Пример: список тикетов для поддержки, отфильтрованный по assignee_id (высокая кардинальность), выигрывает от индекса, тогда как is_archived = false в одиночку часто нет.

Экраны поиска: полнотекст, префиксы и «содержит»

Поля поиска кажутся простыми, но пользователи ожидают многое: несколько слов, разные формы слов и разумную ранжировку. В PostgreSQL это обычно полнотекстовый поиск: храните tsvector (подготовленный текст) и запрашивайте его с tsquery (то, что ввёл пользователь, распаршенное в термы).

Для полнотекстового поиска GIN — частый выбор, потому что он быстрый при ответе на «содержит ли документ эти термы?» на многих строках. Плата — более тяжёлые записи: вставки и обновления строк обходятся дороже.

GiST также может работать для полнотекстового поиска. Он часто меньше по размеру и дешевле при обновлениях, но обычно медленнее для чтения, чем GIN. Если ваши данные постоянно меняются (например, таблицы с событиями), этот баланс чтения/записи может сыграть роль.

Префиксный поиск — это не полнотекст

Префиксный поиск означает «начинается с», например поиск клиентов по префиксу e-mail. Это не то, для чего предназначен полнотекст. Для префиксных паттернов B-tree может помочь (часто с правильным классом операторов), потому что он совпадает с порядком строк.

Для «contains»-поиска типа ILIKE '%error%' B-tree обычно не поможет. Здесь уместны триграммы или другой подход к поиску.

Когда пользователи хотят фильтры плюс текстовый поиск

Большинство реальных экранов комбинирует поиск с фильтрами: status, assignee, диапазон дат, tenant и т. п. Практичная конфигурация обычно:

• GIN (иногда GiST) для колонки tsvector.
• B-tree индексы для самых селективных фильтров (например, account_id, status, created_at).
• Строгое правило «держать минимально», потому что слишком много индексов тормозит записи.

Пример: экран тикетов поддержки, который ищет «refund delayed» и фильтрует status = 'open' и конкретный account_id. Полнотекстовый индекс помогает найти релевантные строки, а B-tree помогает PostgreSQL быстро сузить по аккаунту и статусу.

Поля JSONB: выбор между GIN и целевыми B-tree индексами

JSONB удобен для гибких данных, но может привести к медленным запросам, если относиться к нему как к обычной колонке. Основное решение простое: вы ищете «везде в этом JSON» или же фильтруете по одному-двум путям, которые используются постоянно?

Для containment-запросов типа metadata @> '{"plan":"pro"}' GIN — обычный первичный выбор. Он создан для «содержит ли документ такую форму?» и поддерживает проверки наличия ключа, такие как ?, ?| и ?&.

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

-- Широкая поддержка containment и проверок ключей
CREATE INDEX ON customers USING GIN (metadata);

-- Целевые фильтры и сортировка по одному JSON-пути
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Хорошее эмпирическое правило:

• Используйте GIN, когда пользователи ищут по множеству ключей, тегов или вложенных структур.
• Используйте B-tree expression-индексы, когда вы постоянно фильтруете по конкретным путям.
• Индексируйте то, что реально появляется на экранах, а не всё подряд.
• Если производительность зависит от нескольких ключей JSON, подумайте о вынесении их в реальные столбцы.

Пример: экран поддержки может фильтровать тикеты по (metadata->>'priority') и сортировать по created_at. Индексируйте путь priority и обычный created_at. Широкий GIN имеет смысл только если пользователи ещё и ищут по тегам или вложенным атрибутам.

Гео и диапазонные запросы: где GiST наиболее уместен

Гео- и диапазонные экраны — это как раз случаи, где GiST часто становится очевидным выбором. GiST предназначен для вопросов «пересекает ли это», «содержит ли» или «находится ли рядом с» — а не «равно ли это значению».

Гео-данные обычно означают точки (расположение магазина), линии (маршрут) или полигоны (зона доставки). Частые экраны: «магазины рядом со мной», «вакансии в радиусе 10 км», «показать объекты внутри рамки карты» или «адрес в зоне обслуживания?» GiST-индекс (обычно через PostGIS geometry/geography) ускоряет пространственные операторы, позволяя базе пропустить большинство строк и не проверять каждую фигуру.

Диапазоны похожи. PostgreSQL поддерживает типы диапазонов вроде daterange и int4range, а типичный вопрос — пересечение: «сталкивается ли бронирование с существующим?» или «покажите подписки активные в эту неделю». GiST эффективно поддерживает операторы пересечения и вложения, поэтому он распространён в календарях, расписаниях и проверках занятости.

B-tree всё ещё может иметь значение на гео-подобных экранах. Многие страницы сначала фильтруют по tenant, status или времени, затем применяют пространственное условие, затем сортируют. Например: «только доставки моей компании за последние 7 дней, ближайшие в начале». GiST обрабатывает пространственную часть, а B-tree помогает с селективными фильтрами и сортировкой.

Как выбрать индекс шаг за шагом

Выбор индекса — это в основном про оператор, а не про имя колонки. Одна и та же колонка может требовать разных индексов в зависимости от того, используете ли вы =, >, LIKE 'prefix%', полнотекст, containment JSON или гео-расстояния.

Читайте запрос как чеклист: WHERE решает, какие строки подходят, JOIN — как таблицы связаны, ORDER BY — в каком порядке нужно вывести, а LIMIT — сколько строк вам реально нужно. Лучший индекс часто тот, который помогает найти первые 20 строк быстро.

Простой рабочий процесс, который подходит для большинства экранов:

1. Выпишите точные операторы, которые использует экран (например: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Начните с индекса, который соответствует наиболее селективному фильтру или дефолтной сортировке. Если экран сортирует по created_at DESC, начните с этого.
3. Добавляйте составной индекс, только если вы видите, что одни и те же фильтры идут вместе постоянно. Ставьте колонки с равенством первыми, затем диапазон, затем ключ сортировки.
4. Используйте частичный индекс, когда вы всегда фильтруете на подмножество (например, status = 'open'). Используйте выражение-индекс, когда вы запрашиваете вычисляемое значение (например, lower(email) для регистронезависимых поисков).
5. Валидируйте через EXPLAIN ANALYZE. Оставляйте индекс, если он значительно сокращает время выполнения и число прочитанных строк.

Конкретный пример: дашборд поддержки может фильтровать тикеты по status и сортировать по новым. B-tree на (status, created_at DESC) — хорошая первая попытка. Если тот же экран также фильтрует по JSONB-флагу meta @> '{"vip": true}', это другой оператор и обычно требует отдельного JSON-ориентированного индекса.

Распространённые ошибки, которые тратят время (и замедляют записи)

Одна из частых причин разочарований — выбрать «правильный» тип индекса для неправильного оператора. PostgreSQL может использовать индекс только когда запрос соответствует тому, на что индекс настроен. Если приложение использует ILIKE '%term%', обычный B-tree по этому тексту останется неиспользованным, и вы всё равно будете сканировать таблицу.

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

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

JSONB приносит свои подводные камни. Широкий GIN индекс хорош для гибких фильтров, но многие проверки по путям JSON быстрее с выражением-индексом на извлечённом значении. Если экран всегда фильтрует по payload->>'customer_id', индекс на этом выражении будет меньше и быстрее, чем индекс на весь документ.

И, наконец, каждый дополнительный индекс бьёт по записям. На часто обновляемых таблицах (тикеты, заказы) каждая вставка и обновление должны модифицировать все индексы.

Перед добавлением индекса остановитесь и спросите:

• Соответствует ли индекс точному оператору запроса?
• Можно ли заменить широкий многоколоночный индекс одним-двумя целевыми?
• Может ли это быть частичным индексом, чтобы избежать шума от низкой селективности?
• Для JSONB — не лучше ли выражение-индекс для нужного экрана?
• Достаточно ли таблица нагружена по записям, чтобы стоимость индекса перевесила выгоду для чтения?

Быстрые проверки перед тем, как добавить (или оставить) индекс

Перед созданием нового индекса конкретизируйте, что приложение реально делает. «Нужный» индекс часто превращается в медленные записи и лишнее хранение.

Начните с трёх главных экранов (или API-эндпоинтов) и выпишите их точный вид запроса: фильтры, порядок сортировки и что вводит пользователь. Многие «проблемы с индексами» — это на самом деле «непонятные запросы», особенно когда спорят о B-tree vs GIN vs GiST без указания оператора.

Простой чеклист:

• Выберите 3 реальных экрана и выпишите их точные WHERE и ORDER BY (включая направление и обработку NULL).
• Подтвердите тип оператора: равенство (=), диапазон (>, BETWEEN), префикс, contains, overlap или distance.
• Выберите по одному индексу на распространённый шаблон экрана, протестируйте и оставьте только те, которые значительно уменьшают время или чтение страниц.
• Если таблица write-heavy, будьте строги: лишние индексы умножают стоимость записи и могут усилить нагрузку на vacuum.
• Перепроверяйте после изменений функционала. Новый фильтр, новый дефолтный порядок или смена с «starts with» на «contains» может сделать старый индекс нерелевантным.

Пример: на дашборд добавляют дефолтную сортировку last_activity DESC. Если у вас индексирован только status, фильтр может остаться быстрым, но сортировка теперь добавляет лишнюю работу.

Пример: сопоставление реальных экранов с подходящим индексом

Таблица решений полезна только если вы умеете сопоставлять её с реальными экранами. Вот три частых экрана и какие индексы обычно подходят.

Практический способ применить это — начать с UI:

• Перечислите каждый контрол, который сужает результаты (фильтры).
• Запишите дефолтный порядок сортировки.
• Будьте конкретны по поведению поиска (полнотекст, starts-with, contains).
• Отметьте «особые» поля (JSONB, гео, диапазоны).

Следующие шаги: сделайте индексацию частью процесса разработки

Хорошие индексы следуют за вашими экранами: фильтры, которые люди кликают, порядок сортировки, которого они ожидают, и поиск, которым они реально пользуются. Рассматривайте индексацию как привычку в разработке, и вы избежите большинства сюрпризов с производительностью.

Делайте это повторяемо: определите 1–3 запроса, которые исполняет экран, добавьте самый маленький индекс, который им соответствует, протестируйте на реалистичных данных, затем удалите то, что не даёт эффекта.

Если вы строите внутренний инструмент или портал для клиентов, планируйте потребности индексов заранее, потому что такие приложения часто растут добавлением фильтров и новых списков. Если вы используете AppMaster, полезно рассматривать конфигурацию фильтров и сортировок каждого экрана как конкретный шаблон запроса и добавлять только те индексы, которые соответствуют реальным кликам.