Кейс: поиск по префиксу в автокомплите скроллил всю таблицу

Автокомплит поиска по названию товара работал через LIKE 'запрос%' и обычный B-tree индекс по названию. На маленькой таблице всё было быстро — вопрос всплыл только на масштабе.

Проблема была не в самом индексе — B-tree отлично работает с ведущим wildcard, диапазон 'запрос' до 'запросъ' сканируется по дереву, а не по всей таблице. Проблема была в том, что запрос делали через ILIKE для регистронезависимого поиска, а обычный B-tree индекс регистр не игнорирует.

Postgres в таком случае делает Seq Scan по всей таблице, потому что индекс просто не может ответить на запрос без учёта регистра. На маленьком объёме данных это незаметно, на нескольких миллионах строк каждый ввод символа в поле поиска давал полное сканирование.

Решение — функциональный индекс по lower(name) и запрос через lower(name) LIKE lower(?) || '%'. Индекс снова стал применим, план вернулся к Index Scan.

// было: ILIKE не использует B-tree String sql = "SELECT * FROM products " + "WHERE name ILIKE ?";

// стало: явный lower() под индекс String sql = "SELECT * FROM products " + "WHERE lower(name) LIKE lower(?)";

Что спросят следом: почему тогда не сделать индекс регистронезависимым по умолчанию, коллацией на уровне колонки. Можно, но это меняет поведение всех остальных запросов к этому полю, включая точные сравнения и сортировку — обычно дешевле сделать один функциональный индекс под конкретный сценарий поиска, чем менять коллацию всей таблицы.

EXPLAIN ANALYZE перед деплоем автокомплита на реальном объёме данных стоил бы получаса и сэкономил бы вечер разбора инцидента.

Тренажёр: 600 вопросов, мок с таймером, план повторов

senior·base — что спрашивают на самом деле

В этом посте были ссылки, но мы их удалили по правилам Сетки