Какие типы индексов есть в PostgreSQL и когда выбрать GIN, GiST или BRIN вместо B-tree?
Проверила команда Сопровод · Обновлено
Короткий ответ
В PostgreSQL есть B-tree, Hash, GiST, SP-GiST, GIN и BRIN. B-tree по умолчанию закрывает равенство, диапазоны и сортировку. GIN ищет элемент внутри значения: тег в массиве, ключ в jsonb, слово в тексте. GiST подходит для геометрии и диапазонов. BRIN хранит минимум и максимум по диапазонам страниц и годится для огромных таблиц, где значения растут в порядке записи.
Почему этот вопрос задают
B-tree ставят по привычке, хотя поиск по массиву или jsonb он не ускоряет. От senior ждут, что он выберет индекс под оператор и порядок данных и назовет цену каждого типа.
Как рассуждать
- Начните с B-tree: он закрывает равенство, диапазоны, сортировку и уникальность
- Назовите признак для GIN: ищут элемент внутри значения — тег, ключ
jsonb, слово - Объясните BRIN через физический порядок: строки журнала лежат на диске по времени
- Упомяните GiST для геометрии и диапазонов и проверьте выбор через
EXPLAIN
Ответ с примером
Сайт недвижимости ищет объявления, у которых в массиве tags есть и «терраса», и «парковка». B-tree по массиву сравнивает массивы целиком и для @> бесполезен. GIN хранит каждый тег отдельно со списком объявлений, поэтому в плане идет Bitmap Index Scan по listings_tags_idx. Так же GIN работает для jsonb и полнотекстового поиска, зато каждая вставка обновляет много записей индекса. Журнал показов растет по времени, и строки одного дня лежат рядом на диске. BRIN хранит для каждого диапазона страниц только минимум и максимум viewed_at: в примере он почти в 200 раз меньше B-tree, а запрос за день по нему отсекает лишние страницы. GiST берут для геометрии, диапазонов и поиска ближайших соседей, Hash — только для равенства.
CREATE INDEX ON listings USING gin (tags);
EXPLAIN (COSTS OFF)
SELECT id, title FROM listings WHERE tags @> ARRAY['терраса', 'парковка'];
CREATE INDEX views_btree ON listing_views (viewed_at);
CREATE INDEX views_brin ON listing_views USING brin (viewed_at);
SELECT pg_relation_size('views_btree') AS btree_bytes,
pg_relation_size('views_brin') AS brin_bytes; QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on listings
Recheck Cond: (tags @> '{терраса,парковка}'::text[])
-> Bitmap Index Scan on listings_tags_idx
Index Cond: (tags @> '{терраса,парковка}'::text[])
(4 rows)
btree_bytes | brin_bytes
-------------+------------
4513792 | 24576
(1 row)B-tree, GIN, GiST, BRIN и Hash: в чем разница
| Тип | Что ускоряет | Пример | Цена |
|---|---|---|---|
| B-tree | равенство, диапазоны, сортировку, проверку уникальности | цена, дата, трек-номер | растет вместе с таблицей |
| GIN | поиск элемента внутри значения | теги, jsonb, полнотекстовый поиск | дорогая вставка и обновление |
| GiST | пересечения и близость | точки на карте, диапазоны дат | в полнотекстовом поиске неточный, строки перепроверяются |
| BRIN | диапазоны по столбцу в порядке записи | журнал показов по времени | бесполезен при случайном порядке |
| Hash | только равенство | длинные строки вроде URL | нет диапазонов, сортировки и уникальности |
Как ответить сильнее
Что добавит опытный кандидат
Что спросят следом
Доступно по подписке
Приемы опытных и готовые ответы
Частые ошибки
«GIN всегда лучше B-tree, он новее»
Скажите, что GIN нужен для поиска внутри составных значений. На равенство и диапазоны по обычному столбцу B-tree быстрее и дешевле на запись
«BRIN подойдет для любой большой таблицы»
Уточните, что BRIN работает, когда значения идут в порядке записи. При случайном порядке каждый диапазон страниц покрывает почти все значения, и индекс ничего не отсекает
«Hash-индексы ненадежны, их нельзя использовать»
Поправьте: с PostgreSQL 10 Hash-индексы пишутся в WAL и восстанавливаются после сбоя. Их редко выбирают из-за узких возможностей
«Индекс по
jsonbстроят как обычный B-tree»Объясните, что B-tree по
jsonbсравнивает документы целиком. Для поиска по ключам нужен GIN с классом по умолчаниюjsonb_ops, а если нужен только@>, обычно компактнееjsonb_path_ops
Сигналы для интервьюера
Сильный ответ
Выбирает тип индекса по оператору запроса и порядку данных, называет цену каждого и подтверждает выбор через EXPLAIN
Тревожный сигнал
Знает, какой оператор ускоряет каждый тип, но не может назвать цену GIN на вставке и обновлении
Красный флаг
Выбирает индекс по названию типа и не может сказать, какой оператор запроса он ускоряет
Как еще могут спросить
- Чем GIN-индекс отличается от B-tree?
- Когда в PostgreSQL выгоден BRIN-индекс?
- Какой индекс построить для поиска по массиву или
jsonb? - Расскажите, какие методы индексации есть в PostgreSQL











