PostgreSQL: полнотекстовый поиск и поиск по фразам

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

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

Это вернет строки, где column_nameсоответствует pattern. Никаких сюрпризов.

У вас есть подстановочные знаки, такие как %(например, LIKE 'a%'для поиска столбцов, начинающихся с «a») и _(например, LIKE '_r%'для поиска любых значений, которые имеют «r» во второй позиции); а в PostgreSQL вы также можете использовать ILIKEдля игнорирования случаев. Это достаточно просто и, надеюсь, вы уже хорошо знакомы.

Но PostgreSQL — это РСУБД, способная на гораздо большее, чем просто хранение и извлечение данных. Еще в 2012 году в версии 9.2 был представлен набор функций и операторов текстового поиска, которые часто остаются незамеченными, давайте сначала обсудим их и посмотрим, как они помогают текстовому поиску.

Что такое полнотекстовый поиск?

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

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

Например, вы можете выполнить поиск, если присутствуют существительные «собака» и «лиса», поэтому, если они стоят в форме единственного числа, вы найдете их по ключевому слову LIKE...

SELECT * FROM table_name  
WHERE  
    column_name LIKE '%fox%' AND
    column_name LIKE '%dog%';

... но вы также найдете такие вещи, как «фокстрот» или «догвилль», что не совсем то, что вы хотели.

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

Более эффективным способом решения этой проблемы является получение семантического вектора для всех слов, содержащихся в документе, т. е. языкового представления таких слов. Таким образом, когда вы ищете такое слово, как «прыгать», вы найдете все экземпляры этого слова и его времена, даже если вы искали «прыгал» или «прыгал». Кроме того, вы будете искать не весь документ (что медленно), а вектор (что быстро).

То есть в двух словах принцип полнотекстового поиска. Давайте посмотрим, как это работает.

Введение в tsvector

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

• to_tsvectorдля создания списка токенов ( tsvectorтип данных, где tsрасшифровывается как «текстовый поиск»);
• to_tsqueryдля запроса вектора на наличие определенных слов или фраз.

Например, чтобы создать вектор для предложения «быстрая коричневая лиса перепрыгнула через ленивую собаку» , мы можем сделать следующее:

SELECT to_tsvector('The quick brown fox jumped over the lazy dog.');

Который вернет вектор, где каждая лексема является лексемой (единицей лексического значения) с указателями (позициями в документе), и где слова, которые несут мало смысла, такие как артикли ( the ) и союзы ( and , или ) удобно опущено:

                      to_tsvector
-------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

Здесь по умолчанию каждое слово нормализовано как лексема английского языка (например, «jumped» становится «jump»). Но будьте осторожны, так как это может быть не так, в зависимости от настроек локализации вашей установки PostgreSQL. Кроме того, если вы работаете с другим языком, отличным от английского, PostgreSQL может справиться с этим, если вы передадите его в качестве аргумента:

SELECT to_tsvector('Portuguese', 'Zebras caolhas de Java querem mandar fax para gigante em New York');

И это вернет вектор, нормализованный по правилам португальского языка:

                                    to_tsvector
------------------------------------------------------------------------------------
 'caolh':2 'fax':7 'gigant':9 'jav':4 'mand':6 'new':11 'quer':5 'york':12 'zebr':1

Обратите внимание, как «гигант» нормализуется до «гигант», так как в португальском языке это слово может принимать разные формы — gigante (мужской, единственное число), giganta (женский, единственное число), gigantes (мужское и мужское/женское множественное число), gigantas (женское множественное число ). ). Это прямо здесь, просто чтобы вы знали, это обработка естественного языка (NLP) в действии.

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

Следующее, что нужно сделать для полнотекстового поиска, это запрос вектора.

Введение в tsquery

Следующая интересующая нас функция — это to_tsquery(), которая принимает список слов, которые будут проверяться на соответствие нормализованному вектору, который мы создали с помощью to_tsvector().

Для этого мы будем использовать @@оператор для проверки tsqueryсоответствия tsvector. Попробуем с «лисой»:

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('fox');

 ?column?
----------
 t

Это вернуло true ( t). Теперь с "лисами"...

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('foxes');

 ?column?
----------
 t

Это также возвращает «true», потому что «лисы» — это форма множественного числа слова «лиса». А как же "фокстрот"?

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('foxtrot');

 ?column?
----------
 f

Это неверно, потому что поиск достаточно умен, чтобы не найти ничего, что просто начинается с лисы , если только это не связано с той же семантикой (значением) исходно векторизованного текста; что хорошо, потому что лиса (животное) имеет мало общего с фокстротом (танцем)!

И, наконец, теперь с «прыжками»:

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('jumping');

 ?column?
----------
 t

