Unaccent() предотвращение использования индекса в Postgres

Я хочу получить способ с заданным именем из базы данных OpenStreetMap, импортированной в PostgreSQL 9.3.5, ОС - это 64-разрядная версия Win7. Для того, чтобы быть терпимым к отказу, я использую незаметное расширение 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 индекса? По-моему, это не имеет смысла, потому что результат незатухающего (удаление диакритики и т.д.) Должен быть полностью известен до того, как будет выполнен фактический запрос. Поэтому Postgres должен иметь возможность использовать индекс. Как избежать seq-сканирования при использовании unaccent?

Ответы

Ответ 1

IMMUTABLE вариант unaccent()

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

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

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

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

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

Почему?

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

Если есть функция IMMUTABLE справа от LIKE, ее можно сразу оценить, и указанная оптимизация по-прежнему возможна. Per документация по Категории волатильности функций:

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

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

Повторить:

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