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

Это два разных типа индексов в 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 = '...'), и вы хотите выжать максимальную производительность для этих конкретных запросов.