Индексы — важнейший инструмент оптимизации запросов в базах данных. В 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)


  1. parus-lead
    13.02.2025 09:21

    Благодарю за занимательную статью. Сам сталкивался с вопросами оптимизации в Postgre. Протестирую на практике ваше решение)


  1. Ivan22
    13.02.2025 09:21

    Этот хэш индекс хоть помогает при хэш-джоине ??


    1. ZeroProductivity Автор
      13.02.2025 09:21

      Hash Join в PostgreSQL всегда строит свою временную хеш-таблицу, даже если на соединяемом поле есть хеш-индекс.

      Проблема хеш-индекса в том, что каждый поиск требует отдельного обращения к индексу на диске(!). Если в orders миллионы строк, то поиск каждого user_id через хеш-индекс будет слишком дорогим.

      Hash Join эффективен, потому что он загружает данные в память(!) один раз и использует их для всех сравнений, а не выполняет отдельные обращения к индексу.


      1. Akina
        13.02.2025 09:21

        Проблема хеш-индекса в том, что каждый поиск требует отдельного обращения к индексу на диске(!)

        Hash Join эффективен, потому что он загружает данные в память(!)

        Что-то как-то не срослось... так обращается к индексу для каждой записи или сразу читает всё? проблемный или эффективный? вы уж определитесь, что ли...


        1. ZeroProductivity Автор
          13.02.2025 09:21

          Hash Join и хеш-индексы решают разные задачи:

          Хеш-индекс – это структура данных на диске, которая позволяет быстро находить отдельные значения (=).

          Hash Join – это алгоритм, который массово сопоставляет строки между двумя таблицами, создавая временный хеш в памяти.

          Поскольку Hash Join должен обработать все строки соединяемой таблицы, хеш-индекс ему не помогает – он предназначен только для точечного поиска (WHERE column = value)


          1. Akina
            13.02.2025 09:21

            Задача JOIN - связать записи по условию. И в подавляющем большинстве случаев это условие - равенство значений полей в двух таблицах. Если по полю обоих таблиц есть Hash index, то прочитать два индекса и выбрать совпадения гораздо проще, чем посчитать хэши, отсортировать их, и затем отбирать совпадения. И чем больше массив данных, тем заметнее выигрыш.

            Что же до хэш-индекса, то тут всё зависит от хэшируемых данных. Для компактных данных согласен, с эффективностью будет туговато. Но есть есть надобность поиска по, скажем, длинным строкам, то тут хэш-индекс просто обязан дать определённый профит именно за счёт своей компактности.

            В общем, не всё так уж и однозначно имхо.


  1. Akina
    13.02.2025 09:21

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

    А можно узнать, откуда вы это взяли? Про постоянство количества корзин...

    Потому как практика показывает иное: fiddle


    1. 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

      Исправил статью, спасибо за наблюдение


  1. dolfinus
    13.02.2025 09:21

    Ещё бы в сравнении указать размеры обоих индексов