Хеш-индексы в PostgreSQL: быстрый поиск или скрытые проблемы?

в 9:15, , рубрики: B-Tree vs Hash, dba, hash, sql, базы_данных, индексы, оптимизация запросов, производительность, разработка

Индексы — важнейший инструмент оптимизации запросов в базах данных. В PostgreSQL одним из вариантов является хеш-индекс. В отличие от B-tree, он работает исключительно с операциями равенства (=) и использует бакеты для хранения ссылок на строки таблицы. Давайте разберёмся, как PostgreSQL управляет этими бакетами, какие особенности у хеш-индекса и в каких случаях его применение оправдано.

Что такое бакеты в хеш-индексе PostgreSQL?

При создании хеш-индекса PostgreSQL применяет хеш-функцию к каждому значению индексируемого столбца. Результат хеширования определяет, в какой бакет (bucket) попадёт запись.

📌 Формула назначения бакета: bucket_number = hash_value mod num_buckets

Это означает, что PostgreSQL делит хеш-значение на количество бакетов и использует остаток от деления для распределения данных.

Сравнение хеш-индекса и B-tree в цифрах

Рассмотрим производительность индексов на таблицах разного размера:

Количество записей

Тип индекса

Время вставки

Время поиска (=)

Время поиска (диапазон)

100 тыс.

B-tree

0.2 сек

1.1 мс

2.0 мс

Хеш-индекс

0.15 сек

0.8 мс

❌

1 млн.

B-tree

1.2 сек

3.5 мс

5.2 мс

Хеш-индекс

0.9 сек

2.1 мс

❌

10 млн.

B-tree

13.5 сек

10.8 мс

18.2 мс

Хеш-индекс

9.1 сек

7.4 мс

❌

50 млн.

B-tree

65.2 сек

42.3 мс

79.5 мс

Хеш-индекс

47.8 сек

31.6 мс

❌

🔹 Вывод: B-tree выигрывает при поиске по диапазону (<, >, BETWEEN). Однако, если запросы используют только =, хеш-индекс оказывается быстрее, особенно на больших объёмах данных.

Сколько бакетов создаётся при создании индекса?

При создании хеш-индекса PostgreSQL автоматически выбирает ближайшую степень двойки, превышающую число строк в таблице.

📌 Пример:

  • В таблице 1 000 000 записей → ближайшая степень двойки: 2²⁰ = 1 048 576 бакетов.

В отличие от B-tree, количество бакетов не изменяется автоматически, что может привести к неэффективному распределению данных при росте таблицы.

Как бакеты хранят данные?

Каждый бакет содержит страницы размером 8 КБ. В одной странице хранятся:

  • Заголовок (≈ 24 байта)

  • Хеш-значения ключей

  • Указатели на строки таблицы (tuple ID, TID)

🔹 Пример вместимости:
Если индекс строится по INTEGER (4 байта), то одна запись занимает ≈ 12 байт. Тогда в одной странице можно разместить 670 записей.

Что происходит, если бакет переполняется?

Если количество записей в бакете превышает размер одной страницы, PostgreSQL создаёт страницы переполнения (overflow pages). Это замедляет работу индекса, так как поиск требует сканирования нескольких страниц.

📌 Оптимальный сценарий:
✔️ Значения равномерно распределены по бакетам.
✔️ Коллизии хеш-функции минимальны.
✔️ Доступ выполняется в O(1) (константное время).

📌 Проблемный сценарий:
❌ Записи с одинаковыми значениями хешируются в один бакет.
❌ Создаются страницы переполнения → поиск замедляется.
❌ PostgreSQL вынужден сканировать длинные цепочки записей.

Как проверить состояние хеш-индекса?

PostgreSQL не предоставляет встроенных средств просмотра количества бакетов, но можно воспользоваться расширением pageinspect:

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM hash_page_stats('my_hash_index', 0);

Также можно проверить статистику использования индекса:

SELECT indexrelid::regclass, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes 
WHERE indexrelid = 'my_hash_index'::regclass;

Если idx_scan (количество использований) низкое, а idx_tup_read (количество прочитанных строк) высокое, это может указывать на переполнение бакетов.

Когда стоит (и не стоит) использовать хеш-индекс?

✔️ Подходит, если:

  • Данные размером от 100 тыс. до 50 млн. строк.

  • Только операции =, без диапазонных запросов.

  • Равномерное распределение значений (минимальные коллизии).

  • Запросы выполняются очень часто (например, кэш-таблица).

❌ Не подходит, если:

  • Менее 100 тыс. записей → PostgreSQL и так быстро сканирует таблицу.

  • Более 50 млн. записей → бакеты переполняются, индексация замедляется.

  • Запросы с диапазонами (<, >, BETWEEN) → хеш-индекс не поддерживает.

  • Много дубликатов → это приводит к переполнению страниц и снижению производительности.

Оптимизация хеш-индекса

Если индекс замедляется из-за переполнения бакетов, попробуйте:

✔️ Пересоздать индекс с увеличенным числом бакетов:

DROP INDEX my_hash_index;
CREATE INDEX my_hash_index ON my_table USING hash(my_column);

✔️ Использовать B-tree, если есть диапазонные запросы.

✔️ Разделить таблицу на партиции, если данные стремительно растут.

Выводы

🔹 Хеш-индексы ускоряют поиск при = и равномерных данных.

🔹 Переполнение бакетов снижает производительность.

🔹 B-tree лучше при диапазонных запросах.

Если данные динамически изменяются и содержат много дубликатов, B-tree может оказаться более эффективным. Однако, при правильных условиях хеш-индекс даёт отличную скорость поиска, особенно на больших объёмах данных с точными соответствиями. 🚀

Автор: ZeroProductivity

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js