Индексы PostgreSQL: когда выбирать B-tree, GIN, GiST и BRIN

В PostgreSQL 17 правильный индекс зависит не только от столбца, но и от того, что делает запрос: сравнивает значения, ищет элементы внутри JSONB или отбирает строки по диапазону дат. Ошибка стоит места на диске и замедляет запись, а подходящий индекс не гарантирует ускорения, если планировщик считает последовательное чтение дешевле.

Сначала сопоставьте индекс с запросом

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

Термин — operator class, или операторный класс: набор правил, который связывает метод индекса с типом данных и операторами. Например, для JSONB выбор класса влияет на то, какие запросы сможет обслужить GIN.

B-tree: значения, диапазоны и сортировка

Если запрос ищет строку по равенству, сравнивает значения через больше или меньше, либо сортирует по столбцу, обычно начинают с B-tree. Это метод по умолчанию для CREATE INDEX. Он подходит, например, для номера заказа, даты или адреса электронной почты.

CREATE INDEX orders_created_at_idx
ON orders USING btree (created_at);

SELECT id, created_at
FROM orders
WHERE created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
ORDER BY created_at;

Пример предполагает, что в таблице orders есть столбцы id и created_at. Индекс может помочь и фильтрации, и порядку выдачи, но PostgreSQL не обязан его выбирать: для небольшой таблицы последовательное чтение может оказаться дешевле.

Для составного B-tree важен порядок столбцов. Индекс по (customer_id, created_at) обычно полезнее запросам, которые ограничивают customer_id, а затем работают с датой. Он не становится автоматически равноценным индексу только по created_at. Проверяйте реальные условия фильтрации, а не складывайте в индекс все столбцы из запроса.

GIN: когда ищут внутри значения

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

CREATE INDEX products_attributes_gin_idx
ON products USING gin (attributes);

SELECT id
FROM products
WHERE attributes @> '{"color":"black"}'::jsonb;

Здесь предполагается, что attributes имеет тип jsonb. Индекс со стандартным классом GIN для JSONB поддерживает больше типов проверок, в том числе проверку наличия ключа; класс jsonb_path_ops компактнее для ряда запросов на вхождение, но не поддерживает все те же операции. Выбирайте его только после проверки конкретных запросов в документации PostgreSQL 17.

Цена GIN особенно заметна на часто обновляемых данных: изменение одного JSON-документа может затронуть несколько индексируемых элементов. Если таблица активно принимает записи, измеряйте не только чтение, но и скорость вставки и обновления, размер индекса и задержку обслуживания.

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

GiST — обобщённая индексная структура, а не готовый ответ для любого типа столбца. Её возможности задаёт операторный класс. Встроенные классы применяются, например, к диапазонам и геометрическим типам; для некоторых типов доступны проверки пересечения и поиск ближайших объектов.

Если сервис ищет интервалы бронирования, GiST для столбца-диапазона может подходить для запроса на пересечение диапазонов. Для географических или геометрических данных сперва проверьте, что нужный оператор и нужный вариант поиска поддерживает класс именно для используемого типа. Не переносите выводы о GiST для одного типа на другой.

У GiST возможны приближённые результаты на этапе индексного поиска: PostgreSQL может перечитать найденные строки и проверить условие по исходным данным. В плане запроса это видно по дополнительной проверке условия. Само наличие такой проверки не означает, что индекс сломан.

BRIN: маленький индекс для больших упорядоченных таблиц

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

CREATE INDEX events_created_at_brin_idx
ON events USING brin (created_at);

SELECT count(*)
FROM events
WHERE created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00';

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

Выбирайте индекс по форме запроса и устройству данных, а не по названию типа столбца. Самый компактный индекс бесполезен, если записи в таблице перемешаны, а самый мощный — если он тормозит каждое обновление.

Как выбрать и проверить на своём запросе

1. Возьмите несколько частых запросов из приложения и выпишите условия фильтрации, сортировку и операторы. Проверка: для каждого запроса понятно, ищете ли вы значение целиком, элементы внутри значения или диапазон страниц.

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

3. Создайте индекс на тестовой копии с репрезентативным объёмом данных. Проверка: размер индекса и время записи приемлемы, а не только ускорился один SELECT.

4. Сравните план и фактическое выполнение до и после через EXPLAIN (ANALYZE, BUFFERS). Проверка: план показывает, сколько строк и страниц прочитано и использовался ли индекс; выводы сделаны на тех же условиях и данных.

ANALYZE внутри этой команды действительно выполняет запрос. На тяжёлой выборке запускайте её с осторожностью и не используйте бездумно для запросов, которые изменяют данные.

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

• Не ставьте GIN на столбец только потому, что его тип — JSONB. Если запросы ищут документ целиком или выполняют обычное сравнение, сначала проверьте, подходит ли B-tree или другая схема.

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

• Не берите GiST «для геоданных», не сверив тип столбца, оператор и операторный класс. Совпадение по названию технологии не гарантирует поддержку нужного запроса.

• Не создавайте несколько похожих индексов без замеров. Каждый из них занимает место и добавляет работу при изменении таблицы; планировщик при этом может предпочесть последовательное чтение.

Что делать дальше

Начните с самых дорогих запросов, а не с полного списка столбцов таблицы. Для каждого сохраните план EXPLAIN (ANALYZE, BUFFERS), проверьте объём чтения, затем сравните его после добавления одного индекса. Для BRIN отдельно оцените связь значения с физическим порядком строк; для GIN и GiST — поддержку нужного оператора и цену записи. Сравнение индексов по этим условиям полезнее универсального правила «какой метод быстрее».