Введение

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

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

Полнотекстовый поиск в PostgreSQL: ключевые особенности

Задачи полнотекстового поиска

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

Неэффективность простого поиска

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

SELECT * FROM documents WHERE body LIKE '%keyword%';

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

Поиск с учетом морфологии

Поиск должен учитывать различные формы слов. Например, запрос на слово "категория" должен возвращать документы, где встречаются формы "категорий", "категории" и т. д. Операторы вроде LIKE не позволяют это делать.

SELECT * FROM documents WHERE body LIKE '%категория%' OR body LIKE '%категории%';

Это решается с помощью нормализации словоформ.

Ранжирование результатов

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

Основные подходы к реализации полнотекстового поиска в PostgreSQL

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

Из основных инструментов, которые предлагает PostgreSQL, это tsvector , tsquery и GIN индекс.

Тип данных tsvector

Это тип данных для хранения индексированного текста. Он автоматически нормализует текст, удаляя стоп-слова и преобразуя слова в их основные формы.

Чтобы начать использовать преимущества этого типа данных, достаточно просто создать таблицу с атрибутом специального типа tsvector

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    tsvector_col TSVECTOR
);

И затем заполнить ее нормализованным текстом, используя функцию to_tsvector, которая преобразует текст в формат tsvector.

INSERT INTO documents (title, body, tsvector_col) VALUES 
('Artificial Intelligence', 'Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines.', to_tsvector('english', 'Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines.')),
('Machine Learning', 'Machine learning is a subset of AI that allows systems to learn from data.', to_tsvector('english', 'Machine learning is a subset of AI that allows systems to learn from data.')),
('Deep Learning', 'Deep learning is a subset of machine learning that uses neural networks to model complex patterns.', to_tsvector('english', 'Deep learning is a subset of machine learning that uses neural networks to model complex patterns.')),
('Data Science', 'Data science involves the use of statistical methods to extract insights from data.', to_tsvector('english', 'Data science involves the use of statistical methods to extract insights from data.'));

В результате получим

| id | title                   | body                                                                                                         | tsvector_col                                                                                             |
|----|-------------------------|--------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------|
| 1  | Artificial Intelligence | Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines.        | 'aim':12 'artifici':1 'creat':14 'field':8 'intellig':4,16 'machin':15 'scienc':10 'ai':6,7              |
| 2  | Machine Learning        | Machine learning is a subset of AI that allows systems to learn from data.                                   | 'ai':8 'allow':11 'data':14 'learn':13 'machin':1 'subset':5 'system':10 'learn':3                       |
| 3  | Deep Learning           | Deep learning is a subset of machine learning that uses neural networks to model complex patterns.           | 'complex':18 'deep':1 'learn':2,7 'machin':8 'model':16 'neural':12 'network':13 'pattern':19 'subset':5 |
| 4  | Data Science            | Data science involves the use of statistical methods to extract insights from data.                          | 'data':1,14 'extract':13 'insight':12 'involv':3 'method':9 'scienc':2 'statist':8 'use':7               |

Тип данных tsquery

Тип данных tsquery используется для хранения и выполнения поисковых запросов. Этот тип поддерживает логические операторы, такие как AND, OR, NOT, что делает его гибким и мощным для создания сложных запросов.

Запрос с использованием tsquery выглядит примерно так

SELECT * FROM documents WHERE tsvector_col @@ to_tsquery('machine & learning');

Этот запрос ищет все документы, в которых одновременно присутствуют слова "machine" и "learning". Оператор @@ используется для сопоставления запроса с индексированным текстом (tsvector).

В результате получим следующую выборку

 id |      title       |                                                body                                                |                                                   tsvector_col                                                    
----+------------------+----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------
  2 | Machine Learning | Machine learning is a subset of AI that allows systems to learn from data.                         | 'ai':7 'allow':9 'data':14 'learn':2,12 'machin':1 'subset':5 'system':10
  3 | Deep Learning    | Deep learning is a subset of machine learning that uses neural networks to model complex patterns. | 'complex':15 'deep':1 'learn':2,8 'machin':7 'model':14 'network':12 'neural':11 'pattern':16 'subset':5 'use':10

