Другие индексы: GIN, GiST, SP-GiST, BRIN и Hash

B-tree подходит для равенства, диапазона и порядка, но не для каждой структуры данных. PostgreSQL предлагает другие методы доступа — способы организации индекса.

Одного названия метода недостаточно. Класс операторов (operator class) связывает тип данных и конкретные операции с этим методом. Например, два класса GIN для JSONB поддерживают разные операторы.

Выбор начинается с операторов реального запроса, распределения данных и стоимости записи.

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

jsonb_path_ops подходит для @> и части jsonpath, но не для всех операций проверки ключа. Общий jsonb_ops шире, но обычно больше.

Режим fastupdate сначала накапливает новые записи в ожидающем списке, а позже сливает их с основным индексом. Если обслуживание нерегулярно, отдельный запрос может столкнуться с дорогим слиянием. Наблюдайте не только чтение, но и запись, размер ожидающего списка и VACUUM.

GiST — основа для разных деревьев, а не один конкретный алгоритм. С помощью подходящего класса он поддерживает пересечение диапазонов &&, вхождение и поиск ближайшего объекта.

SP-GiST разбивает пространство на непересекающиеся области. Он подходит некоторым точкам, префиксным деревьям и неравномерным структурам. Сам факт, что данные «пространственные», ещё не доказывает преимущество SP-GiST: сравнивайте поддерживаемые операторы и планы.

BRIN хранит краткое описание группы соседних страниц, например минимальное и максимальное время. Поэтому индекс получается очень компактным.

Он эффективен, когда значение связано с физическим порядком. В журнале, который только дописывается по occurred_at, соседние страницы содержат близкое время. Поиск одного дня отбросит большинство диапазонов.

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

Параметр pages_per_range задаёт число страниц в одной сводке: большая группа уменьшает индекс, но увеличивает ложные совпадения. Проверяйте корреляцию, Heap Blocks: lossy и фактическое чтение.

Hash-индекс поддерживает равенство. Современный PostgreSQL журналирует его в WAL. Но B-tree тоже умеет равенство и дополнительно диапазон и порядок, поэтому Hash выбирают только после измерения конкретного запроса, размера и записи.

CREATE INDEX CONCURRENTLY разрешает изменения таблицы, но дольше работает, ждёт старые транзакции и после ошибки может оставить недействительный индекс. Нужны ограничения времени ожидания, наблюдение через pg_stat_progress_create_index и заранее описанное удаление неудачного результата.

В лаборатории 24 вы сравните два класса GIN для JSONB и BRIN на упорядоченных и перемешанных по времени событиях.

Подробнее: типы индексов.