Глава 10. Релевантный поиск

Главы

1. Введение в документы
2. Базовые возможности JSON
3. JSON в таблицах
4. Индексирование JSON
5. Ограничения в документах
6. Язык путей JSONPath
7. Отчеты, функции, расписание
8. Функции на языке Python
9. Версии и патчи
10. Релевантный поиск

Содержание

• Главы
• Поиск в конкатенации полей
• Слоеный поиск
• Замечания к UNION
• Последующая обработка
• Замена UNION на FULL JOIN
• Коротко о MapReduce
• Поиск в исторической таблице
• О быстродействии
• Дальнейшие улучшения
• Страничная навигация
• Полнотекстовый поиск
• Объект запроса
• Определение и хранение языка
• Полнотекстовый слой в UNION
• Оптимизации и доработки
• Заключение

В последней главе речь пойдет о релевантном поиске документов. При таком поиске документу назначено число, которое означает, насколько точно он подходит запросу. Мы узнаем, как выбирать документы по разным критериям, ранжировать и объединять их двумя способами. В числе прочего мы рассмотрим полнотекстовый поиск силами tsvector и tsquery.

В главе про индексы мы упомянули поиск документов. Поиск необходим любой системе, неважно стартап ли это или крупный банк. В основе поиска лежат правильные индексы, но их одних недостаточно: понадобятся и другие техники.

К поиску подходят разными способами. В одном из них пользователь заполняет форму: какое поле содержит какое значение. Интерфейс программы выглядит примерно так:

┌───────────────────────────────────────────┐
│                                           │
│   Номер заявки                            │
│  ┌──────────────────────────┐             │
│  │12345                     │             │
│  └──────────────────────────┘             │
│                                           │
│   Название организации                    │
│  ┌──────────────────────────┐             │
│  │Acme                      │             │
│  └──────────────────────────┘             │
│                                           │
│   Сотрудник                               │
│  ┌──────────────────────────┐             │
│  │Maria                     │             │
│  └──────────────────────────┘             │
│                                           │
│   Комментарий                             │
│  ┌──────────────────────────┐             │
│  │reconciliation            │             │
│  └──────────────────────────┘             │
│                                           │
│  ┏━━━━━━━━━━━━━━┓                         │
│  ┃    Найти     ┃                         │
│  ┗━━━━━━━━━━━━━━┛                         │
│                                           │
└───────────────────────────────────────────┘

Клиент отправляет форму на сервер. По четырем полям приложение строит запрос с условиями:

select
    id, doc
from
    applications