Хороший! В конце концов, «прыгать» — это настоящая продолженная форма слова «прыгать».

Операторы и использование

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

У вас есть оператор И ( & ):

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('fox & dog');

Это возвращает true, потому что в документе присутствуют и «лиса», и «собака»:

 ?column?
----------
 t

У вас есть оператор ИЛИ ( | ):

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('fox | clown');

Что также возвращает true, потому что в документе присутствует по крайней мере слово «лиса»:

 ?column?
----------
 t

Также доступен оператор ОТРИЦАНИЕ ( ! ) . Вот как мы проверяем, отсутствует ли слово «клоун»:

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('!clown');

Что также возвращает "true":

 ?column?
----------
 t

И мы можем, конечно, объединить их все. Давайте запросим «лиса И (собака ИЛИ клоун) И НЕ королева» (круглые скобки в данном конкретном случае не нужны, но они помогают понять запрос):

SELECT to_tsvector('The quick brown fox jumped over the lazy dog')  
    @@ to_tsquery('fox & (dog | clown) & !queen');

Соблюдены все условия:

 ?column?
----------
 t

Все, что мы видели до сих пор, фактически превращает PostgreSQL в механизм полнотекстового поиска . Но подождите, есть еще!

Новый и улучшенный поиск по фразе

До сих пор вы могли найти, присутствует ли слово или набор слов в документе, но что, если вы хотите найти фразу?

Скажем, вы ищете что-то вроде «быстро прыгать» или «прыгать очень быстро», если вы будете искать (jump & quick)с помощью описанных выше методов, вы можете найти документы, содержащие эти слова, но вы получите любую случайную конфигурацию внутри документа. независимо от того, связаны они синтаксически или нет. Чтобы решить эту проблему, PostgreSQL 9.6 представил <->оператор для дальнейшего улучшения tsquery, позволяя вам искать слова близости или расстояния. Давайте посмотрим, как это работает, на нескольких примерах.

Давайте создадим документы

Для простоты предположим, что у нас есть следующие документы:

id | text                                     | tokens  
---|------------------------------------------|-----------------------------------------------------------
 1 | Pack my box with five dozen liquor jugs. | 'box':3 'dozen':6 'five':5 'jug':8 'liquor':7 'pack':1
 2 | Jackdaws love my big sphinx of quartz.   | 'big':4 'jackdaw':1 'love':2 'quartz':7 'sphinx':5
 3 | The five boxing wizards jump quickly.    | 'box':3 'five':2 'jump':5 'quick':6 'wizard':4
 4 | How vexingly quick daft zebras jump!     | 'daft':4 'jump':6 'quick':3 'vex':2 'zebra':5
 5 | Bright vixens jump; dozy fowl quack.     | 'bright':1 'dozi':4 'fowl':5 'jump':3 'quack':6 'vixen':2

Итак, давайте создадим таблицу для хранения всего этого (обратите внимание на tsvectorтип данных для document_tokensстолбца):

CREATE TABLE documents  
(
    document_id SERIAL,
    document_text TEXT,
    document_tokens TSVECTOR,

    CONSTRAINT documents_pkey PRIMARY KEY (document_id)
)

Теперь вставим в него документы:

INSERT INTO documents (document_text) VALUES  
('Pack my box with five dozen liquor jugs.'),
('Jackdaws love my big sphinx of quartz.'),
('The five boxing wizards jump quickly.'),
('How vexingly quick daft zebras jump!'),
('Bright vixens jump; dozy fowl quack.'),
('Sphinx of black quartz, judge my vow.');

INSERT 0 6

Наконец, небольшая UPDATEкоманда удобно заполнит столбец токенов соответствующим вектором для каждого документа:

UPDATE documents d1  
SET document_tokens = to_tsvector(d1.document_text)  
FROM documents d2;

UPDATE 6

Давайте фразовый поиск документов

Теперь, когда у нас все на месте, вернемся к нашему примеру «быстрого прыжка» с поиском:

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('jump & quick');  

Как мы уже упоминали, оператор to_tsquerywith ANDне делает различий в отношении расположения слов в документах:

 document_id |             document_text
-------------+---------------------------------------
           3 | The five boxing wizards jump quickly.
           4 | How vexingly quick daft zebras jump!
(2 rows)

Давайте попробуем это сейчас с оператором близости <->:

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('jump <-> quick');  

Это меняет дело:

 document_id |             document_text
-------------+---------------------------------------
           3 | The five boxing wizards jump quickly.
(1 row)

Итак, теперь вы можете найти слова рядом друг с другом, но можете ли вы найти слова, «близкие» друг к другу, даже если одно не следует сразу за другим? На самом деле тире -в операторе близости <->— это заполнитель для величины близости, которую вы ищете. Приведем несколько примеров:

