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

Предполагая, что вы спрашиваете об общей функции "подсказки по индексам", которая есть во многих базах данных, PostgreSQL не предоставляет такой возможности. Это было осознанное решение команды PostgreSQL. Хороший обзор того, почему и что вы можете сделать вместо этого, можно найти в здесь. Причины в основном в том, что это взлом производительности, который, как правило, вызывает больше проблем позже, когда ваши данные изменяются, тогда как оптимизатор PostgreSQL может повторно оценить план на основе статистики. Другими словами, то, что могло бы быть хорошим планом запроса сегодня, вероятно, не будет хорошим планом запроса на все времена, а подсказки индекса навязывают конкретный план запроса на все времена.

Как очень тупой молоток, пригодный для тестирования, можно использовать параметры enable_seqscan и enable_indexscan. Видеть:

• Изучение использования индекса
• Параметры enable_

Это не подходит для постоянного производственного использования. Если у вас есть проблемы с выбором плана запроса, вы должны увидеть документация по отслеживанию проблем с производительностью запросов. Не просто установите параметры enable_ и уходите.

Если у вас нет веской причины использовать индекс, Postgres может сделать правильный выбор. Почему?

• Для небольших таблиц быстрее выполнять последовательное сканирование.
• Postgres не использует индексы, когда типы данных не совпадают должным образом, вам может потребоваться включить соответствующие преобразования.
• Настройки вашего планировщика могут вызывать проблемы.

См. Также это старое сообщение группы новостей.

Вопрос сам по себе очень некорректный. Принудительное использование (например, enable_seqscan = off) - очень плохая идея. Было бы полезно проверить, будет ли он быстрее, но производственный код никогда не должен использовать такие уловки.

Вместо этого - объясните анализ вашего запроса, прочтите его и выясните, почему PostgreSQL выбирает плохой (на ваш взгляд) план.

В сети есть инструменты, которые помогают с чтением результатов анализа и объяснения - один из них - объяснять.depesz.com - написанный мной.

Другой вариант - присоединиться к каналу #postgresql в IRC-сети Freenode и поговорить с парнями, чтобы помочь вам - поскольку оптимизация запроса не сводится к тому, чтобы «задать вопрос, получить ответ и быть счастливым». это больше похоже на беседу, в которой нужно многое проверить, многому научиться.

Вероятно, единственная веская причина для использования

set enable_seqscan=false

это когда вы пишете запросы и хотите быстро увидеть, каким был бы план запроса, если бы в таблице (ах) были большие объемы данных. Или, конечно, если вам нужно быстро подтвердить, что ваш запрос не использует индекс просто потому, что набор данных слишком мал.

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

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description = 'Refund'
GROUP BY client_id

PostgreSQL может выбрать использование индекса transaction_description_idx вместо transaction_date_idx, что может привести к тому, что запрос займет несколько минут вместо менее одной секунды. В этом случае вы можете принудительно использовать индекс по дате, подделав условие следующим образом:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description||'' = 'Refund'
GROUP BY client_id

Существует трюк, чтобы подтолкнуть postgres к предпочтению seqscan, добавив OFFSET 0 в подзапрос

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

Допустим, вы ищете первые / последние 20 элементов, включающих несколько таблиц, имеющих 100 тыс. (Или более) записей, без создания / связывания всего запроса по всем данным, когда то, что вы будете искать, находится в первых 100 или 1000 записи. В этом сценарии, например, последовательное сканирование оказывается более чем в 10 раз быстрее.

см. Как я могу запретить Postgres встраивать подзапрос?

Проверьте свой random_page_cost

Эта проблема обычно возникает, когда оценочная стоимость сканирования индекса слишком высока и не соответствует действительности. Вам может потребоваться понизить параметр конфигурации random_page_cost, чтобы исправить это. Из Документация Postgres:

Reducing this value [...] will cause the system to prefer index scans; raising it will make index scans look relatively more expensive.

Вы можете быстро проверить, действительно ли это заставит Postgres использовать индекс:

EXPLAIN <query>;              # Uses sequential scan
SET random_page_cost = 1;
EXPLAIN <query>;              # May use index scan now

Вы можете снова восстановить значение по умолчанию с помощью SET random_page_cost = DEFAULT;.

Вы можете навсегда изменить глобальное значение по умолчанию с помощью ALTER SYSTEM SET random_page_cost=1;.

Фон

Для сканирования индекса требуются непоследовательные выборки страниц с диска. Postgres использует random_page_cost для оценки стоимости таких непоследовательных выборок по сравнению с последовательными выборками. Значение по умолчанию - 4.0, таким образом, предполагается, что коэффициент стоимости в среднем равен 4 по сравнению с последовательными выборками (с учетом эффектов кэширования).

Однако проблема в том, что это значение по умолчанию не подходит в следующих важных реальных сценариях:

1) твердотельные накопители

Согласно документации:

Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might be better modeled with a lower value for random_page_cost, e.g., 1.1.

Этот слайд из выступления на PostgresConf 2018 также говорит, что random_page_cost должен быть установлен на что-то среднее между 1.0 и 2.0 для твердотельных накопителей.

2) Кэшированные данные

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

If your data is likely to be completely in cache, [...] decreasing random_page_cost can be appropriate.

Проблема в том, что вы, конечно, не можете легко узнать, кэшированы ли уже соответствующие данные. Однако, если часто используется конкретный индекс и если в системе достаточно ОЗУ, то данные, вероятно, в конечном итоге будут кэшированы, и для random_page_cost следует установить более низкое значение. Вам придется поэкспериментировать с разными значениями и посмотреть, что вам подходит.

Вы также можете использовать расширение pg_prewarm для явного кэширования данных.

По-видимому, есть случаи, когда Postgre может получить подсказку об использовании индекса, дважды повторив подобное условие.

В конкретном случае, который я наблюдал, использовался индекс PostGIS gin и предикат ST_Within следующим образом:

select *
from address
natural join city
natural join restaurant
where st_within(address.location, restaurant.delivery_area)
and restaurant.delivery_area ~ address.location

Обратите внимание, что первый предикат st_within(address.location, restaurant.delivery_area) автоматически разлагается PostGIS на (restaurant.delivery_area ~ address.location) AND _st_contains(restaurant.delivery_area, address.location), поэтому добавление второго предиката restaurant.delivery_area ~ address.location полностью избыточно. Тем не менее, второй предикат убедил планировщика использовать пространственный индекс на address.location, и в конкретном случае, который мне нужен, увеличил время выполнения в 8 раз.