Почему мои запросы работают медлено? Почему они не используют мои индексы?
Индексы не используются для каждого запроса автоматически. Они используются только если таблица больше минимального размера и запрос выбирает только маленький процент строк в таблице. Так устроено, потому что доступ к диску с применением рандомизации при сканировании индексов может быть медленнее, чем простое чтение таблицы или ее последовательное сканирование.
Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE
или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка связывания и метода связывания. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.
Обычно индексы не используются для ORDER BY или для выполнения связываний. Последовательный перебор следующий за явной сортировкой обычно быстрее, чем поиск по индексам в большой таблице. Однако, ORDER BY часто комбинируется с LIMIT
и в этом случае индекс будет использоваться, поскольку при выполнении будет возвращаться небольшая часть таблицы. Фактически MAX() и MIN() не используют индексы, но индекс используется при построении запросов с ORDER BY и LIMIT:
SELECT col FROM tab ORDER BY col [ DESC ] LIMIT 1;
Если вам кажется, что оптимизатор некорректно выбирает последовательный перебор, используйте SET enable_seqscan TO 'off' и запустите тесты, чтобы увидеть, не стало-ли сканирование индексов быстрее.
Когда используются операции с шаблонами, например LIKE
или ~, индексы могут быть использованы в следующих случаях:
- LIKE шаблоны не должны начинаться с %..
- ~ шаблоны регулярных выражений должна начинаться на ^.
- Строка поиска не должна начинаться с символа класса, т.е. [a-e].
- Поиск независимый от регистра, такой как ILIKE и ~* не использует индексы. Вместо него, используйте индексы выражений, которые описываются в секции .
- Во время initdb должна использоваться локаль по умолчанию C, потому что не существует возможности узнать следующий наибольший символ для не-C локали. Вы можете для таких случаев создать специальный индекс text_pattern_ops который работает только для LIKE индексирования.
В выпусках до версии 8.0, индексы часто нельзя было использовать, если типы данных точно не совпадали с индексными типами колонок. Это особенно касалось int2, int8 и numeric индексов колонок.
