Intereting Posts
Задача Linq to SQL nvarchar Запрос с помощью group_concat возвращает только одну строку Заявление SQL для устранения дубликатов на основе значения в другом столбце создание триггера в оракуле, который использует данные из другой таблицы Django оставил внешнее соединение с фильтром Синтаксическая ошибка: неожиданный конец файла Производительность запросов по двум базам данных mysql на одном сервере? Запрос обновления MySQL с несколькими значениями Как я могу получить родителей всех родителей в качестве столбцов для дочернего объекта в таблице с круговыми ссылками? Использование предложения WHERE для поиска POI в диапазоне расстояний от долготы и широты Joins vs Присоединиться как запросы – они эквивалентны? Как проверить существование строки в SQLite с помощью Python? Поверните SQL-запрос в ActiveRecord Relation Как использовать функциональный индекс в столбце, который содержит NULL в Oracle 10+? sql, если существует

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?

НЕМЕДЛЕННЫЙ вариант неактивного unaccent()

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

  • Поддерживает ли PostgreSQL «нечувствительные к акценту» сортировки?

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

  • Аккаунт PostgreSQL + регистр без учета регистра

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

Оператор LIKE в вашем запросе является не совсем правильным (скорее всего). Вы не хотите интерпретировать «Weststrasse» как шаблон поиска, вы хотите сопоставить (нормированную) строку как есть. Замените с помощью оператора = , и вы увидите сканирование индекса (bitmap) с вашим текущим индексом, независимо от волатильности функции 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 и шаблонами, используйте оператор равенства =