В чем разница между B-Tree и Hash индексами в PostgreSQL?

Question

В чем разница между B-Tree и Hash индексами в PostgreSQL?

Тип вопроса: Базы данных Вероятность: 22% Категория: Golang Разработчик

Ответ

Это два разных типа индексов в PostgreSQL, оптимизированных для разных задач.

B-Tree Индекс (по умолчанию)

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

Поддерживаемые операторы: =, >, <, >=, <=, BETWEEN, IN, IS NULL, IS NOT NULL, а также LIKE с шаблонами, начинающимися с начала строки (например, 'prefix%').
Производительность: O(log n). Очень быстр для большинства сценариев.
Ключевая особенность: Благодаря сортировке, может использоваться для ускорения ORDER BY и эффективен для запросов по диапазону.

-- Создается по умолчанию, если не указан тип
CREATE INDEX idx_users_created_at ON users (created_at);

Hash Индекс

Специализированный индекс, оптимизированный исключительно для запросов на точное равенство.

Поддерживаемые операторы: Только =.
Производительность: O(1) в среднем. Теоретически быстрее B-Tree для точечных запросов, но может деградировать при большом количестве коллизий.
Ключевые особенности:
- Не хранит данные в отсортированном виде, поэтому бесполезен для ORDER BY и запросов по диапазону.
- До PostgreSQL 10 не был WAL-логируемым, что делало его неустойчивым к сбоям (требовал REINDEX после сбоя). Сейчас эта проблема решена.

-- Явно указываем тип индекса USING HASH
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Когда что использовать?

B-Tree: Ваш выбор в 99% случаев. Используйте его всегда, если не уверены. Он незаменим, если вам нужны запросы по диапазону, сортировка или поиск по префиксу.
Hash: Рассматривайте только для очень больших таблиц, где запросы выполняются исключительно по точному равенству (WHERE email = '...'), и вы хотите выжать максимальную производительность для этих конкретных запросов.

Ответ 18+ 🔞

Смотри, вот тебе, блядь, простая разводка по этим индексам в PostgreSQL, чтобы не путаться, как последний распиздяй.

B-Tree Индекс (дефолтный, он же наш, родной)

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

Что ловит: =, >, <, >=, <=, BETWEEN, IN, IS NULL, IS NOT NULL. Даже LIKE 'привет%' потянет, если с начала строки искать.
Скорость: O(log n). Быстро, блядь, как чёрт из табакерки.
Фишка главная: Раз уж всё отсортировано, то и ORDER BY твой он разгонит, и диапазоны (WHERE date BETWEEN ...) — всё ему по плечу.

-- Так и создаётся, даже думать не надо. Просто создай.
CREATE INDEX idx_users_created_at ON users (created_at);

Hash Индекс (узкий специалист, ебаный гений)

А этот, сука, туп как пробка, но в своём деле — мастер. Заточен только под одно: точное равенство. Всё. Больше нихуя.

Что ловит: ТОЛЬКО =. Слышишь? Только знак равенства! Остальное — пошёл нахуй.
Скорость: O(1) в теории, то есть мгновенно. Но если коллизий дохуя — будет тормозить, как говно в проруби.
Фишки, блядь:
- Данные не сортирует, поэтому про ORDER BY и диапазоны можешь сразу забыть.
- Раньше, до десятой версии, был ненадёжный, как зарок алкаша — после сбоя надо было переделывать. Сейчас вроде починили, но осадочек-то остался.

-- Вот тут надо явно сказать, что ты хочешь именно HASH.
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Так что же выбрать, ёпта?

B-Tree: Бери его, блядь. Всегда. В 99 случаях из 100. Если сомневаешься — ставь B-Tree и не парься. Универсальный солдат.
Hash: А вот это уже для извращенцев. Только если у тебя таблица — овердохуища большая, и ты тупо-претупо ищешь по точному совпадению (WHERE email = 'vasya@mail.ru'), и тебе позарез нужна каждая микросекунда. Во всех остальных случаях — это просто мудёжь.