Типы индексов: B-tree, Hash, GIN, GiST, BRIN

В уроке про индексы и EXPLAIN мы создали первый индекс и увидели, как Seq Scan сменился на Index Scan. Тот индекс был B-деревом, видом по умолчанию. Но у PostgreSQL их пять, и каждый отвечает на свой вопрос. С этого урока начинается кластер про оптимизацию запросов, и первым делом разберёмся, какой индекс для какого запроса.

Пять каталогов одной библиотеки

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

• Алфавитный каталог. Карточки по фамилиям авторов. Найдёшь и «Булгакова», и всех авторов от «Б» до «Г», и пройдёшь их по порядку.
• Номерок в гардеробе. Назвал номер 147, получил пальто. Быстро, но спросить «все пальто с номерами от 100 до 200» нельзя: номерки висят не по порядку.
• Предметный указатель. Слово «дракон» и рядом список всех книг, где оно встречается. Одна книга попадает во много строк указателя.
• Карта зала. Показывает, какие стеллажи стоят рядом и какие зоны пересекаются.
• Таблички на стеллажах. «Стеллаж 12: поступления за март». Не говорит, где лежит конкретная книга, зато занимает одну строчку на целый стеллаж.

У PostgreSQL на каждый из этих каталогов есть свой вид индекса. Посмотреть, какие виды есть в сервере, можно в таблице pg_am:

SELECT amname FROM pg_am WHERE amtype = 'i' ORDER BY amname;

 amname
--------
 brin
 btree
 gin
 gist
 hash
 spgist
(6 rows)

Шестой, spgist, нужен для редких задач вроде телефонных префиксов и точек на плоскости, его оставим. Вид выбирают словом USING в CREATE INDEX. Без него получается B-tree.

Таблица для опытов

Сгенерируем просмотры магазина: сто тысяч строк, у каждой идентификатор сессии, день и метки.

CREATE TABLE visits AS
SELECT g AS id,
       md5(g::text) AS session_id,
       date '2026-01-01' + (g / 400) AS visited_on,
       CASE WHEN g % 100 = 0 THEN ARRAY['promo', 'mobile']
            WHEN g % 3 = 0 THEN ARRAY['mobile']
            ELSE ARRAY['web'] END AS tags
FROM generate_series(1, 100000) AS g;

ANALYZE visits;

md5 превращает число в строку из 32 символов, похожую на настоящий идентификатор сессии. День растёт вместе с id: каждые 400 просмотров наступает следующий. Метка promo достаётся одному просмотру из ста. Оценки rows в планах ниже у тебя могут слегка отличаться: ANALYZE смотрит случайную выборку строк.

B-tree: алфавитный каталог

B-tree хранит значения отсортированными. Поэтому он отвечает почти на всё, что связано с порядком: =, <, >, BETWEEN, IN, IS NULL и ORDER BY. Устройство дерева разобрано в уроке теории. Если не знаешь, какой индекс нужен, начинай с B-tree: в девяти случаях из десяти он и правильный.

Hash: номерок в гардеробе

Hash-индекс вычисляет из каждого значения короткое число, хеш, и раскладывает строки по корзинам с этими номерами. Сравнивать хеши на «больше и меньше» бессмысленно, поэтому такой индекс умеет одно: искать на точное равенство.

CREATE INDEX visits_session_hash ON visits USING hash (session_id);

EXPLAIN SELECT * FROM visits WHERE session_id = 'c4ca4238a0b923820dcc509a6f75849b';

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using visits_session_hash on visits  (cost=0.00..8.02 rows=1 width=71)
   Index Cond: (session_id = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(2 rows)

А сравнение тот же индекс не ускорит:

EXPLAIN SELECT * FROM visits WHERE session_id < 'a';

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on visits  (cost=0.00..2594.00 rows=62999 width=71)
   Filter: (session_id < 'a'::text)
(2 rows)

Зачем тогда hash, если B-tree умеет и равенство? Для длинных значений hash меньше: B-tree хранит строку целиком, а hash только её короткий хеш. На наших сессиях B-tree занял 5792 kB, а hash 4112 kB. Если по колонке ищут только на равенство, а значения длинные, hash стоит попробовать. В старых статьях можно встретить совет обходить hash стороной: до 10-й версии он не писался в журнал и не переживал сбой. Сейчас это не так.