#postgresql #postgresql-12 #postgresql-13
Вопрос:
Пример (поддельный) случай: индексирование столбца, содержащего электронные письма (текстовый тип), когда столбец будет запрашиваться исключительно для точного совпадения строк, например.
SELECT * FROM mytable WHERE email = 'test@test.com'
Обеспечивают ли хэш-индексы преимущества перед B-деревьями без каких-либо недостатков в этих случаях?
Влияют ли они по-разному на производительность вставок/обновлений?
(правка: и никогда не заказывался по этой колонке)
Комментарии:
1. это хороший вопрос. в настоящее время многие случаи являются аналитической рабочей нагрузкой, которая представляет собой таблицу с однократной записью и простой запрос соответствия для текста. теоретически оптимальным типом индекса для которого является хэш для O(1).
Ответ №1:
Единственное явное преимущество заключается в том, что индексируемая текстовая строка может быть довольно длинной. Тогда хэш — индекс может быть намного меньше и, следовательно, лучше кэшироваться. В крайнем случае, вставка значений длиной более 2700 байт может привести к ошибкам в индексах btree, но все равно будет работать с хэш-индексами.
При использовании более коротких строк хэш-индексы имеют больше недостатков, чем преимуществ. В дополнение к тому, что они не поддерживают упорядочение, они также не поддерживают уникальные ограничения (и, следовательно, не могут быть на приемной стороне внешних ключей), а на неуникальной стороне они, как правило, становятся несбалансированными, если некоторые значения гораздо более распространены, чем другие значения. Кроме того, меньшее использование означает, что они менее хорошо протестированы и, следовательно, с большей вероятностью содержат нераскрытые ошибки; и меньше работы было потрачено на их оптимизацию.
Кроме того, я думаю, что метапапаж хэш-индексов по-прежнему является предметом спора для вставок, которые могут снизить производительность.
Ответ №2:
если вы сравниваете только столбец электронной почты с равным оператором и нет порядка , то хэш-индекс намного быстрее.
- B-деревья могут обрабатывать запросы на равенство и диапазон для данных, которые могут быть отсортированы в некотором порядке. В частности, планировщик запросов PostgreSQL рассмотрит возможность использования индекса B-дерева всякий раз, когда индексированный столбец участвует в сравнении с использованием одного из этих операторов: <, <=, =, >=, <=, =, >>
- Хэш-индексы могут обрабатывать только простые сравнения на равенство. Планировщик запросов рассмотрит возможность использования хэш-индекса всякий раз, когда индексированный столбец участвует в сравнении с использованием оператора=.
вот сравнение двух типов индексов в документах mysql, но оно применимо и к postgresql, а также сравнение MySQL B-дерева и хэш-индексов