Какие проблемы возникают при использовании оператора LIKE со строками и индексом B-tree в PostgreSQL

Что проверяют

Знаете ли вы, когда B-tree вообще может обслужить LIKE, и понимаете ли роль правила сортировки (collation). Фразы «с процентом в начале индекс не работает» мало, её знают все. Следом обычно спрашивают, как ускорить поиск по подстроке.

Как отвечать

B-tree обслуживает LIKE только при фиксированном префиксе: name LIKE 'ivan%' превращается в диапазон name >= 'ivan' AND name < 'ivao'. Но и тут есть условия:

• collation: если база создана не с локалью C, обычный индекс для LIKE не подходит, потому что порядок строк по правилам языка не совпадает с побайтовым. Нужен класс операторов: CREATE INDEX ON users (name text_pattern_ops) или varchar_pattern_ops. Такой индекс не поможет ORDER BY name и сравнениям по правилам локали;
• ведущий %: LIKE '%ivan%' и LIKE '%ivan' B-tree не использует никогда;
• ILIKE: B-tree его почти не обслуживает. Для префиксного поиска без учёта регистра делайте индекс по выражению lower(name) text_pattern_ops и пишите условие lower(name) LIKE lower(?);
• параметры: при серверных prepared statements планировщик может перейти на generic-план, где шаблон неизвестен, и префиксная оптимизация не применится. Проверяйте план с реальными параметрами.

Поиск по подстроке решает расширение pg_trgm: индекс USING gin (name gin_trgm_ops) поддерживает LIKE и ILIKE с любым расположением %, а также регулярные выражения. Шаблоны короче трёх символов он ускоряет плохо. Полнотекстовый поиск (tsvector) это другая задача: он ищет слова и их формы, а не произвольные подстроки.

Со стороны Java: метод Spring Data findByNameContaining генерирует LIKE с % с обеих сторон, то есть без триграмм это полный скан. В самописных запросах экранируйте % и _ из пользовательского ввода.

Где спотыкаются

Уверены, что LIKE 'abc%' использует любой B-tree индекс. На базе с локалью ru_RU.UTF-8 без text_pattern_ops будет Seq Scan.

Предлагают полнотекстовый поиск для подстрок. Он не найдёт «ван» внутри «Иван».

Проверяют на маленькой таблице. На паре тысяч строк планировщик выберет последовательное чтение и при правильном индексе, выводы делайте на реальном объёме.