Индексы — важнейший инструмент оптимизации запросов в базах данных. В 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
). Однако, если запросы используют только =
, хеш-индекс оказывается быстрее, особенно на больших объёмах данных.
Как бакеты хранят данные?
Каждый бакет содержит страницы размером 8 Кб. В одной странице хранятся:
Заголовок (≈ 24 байта)
Хеш-значения ключей (по 4 байта)
Указатели на строки таблицы (tuple ID, TID) (по 6 байт)
⚠️ Важно: хеш-индекс хранит не ключ, а только его хеш-значение (в отличие от B-Tree).
Тогда на одной странице можно разместить (8Кб - 24б) / (4б + 6б) ≈ 800 записей.
Что происходит, если бакет переполняется?
Если количество записей в бакете превышает размер одной страницы, 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
) → хеш-индекс не поддерживает.Много дубликатов → это приводит к переполнению страниц и снижению производительности.
Оптимизация хеш-индекса
Если индекс замедляется из-за переполнения бакетов, попробуйте:
✔️ Перестроить индекс заново, освобождая неиспользуемое пространство и улучшая производительность запросов REINDEX INDEX my_index;
✔️ Использовать B-tree, если есть диапазонные запросы.
✔️ Разделить таблицу на партиции, если данные стремительно растут.
Выводы
? Хеш-индексы ускоряют поиск при = и равномерных данных.
? Переполнение бакетов снижает производительность.
? B-tree лучше при диапазонных запросах.
Если данные динамически изменяются и содержат много дубликатов, B-tree может оказаться более эффективным. Однако, при правильных условиях хеш-индекс даёт отличную скорость поиска, особенно на больших объёмах данных с точными соответствиями. ?
Комментарии (9)
Ivan22
13.02.2025 09:21Этот хэш индекс хоть помогает при хэш-джоине ??
ZeroProductivity Автор
13.02.2025 09:21Hash Join в PostgreSQL всегда строит свою временную хеш-таблицу, даже если на соединяемом поле есть хеш-индекс.
Проблема хеш-индекса в том, что каждый поиск требует отдельного обращения к индексу на диске(!). Если вorders
миллионы строк, то поиск каждогоuser_id
через хеш-индекс будет слишком дорогим.Hash Join эффективен, потому что он загружает данные в память(!) один раз и использует их для всех сравнений, а не выполняет отдельные обращения к индексу.
Akina
13.02.2025 09:21Проблема хеш-индекса в том, что каждый поиск требует отдельного обращения к индексу на диске(!)
Hash Join эффективен, потому что он загружает данные в память(!)
Что-то как-то не срослось... так обращается к индексу для каждой записи или сразу читает всё? проблемный или эффективный? вы уж определитесь, что ли...
ZeroProductivity Автор
13.02.2025 09:21Hash Join и хеш-индексы решают разные задачи:
Хеш-индекс – это структура данных на диске, которая позволяет быстро находить отдельные значения (=).
Hash Join – это алгоритм, который массово сопоставляет строки между двумя таблицами, создавая временный хеш в памяти.
Поскольку Hash Join должен обработать все строки соединяемой таблицы, хеш-индекс ему не помогает – он предназначен только для точечного поиска (
WHERE column = value
)Akina
13.02.2025 09:21Задача JOIN - связать записи по условию. И в подавляющем большинстве случаев это условие - равенство значений полей в двух таблицах. Если по полю обоих таблиц есть Hash index, то прочитать два индекса и выбрать совпадения гораздо проще, чем посчитать хэши, отсортировать их, и затем отбирать совпадения. И чем больше массив данных, тем заметнее выигрыш.
Что же до хэш-индекса, то тут всё зависит от хэшируемых данных. Для компактных данных согласен, с эффективностью будет туговато. Но есть есть надобность поиска по, скажем, длинным строкам, то тут хэш-индекс просто обязан дать определённый профит именно за счёт своей компактности.
В общем, не всё так уж и однозначно имхо.
Akina
13.02.2025 09:21В отличие от B-tree, количество бакетов не изменяется автоматически, что может привести к неэффективному распределению данных при росте таблицы.
А можно узнать, откуда вы это взяли? Про постоянство количества корзин...
Потому как практика показывает иное: fiddle
ZeroProductivity Автор
13.02.2025 09:21Да, действительно, начиная с версии 10 количество бакетов увеличивается динамически.
"Hash indexes may expand the number of bucket pages as the number of rows indexed grows.." https://www.postgresql.org/docs/10/hash-intro.html
Исправил статью, спасибо за наблюдение
parus-lead
Благодарю за занимательную статью. Сам сталкивался с вопросами оптимизации в Postgre. Протестирую на практике ваше решение)