Давайте поищем слова «сфинкс» и «кварц» рядом друг с другом ( <->):

SELECT * FROM documents  
WHERE document_tokens @@ to_tsquery('sphinx <-> quartz');  

Без результатов:

 document_id | document_text
-------------+--------------
(0 rows)

Увеличим близость между «сфинкс» и «кварц» до двух слов друг от друга ( <2>):

SELECT * FROM documents  
WHERE document_tokens @@ to_tsquery('sphinx <2> quartz');  

Именно так:

 document_id |             document_text
-------------+---------------------------------------
           2 | Jackdaws love my big sphinx of quartz.
(1 row)

И через три слова ( <3>):

SELECT * FROM documents  
WHERE document_tokens @@ to_tsquery('sphinx <3> quartz');  

Как и ожидалось:

 document_id |             document_text
-------------+--------------------------------------
           6 | Sphinx of black quartz, judge my vow.
(1 row)

Слово предостережения при выполнении поиска близости. В отличие от текстового поиска, где (jump & quick)и (quick & jump)дает те же результаты, поиск по фразе не является симметричным! То есть поиск — (jump <-> quick)это не то же самое, что поиск, (quick <-> jump)поскольку движок PostgreSQL будет учитывать порядок, в котором вы размещаете слова, поэтому будьте осторожны.

И просто чтобы вы знали, <->это действительно синтаксический сахар для tsquery_phrase()функции; так to_tsquery('sphinx <3> quartz')что эквивалентноtsquery_phrase('sphinx', 'quartz', 3)

Создание и сохранение tsvector типа данных

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

Для создания tsvectorвы можете либо использовать to_tsvector()функцию с входным текстом, либо сохранить результат в таблице с помощью INSERT INTOкоманды, например:

INSERT INTO documents (document_text, document_tokens)  
VALUES ('Pack my box with five dozen liquor jugs.',  
        to_tsvector('Pack my box with five dozen liquor jugs.'));

Также можно взять входной текст из одной таблицы, чтобы сохранить полученный tsvectorв другую таблицу, либо в ту же с помощью UPDATEкоманды; точно так же, как мы это делали в разделе «Давайте создадим документы» этой статьи.

Последний вариант — создать вектор «на лету», что хорошо для небольших приложений или для тестирования, но загрузка ЦП увеличится, если вы выполните тысячи таких операций. Вот как вы это делаете, также с помощью to_tsvector()функции:

SELECT document_id, document_text FROM documents  
WHERE to_tsvector(document_text) @@ to_tsquery('jump & quick');  

Еще больше возможностей!

Это вводная статья, и мы не можем подробно описывать все аспекты полнотекстового поиска; тем не менее, вот краткий обзор способов, которыми вы все еще можете расширить возможности PostgreSQL в этой области.

Словари поиска

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

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

Чтобы лучше справляться с этими сложностями, PostgreSQL позволяет нам создавать словари текстового поиска , чтобы вы могли сопоставлять определенные слова с синонимами для конкретных полей для вашего приложения с помощью CREATE TEXT SEARCH DICTIONARY. Это трудоемкий процесс, требующий глубокого понимания словарного запаса, относящегося к конкретной области, но если ваше приложение действительно требует этого, PostgreSQL может это сделать.

Конфигурации поиска

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

Ознакомьтесь с TEXT SEARCH CONFIGURATIONинструкциями и примерами конфигурации из документации PostgreSQL.

Веса и рейтинги

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

Ну... мы говорим о PostgreSQL, так что, конечно, вы можете это сделать! Однако технические детали для этого немного выходят за рамки этой статьи, но вы можете проверить документацию PostgreSQL для управления текстовым поиском (где интересующие функции setweight()и ts_rank()), и прочитать прекрасную статью Тима ван дер Линдена о эти особенности .

Предостережения

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

Языки

Будьте осторожны при работе с сильно интернационализированными приложениями, потому что, хотя PostgreSQL поддерживает несколько языков, вам необходимо убедиться, что настройки для рассматриваемых языков (словари, шаблоны) доступны. Вы можете запустить \dFв командной строке PostgreSQL:

postgres=# \dF

Для стандартной установки 9.6:

   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
(16 rows)

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

Проблемы с производительностью

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

Поэтому хорошей практикой является хранение векторов вместе с документами, как мы это делали в нашем примере с поиском по фразе. Таким образом, вы можете извлечь выгоду из ускорения и гибкости пары tsvector/ tsquery, оплачивая при этом небольшие затраты на создание и хранение токенов документа.

Настройки

Кроме того, для настройки собственных словарей и шаблонов вам потребуется доступ к файлуpostgres.conf , который не всегда предоставляется.

Подводим итоги

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

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

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