Здесь поиск выполняется быстро благодаря использованию нормализованных данных в формате tsvector.

Преимущества tsquery

• Логические операторы: можно использовать такие операторы, как & (И), | (ИЛИ), и ! (НЕ) для гибкого составления запросов.

• Префиксные запросы: например, поиск по префиксу можно выполнять с помощью * . Это позволит находить все слова, начинающиеся на "sci", такие как "science".

  SELECT * FROM documents WHERE to_tsvector(body) @@ to_tsquery('sci:*');
  
  >>
   id |          title          |                                                 body                                                  |                                            tsvector_col                                             
  ----+-------------------------+-------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------
    4 | Artificial Intelligence | Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines. | 'ai':3 'aim':11 'artifici':1 'comput':8 'creat':13 'field':6 'intellig':2,14 'machin':15 'scienc':9
    7 | Data Science            | Data science involves the use of statistical methods to extract insights from data.                   | 'data':1,13 'extract':10 'insight':11 'involv':3 'method':8 'scienc':2 'statist':7 'use':5
  

На что стоит обратить внимание при работе с tsvector и tsquery ?

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

Функция to_tsvector используется для преобразования текста в формат tsvector, который представляет собой структуру, пригодную для полнотекстового поиска. Функция разбивает текст на токены (слова), нормализует их, а затем хранит в виде индексов для поиска.

Функция to_tsquery используется для создания запросов для полнотекстового поиска, которые работают с типом данных tsvector. Эти запросы позволяют находить документы, содержащие конкретные слова или их комбинации, используя логические операторы.

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

Например, рассмотри следующие выражения

SELECT to_tsvector('simple','Это простой государственно-правовой текст для теста. This is a simple text example for testing.');

                                                                                         to_tsvector                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 'a':11 'example':14 'for':15 'is':10 'simple':12 'testing':16 'text':13 'this':9 'государственно':4 'государственно-правовой':3 'для':7 'правовой':5 'простой':2 'текст':6 'теста':8 'это':1

