На сколько быстро происходит пересчет hash индекса?

Question

[beduin01]

Решил поменять тип индекса с b-tree на hash на таблице в 20 млн записей. PostgreSQL загрузил проц, а SQL менеджер завис. Подождав 40 минут я рестартовал службу PostgreSQL т.к. не понял зависла она или работает (1 ядро Xeon было загружено полностью)

Операция хэширования получается такая медленная?

Comments

[Dimonchik]

как-то по- колхозному

[beduin01]

Dimonchik, а как надо? Я просто с БД не так часто работаю

Answer 1

[Melkij]

hash индекс строится в один поток, для них многопоточный create index не делали. Потому загрузка 100% одного ядра - ситуация нормальная.
Нет, create index не зависает.
Если у вас селективность поля хреновая - то hash индекс будет строиться медленно, т.к. много строк попадает в одно и то же значение хеширования. Не помню точно, как именно хранится список TID при коллизиях хеш-функции, но точно работает хреново при большом числе дублей значений.

Если версия postgresql достаточно свежая - то смотрите pg_stat_progress_create_index системное представление. Там будут некоторый progress reporting.

Comments

[beduin01]

Получается btree более универсален? И у hash преимуществ кроме как меньшего размера индекса толком то и нет?

[Melkij]

Да, btree более универсален.
До postgresql 10 пользоваться hash было вообще невозможно, не логировались в wal и потому не реплицировались, не crash-safe. В 10 переделали. По размеру на самом деле тоже спорно, надо проверять на конкретных данных. Видел ситуации, где hash по объёму как btree получается. Ну и дедубликация btree в pg13 весьма хороша.

[beduin01]

Melkij, а для каких случаев хэш будет лучше? Какие условия и какие паттерны доступа?

[Melkij]

У меня нет примеров из практики, где необходим именно using hash.
Заведомо известно, что для hash нужны условия только на строгое сравнение, разнообразность входных данных. Желательно входные данные побольше 4 байт размером, иначе на 4-байтовых значениях хэш-функции мы будем только расти в размере.

Ну, например, из недавнего, табличка со статистикой запросов к базе по дням. Тексты запросов часто каждый день одни и те же, потому выношу их в отдельную таблицу-словарь и экономлю прилично места на диске. Нужно уметь при записи свежей статистики искать, был ли такой текст запроса ранее. При том, текст запроса может быть слишком длинным, чтобы умещаться в btree. Вот здесь уместен hash индекс. Но вполне может посоревноваться и btree поверх какой-нибудь хэширующей функции.