Postgresql — несколько индексов с одинаковыми столбцами

Предположим, я

  • иметь таблицу со столбцами a, b, c и d
  • хотите, чтобы запросы SELECT с комбинациями этих столбцов выполнялись молниеносно
  • ожидать, что a будет в каждом запросе
  • не заботьтесь о медленных UPDATE и INSERT

Какие индексы я должен создать, и является ли создание нескольких индексов с одними и теми же столбцами в разном порядке ужасной идеей?

(a, b)

(a, c)

(a, b, c)

(a, c, b)

...

(a, b, c, d)

(a, d, c, b)

...


postgresql database indices postgresql-9.5
person Palisand    schedule 12.04.2017    source источник
comment
какой тип а, б, в, г?   -  person Evan Carroll    schedule 13.04.2017
comment
один целочисленный, остальные varchar   -  person Palisand    schedule 13.04.2017
comment
Postgres может объединять несколько индексов в одной таблице для одного запроса. Поэтому я бы попробовал один индекс для каждого столбца и посмотрел, насколько это эффективно с обычными запросами, которые вы используете. Если условие для столбца a уже достаточно уменьшает количество строк в результате, может быть достаточно одного B-дерева для всех столбцов. Вы также можете попробовать индекс BRIN или индекс фильтра Блума для всех столбцов   -  person a_horse_with_no_name    schedule 13.04.2017

Ответы (2)


arrow_upward
5
arrow_downward

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

Порядок столбцов индекса играет очень важную роль. Столбцы должны быть упорядочены по кардинальности. Давайте посмотрим на пример:

У нас есть таблица людей (id, фамилия, имя, year_of_birth, пол). Какие индексы здесь уместны?

  1. Фильтрация по имени

Какой индекс добавить - (фамилия, имя) или (имя, фамилия)? Правильный ответ (фамилия, имя), потому что, если вы сгруппируете записи по фамилии, в ней наверняка будет больше записей.

  1. Фильтрация по имени и году рождения

Допустим, у нас уже есть (фамилия, имя) индекс. Должны ли мы изменить его на (фамилия, имя, год)? Возможно, есть какая-то польза, но я сомневаюсь в этом. Для любой данной фамилии и имени, вероятно, будет всего несколько записей с разным возрастом. Дело в том, что если у нас почти уникальная комбинация (например, фамилия + имя), то добавление дополнительных столбцов в индекс мало чем поможет, если вообще поможет.

  1. Фильтрация по полу

Индекс не нужен. Потому что есть только два возможных значения: мужчина/женщина. Таким образом, индекс не будет эффективным.

Помимо того, что индексы великолепны и важны, я хотел бы отметить несколько вещей:

  • индексы занимают дополнительное место на диске
  • индексы влияют на скорость обновления/вставки
  • не все индексы эффективны (для небольшого набора записей последовательное сканирование выполняется быстрее, потому что поиск по индексу не является бесплатным с точки зрения производительности)
  • в конце концов, какой индекс будет использоваться, решается планировщиком запросов, и это зависит от МНОЖЕСТВА факторов. Иногда он может предпочесть последовательное сканирование, даже если у вас есть индексы. Так что никогда не узнаешь, пока не проверишь.

Хороший момент из документации: Объединение нескольких индексов

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

person AlexM    schedule 13.04.2017

arrow_upward
0
arrow_downward

На этот вопрос нет ответа в его нынешнем виде. Факторы, влияющие на выбор индекса и его полезность, включают:

  • кардинальность данных в столбце
  • размер данных в таблице
  • тип данных
  • свежесть индекса
  • параметры настройки запроса
  • и да, порядок столбцов в индексе

При всем при этом единственный способ узнать, как будут работать ваши индексы, — это протестировать репрезентативные данные. Извините, здесь нет ярлыков.

person Kirk Roybal    schedule 13.04.2017