Ускоряем поиск с использованием LIKE

Прислали мне SQL-запрос с жалобой, что при определенных параметрах он работает около 10 минут. Хотя в большинстве случаев возвращает данные быстрее, чем за секунду. Запрос не простой. В нем объединяется несколько таблиц и вьюшек, а среди всех условий – регистронезависимый поиск по вхождению строки: UPPER(поле) LIKE '%СТРОКА%'. Мои замечания по поводу UPPER и LIKE по вхождению, так же как и предложения изменить запрос или использовать полнотекстовый поиск не приняли, т.к. запрос создан генератором запросов и переписывать его никто не будет. Проблему усугублял планировщик запросов PostgreSQL. Судя по плану, когда запрос возвращал данные, LIKE выполнялся по уже отфильтрованным данным и проверял несколько десятков строк, и время выполнения было приемлемое. А с параметрами при которых результат запроса был пустой, LIKE выполнялся первым условием и перебирал все строки в таблице. Как результат – жуткие тормоза. Это поведение моделировалось на PostgreSQL с 12-й версии по 17-ю. На MS SQL Server план этого запроса составлялся всегда корректно.

Все знают, что оператор LIKE использует индекс только в случае, если он ищет по префиксу. Например, x LIKE 'СТРОКА%'. Но у PostgreSQL это поправимо! У него есть "Generalized Inverted Index" (обобщённый инвертированный индекс или GIN-индекс), который предназначен для случаев, когда индексируемые значения являются составными (array, jsonb, tsvector, text\varchar), а запросы ищут значения элементов в этих составных объектах. GIN-индекс сохраняет набор пар ключ + список идентификаторов, где для text\varchar ключи – это отдельные слова (лексемы), а список идентификаторов содержит идентификаторы строк таблицы, в которых находится слово. У PostgreSQL есть стандартное расширение pg_trgm (под Linux необходимо установить postgresql-contrib). Оно предоставляет функции и операторы для определения схожести алфавитно-цифровых строк на основе триграмм, а также классы операторов индексов, поддерживающие быстрый поиск схожих строк. Его класс оператора индексов gin_trgm_ops позволяет запросам с LIKE, ILIKE, ~ и ~* использовать GIN-индекс для поиска.

Посмотрите, на работу различных вариантов регистронезависимого поиска с использованием оператора LIKE (я не о ILIKE, а о комбинации LIKE + UPPER) при наличии созданного по текстовому полю BTREE или GIN индекса. Для тестов я создал простенькую таблицу

create extension pg_trgm;
create table test
(
  id    SERIAL,
  value TEXT,
  constraint pk_test primary key (id)
);
alter table test alter column value set storage EXTERNAL;

и перелил в нее данные с тестового стенда, на котором проверял работу присланного мне "плохого" запроса.

Поиск по вхождению строки. В "плохом" запросе в строке искали слово "тайна". Будем искать ее тоже.

Как вы видите, LIKE проигнорировал BTREE-индекс (что было ожидаемо), а вот GIN-индекс ему понравился. Кроме того, для "плохого" запроса наличие индекса хорошо повлияло на планировщик запросов и LIKE в нем стал выполнялся всегда по отфильтрованным данным. Оказалось, что "тайна" у меня скрывается почти в 13% строк.

Поиск по окончанию строки. Искать будем все строки заканчивающиеся на слово "документ".

GIN-индекс снова оказался на высоте.

Поиск строки по префиксу. Искать будем все строки начинающиеся на слово "приказ".

GIN-индекс хорош и в поиске строки по префиксу! Но обычный BTREE-индекс еще лучше!

Поиск строки по полному совпадению. Ищем строку "документ №1".

Тут неоспоримая победа BTREE-индекса. Хотя GIN-индекс так же не бесполезен.

Последняя информация к размышлению – GIN-индекс, в отличие от BTREE-индекса, требует больше времени на обновление при DML и значительно больше места на диске:

Что можно сказать по результатам теста? GIN-индекс – мощный инструмент PostgreSQL, позволяющий использовать стандартный LIKE в качестве альтернативы полнотекстового поиска.