Многостолбцовый индекс против отдельных индексов против частичных индексов

Question

Работая сегодня над моим Rails-приложением, я заметил, что гем паранойи говорит, что индексы должны быть обновлены, чтобы добавить deleted_at IS NOT NULL как где при создании индекса ( ссылка на github). Но мне пришло в голову, что инвертированное условие, когда я хочу with_deleted, не получит выгоду от индекса.

Это заставляет меня задуматься...

Я знаю, что это несколько глупо, потому что ответ, очевидно, "это зависит от того, что вам нужно", но я пытаюсь получить представление о различиях между индексами с несколькими столбцами и отдельными индексами и частичными индексами в моем веб-приложении, поддерживаемом PostgreSQL.

По сути, у меня есть 2 поля, по которым я запрашиваю: p_id и dele_at. Большую часть времени я запрашиваю WHERE p_id=1 AND deleted_at IS NOT NULL - но иногда я только запрашиваю WHERE p_id=1, Очень редко я буду WHERE p_id=1 AND deleted_at=1/1/2017,

Итак, мне лучше

1. Иметь индекс на p_id и отдельный индекс на удаленных_катах?
2. Имеете индекс для p_id, но добавляете "где Удалено_ не является пустым"?
3. Имеете объединенный индекс для p_id и delete_at вместе?

Примечание: возможно, я должен упомянуть, что p_id в настоящее время является ссылкой внешнего ключа на p.id. Что напоминает мне, в Postgres, необходимо ли, чтобы внешние ключи также имели индексы (или они получают индекс, полученный из ограничения внешнего ключа - я прочитал противоречивые ответы по этому вопросу)?

Answer 1

Ответ зависит от

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

Идеальными показателями для трех предложений являются:

1. WHERE p_id=1 AND deleted_at IS NOT NULL

   CREATE INDEX ON mytable (p_id) WHERE deleted_at IS NOT NULL;
   

2. WHERE p_id=1 AND deleted_at=1/1/2017

   CREATE INDEX ON mytable (p_id, deleted_at);
   

3. WHERE p_id=1

   CREATE INDEX ON mytable (p_id);
   

Индекс, созданный для 2., также можно использовать для 3., поэтому, если вам нужно максимально ускорить второй запрос, и немного больший индекс вас не беспокоит, создайте только индекс из 2. для обоих запросов.

Однако индекс из 3. также ускорит запрос в 2., но не настолько сильно, насколько это возможно, поэтому, если вы можете жить с несколько худшей производительностью для запроса в 2. и хотите, чтобы индекс был как можно меньшим и эффективным для запроса в 3. создайте только индекс в 3.

Я бы не стал создавать оба индекса из 2. 3; Вы должны выбрать то, что лучше для вас.

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

Другим показателем для создания индекса в 1. является, если удовлетворяет только небольшой процент строк deleted_at IS NOT NULL, Если нет, индекс в 1. не имеет большого преимущества перед индексом в 3., и вы должны просто создать последний.

Наличие двух отдельных индексов в двух столбцах, вероятно, не лучший выбор - их можно использовать только в сочетании со сканированием индекса растрового изображения, и вполне может быть, что PostgreSQL выберет только один из индексов (зависит от распределения, но вероятно тот на p_id), а другой бесполезен.