Какой тип индекса наиболее подходит для низкоселективного столбца

У меня есть таблица с около 60 млн записей, и потенциально она скоро вырастет до ~ 500 млн (потом будет медленно расти). В таблице есть столбец, допустим категория. Общее количество категорий составляет около 20 тысяч и растет очень медленно и время от времени. Записи распределены по категориям неравномерно, есть категории, которые охватывают 5% всех записей, в то время как другие категории представлены лишь очень небольшой долей записей.

У меня есть ряд запросов, которые работают только с одной или несколькими категориями (используйте условия = или IN/ANY), и я хочу оптимизировать производительность этих запросов.

  1. Учитывая малоизбирательный характер данных в столбце, какой тип индекса Postgres будет более выгодным: HASH или B-TREE?
  2. Есть ли другие способы оптимизировать производительность этих запросов?

postgresql indexing postgresql-10
person greatvovan    schedule 23.08.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Я могу дать только общий ответ на этот широкий вопрос.

Используйте индексы B-tree, а не хэш-индексы.

Если у вас есть несколько условий, которые не очень избирательны, создайте индекс для каждого из столбцов, тогда их можно будет комбинировать со сканированием битового индекса.

person Laurenz Albe    schedule 24.08.2018
comment
Не могли бы вы прокомментировать, почему? Они пишут, что хэш-индексы немного быстрее, чем btree: enterprisedb.com/ blog/hash-indexes-are-faster-btree-indexes Это связано с низкой избирательностью? - person greatvovan; 24.08.2018
comment
Индексы B-дерева просто более проверены, в то время как хэш-индексы стали полезными только недавно. Но вы правы - возможно, вам следует сравнить их для вашего варианта использования. - person Laurenz Albe; 24.08.2018

arrow_upward
1
arrow_downward

В общем, столбец, который не очень избирательен, не является хорошим кандидатом для индекса. Индексы не бесплатны. Их необходимо поддерживать, и во время запроса, в большинстве случаев, Postgres все равно придется обращаться к таблице для каждой строки, которой соответствует поиск по индексу (исключение составляют индексы).

С учетом сказанного, я не уверен в вашем анализе селективности. Если самый высокий процент, который вы отфильтруете до наихудшего случая, составляет 5%, а большинство из них намного ниже этого, я бы сказал, что у вас очень избирательный столбец.

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

Хэш-индексы быстрее запрашиваются, чем индексы b-дерева, но их нельзя использовать для поиска диапазона, только для проверки равенства. Хэш-индексы поддерживаются не во всех СУБД и, как следствие, менее понятны в сообществе, что может препятствовать поддержке.

person Brandon    schedule 24.08.2018
comment
Я знаю эту информацию о хеш-индексах, но, как я уже сказал, этот столбец будет использоваться только с = и IN. Абсолютно маловероятно, что будут применены условия дальности. Почему вы до сих пор думаете, что хэш здесь не лучший выбор? - person greatvovan; 24.08.2018
comment
Как уже упоминалось, они не очень хорошо понимаются в сообществе. В случае возникновения проблемы будет сложнее получить помощь. Когда люди думают об индексах, они думают о b-деревьях. - person Brandon; 24.08.2018
comment
Вот проблема с хеш-индексом, которую я только что обнаружил. Мой недостаток опыта с ними укусил бы меня, если бы я внимательно не прочитал документацию и не заметил, что они не регистрируются в WAL, что относится к безопасности при сбоях: postgresql.org/docs/9.6/static/indexes-types.html - person Brandon; 24.08.2018
comment
Есть причина, по которой b-tree используется Postgres по умолчанию. - person Brandon; 24.08.2018
comment
В текущей версии такого уведомления нет: postgresql.org/docs/ 10/static/indexes-types.html - person greatvovan; 24.08.2018