unaccent(), предотвращающий использование индекса в Postgres

Question

Я хочу получить путь с заданным именем из базы данных OpenStreetMap, импортированной в PostgreSQL 9.3.5, операционная система Win7 64-разрядная. Чтобы быть немного отказоустойчивым, я использую unaccent расширение Postgres.

Мой запрос выглядит следующим образом:

SELECT * FROM germany.ways
WHERE lower(tags->'name') like lower(unaccent('unaccent','Weststrasse'))

План запроса:

Seq Scan on ways  (cost=0.00..2958579.31 rows=122 width=465)
  Filter: (lower((tags -> 'name'::text)) ~~ lower(unaccent('unaccent'::regdictionary, 'Weststrasse'::text)))

Странно то, что этот запрос использует последовательное сканирование путей, хотя на lower(tags->'name'):

CREATE INDEX ways_tags_name ON germany.ways (lower(tags -> 'name'));

Postgres использует индекс, как только я удаляю unaccent из запроса:

SELECT * FROM germany.ways
WHERE lower(tags->'name') like lower('Weststrasse')

План запроса:

Index Scan using ways_tags_name on ways  (cost=0.57..495.43 rows=122 width=465)
  Index Cond: (lower((tags -> 'name'::text)) = 'weststrasse'::text)
  Filter: (lower((tags -> 'name'::text)) ~~ 'weststrasse'::text)

Почему unaccent мешает Postgres использовать индекс? По моему мнению, это не имеет смысла, потому что результат unaccent (удаление диакритических знаков и т. Д.) Должен быть полностью известен до того, как будет выполнен фактический запрос. Таким образом, Postgres должен иметь возможность использовать индекс. Как избежать сканирования seq при использовании unaccent?

Answer 1

НЕМЕРТНЫЙ вариант unaccent()

Для уточнения дезинформации в принятом в настоящее время неверном ответе:
Индексы выражений позволяют только IMMUTABLE функции (по понятным причинам) и unaccent() только STABLE, Решение, которое вы предложили в комментарии, также проблематично. Подробное объяснение и правильное решение для этого:

• Поддерживает ли PostgreSQL сортировку без учета акцента?

В зависимости от содержания tags->name может быть полезно добавить unaccent() к индексу выражения, но это ортогонально вопросу, почему индекс не использовался:

• PostgreSQL акцент + регистронезависимый поиск

Актуальная проблема / решение

Оператор LIKE в вашем запросе немного ошибочно (скорее всего). Вы не хотите интерпретировать 'Weststrasse' как шаблон поиска, вы хотите сопоставить (нормализованную) строку как есть. Заменить на = оператор, и вы увидите (растровое) индексное сканирование с вашим текущим индексом, независимо от волатильности функции unaccent():

SELECT * FROM germany.ways
WHERE lower(tags->'name') = lower(unaccent('unaccent','Weststrasse'))

Зачем?

Правильный операнд LIKE это шаблон. Postgres не может использовать обычный индекс btree для сопоставления с образцом ( применяются исключения). LIKE с простой строкой в ​​качестве шаблона (без специальных символов) можно оптимизировать проверку на равенство по индексу btree. Но если в строке есть специальные символы, этот индекс отсутствует.

Если есть IMMUTABLE функция справа от LIKE, это может быть оценено немедленно, и упомянутая оптимизация все еще возможна. Согласно документации по категориям волатильности функций:

IMMUTABLE...
Эта категория позволяет оптимизатору предварительно оценить функцию, когда запрос вызывает ее с постоянными аргументами.

То же самое невозможно при меньшей волатильности функции (STABLE или же VOLATILE). Вот почему ваше "решение" подделать IMMUTABLE unaccent() Казалось, работает, но это действительно положить помаду на свинью.

Повторить:

• Если вы хотите работать с LIKE и шаблоны, используйте индекс триграммы.
• Если вы не хотите работать с LIKE и шаблоны, используйте оператор равенства =