В прошлом уроке мы разобрали B-tree - универсальный индекс postgresql, который ускоряет сравнения и сортировку. Но B-tree умеет не всё. Если вы ищете точку на карте, проверяете пересечение диапазонов дат, ищете элемент внутри массива или строите полнотекстовый поиск, B-tree вам не поможет: его модель упорядоченного дерева тут просто не подходит. Этот урок про остальные методы доступа: Hash, GiST, SP-GiST, GIN и BRIN. Разберём, какую задачу решает каждый и как выбрать индекс под конкретный запрос, а не по привычке.
Как это работает
Главная идея: индекс - это не одна структура на все случаи, а семейство методов доступа. Каждый метод понимает свой набор операторов. B-tree понимает = < > и сортировку, потому что хранит ключи в порядке. Другие методы хранят данные иначе и поэтому отвечают на другие вопросы.
Hash хранит не сами значения, а их хеши, и умеет ровно один оператор - равенство. Диапазоны и сортировку он не поддерживает в принципе. До PostgreSQL 10 hash-индексы не писались в WAL и не переживали сбой, поэтому их избегали. Сейчас они полноценные, но выигрывают у B-tree редко - разве что на очень длинных ключах, где хеш короче самого значения.
GiST это обобщённое сбалансированное дерево, в узлах которого лежат не точные ключи, а описания областей: ограничивающий прямоугольник для геометрии, объединённый диапазон для range-типов. Спускаясь по дереву, мы отсекаем ветки, которые точно не пересекаются с запросом. Поэтому GiST отвечает на вопросы вида пересекается, содержит, ближайший сосед (KNN, оператор расстояния), и под него заточены геометрия, диапазоны, исключающие ограничения и полнотекстовый поиск.
SP-GiST это пространственное разбиение: quad-дерево, k-d дерево, radix-дерево (trie). Оно не балансируется, а делит пространство на непересекающиеся куски. Это выигрывает на неравномерно распределённых данных - точки, IP-адреса, префиксы строк, телефонные коды, где B-tree или GiST давали бы перекос.
GIN это инвертированный индекс. Если в одной строке много значений (элементы массива, ключи jsonb, слова документа), GIN строит отображение элемент -> список строк, где он встречается. Отсюда его сила: операторы содержит (@>), есть ли ключ (?), полнотекст @@. Цена - запись медленнее, потому что одна строка трогает много записей индекса (отсюда буфер fastupdate).
BRIN устроен принципиально иначе: он не указывает на строки, а хранит сводку (например min/max) по блокам таблицы. Он крошечный, но полезен только когда физический порядок строк совпадает с порядком значений - типичный случай это лог или таблица, растущая по времени или по возрастающему id. На перемешанных данных BRIN бесполезен. В PostgreSQL 16 ускорили параллельную сборку BRIN, но сама логика прежняя.
SQL и примеры
В демобазе Авиаперевозки у аэропортов есть координаты типа point. GiST по ним даёт поиск ближайших аэропортов:
Код: Выделить всё
CREATE INDEX idx_airports_coord ON airports USING gist (coordinates);
-- 5 аэропортов, ближайших к точке (оператор <-> это KNN)
SELECT airport_name, city
FROM airports
ORDER BY coordinates <-> point '(37.62, 55.75)'
LIMIT 5;
Код: Выделить всё
ALTER TABLE tickets ADD COLUMN route text[];
CREATE INDEX idx_tickets_route ON tickets USING gin (route);
-- быстро находим билеты, чей маршрут содержит и SVO, и LED
SELECT ticket_no FROM tickets
WHERE route @> ARRAY['SVO','LED'];
Код: Выделить всё
CREATE INDEX idx_fl_brin ON flights USING brin (scheduled_departure)
WITH (pages_per_range = 64);
-- запрос по узкому окну дат отсекает целые диапазоны блоков
SELECT count(*) FROM flights
WHERE scheduled_departure >= '2017-08-01'
AND scheduled_departure < '2017-08-08';
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- запрет на пересекающиеся периоды для одного ресурса:
-- EXCLUDE USING gist (resource_id WITH =, during WITH &&)
Код: Выделить всё
CREATE INDEX idx_bp_ticket_hash ON boarding_passes USING hash (ticket_no);
- Ставят hash, ожидая ускорения диапазонов и ORDER BY - он умеет только =, для остального остаётся B-tree.
- BRIN на таблице, где данные перемешаны (нет корреляции физического порядка и значения), бесполезен: проверьте correlation в pg_stats.
- GIN сильно замедляет INSERT/UPDATE. На потоке вставок не забывайте про fastupdate и gin_pending_list_limit.
- Забывают, что нужный класс операторов лежит в расширении: pg_trgm для триграмм, btree_gist чтобы смешать = и && в одном GiST.
- GIN и GiST по jsonb/массивам бывают огромными - проверяйте размер через \di+ или pg_relation_size, прежде чем катить в прод.
- Создание индекса блокирует таблицу. На бою всегда CREATE INDEX CONCURRENTLY (подробно в следующих уроках).
- Разверните демобазу bookings и подключитесь: psql -d demo.
- Создайте GiST-индекс по airports(coordinates) и выполните KNN-запрос с ORDER BY coordinates <-> point. Сравните план через EXPLAIN до и после индекса.
- Добавьте в tickets текстовый массив route, заполните парой значений и постройте GIN-индекс; проверьте оператор @>.
- Постройте BRIN по flights(scheduled_departure) и сравните его размер с B-tree по тому же столбцу через \di+.
- Посмотрите correlation для scheduled_departure в pg_stats и объясните, почему BRIN тут уместен.
- Через EXPLAIN (ANALYZE, BUFFERS) сравните чтение блоков BRIN против B-tree на запросе по неделе.
- Почему hash-индекс не ускоряет ORDER BY и запросы с диапазоном?
- В чём принципиальная разница между GiST и SP-GiST по способу организации данных?
- Какой индекс выберете для поиска по jsonb-документам и почему именно GIN?
- От какого свойства таблицы зависит, будет ли BRIN полезен?
- Зачем нужно расширение btree_gist и какую задачу решает EXCLUDE-ограничение?
- Что изменилось для hash-индексов начиная с PostgreSQL 10?