SELECT to_tsvector('english', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.');

                                                                      to_tsvector                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 'exampl':14 'simpl':12 'test':16 'text':13 'государственно':4 'государственно-правовой':3 'для':7 'правовой':5 'простой':2 'текст':6 'теста':8 'это':1

SELECT to_tsvector('russian', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.');

                                                             to_tsvector                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 'exampl':14 'simpl':12 'test':16 'text':13 'государствен':4 'государственно-правов':3 'правов':5 'прост':2 'текст':6 'тест':8 'эт':1

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

Например

SELECT to_tsvector('simple', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.')
@@ to_tsquery('simple', 'просто & для & examples & test');
>>
 ?column? 
----------
 f

SELECT to_tsvector('english', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.')
@@ to_tsquery('english', 'просто & для & examples & test');
>>
 ?column? 
----------
 f

SELECT to_tsvector('russian', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.')
@@ to_tsquery('russian', 'просто & для & examples & test');
>>
 ?column? 
----------
 t

Как можно заметить как только мы включили поддержку морфологии обоих языков (конфигурация russian наследуется от english ) запрос выполнился с применением стемминга для русских и аглийских слов.

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

Словарь синонимов позволяет сопоставлять разные слова (синонимы) с одной нормализованной формой для более гибкого поиска. Например, если в запросе используется слово "машина", оно также будет находить документы, содержащие слово "автомобиль".

Чтобы настроить этот словарь, создайте файл с синонимами, например, synonyms_russian.sample, где каждая строка содержит список синонимов, разделенных запятыми

авто, машина, автомобиль
компьютер, ПК, комп

Определите словарь синонимов в PostgreSQL

CREATE TEXT SEARCH DICTIONARY synonym_russian (
  TEMPLATE = synonym,
  FILE = synonyms_russian.sample,   -- путь к файлу синонимов
  DICTIONARY = russian_stem         -- использовать стемминг на русском
);

Добавьте этот словарь в конфигурацию полнотекстового поиска

ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR asciiword, word WITH synonym_russian, russian_stem;

Теперь при поиске по словам "автомобиль" или "машина" будут находиться документы, содержащие оба синонима.

Тезаурус — это расширение словаря синонимов, которое поддерживает более сложные сопоставления между терминами, включая многоуровневые синонимы и словосочетания.

Чтобы настроить тезаурус, создайте файл с тезаурусом, например, thesaurus_russian.sample. В этом файле можно сопоставлять слова и фразы с их основными формами:

automobile: авто, машина, автомобиль
computer: ПК, компьютер, комп

Создайте словарь тезауруса в PostgreSQL

CREATE TEXT SEARCH DICTIONARY thesaurus_russian (
  TEMPLATE = thesaurus,
  FILE = thesaurus_russian.sample,   -- путь к файлу с тезаурусом
  DICTIONARY = russian_stem          -- использовать стемминг
);

Примените этот словарь к конфигурации

ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR asciiword, word WITH thesaurus_russian, russian_stem;

Теперь, используя тезаурус, поиск по слову "машина" может находить также "автомобиль", а запрос по "компьютер" найдет и "ПК", и "комп".

Ispell — это система исправления орфографии, которая может использоваться для нормализации токенов, исправляя орфографические ошибки. Она полезна для обработки текста с возможными опечатками.

Чтобы ее сконфигурировать, создайте файл с правилами Ispell для русского языка. Обычно это два файла: один с аффиксами (например, russian.affix), другой со словарем (например, russian.dict).

Файл с аффиксами (.aff) определяет правила для изменения слов, такие как склонение и спряжение. Он содержит информацию о суффиксах и префиксах, которые могут применяться к словам, и о том, как изменяются формы слов.

Пример файла russian.affix:

SET KOI8-R
TRY аеиоунстлвркмпдгбзчжцйхфшщэё

PFX A Y 1
PFX A   0   пре     .

SFX B Y 2
SFX B   0   а       [^аеёиоуыэюя]
SFX B   0   ов      [^аеёиоуыэюя]
SFX B   а   е       [^аеёиоуыэюя]
SFX B   ов   0      [^аеёиоуыэюя]

SFX C Y 1
SFX C   0   н       .

SFX D Y 2
SFX D   0   и       [^аеёиоуыэюя]
SFX D   и   о       [^аеёиоуыэюя]

В этом примере:

• PFX и SFX обозначают префиксы и суффиксы соответственно.

• A, B, C, D — это группы аффиксов.

• Y обозначает, что правило аффикса активно.

• 0 — означает, что суффикс или префикс не изменяется.

• Например, префикс A говорит, что к слову может быть добавлен префикс "пре", а суффиксы типа B добавляют окончания для склонения существительных.

Файл словаря (.dict) содержит список корней слов, которые могут изменяться в соответствии с правилами из файла аффиксов.

Пример файла russian.dict:

машин/B
программ/A
интеллект/C
робот/D
алгоритм
данн/C
систем/AB

В этом примере:

• машин/B означает, что слово "машин" может изменяться согласно правилам группы суффиксов B из файла аффиксов (например, "машина", "машины", "машиной" и т.д.).

• программ/A означает, что слово "программ" может использовать префиксы или суффиксы из группы A.

• систем/AB — это слово может использовать как префиксы из группы A, так и суффиксы из группы B.

Далее создайте словарь Ispell в PostgreSQL

CREATE TEXT SEARCH DICTIONARY ispell_russian (
  TEMPLATE = ispell,
  DictFile = russian,
  AffFile = russian,
  StopWords = russian
);

Добавьте словарь Ispell в конфигурацию полнотекстового поиска

ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR word, asciiword WITH ispell_russian, russian_stem;

Теперь система будет корректировать орфографические ошибки в тексте на основе правил Ispell.

Стоп-слова — это часто встречающиеся слова, которые не влияют на смысл запроса и, как правило, исключаются из индексации и поиска (например, предлоги, союзы и т.д.).

Чтобы изменить эту часть конфигурации, создайте файл со списком стоп-слов, например, russian_stopwords.sample.

Например

и
в
на
с
для
к

Определите словарь стоп-слов в PostgreSQL

CREATE TEXT SEARCH DICTIONARY stopwords_russian (
  TEMPLATE = simple,
  STOPWORDS = russian_stopwords.sample
);

Добавьте этот словарь в конфигурацию полнотекстового поиска

ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR asciiword, word WITH stopwords_russian, russian_stem;

Теперь все указанные стоп-слова будут исключаться из процесса индексирования и не будут участвовать в поисковых запросах.

И если теперь собрать все это вместе, то для создания своей полноценной конфигурации полнотекстового поиска необходимо выполнить примерно такой скрипт https://postgrespro.ru/docs/postgresql/17/textsearch-dictionaries

CREATE TEXT SEARCH CONFIGURATION my_russian ( COPY = russian );

-- Настройка словаря синонимов
CREATE TEXT SEARCH DICTIONARY synonym_russian (
  TEMPLATE = synonym,
  FILE = synonyms_russian.sample,
  DICTIONARY = russian_stem
);

-- Настройка тезауруса
CREATE TEXT SEARCH DICTIONARY thesaurus_russian (
  TEMPLATE = thesaurus,
  FILE = thesaurus_russian.sample,
  DICTIONARY = russian_stem
);

-- Настройка Ispell для исправления орфографии
CREATE TEXT SEARCH DICTIONARY ispell_russian (
  TEMPLATE = ispell,
  DictFile = russian,
  AffFile = russian,
  StopWords = russian
);

-- Настройка стоп-слов
CREATE TEXT SEARCH DICTIONARY stopwords_russian (
  TEMPLATE = simple,
  STOPWORDS = russian_stopwords.sample
);

-- Применение всех словарей к конфигурации
ALTER TEXT SEARCH CONFIGURATION my_russian
  ALTER MAPPING FOR word, asciiword WITH synonym_russian, thesaurus_russian, ispell_russian, stopwords_russian, russian_stem;

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

SELECT to_tsvector('my_russian', 'Это простой государственно-правовой текст для теста. This is a simple text example for testing.')
@@ to_tsquery('my_russian', 'просто & для & examples & test');

Индексы GIN

Индекс GIN (Generalized Inverted Index) — это тип индекса в PostgreSQL, который эффективно обрабатывает многозначные атрибуты, такие как массивы, JSON, и, что наиболее важно в нашем случае, полнотекстовые поисковые запросы через tsvector. GIN индексы ускоряют процесс поиска по текстовым данным, особенно при работе с морфологически сложными языками.

Создадим индекс GIN для нашего поля tsvector_col, чтобы ускорить выполнение запросов

CREATE INDEX idx_fulltext ON documents USING GIN(tsvector_col);

Индекс GIN работает по принципу инвертированного индекса, где каждому слову (лексеме) соответствует список документов, содержащих эту лексему. Основная структура GIN состоит из следующих элементов:

1. Ключ (lexeme): каждая лексема из текста представляет собой ключ в индексе.

2. Список значений (Posting list): для каждого ключа создается список всех документов (или строк таблицы), в которых встречается эта лексема. В случае PostgreSQL, для полнотекстового поиска этот список будет включать ссылки на строки таблицы, содержащие лексему.

3. Структура дерева: GIN индекс построен в виде дерева, где каждая лексема связана с документами. Это позволяет быстро находить нужные документы по лексеме.

Когда создаётся индекс GIN на поле tsvector_col, процесс поиска по текстовым данным значительно ускоряется за счёт следующих особенностей:

1. Инвертированный индекс: в отличие от стандартного полного сканирования таблицы, GIN индекс хранит лексемы и их позиции в виде инвертированного индекса. Это значит, что запрос может сразу обратиться к нужной лексеме и получить список документов, в которых она присутствует, минуя ненужные строки.

2. Быстрое сопоставление лексем с документами: когда запрос использует оператор @@, PostgreSQL обращается к GIN индексу и быстро извлекает все документы, где встречаются лексемы, указанные в запросе. Например, запрос to_tsquery('fox & dog') будет искать документы, где встречаются обе лексемы ("fox" и "dog").

3. Поиск по множеству лексем: если запрос содержит несколько ключевых слов (лексем), например, fox & dog, GIN индекс позволяет одновременно искать несколько лексем и их пересечения, что значительно ускоряет выполнение запроса по сравнению с последовательным обходом.

Рассмотрим запрос и обсудим, как будет работать GIN индекс.

SELECT * FROM documents WHERE tsvector_col @@ to_tsquery('fox & dog');

Шаг 1: Использование GIN индекса:

• PostgreSQL обращается к GIN индексу и ищет, в каких документах встречаются лексемы "fox" и "dog".

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

• В инвертированном индексе GIN для каждой лексемы уже сохранён список документов, в которых она присутствует. Например:

  • "fox" — документы 1, 2, 5.

  • "dog" — документы 2, 3, 5.

• PostgreSQL быстро находит пересечение этих списков (документы 2 и 5) и возвращает только их.

Шаг 3: Чтение данных:

• После того как PostgreSQL нашёл документы с нужными лексемами, он извлекает полные строки данных и возвращает их как результат запроса.

И как это нам поможет?

• Быстрота выполнения: GIN индекс позволяет избежать полного сканирования таблицы (sequential scan) при поиске текстовых данных, что особенно полезно при больших объёмах данных. Запросы, которые без индекса могли бы занять минуты, с GIN индексом выполняются за миллисекунды.

• Меньшее использование ресурсов: за счёт использования индекса минимизируется количество данных, которые нужно прочитать с диска, что снижает нагрузку на дисковую систему и процессор.

• Поддержка сложных запросов: GIN индекс поддерживает поиск по нескольким словам (лексемам), используя логические операторы (например, AND, OR, NOT). Это делает его незаменимым для полнотекстового поиска, где важны пересечения множества лексем.

На что стоит обратить внимание?

1. Медленное обновление: создание и обновление индексов GIN может быть медленным, особенно для больших таблиц, что делает их менее подходящими для динамических данных, которые часто обновляются.

2. Размер индекса: индексы GIN могут занимать значительное место в базе данных. Это может стать проблемой при ограниченных ресурсах.

Чем же нам GIN индекс может помочь?

Для нашей таблички рассмотри вот такой простой как топор запрос

SELECT * FROM documents WHERE body LIKE '%Artificial%' AND body LIKE '%learning%';

Если взглянуть на план запроса, то увидим, что там происходит банальный полный перебор всех записей

                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on public.documents  (cost=0.00..19.45 rows=1 width=100) (actual time=0.009..0.020 rows=2 loops=1)
   Output: id, title, body, tsvector_col
   Filter: ((documents.body ~~ '%Artificial%'::text) AND (documents.body ~~ '%learning%'::text))
   Rows Removed by Filter: 27
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.075 ms
 Execution Time: 0.031 ms

Аналогичный запрос с использованием tsvector даст следующий результат.

SELECT * FROM documents WHERE tsvector_col @@ to_tsquery('Artificial & learning');

                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Seq Scan on public.documents  (cost=0.00..175.38 rows=1 width=100) (actual time=0.029..0.128 rows=3 loops=1)
   Output: id, title, body, tsvector_col
   Filter: (documents.tsvector_col @@ to_tsquery('Artificial & learning'::text))
   Rows Removed by Filter: 26
   Buffers: shared hit=2
 Planning Time: 0.048 ms
 Execution Time: 0.135 ms

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

Теперь добавим GIN индекс и посмотрим, что изменится.

CREATE INDEX idx_fulltext ON documents USING GIN(tsvector_col);
SELECT * FROM documents WHERE tsvector_col @@ to_tsquery('Artificial & learning');

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.documents  (cost=20.25..24.51 rows=1 width=100) (actual time=0.044..0.045 rows=3 loops=1)
   Output: id, title, body, tsvector_col
   Recheck Cond: (documents.tsvector_col @@ to_tsquery('Artificial & learning'::text))
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_fulltext  (cost=0.00..20.25 rows=1 width=0) (actual time=0.041..0.041 rows=3 loops=1)
         Index Cond: (documents.tsvector_col @@ to_tsquery('Artificial & learning'::text))
         Buffers: shared hit=5
 Planning:
   Buffers: shared hit=4 read=4
 Planning Time: 0.206 ms
 Execution Time: 0.062 ms
(12 rows)

В итоге мы видим, что при сохранении полноты поисковой выдачи (нашли все три документа), косты резко сократились cost=20.25..24.51 против cost=0.00..175.38 ранее. И это заметно даже для нашей маленькой таблички. На больших объемах данных такой вариант поиска будет значительно опережать и обычный LIKE .

BitMap Index Scan, который вы видите в планах запросов, — это не полный перебор строк. Вместо этого PostgreSQL создает битовую карту (bitmap) всех строк, которые соответствуют вашему запросу, и затем использует ее для выборки нужных строк. Это быстрее, чем последовательное сканирование таблицы по нескольким причинам:

• Минимизация операций I/O: PostgreSQL не выполняет непосредственное чтение всех строк сразу, а создает карту (bitmap) в памяти, которая хранит только информацию о том, какие строки нужно извлечь.

• Параллелизм: BitMap Scan может обрабатываться параллельно, что еще больше ускоряет процесс.

Третий план (с использованием GIN индекса) является лучшим вариантом на практике, несмотря на несколько более высокие затраты и использование буферов. Вот почему:

1. Масштабируемость: хотя первый план быстрее на небольших данных, он не масштабируется. Второй план использует более сложный поиск, но всё ещё полагается на полное сканирование таблицы, что делает его медленным на больших объемах данных.

2. Использование индекса: третий план использует GIN индекс, который существенно ускоряет поиск, особенно на больших наборах данных. Полнотекстовый поиск с индексами обычно быстрее и эффективнее, чем последовательное сканирование таблиц, когда размер данных увеличивается.

3. Более эффективное управление ресурсами: Bitmap Heap Scan и Bitmap Index Scan оптимизируют использование памяти и позволяют системе избегать полного сканирования таблицы, что критично для производительности.

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

Как PostgreSQL ранжирует поисковую выдачу?

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

1. Запрос включает несколько ключевых слов: если пользователь ищет документ с несколькими словами, важна не только их наличие, но и то, насколько тесно они связаны друг с другом.

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

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

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

PostgreSQL предоставляет несколько встроенных функций для ранжирования результатов поиска:

• ts_rank(): оценивает частоту встречаемости ключевых слов из запроса в документе.

• ts_rank_cd(): оценивает "плотность" найденных ключевых слов, т.е. как близко друг к другу они находятся в документе.

Выполним полнотекстовый поиск по ключевым словам "AI" и "machine learning", используя ts_rank() для ранжирования результатов:

SELECT body, ts_rank(tsvector_col, to_tsquery('AI & machine & learning')) AS rank
FROM documents
WHERE tsvector_col @@ to_tsquery('AI | machine | learning')
ORDER BY rank DESC;

В результате получим

                                                 body                                                  |    rank     
-------------------------------------------------------------------------------------------------------+-------------
 Machine learning is a subset of AI that allows systems to learn from data.                            |   0.3400137
 Deep learning is a subset of machine learning that uses neural networks to model complex patterns.    |  0.18152626
 Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines. | 0.021730691

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

Теперь улучшим наш запрос с помощью ts_rank_cd(), который учитывает не только частоту слов, но и их близость друг к другу

SELECT body, ts_rank_cd(tsvector_col, to_tsquery('AI & machine & learning')) AS rank
FROM documents
WHERE tsvector_col @@ to_tsquery('AI | machine | learning')
ORDER BY rank DESC;

В результате получим

                                                 body                                                  | rank 
-------------------------------------------------------------------------------------------------------+------
 Machine learning is a subset of AI that allows systems to learn from data.                            | 0.02
 Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines. |    0
 Deep learning is a subset of machine learning that uses neural networks to model complex patterns.    |    0

Здесь документы, где ключевые слова расположены близко друг к другу (например, "Artificial Intelligence and machine learning"), получают более высокий ранг. Это особенно полезно, если важен не только сам факт наличия слов, но и контекст их использования в тексте.

Когда требуется ранжирование?

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

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

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

Функция ts_rewrite

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

До появления функции ts_rewrite, для работы с синонимами или эквивалентами приходилось вручную добавлять в поисковые запросы все возможные вариации лексем. Это делало запросы громоздкими и сложными в управлении. Например, если вы хотите найти документы, где встречаются слово "Artificial intelligence" или его синоним "AI", вам приходилось заранее закладывать все возможные варианты в запрос:

Например

SELECT * FROM documents WHERE to_tsvector(body) @@ to_tsquery('(Artificial & intelligence) | AI | ML');

 id |          title          |                                                 body                                                  |                                            tsvector_col                                             
----+-------------------------+-------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------
  1 | Artificial Intelligence | Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines. | 'ai':3 'aim':11 'artifici':1 'comput':8 'creat':13 'field':6 'intellig':2,14 'machin':15 'scienc':9
  2 | Machine Learning        | Machine learning is a subset of AI that allows systems to learn from data.                            | 'ai':7 'allow':9 'data':14 'learn':2,12 'machin':1 'subset':5 'system':10

Недостатки такого подхода:

1. Запросы становятся сложными, если необходимо учитывать множество синонимов.

2. Каждое изменение набора синонимов требовало бы изменения в запросах или в логике приложения, что повышало сложность поддержки системы.

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

С появлением функции ts_rewrite, стало возможно определять синонимы и автоматически переписывать поисковые запросы в зависимости от правил, не изменяя структуру запроса или индексов. Это значительно упрощает работу с запросами и улучшает релевантность результатов.

Как это работает:

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

• Упрощение логики: вместо необходимости постоянно вносить изменения в запросы, можно хранить синонимы в отдельной таблице и динамически переписывать запросы во время их выполнения.

Пример использования ts_rewrite

• Создадим таблицу для хранения синонимов:

CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);

• Вставим данные в таблицу синонимов. Здесь, например, для слова "AI" добавлен синоним "Artificial intelligence":

INSERT INTO aliases VALUES(to_tsquery('AI'), to_tsquery('Artificial & intelligence | AI'));
INSERT INTO aliases VALUES(to_tsquery('ML'), to_tsquery('Machine & learning | ML'));

• Теперь, используя функцию ts_rewrite, мы можем динамически переписать запрос:

SELECT ts_rewrite(to_tsquery('AI & ML'), 'SELECT * FROM aliases');

• Результат

                             ts_rewrite                             
--------------------------------------------------------------------
 ( 'ml' | 'machin' & 'learn' ) & ( 'ai' | 'intellig' & 'artifici' )

Здесь запрос автоматически переписывается: вместо простого поиска по слову "AI" он также ищет документы, содержащие "Artificial intelligence", не требуя от разработчика указывать это вручную.

SELECT * FROM documents 
WHERE to_tsvector(body) @@ ts_rewrite(to_tsquery('AI | ML'), 'SELECT * FROM aliases');

>>
 id |          title          |                                                 body                                                  |                                                   tsvector_col                                                    
----+-------------------------+-------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------
  1 | Artificial Intelligence | Artificial intelligence (AI) is a field of computer science that aims to create intelligent machines. | 'ai':3 'aim':11 'artifici':1 'comput':8 'creat':13 'field':6 'intellig':2,14 'machin':15 'scienc':9
  2 | Machine Learning        | Machine learning is a subset of AI that allows systems to learn from data.                            | 'ai':7 'allow':9 'data':14 'learn':2,12 'machin':1 'subset':5 'system':10
  3 | Deep Learning           | Deep learning is a subset of machine learning that uses neural networks to model complex patterns.    | 'complex':15 'deep':1 'learn':2,8 'machin':7 'model':14 'network':12 'neural':11 'pattern':16 'subset':5 'use':10

Что же стало лучше с использованием ts_rewrite:

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

2. Гибкость: легкость изменения набора синонимов без необходимости переиндексации данных или переписывания логики запросов.

3. Улучшение производительности: запросы остаются простыми и более оптимальными для выполнения, так как не требуется создание сложных конструкций на уровне приложений.

Операторы фразового поиска: <-> и

Эти операторы предназначены для создания запросов, которые ищут лексемы (ключевые слова) в тексте, находящиеся рядом друг с другом или на заданном расстоянии друг от друга.

Оператор <-> используется для поиска документов, где два слова следуют друг за другом (соседние лексемы).

Например

SELECT to_tsvector('english', 'fat cat'), to_tsvector('english', 'fat cat') @@ to_tsquery('fat <-> cat');

Результат

   to_tsvector   | ?column? 
-----------------+----------
 'cat':2 'fat':1 | t

Этот запрос вернёт true, потому что слова "fat" и "cat" следуют друг за другом.

Оператор <N> используется для поиска документов, где два слова идут в заданном порядке и находятся на расстоянии ровном ровно N .

Например

SELECT to_tsvector('english', 'fat rat and cat'), to_tsvector('english', 'fat rat and cat') @@ to_tsquery('fat <3> cat');

Результат

       to_tsvector       | ?column? 
-------------------------+----------
 'cat':4 'fat':1 'rat':2 | t

Запрос вернёт true, так как "fat" предшествует "cat" и они находятся на расстоянии равном 3 ('cat':4 - 'fat':1).

Функции tsquery_phrase

Функция tsquery_phrase() предоставляет удобный синтаксис для работы с фразовым поиском. Она делает запросы более понятными и предоставляет дополнительную гибкость при задавании расстояния между словами.

tsquery_phrase(query1 tsquery, query2 tsquery) Создаёт фразовый запрос, эквивалентный оператору <->.

Например

SELECT to_tsvector('english', 'fat cat'), to_tsvector('english', 'fat cat') @@ tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'));

Результат

   to_tsvector   | ?column? 
-----------------+----------
 'cat':2 'fat':1 | t

Этот запрос вернёт true, так как "fat" и "cat" следуют друг за другом.

tsquery_phrase(query1 tsquery, query2 tsquery, distance integer) позволяет задавать расстояние между двумя словами.

Например

SELECT to_tsvector('english', 'fat rat and cat'), to_tsvector('english', 'fat rat and cat') @@ tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 3);

Результат:

       to_tsvector       | ?column? 
-------------------------+----------
 'cat':4 'fat':1 'rat':2 | t

Запрос вернёт true, так как между "fat" стоит перед "cat" и находится и находится на расстоянии равном 3 (cat':4 - 'fat':1).

Вместо заключения

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

Простые методы, такие как LIKE, неэффективны для поиска по большим текстам, особенно когда пользователям нужно искать фразы или синонимы. PostgreSQL предлагает мощные инструменты, такие как tsvector, tsquery, и индексы GIN, которые ускоряют поиск, учитывая морфологию и предоставляя возможность ранжирования результатов по релевантности.

Что почитать?

• https://pganalyze.com/blog/gin-index

• http://www.sai.msu.su/~megera/postgres/fts/fts.pdf

• https://rachbelaid.com/postgres-full-text-search-is-good-enough/

• https://postgrespro.ru/docs/postgresql/17/textsearch

• https://postgrespro.ru/docs/postgresql/17/functions-textsearch