Позвольте PostgreSQL выбрать индекс за вас

Компания Supabase выпустила Index Advisor - расширение для Postgres, позволяющее рекомендовать индексы, повышающие производительность запросов, и облегчающее бремя разработчика.

Обычно при создании таблиц вы также хотите создать индексы для их быстрого поиска. Для этого вам потребуется хорошее понимание вашего домена и того, как код обращается к данным. RDBM оптимизированы для работы в декларативном режиме; то есть вы говорите им, чего хотите, не указывая, как это сделать, как это делается в императивной модели программирования, позволяя им самим решать, как лучше поступить.

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

А что если обратить этот процесс вспять и перейти к полностью декларативной модели? То есть позволить движку создать индекс за вас, изучив ваши запросы. Именно это и делает новое расширение "index advisor" от Supabase. Вы передаете ему sql-код, который будет выполнять ваше приложение, и советник рекомендует вам, какие индексы следует создать.

На примере все станет ясно. Сначала нужно указать Postgres на использование расширения:

create extension if not exists index_advisor cascade;

Затем отправьте в него запрос:

select * from index_advisor

('select book.id from book where title = $1');

В этом случае советник предложит следующее:

включая предполагаемое улучшение стартовых и общих затрат:

Начальные затраты - это затраты на получение первой строки, Общие затраты - это затраты на получение всех строк. Затраты выражены в произвольных единицах, где одно последовательное чтение страницы стоит 1,0 единицу.

Приведенный пример является простым, но советник может работать с более сложными запросами, которые могут генерировать дополнительные предлагаемые индексы:

В текущем виде index_advisor будет рекомендовать только одноколоночные индексы с B-деревом. Более сложные индексы будут поддерживаться в будущих версиях. Поддерживает :

• Общие параметры, например, $1, $2.
• Поддерживает материализованные представления
• Идентифицирует таблицы/столбцы, замаскированные представлениями
• Пропускает дублирующиеся индексы

Советник в значительной степени опирается на расширение hypopg (Hypothetical Indexes), которое должно быть установлено до него. Это необходимо, потому что HypoPG оценивает стоимость запроса при наличии заданного (гипотетического) индекса, не создавая его на самом деле.

Найти его можно в репозитории Github или в качестве опции на панели управления Supabase.