where
        (doc #>> '{application_id}')          =     '12345'
    and (doc #>> '{organization,short_name}') ilike '%acme%'
    and (doc #>> '{created_by,name}')         ilike '%maria%'
    and (doc #>> '{comment}')                 ilike '%reconciliation%'
limit
    100;

Подход “поле – значение” был популярен и встречается до сих пор. Однако со временем стало ясно: чем меньше полей требуется от клиента, тем проще ему пользоваться программой. С появлением сервисов вроде Гугла и Яндекса форма поиска сократилась до одного поля. Привыкнув к минимализму, некоторые пользователи теряются, видя интерфейс с дюжиной полей.

В случае Гугла и Яндекса минимализм, конечно, ложный. Поисковое выражение может содержать параметры: искать только на указанном сайте, обязательно включить одно слово, исключить другое. Поисковые системы опираются на историю запросов, геолокацию, интересы пользователя, собранные из разных источников. Запрос, отправленный клиентом, играет далеко не решающую роль. В этом легко убедиться, если выполнить в Гугле два одинаковых запроса под разными учетными записями.

Мы не можем построить поиск такого же масштаба, однако возьмем его за ориентир. Задача в том, чтобы принять от клиента как можно меньше данных и вернуть документы, которые соответствуют запросу в некоторой степени. Эта степень называется релевантностью, и мы рассмотрим ее ниже.

Поиск в конкатенации полей

В главе про индексы мы обсудили следующий прием. Предположим, форма имеет только одно поле – поисковое выражение (терм):

┌─────────────────────────────────────────────────────────────┐
│                                                             │
│  Номер заявки, ФИО сотрудника, комментарий (см. справку)    │
│ ┌──────────────────────────────────────────────┐┏━━━━━━━━━┓ │
│ │12345                                         │┃  Поиск  ┃ │
│ └──────────────────────────────────────────────┘┗━━━━━━━━━┛ │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Это выражение проверяется для многих полей с разной логикой. Если пользователь ввел 123456, мы должны показать заявки, где:

• либо номер заявки включает 123456;
• либо название организации, подавшей заявку, включает 123456;
• либо над заявкой работал пользователь, чье имя содержит 1234565;
• либо в комментарии встречается такая последовательность.

Очевиден шаблон: каждое условие – отбор по определенному полю; условия объединяются оператором OR:

select
    id, doc
from
    applications
where
       (doc #>> '{application_id}')          ilike '%12398%'
    or (doc #>> '{organization,short_name}') ilike '%12398%'
    or (doc #>> '{created_by,name}')         ilike '%12398%'
    or (doc #>> '{comment}')                 ilike '%12398%'
limit
    100;

Для каждого поля и оператора создан индекс: btree для равенства или триграммный для вхождения. Если требуется пятый критерий, в часть WHERE добавляется новое поле, и для него строится индекс.

Подход со многими полями можно обобщить – объединить их в одно и искать по нему. Для этого пишут функцию, которая принимает документ и возвращает строку. В ней через пробел следуют поля, по которым ищут документ:

create or replace function immutable_concat_ws(text, variadic text[])
  returns text
  language internal immutable parallel safe AS 'text_concat_ws';

create or replace function application_search_pattern(doc jsonb)
returns text
language sql immutable strict parallel safe
return immutable_concat_ws(
    ' ',
    (doc #>> '{application_id}'),
    (doc #>> '{organization,short_name}'),
    (doc #>> '{created_by,name}'),
    (doc #>> '{comment}')
);

Создается триграммный индекс на выражение с функцией:

create index if not exists
idx_applications_application_trgm_pattern
on applications using gin
((application_search_pattern(doc)) gin_trgm_ops);

С ним поиск документов выглядит так:

select id from applications
where application_search_pattern(doc) ilike '%12345%'
limit 100;

Если вам что-либо непонятно из кода выше, обратитесь к третьей главе, где мы подробно объяснили каждое действие.

Несмотря на преимущества, подход с единым выражением обладает недостатком: поиск по нему не знает о релевантности. Оператор ilike сообщает, что совпадение нашлось, но в какой именно части – неизвестно. Наоборот, нам бы хотелось это знать, потому что поля следуют по убыванию важности. Чем ближе к началу нашлось совпадение, тем выше должен быть документ в выдаче.

Предположим, для двух документов функция application_search_pattern возвращает строки как в примере ниже (пробелы добавлены для читаемости):

code  org name         user name     comment
61312 Global Corp      Suzanne Smith reconciliation started ref #991015123
10151 General Acme Inc John Brown    some long comment about the app

Видим, что последовательность 10151 встретилась в обоих случаях, однако в первой строке это комментарий (последнее поле), а во втором – код заявки (первое). Из-за меньшей позиции второй документ считается более приоритетным, и мы должны показать его раньше. Однако оператор ilike не сообщает позицию, в которой нашлось совпадение. Если не принять мер, в поисковой выдаче документы окажутся в произвольном порядке.

Слоеный поиск

Рассмотрим технику, которая определяет приоритет точнее. Идея в следующем: документы разбивают на группы (слои), которым назначают им число – показатель релевантности (ранг). В нашем поиске выделим следующие группы:

• код заявки в точности равен поисковому значению;
• код организации равен поисковому значению;
• код заявки содержит значение;
• код организации содержит значение;
• комментарий содержит значение.

Мы не случайно разделяем точность и вхождение. Во-первых, если пользователь ищет заявку 999, будет странно обнаружить ее на десятом месте после 9991, 3999 и других чисел с тремя девятками. Во-вторых, точное совпадение опирается на индекс btree, который быстрее триграммного.

Назначим группам нарастающие числа: 10, 20, 30 и так далее. Чем меньше этот показатель, тем раньше в выдаче идет строка. Интервал 10 подобран с умыслом: с ним между группами можно вставить еще одну, например с рангом 15. Если же интервал равен единице, придется сдвигать остальные ранги или переходить к вещественным числам: 1.0, 1.5, 2.0.

Для каждой группы пишут отдельный SELECT, который выбирает ключ документа и его ранг. Напомним, первая группа – та, где номер заявки в точности равен введенному выражению. Ее запрос следующий:

select id, 10 as rank
from applications
where (doc #>> '{application_id}') = '12398'

Ожидаемый результат:

┌──────────────────────────────────────┬──────┐
│                  id                  │ rank │
├──────────────────────────────────────┼──────┤
│ 00000000-0000-0000-0000-000000012398 │   10 │
└──────────────────────────────────────┴──────┘

Чтобы поиск по равенству был быстрым, понадобится btree-индекс. А поскольку номер заявки уникален, добавим директиву unique:

create unique index idx_doc_application_id
on applications ((doc #>> '{application_id}'))
nulls not distinct;

Вторая группа – равенство коду организации. Запрос аналогичен тому, что мы написали выше, отличаются только поле и ранг:

select id, 20 as rank
from applications
where (doc #>> '{organization.code}') = '12398'

Конкретно для 12398 результат будет пустым, потому что в базе нет организаций с таким кодом.

Третья группа – поиск по вхождению строки в номер заявки. Здесь мы используем триграммный поиск с шаблоном:

select id, 30 as rank
from applications
where (doc #>> '{application_id}') ilike '%12398%'

Запрос возвратит около десяти документов:

┌──────────────────────────────────────┬──────┐
│                  id                  │ rank │
├──────────────────────────────────────┼──────┤
│ 00000000-0000-0000-0000-000000912398 │   30 │
│ 00000000-0000-0000-0000-000000012398 │   30 │
│ 00000000-0000-0000-0000-000000112398 │   30 │
│ 00000000-0000-0000-0000-000000123980 │   30 │
│ 00000000-0000-0000-0000-000000123981 │   30 │
│ 00000000-0000-0000-0000-000000123982 │   30 │
│ 00000000-0000-0000-0000-000000123983 │   30 │
│ 00000000-0000-0000-0000-000000123984 │   30 │
│ 00000000-0000-0000-0000-000000123985 │   30 │

Четвертая группа – то же самое, но для кода клиента:

select id, 40 as rank
from applications
where (doc #>> '{organization.code}') ilike '%12398%'

Пятая и последняя – вхождение строки в комментарий:

select id, 50 as rank
from applications
where (doc #>> '{comment}') ilike '%12398%'

Частичный результат:

┌──────────────────────────────────────┬──────┐
│                  id                  │ rank │
├──────────────────────────────────────┼──────┤
│ 00000000-0000-0000-0000-000000912398 │   50 │
│ 00000000-0000-0000-0000-000000012398 │   50 │
│ 00000000-0000-0000-0000-000000112398 │   50 │
│ 00000000-0000-0000-0000-000000123980 │   50 │
│ 00000000-0000-0000-0000-000000123981 │   50 │
│ 00000000-0000-0000-0000-000000123982 │   50 │

Во всех случаях мы выбираем только ключ и ранг, но не сам документ. Это сделано нарочно: на данном этапе документ избыточен, требуются лишь минимальные сведения о нем.

Имея пять отдельных запросов, объединим их оператором UNION ALL. Слово ALL необходимо, чтобы Postgres не пытался удалить дубликаты строк – их все равно не будет. Даже если один и тот же документ встречается в разных выборках, у них будет разный ранг, и записи будут отличаться.

Предположим, пользователь ввел строку 12398 нажал ввод. Вот каким окажется промежуточный запрос:

select id, 10 as rank
from applications
where (doc #>> '{application_id}') = '12398'

union all

select id, 20 as rank
from applications
where (doc #>> '{organization.code}') = '12398'

union all

select id, 30 as rank
from applications
where (doc #>> '{application_id}') ilike '%12398%'

union all

select id, 40 as rank
from applications
where (doc #>> '{organization.code}') ilike '%12398%'

union all

select id, 50 as rank
from applications
where (doc #>> '{comment}') ilike '%12398%'

limit 200;

Если выполнить этот запрос, получим таблицу ниже. Изучите ее, а в следующем параграфе мы обсудим, какие доработки ей требуются.

┌──────────────────────────────────────┬──────┐
│                  id                  │ rank │
├──────────────────────────────────────┼──────┤
│ 00000000-0000-0000-0000-000000012398 │   10 │
│ 00000000-0000-0000-0000-000000812398 │   30 │
│ 00000000-0000-0000-0000-000000712398 │   30 │
│ 00000000-0000-0000-0000-000000312398 │   30 │
│ 00000000-0000-0000-0000-000000912398 │   30 │
│ 00000000-0000-0000-0000-000000012398 │   30 │
│ 00000000-0000-0000-0000-000000112398 │   30 │
│ 00000000-0000-0000-0000-000000123980 │   30 │
│ 00000000-0000-0000-0000-000000123981 │   30 │
│ 00000000-0000-0000-0000-000000123982 │   30 │
│ 00000000-0000-0000-0000-000000123983 │   30 │
│ 00000000-0000-0000-0000-000000123984 │   30 │
│ 00000000-0000-0000-0000-000000123985 │   30 │
│ 00000000-0000-0000-0000-000000123986 │   30 │
│ 00000000-0000-0000-0000-000000123987 │   30 │
│ 00000000-0000-0000-0000-000000123988 │   30 │
│ 00000000-0000-0000-0000-000000123989 │   30 │
│ 00000000-0000-0000-0000-000000212398 │   30 │
│ 00000000-0000-0000-0000-000000412398 │   30 │
│ 00000000-0000-0000-0000-000000512398 │   30 │
│ 00000000-0000-0000-0000-000000612398 │   30 │
│ 00000000-0000-0000-0000-000000812398 │   50 │
│ 00000000-0000-0000-0000-000000012398 │   50 │
│ 00000000-0000-0000-0000-000000112398 │   50 │
│ 00000000-0000-0000-0000-000000123980 │   50 │
│ 00000000-0000-0000-0000-000000123981 │   50 │
│ 00000000-0000-0000-0000-000000123982 │   50 │
│ 00000000-0000-0000-0000-000000123983 │   50 │

Замечания к UNION

Результат, что мы получили, заслуживает некоторых обсуждений. Во-первых, за счет ранга четко видны группы (слои), к которым относятся документы. В нашем случае они пришли из первой, третьей и пятой групп (ранги 10, 30 и 50), а второй и четвертый запрос ничего не нашли.

Во-вторых, записи следуют в том же порядке, в каком расположены запросы в UNION. Это случайность: порядок запросов в UNION не влияет на порядок финальных строк. В простейшем случае Postgres выполнит запросы по одному и объединит результат. Но если условия позволяют, запросы могут быть выполнены параллельно, и предсказать порядок строк невозможно. Первый слой может оказаться третьим, второй – первым, а иногда записи из разных запросов чередуются, как зубчики в замке-молнии.

Третье важное замечание – один и тот же документ может быть в разных слоях. Выше мы привели пример, где последовательность 12345 встречалась в коде заявки и комментарии. Если присмотреться, наша выборка тоже содержит повторы: документ 00000000-0000-0000-0000-000000012398 встречается в трех слоях с рангами 10, 30 и 50. Показать его трижды в выдаче значит запутать пользователей.

Из сказанного следует, что выборка (id, rank), прежде чем присоединять к ней документы, нуждается в обработке. Сперва удалим повторные документы с учетом их ранга: из записей с одинаковым id оставить ту, что имеет наименьший ранг. Например, если документ А встречается с рангами 10, 20 и 30, оставим только ранг 10. Затем, добившись уникальности документов, упорядочим их по рангу.

Последующая обработка

Задача решается разными способами. Один из них в том, чтобы вынести UNION в подзапрос или общее табличное выражение. Сгруппируем его по ключу документа (id), а рангу назначим агрегатную функцию MIN – наименьшее значение. Упорядочим по наименьшему рангу результат, и задача решена. Все вместе дает запрос (приведем в сокращении):

with
layers as (

  select id, 10 as rank
  from applications
  where (doc #>> '{application_id}') = '12398'

  union all

  select id, 20 as rank
  from applications
  where (doc #>> '{organization.code}') = '12398'

  union all

  select id, 30 as rank
  from applications
  where (doc #>> '{application_id}') ilike '%12398%'

  union all

  select id, 40 as rank
  from applications
  where (doc #>> '{organization.code}') ilike '%12398%'

  union all

  select id, 50 as rank
  from applications
  where (doc #>> '{comment}') ilike '%12398%'

)
select
    id, min(rank) as rank
from
    layers
group by id
order by 2;

Выражение order by 2 означает сортировку по второму столбцу, в нашем случае – минимальному рангу. Выполним запрос и убедимся, что дубликаты ушли и порядок записей верный:

┌──────────────────────────────────────┬──────┐
│                  id                  │ rank │
├──────────────────────────────────────┼──────┤
│ 00000000-0000-0000-0000-000000012398 │   10 │
│ 00000000-0000-0000-0000-000000112398 │   30 │
│ 00000000-0000-0000-0000-000000123980 │   30 │
│ 00000000-0000-0000-0000-000000123981 │   30 │
│ 00000000-0000-0000-0000-000000123982 │   30 │
│ 00000000-0000-0000-0000-000000123983 │   30 │
│ 00000000-0000-0000-0000-000000123984 │   30 │
│ 00000000-0000-0000-0000-000000123985 │   30 │
│ 00000000-0000-0000-0000-000000123986 │   30 │
│ 00000000-0000-0000-0000-000000123987 │   30 │
│ 00000000-0000-0000-0000-000000123988 │   30 │
│ 00000000-0000-0000-0000-000000123989 │   30 │
│ 00000000-0000-0000-0000-000000212398 │   30 │
│ 00000000-0000-0000-0000-000000312398 │   30 │
│ 00000000-0000-0000-0000-000000412398 │   30 │
│ 00000000-0000-0000-0000-000000512398 │   30 │
│ 00000000-0000-0000-0000-000000612398 │   30 │
│ 00000000-0000-0000-0000-000000712398 │   30 │
│ 00000000-0000-0000-0000-000000812398 │   30 │
│ 00000000-0000-0000-0000-000000912398 │   30 │
└──────────────────────────────────────┴──────┘

Обратите внимание, что записи из пятого слоя исчезли: все они встречались в слоях с рангом 10 или 30 и были исключены группировкой.

Теперь добавим к полученной выборке документы. Поместим ее в табличное выражение с именем grouped и свяжем с таблицей applications по полю id:

with
layers as (
  ...
),
grouped as (
  select
      id, min(rank) as rank
  from
      layers
  group by id
  order by 2
)
select
    g.id, a.doc
from grouped g
join applications a on g.id = a.id;

Полученный запрос и есть то, к чему мы стремились:

• он выбирает документы по разным критериям;
• записи упорядочены по рангу;
• дубликаты удаляются, при этом выигрывает документ с минимальным рангом, то есть наилучший вариант.

Подобные запросы полезно изображать схемой: так легче понять их устройство. Приведем схему для нашего запроса:

┌───────────────────────────────────────────────────────────────┐
│┌───────────────┐                                              │
││    id, 10     │──┐                                           │
│└───────────────┘  │                                           │
│                   │                                           │
│┌───────────────┐  │                                           │
││    id, 20     │──┤                                           │
│└───────────────┘  │   ┌──────────────────┐    ┌──────────────┐│
│                   │   │                  │    │              ││
│┌───────────────┐  │   │remove duplicates │    │    fetch     ││
││    id, 30     │──┼──▶│   sort by rank   │───▶│  documents   ││
│└───────────────┘  │   │                  │    │              ││
│                   │   └──────────────────┘    └──────────────┘│
│┌───────────────┐  │                                           │
││    id, 40     │──┤                                           │
│└───────────────┘  │                                           │
│                   │                                           │
│┌───────────────┐  │                                           │
││    id, 50     │──┘                                           │
│└───────────────┘                                              │
└───────────────────────────────────────────────────────────────┘

Из нее видно, что в левую часть можно добавить новые слои, удалить текущие, поменять им ранги – на последующих шагах это не скажется.

Замена UNION на FULL JOIN

Запрос можно переписать так, чтобы он не использовал UNION. Для этого результаты собирают не по вертикали, а по горизонтали при помощи FULL JOIN. Первый слой выступает точкой опоры, а последующие “цепляются” к результату, просматривая ключи слева в поисках первого отличного от NULL. Для ясности приведем схему:

┌─────────────────┬─────────────────┬─────────────────┬─────────────────┬─────────────────┐
│   app.code =    │   org.code =    │ app.code ilike  │ org.code ilike  │  comment ilike  │
├────────┬────────┼────────┬────────┼────────┬────────┼────────┬────────┼────────┬────────┤
│   id   │  rank  │   id   │  rank  │   id   │  rank  │   id   │  rank  │   id   │  rank  │
├────────┴────────┼────────┼────────┼────────┴────────┼────────┴────────┼────────┴────────┤
                  │  1002  │   20   │
│                 ├────────┼────────┼────────┬────────┤                 │                 │
                  │  1003  │   20   │  1003  │   30   │
│                 ├────────┼────────┼────────┼────────┤                 │                 │
                  │  1004  │   20   │  1004  │   30   │
├────────┬────────┼────────┼────────┼────────┼────────┤                 │                 │
│  1005  │   10   │  1005  │   20   │  1005  │   30   │
├────────┼────────┼────────┴────────┼────────┼────────┤                 │                 │
│  1006  │   10   │                 │  1006  │   30   │
├────────┼────────┤                 ├────────┼────────┤                 │                 │
│  1007  │   10   │                 │  1007  │   30   │
├────────┼────────┤                 ├────────┴────────┤                 │                 │
│  1008  │   10   │
├────────┴────────┤                 │                 ├────────┬────────┤                 │
                                                      │  1009  │   40   │
│                 │                 │                 ├────────┴────────┼────────┬────────┤
                                                                        │  1010  │   50   │
└ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┴────────┴────────┘

Так выглядят слои, соединенные FULL JOIN по полю id. Видим, что в каждой строке есть хотя бы один ключ: полностью пустых строк получиться не может. Если строка пересекает более одного слоя, это значит, один и тот же документ нашелся в нескольких выборках.

Полученную матрицу нужно свернуть, то есть получить финальные ключ и ранг. Для обоих полей алгоритм одинаков: движемся слева направо и берем первое отличное от NULL значение. Поскольку ранги слоев нарастают тоже слева направо, первый непустой ранг будет минимальным. Вот что вернет описанный алгоритм:

┌────────┬────────┐
│   id   │  rank  │
├────────┼────────┤
│  1002  │   20   │
├────────┼────────┤
│  1003  │   20   │
├────────┼────────┤
│  1004  │   20   │
├────────┼────────┤
│  1005  │   10   │
├────────┼────────┤
│  1006  │   10   │
├────────┼────────┤
│  1007  │   10   │
├────────┼────────┤
│  1008  │   10   │
├────────┼────────┤
│  1009  │   40   │
├────────┼────────┤
│  1010  │   50   │
└────────┴────────┘

Полученную свертку сортируют по рангу. Результат готов к тому, чтобы присоединить документы и вернуть их клиенту.

┌────────┬────────┐
│   id   │  rank  │
├────────┼────────┤
│  1005  │   10   │
├────────┼────────┤
│  1006  │   10   │
├────────┼────────┤
│  1007  │   10   │
├────────┼────────┤
│  1008  │   10   │
├────────┼────────┤
│  1002  │   20   │
├────────┼────────┤
│  1003  │   20   │
├────────┼────────┤
│  1004  │   20   │
├────────┼────────┤
│  1009  │   40   │
├────────┼────────┤
│  1010  │   50   │
└────────┴────────┘

Приведем запрос, построенный по этой схеме:

with
matrix as (
  select
      coalesce(sub1.id, sub2.id, sub3.id, sub4.id, sub5.id) as id,
      coalesce(sub1.rank, sub2.rank, sub3.rank, sub4.rank, sub5.rank) as rank
  from (
      select id, 10 as rank
      from applications app
      where (doc #>> '{application_id}') = '12398'
  ) as sub1

  full join (
      select id, 20 as rank
      from applications
      where (doc #>> '{organization.code}') = '12398'
  ) as sub2 on coalesce(sub1.id) = sub2.id

  full join (
      select id, 30 as rank
      from applications
      where (doc #>> '{application_id}') ilike '%12398%'
  ) as sub3 on coalesce(sub1.id, sub2.id) = sub3.id

  full join (
      select id, 40 as rank
      from applications
      where (doc #>> '{organization.code}') ilike '%12398%'
  ) as sub4 on coalesce(sub1.id, sub2.id, sub3.id) = sub4.id

  full join (
      select id, 50 as rank
      from applications
      where (doc #>> '{comment}') ilike '%12398%'
  ) as sub5 on coalesce(sub1.id, sub2.id, sub3.id, sub4.id) = sub5.id

  order by 2
)
select
    matrix.id, app.doc
from
    matrix
join applications app
    on matrix.id = app.id;

В запросе часто встречается выражение coalesce. Напомним, что это не функция, а особая форма, которая принимает произвольное аргументы и возвращает первый отличный от NULL. Аргументы вычисляются лениво по мере того как Postgres перебирает их. Конструкция coalesce(sub1.id, sub2.id, sub3.id, ...) сканирует столбцы слева направо и возвращает первый не пустой. В случае с рангами форма coalesce(sub1.rank, sub2.rank, ...) работает как функция LEAST, потому что мы специально расположили ранги по нарастанию.

Какой именно способ предпочесть – по вертикали с UNION или горизонтали с FULL JOIN, – зависит от предпочтений. UNION несколько проще в понимании и удобстве: можно переставить слои местами, и это не повлияет на дальнейшие шаги. Для FULL JOIN это не так, потому что перестановка нарушит выражения coalesce, по которым мы соединяем слои. Текст ниже подразумевает, что мы остановили выбор на UNION.

Коротко о MapReduce

Поиск, что мы написали, отдаленно напоминает технику MapReduce. Согласно ей, поиск информации проходит в два этапа. На первом этапе (map – назначить, разметить) приложение собирает данные из разных источников. На втором этапе (reduce – свернуть, понизить) из каждого фрагмента берутся лучшие экземпляры. Вместе они образуют результат поиска.

MapReduce предполагает, что данные приходят не только из реляционных таблиц, но и вообще отовсюду, включая файлы, сетевые хранилища, сервисы. В нашей книге мы работаем с SQL и не можем выйти за пределы реляционной базы. Технически возможно обратиться из Postgres к другой базе данных (postgres_fdw) или HTTP-сервису (pgsql-http), однако такие трюки мы не рассматриваем.

Поиск в исторической таблице

Доработаем поиск так, чтобы наряду с таблицей applications он учитывал и другие. Так, в прошлой главе мы рассмотрели версии документов и таблицу history. Коротко рассмотрим, чем она может быть полезна.

Предположим, организация сменила название с “Acme Inc” на “Global Corp”. Пользователь, который еще об этом не знает, вводит “Acme” и ничего не находит – в таблице applications нет заявки с organization.short_name, равным Acme. Но если выполнить поиск в history, мы найдем версию документа с прежним названием. По полю pk мы присоединим актуальный документ, и он окажется в выдаче.

Задача сводится к тому, чтобы добавить в подзапрос layers новый слой. Его ранг еще ниже – 60, потому что исторические данные интересуют нас в последнюю очередь. Приведем запрос с учетом истории:

with
layers as (

  select id, 10 as rank
  from applications
  where (doc #>> '{application_id}') = '12398'

  union all

  select id, 20 as rank
  from applications
  where (doc #>> '{organization.code}') = '12398'

  union all

  select id, 30 as rank
  from applications
  where (doc #>> '{application_id}') ilike '%12398%'

  union all

  select id, 40 as rank
  from applications
  where (doc #>> '{organization.code}') ilike '%12398%'

  union all

  select id, 50 as rank
  from applications
  where (doc #>> '{comment}') ilike '%12398%'

  union all

  select pk, 60 as rank
  from history
  where
      entity = 'application'
  and created_at > now() - interval '1 week'
  and (doc #>> '{organization,short_name}') ilike '%12398%'
),
grouped as (
  select
      id, min(rank) as rank
  from
      layers
  group by id
  order by 2
)
select
    g.id, a.doc
from grouped g
join applications a on g.id = a.id;

Последний слой (ранг 60) интересен следующим. Во-первых, мы выбираем поле pk, а не id, потому что id – код версии, а нам нужен код документа (pk). Во-вторых, обратите внимание на фильтрацию по created_at. Поиск по всей истории будет дорогим и избыточным, поэтому ограничим его разумным диапазоном, например за последнюю неделю. Диапазон можно передавать параметром. Также учтите отбор по полю entity – историческая таблица хранит все сущности, и нам не хотелось бы искать что-либо, отличное от заявок.

Убедитесь, что исторический запрос попадает в индекс. Скопируйте часть select pk, 60 as rank..., предварите ее командой explain analyze и выполните c разными параметрами.

О быстродействии

До сих пор мы откладывали вопрос быстродействия нашего поиска. На первый взгляд кажется, что он сложен: без сокращений запрос занимает больше экрана. Однако это не значит, что он медленный. Обобщая, можно сказать, что длина запроса мало связана с производительностью. Большой запрос может выполняться быстро и наоборот: короткий, но неоптимальный запрос замедляет базу.

Далее по тексту мы берем слово “быстрый” в кавычки. Этим мы подчеркиваем относительность термина, ведь на каждой машине один и тот же запрос ведет себя по-разному в зависимости от оборудования, настроек и объема данных. Под быстротой мы имеем в виду прогнозируемость и постоянство. Например, если поиск по номеру заявки занимает три миллисекунды, потому что опирается на индекс, то при добавлении еще миллиона записей время почти не увеличится. И наоборот: запрос, быстрый на небольшом числе записей, но без индекса, перестанет быть таковым с ростом данных.

Когда запускается поиск с UNION, выполняются SELECT-запросы слоев: точное равенство, ilike и другие. Если условие попадает в индекс, выборка производительна. Предполагается, что мы создали индексы для каждого слоя и все они “быстрые”. В UNION запросы могут выполняться параллельно – при условии, что это позволяет окружение и планировщик выбрал такой алгоритм. Из этих соображений можно сделать вывод, что часть UNION тоже “быстрая”.

Шаг, на котором удаляются дубли, включает группировку по ключу документа. Хотя группировка считается дорогой операцией, ее реальное время зависит от объема данных. Если предположить, что каждый слой UNION вернул в среднем пятьдесят записей, а всего слоев пять, записей окажется не более двухсот пятидесяти. Это немного, и даже на слабой машине их группировка недолгой.

Последний шаг, где мы присоединяем документы, тоже хорошо прогнозируется. Условие JOIN опирается на первичный ключ id таблицы applications, который индексирован. Соединение двухсот документов по ключам тоже сработает “быстро”. Итого – если каждый шаг “быстрый” (опирается на индекс), запросом можно пользоваться, не боясь деградации базы.

Привести полные планы запросов мы не сможем, потому что они слишком велики; ограничимся лишь финальными материками. На ноутбуке Macbook M4 Pro 48G поиск с UNION ALL занял в среднем 12 миллисекунд (Execution Time), cost верхнего узла равен 2951..4621. Запрос с FULL JOIN оказался эффективней: 3 миллисекунды (Execution Time), стоимость верхнего узла – 3795..4636. Полные планы вы найдете в репозитории с исходным кодом глав.

Дальнейшие улучшения

Изучив метрики, подумаем, как можно улучшить поиск. Одна из доработок в следующем: перед тем как выполнить запрос, проверьте поисковое выражение на разные критерии. Уже на этом этапе можно определить, что некоторые слои не понадобятся, и не включать их в построитель SQL.

Очевидный случай – пустая строка или содержащая только незначащие символы (пробелы, перенос строки, табуляция). Такие запросы не должны выполняться, потому что напрасно расходуют ресурсы. Другой вариант – проверка на определенные шаблоны, например, что номер заявки содержит только цифры. Если в поиск ввели что-то отличное от цифр, поиск по номеру будет заведомо пустым, и этот слой исключают из UNION. Обратная ситуация – имя пользователя: если на вход поступили только цифры, поиск по такому “имени” ничего не даст.

В главе про индексы мы упоминали, что триграммному индексу нужны хотя бы три символа. Иногда пользователь ищет заявку с коротким номером: 26 или 88. В такой ситуации триграммные слои не участвуют в запросе.

Другая доработка касается длины выборки. На текущий момент каждый слой UNION неограничен – мы получим столько записей, сколько подошло условию. Для точных совпадений это не проблема: мы получим одну или несколько строк. Однако неточные совпадения порой дают сотни записей. Например, для выражения 123 мы получим документы 551235, 9123, 123512 и так далее. Если предположить, что код заявки меняется от единицы до миллиона, то кодов, содержащих “123”, окажется много.

Сколько именно? Проверим следующим запросом:

select count(*)
from generate_series(1, 1000000) as _(x)
where x::text like '%123%'

-- 3999

По поиску “123” мы получим без одного четыре тысячи документов! Недостаток такого слоя в том, что он нарушает технику MapReduce. Согласно ей, из каждого слоя выбирается часть записей, а не все подряд. Клиенту будет неудобно просматривать столь длинную выдачу. Кроме того, неограниченная выборка может замедлить запрос даже с учетом индекса.

Ограничим каждый SELECT оператором LIMIT. Конкретное значение зависит от многих факторов и подбирается вручную; в случае автора им оказалось число 50. Приведем фрагмент запроса, где в каждый слой добавлен LIMIT:

with
layers as (

  select * from (
    select id, 10 as rank
    from applications
    where (doc #>> '{application_id}') = '12398'
    limit 50
  )

  union all

  select * from (
    select id, 20 as rank
    from applications
    where (doc #>> '{organization.code}') = '12398'
    limit 50
  )

Обратите внимание: мы обернули каждый SELECT в подзапрос и выбираем из него. Дело в том, что если добавить LIMIT 250 в конец UNION (250 мы получили, умножив 50 на число слоев), оператор действует на объединение в целом, а не слои. Мы словно говорим Postgres: выбери из нескольких запросов не более 250 записей, а в какой пропорции – решай сам. Может оказаться, что все записи пришли из одного слоя, а до остальных даже не дошло. Чтобы избежать диспропорции, каждый слой оборачивается в подзапрос с персональным LIMIT, в нашем случае 50.

Порой определенному слою делают исключение и выделяют больший LIMIT. Стремитесь к тому, чтобы основные показатели слоев (ранг и limit) хранились в настройках приложения и передавались параметрами.

Страничная навигация

Возможно, читатель задался вопросом: как организовать в поиске страничную навигацию (пагинацию)? Хотя технически это возможно, мы воздержимся от решения на страницах книги. Во-первых, оно требует деталей, которые уведут нас от темы. Во-вторых, прежде чем садиться за пагинацию, подумаем – нужна ли она вообще?

На первый взгляд ответ очевиден: разумеется да! В каждой поисковой системе есть навигатор, с помощью которого мы переходим на вторую, третью и другие страницы. Иной раз число страниц поражает воображение. Так, по слову “python” Гугл находит почти 400 миллионов ссылок (если точнее, 394,000,000). Если предположить, что одна страница вмещает десять ссылок, всего страниц окажется 40 миллионов. Одному человеку физически не под силу их разобрать.

Чем больше номер страницы, тем меньше людей ее посещают. Эта зависимость даже не линейна, а обратно экспоненциальна: с увеличением номера посещаемость падает в разы или даже на порядки. Как давно вы искали что-нибудь на седьмой странице Гугла? Как часто заходите так далеко?

Другой довод против пагинации – она раскрывает данные. Если из логов видно, что клиент перебирает страницы с первой по десятитысячную, очевидно, это бот, собирающий данные. Здесь поиск оказывает услугу злоумышленнику.

Мы подталкиваем читателя к следующей мысли. Вместо того, чтобы делить выдачу на страницы и предлагать клиентам листать ее, улучшайте качество поиска. Пусть клиент получит не миллион, а только сто записей, которые совпадают с его запросом. Если нужного документа нет, пусть клиент перефразирует запрос. Определите наиболее частые сценарии поиска и оптимизируйте их. Поговорите с клиентами: чего они ожидают от поиска, насколько довольны его результатом? Скорее всего, вы получите много замечаний, и все они будут связаны с точностью, а не размером выдачи.

Полнотекстовый поиск

Напоследок рассмотрим еще один вопрос, связанный с качеством поиска. До сих мы писали запросы с точным совпадением (=) и соответствием шаблону (ilike). Postgres предлагает полнотекстовый поиск с богатыми возможностями. Среди них — поддержка многих языков, включая русский, позиция слов, расстояние между ними, словари синонимов и многое другое.

Чтобы начать работу с поиском, не требуется расширений: все доступно по умолчанию. Однако желательно, чтобы перед этим разработчик изучил документацию. На сайте Postgres Pro она занимает одиннадцать разделов; каждый из них рассматривает свой аспект поиска с примерами.

В этой книге мы не ставим цель пересказывать документацию. Ограничимся лишь кратким введением, достаточным, чтобы внедрить полнотекстовый поиск в наш запрос.

Поиск в Postgres опирается на понятие “документ” — специальный вектор, полученный из текста. Просим не путать его с JSON-документом, о котором мы говорим всю книгу. Чтобы избежать путаницы, по возможности будем писать “поисковой документ” и “JSON-документ”.

Чтобы получить поисковой документ из обычного текста, его преобразуют следующим образом:

• все символы приводятся к нижнему регистру.
• Удаляются незначащие (стоп-) слова: союзы, предлоги, артикли (английские a, an, the, французские le, la).
• Удаляются окончания слов, чтобы падеж и число не влияли на результат. Усеченные слова называют лексемами.
• Каждая лексема знает позицию в исходном тексте. Это необходимо для расчета близости слов: чем она меньше, тем выше релевантность. Каждая лексема хранится в одном экземпляре. Если в тексте их несколько, перечислены все позиции.
• Каждой лексеме можно назначить вес, который позже учитывается в ранге.

Для хранения поискового документа служит особый тип tsvector, а для запроса к нему – tsquery. В обоих случаях префикс ts_ означает text search. С него начинаются все типы и функции, имеющих отношение к поиску.

Чтобы получить документ из текста, вызовите функцию to_tsvector с указанием языка и произвольным текстом:

select to_tsvector('russian', 'Все животные равны, но некоторые животные равнее других');

Вот что окажется в векторе. Обратите внимание на изменения, что мы перечислили: регистр, предлоги, окончанния, повторы слов.

┌──────────────────────────────────────────────┐
│                 to_tsvector                  │
├──────────────────────────────────────────────┤
│ 'друг':8 'животн':2,6 'некотор':5 'равн':3,7 │
└──────────────────────────────────────────────┘

Функцию to_tsquery можно вызвать без языка (первого аргумента). В этом случае сработает язык по умолчанию; узнать его можно командой show:

show default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english

Если применить к тексту не тот язык, это резко ухудшит его обработку. Вызовем to_tsquery еще раз с языком по умолчанию (английским):

select to_tsvector('Все животные равны, но некоторые животные равнее других');

┌─────────────────────────────────────────────────────────────────────────────┐
│                                 to_tsvector                                 │
├─────────────────────────────────────────────────────────────────────────────┤
│ 'все':1 'других':8 'животные':2,6 'некоторые':5 'но':4 'равнее':7 'равны':3 │
└─────────────────────────────────────────────────────────────────────────────┘

Английский язык не исключил союз “но” – с его точки зрения это обычное слово. Предлоги, союзы и артикли встречаются в тексте часто и, если не удалить их, приводят к ложным совпадениям. Слово “животные” не потеряло окончание: если подать на вход слово “животное”, совпадения не будет. Также русская грамматика приводит букву “ё” к “е”. Хотя этот случай не входит в наш пример, он тоже важен для поиска.

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

Объект запроса

Когда мы получили поисковой документ, его проверяют на соответствие запросу. Запрос – это сущность с типом tsquery. Из него тоже удаляются союзы, артикли и незначащие слова; у слов отбрасываются окончания. Вместе это приводит к лучшему совпадению запроса с вектором.

В общем случае запрос получают функцией to_tsquery. Ее первый необязательный документ – язык, который, как и в случае с tsvector, оказывает решающее значение на разбор слов.

Важно, что в запросе не может быть пробелов между словами. Вместо них стоят операторы, которые означают, как обрабатывать слова. Наиболее частый оператор & означает, что слова встречаются в документе без учета порядка. Оператор <-> строже: совпадение случится, если в поисковом документе слова идут в том же порядке, что и в запросе. Палочка | означает либо одно слово, либо другое.

select to_tsquery('russian', 'все & животные & равны');

┌───────────────────┐
│    to_tsquery     │
├───────────────────┤
│ 'животн' & 'равн' │
└───────────────────┘

Для получения tsquery служит целое семейство функций, каждая со своей особенностью. Среди прочих нас интересует plainto_tsquery, которая подходит под самый востребованный случай. Она принимает обычный текст, находит лексемы и объединяет их оператором &.

select plainto_tsquery('russian', 'все животные равны');

┌───────────────────┐
│  plainto_tsquery  │
├───────────────────┤
│ 'животн' & 'равн' │
└───────────────────┘

Полученный запрос проверяет, что в документ содержит все указанные слова без учета порядка или расстояния.

Определение и хранение языка

Postgres не предлагает способа определить язык, и как это сделать – остается на ваше усмотрение. Один из способов в том, чтобы вызвать библиотеку langdetect на Python через расширение plpython3u. Приведем минимальный пример:

import langdetect
from langdetect import detect, detect_langs

text = "Все животные равны, но некоторые животные равнее других"

print(detect(text))
print(detect_langs(text))

# ru
# [ru:0.9999961866373268]

Функция detect возвращает наиболее вероятный язык, а detect_langs – их список с вероятностью каждого, если подходят несколько вариантов.

Для Perl написано семейство модулей Lingua, один из которых служит для определения текста. Для вызова кода на Perl установите расширение plperl. Автор уверен, что после главы об интерпретаторах читатель справится с этой задачей.

Значения tsvector и tsquery должны быть произведены с одинаковым языком. Как мы уже видели, в противном случае для одинаковых слов получаются разные лексемы, и качество поиска снижается.

Язык, что мы передаем в функции, на самом деле не строка, а более сложный тип — regconfig. Список поддерживаемых языков мы узнаем запросом:

select cfgname from pg_ts_config;

┌────────────┐
│  cfgname   │
├────────────┤
│ simple     │
│ arabic     │
│ armenian   │
│ basque     │
│ catalan    │
│ danish     │
│ dutch      │
│ english    │
│ finnish    │
│ french     │
│ german     │
│ greek      │
│ hindi      │
│ hungarian  │
│ indonesian │
│ irish      │
│ italian    │
│ lithuanian │
│ nepali     │
│ norwegian  │
│ portuguese │
│ romanian   │
│ russian    │
│ serbian    │
│ spanish    │
│ swedish    │
│ tamil      │
│ turkish    │
│ yiddish    │
└────────────┘

Упоминания заслуживает первый элемент simple — самый простой, примитивный анализатор текста. Постарайтесь не использовать его в боевом запуске.

Один из способов добиться того, чтобы язык tsvector и tsquery был одинаков – хранить его в отдельной колонке:

alter table applications add column lang regconfig null;

update applications set lang = 'english';

Приведем функцию, которая по документу определяет язык. В ней поле comment передается в функцию detect модуля langdetect. Далее ответ detect приводится к одному из значений regconfig силами промежуточного словаря. Для краткости мы добавили в него только три элемента:

create or replace function app_detect_lang(doc jsonb)
returns regconfig
transform for type jsonb
language plpython3u immutable strict parallel safe as $$
    from langdetect import detect
    lang = detect(doc["comment"])

    mapping = {
        "en": "english",
        "ru": "russian",
        "fr": "french"
    }

    if lang in mapping:
        return mapping[lang]
    else:
        return 'simple'
$$;

Определение языка можно вынести в фоновую задачу pg_cron. Задача обновляет те документы, где язык еще не определен:

update applications
set lang = app_detect_lang(doc)
where lang is null;

Полнотекстовый слой в UNION

Теперь когда у нас есть поисковой документ и запрос, проверим их на соответствие. Для этого служит оператор @@, с которым мы познакомились в главе про JSON Path. У него схожая семантика: документ слева проверяется на соответствие запросу справа. Оператор возвращает истину или ложь:

select to_tsvector('russian', 'Все животные равны, но некоторые животные равнее других')
    @@ plainto_tsquery('russian', 'животные равны') as res;

┌─────┐
│ res │
├─────┤
│ t   │
└─────┘

Убедимся, что из-за неверного языка совпадения не будет:

select to_tsvector('russian', 'Все животные равны, но некоторые животные равнее других')
    @@ plainto_tsquery('english', 'животные равны') as res;

┌─────┐
│ res │
├─────┤
│ f   │
└─────┘

Встроим полнотекстовый поиск в наш запрос. Предположим, в комментариях к заявкам пишут большие сообщения. Ранее мы использовали триграммный поиск, но его стало не хватать. Идея в том, чтобы заменить этот слой в операторе UNION на другой, основанный на tsvector. Новый слой выглядит так:

  select * from (
    select id, 50 as rank
    from applications
    where to_tsvector('english', doc->>'comment') @@ to_tsquery('english', '12398')
    limit 50
  ) as sub5

Без индекса полнотекстовый поиск будет медленным. Объявите его следующим образом:

create index idx_application_comment_tsvector
on applications
using gin (to_tsvector('english', doc->>'comment'));

Обратите внимание, что индекс зависит от языка, в нашем случае – английского. В запросе с другим языком индекс не будет использоваться. Поэтому повторим: необходимо знать язык документа заранее, в идеале быстро вычислять его или хранить в колонке. В промышленном запуске значение 'english' заменяют на колонку lang, где хранится язык.

Оптимизации и доработки

К полнотекстовому поиску применяются все оптимизации, что мы рассмотрели для триграммного. Можно индексировать не одно поле, а несколько за счет конкатенации строк. Объединяет строки оператор || или функция concat_ws:

where to_tsvector(lang, concat_ws(
    ', ',
    doc #>> '{comment}',
    doc #>> '{description}',
    doc #>> '{path,to,field}'
)) @@ plainto_tsquery(lang, '12398')

Логику, которая вычисляет tsvector, лучше поместить в функцию и построить по ней индекс. Тем самым достигается изоляция и снижение копирования. Функция:

create or replace function app_tsvector(doc jsonb, lang regconfig)
returns tsvector
language sql immutable strict parallel safe
return ...;

Индекс:

create index idx_application_comment_tsvector
on applications
using gin (app_tsvector(doc, lang));

Поиск:

select id, doc from applications
where app_tsvector(doc, lang) @@ <query>

Поисковой вектор можно вынести в вычисляемый столбец. При обновлении документа перестроится и вектор:

alter table applications
add column _ts_vect tsvector generated always
as (app_tsvector(doc, lang)) stored;

select id, doc from applications
where _ts_vect @@ <query>

Еще одна оптимизация: вынести вектор в отдельную таблицу по принципу “один к одному”:

create table applications_tsvector (
    app_id uuid primary key references applications(id),
    tsvect tsvector not null
);

Чтобы найти документ, сперва мы фильтруем таблицу applications_tsvector по полю tsvect, а затем JOIN-им документ по app_id. Это позволяет индексировать документ, не затрагивая таблицу applications. Тем самым достигается разделение ответственности: сущности – отдельно, поисковой индекс – отдельно.

Когда вектор находится в отдельной таблице, можно индексировать документы отложенно. Сперва документ пишется только в applications, чтобы его индексирование не замедляло вставку. Затем фоновая задача заполняет applications_tsvector для этого документа. Если мы обнаружили, что вектор можно улучшить (об этом ниже), мы пересчитаем его, не затрагивая таблицу applications.

Вектор можно улучшить по многим критериям. Один из них в том, что иногда документ содержит фрагменты на разных языках, например основная часть изложена на русском, а цитаты – на английском. Предположим, мы написали скрипт, который делит документ на языковые блоки. Для каждого блока вычисляется tsvector. Полученные векторы объединяются оператором || (“палочки”), который порождает новый вектор. В нем окажутся все уникальные лексемы, разобранные соответствующим языком. Такой вектор обслуживает запросы на русском и английском без потери качества.

Давайте получим вектор из двух других: русского и английского.

select to_tsvector(
    'russian',
    'Все животные равны но некоторые животные равнее других'
) || to_tsvector(
    'english',
    'All animals are equal, but some animals are more equal than others'
) as vec;

┌────────────────────────────────────────────────────────────────────────────────────┐
│                                        vec                                         │
├────────────────────────────────────────────────────────────────────────────────────┤
│ 'anim':10,15 'equal':12,18 'other':20 'друг':8 'животн':2,6 'некотор':5 'равн':3,7 │
└────────────────────────────────────────────────────────────────────────────────────┘

Убедимся, что запросы на разных языках ему соответствуют (<res> означает вектор из прошлого примера):

select <res> @@ plainto_tsquery('russian', 'животные равны') as res;
-- true

select <res> @@ plainto_tsquery('english', 'animal equal') as res;
-- false

Другие возможные улучшения – подключить словари стоп-слов и синонимов, специфичных для вашей отрасли. Скажем, можно задать правило, чтобы при разборе текста слово “application” сокращалось до “app”. В этом случае запрос “app 123” найдет документ, где в исходном тексте встречается “Application 123”. То же самое касается слова “customer”, которое часто сокращают до “cust”. Устойчивые сочетания слов можно свести к аббревиатурам, например “country of risk” → “cor”, “know your customer” → “kyc” и другие. В разделе “Textsearch Dictionaries” документации Postgres эта техника описана подробно.

Еще больше сведений, связанных с поиском и релевантностью, вы найдете в англоязычной статье “Postgres as a search engine”. На Хабре доступен ее перевод на русский язык.

Заключение

Подведем итоги этой главы. Для поиска документов используют разные стратегии. В простом случае это точное совпадение с btree-индексом. Такой поиск быстр и потребляет мало ресурсов. Индексированное поле можно использовать для сортировки.

Поиск на вхождение строки реализуют силами триграмм. Строка может быть шаблоном, где часть символов заменяется маской (один любой символ, произвольное число символов). Триграммный индекс поддерживает регулярные выражения. Они полезны для внутренних нужд, например чтобы найти документы по особо сложному критерию.

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

Поиск бывает гибридным, когда несколько запросов объединяют в один при помощи UNION. Промежуточные результаты называют группами или слоями. Каждому слою назначают числовую оценку – ранг или релевантность. По этой колонке сортируется итоговый результат, и клиент увидит первыми те документы, которые точнее соответствуют запросу.

Работая над поиском, не спешите внедрять сторонние решения вроде ElasticSearch, OpenSearch и аналогов. Даже если их результаты точнее, затраты на ввод в эксплуатацию и поддержку окажутся значительными. Идите на этот шаг только если полностью исчерпали потенциал Postgres.

Не существует универсального рецепта поиска. В каждой фирме используют свой подход, отталкиваясь от конкретных задач. Надеемся, мы привели достаточно примеров и техник, из которых читатель составит поиск под свои нужды.