Как Postgres выбирает, какой индекс использовать в случае, если присутствует несколько индексов?

#postgresql #query-performance #database-indexes

#postgresql #запрос-производительность #база данных-индексы

Вопрос:

Я новичок в Postgres и немного смущен тем, как Postgres решает, какой индекс использовать, если у меня более одного индекса btree, определенного как показано ниже.

 CREATE INDEX index_1 ON sample_table USING btree (col1, col2, COALESCE(col3, 'col3'::text));

CREATE INDEX index_2 ON sample_table USING btree (col1, COALESCE(col3, 'col3'::text));

Я использую col1, col2, COALESCE (col3, ‘col3’::text) в моем условии соединения, когда я пишу в sample_table (из исходных таблиц), но когда я выполняю анализ explain для получения плана запроса, я иногда вижу, что он использует index_2 для сканирования, а не index_1, а иногда просто переходитс последовательным сканированием.Я хочу понять, что может заставить Postgres использовать один индекс поверх другого?

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

2. Спасибо @a_horse_with_no_name, за ваше объяснение

Ответ №1:

Не видя EXPLAIN (ANALYZE, BUFFERS) выходных данных, я могу дать только общий ответ.

PostgreSQL рассматривает все возможные планы выполнения и оценивает количество строк и стоимость для каждого узла. Затем он принимает план с наименьшей оценкой затрат.

Возможно, условие on col2 иногда более избирательно, а иногда и менее, например, потому, что вы иногда сравниваете его с редкими, а иногда и с частыми значениями. Если условие, включающее col2 не является выборочным, это не сильно влияет на то, какой из двух индексов используется. В этом случае PostgreSQL предпочитает меньший индекс из двух столбцов.

1. Большое спасибо @Laurenz Albe за объяснение, еще один вопрос в тех же строках: можно ли заставить / подсказать postgres sql использовать индекс, и если это возможно, стоит ли это делать?

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