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

Ubuntu | Amber | Комментировать запись

Полнотекстовый поиск (FTS) – это метод, используемый поисковыми системами для поиска результатов в базе данных. Его можно использовать для поиска информации на различных сайтах: в интернет-магазине, онлайн-газетах и т.п.

FTS извлекает документы – объекты базы данных, содержащие текстовые данные, которые не соответствуют критериям поиска. Если, к примеру, пользователь вводит запрос «овощи и фрукты», приложение с поддержкой FTS может возвращать результаты, которые содержат:

• отдельные слова из запроса (например, только «овощи» или «фрукты»)
• слова в другом порядке («фрукты и овощи»)
• варианты слов («овощ» или «фрукт»).

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

Системы управления базами данных (СУБД), такие как PostgreSQL, обычно поддерживают частичный поиск текста помощью оператора LIKE. Однако такие запросы, как правило, не срабатывают на больших наборах данных. Они также должны полностью соответствовать запросу пользователя, то есть запрос может не дать никаких результатов, даже если в БД есть документы с соответствующей информацией.

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

Требования

• Сервер Ubuntu 16.04.
• Пользователь sudo (читайте руководство по начальной настройке сервера).
• СУБД PostgreSQL (инструкции по установке – здесь). В руководстве БД и пользователь условно называются 8host. Если вы установили PostgreSQL самостоятельно, установите пакет postgresql-contrib:

sudo apt-get list postgresql-contrib

1: Создание тестовой базы данных

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

Подключитесь к PostgreSQL:

sudo -u postgres psql 8host

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

Это откроет интерактивную сессию PostgreSQL; в командной строке будет указано имя базы данных, с которой вы работаете:

8host=#

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

CREATE TABLE news (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
author TEXT NOT NULL
);

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

Добавьте в таблицу несколько записей с помощью команды INSERT.

INSERT INTO news (id, title, content, author) VALUES
(1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
(2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then checking out local parks.', 'Ethan'),
(3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

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

Создайте документ с несколькими текстовыми столбцами из таблицы. Затем можно преобразовать полученную строку в текстовый вектор, который мы будем использовать в запросах.

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

\x
Expanded display is on.

Объедините столбцы с помощью функции конкатенации PostgreSQL, ||. Затем преобразуйте функцию to_tsvector().

SELECT title || '. ' || content as document, to_tsvector(title || '. ' || content) as metadata FROM news WHERE id = 1;

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

-[ RECORD 1 ]-----------------------------------------------------
document    | Pacific Northwest high-speed rail line. Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
metadata    | '140':18 'current':8 'high':4 'high-spe':3 'ideal':29 'line':7 'mile':19 'none':25 'northwest':2 'option':14 'pacif':1 'rail':6 'seattl':21 'speed':5 'travel':16 'vancouv':23

Возможно, вы заметили, что в преобразованной версии (metadata) меньше слов, чем в исходном документе (document). Некоторые слова отличаются, рядом с каждым словом стоит двоеточие и номер. Это связано с тем, что функция to_tsvector () анализирует слова в тексте и сводит их к лексемам, а затем сортирует результат по алфавиту. Это позволяет находить варианты форм одного и того же слова. Номер – это позиция слова в документе. Если слово появляется в документе несколько раз, после двоеточия будет указано несколько номеров через запятую.

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

SELECT * FROM news WHERE to_tsvector(title || '. ' || content) @@ to_tsquery('Explorations');

Рассмотрим использованные функции и операторы подробнее.

• Функция to_tsquery() преобразовывает параметр, который может быть точным или немного откорректированным запросом пользователя, в критерий текстового поиска, который будет анализировать ввод таким же образом, что и to_tsvector(). Кроме того, функция позволяет выбрать язык и указать, должны ли все слова присутствовать в результате.
• Оператор @@ определяет, соответствует ли tsvector значению tsquery или другой функции tsvector. Он возвращает true или false, благодаря чему его можно использовать в критерии WHERE.

-[ RECORD 1 ]-----------------------------------------------------
id      | 2
title   | Hitting the beach was voted the best part of life in the region
content | Exploring tracks and trails was second most popular, followed by visiting the shops and then checking out local parks.
author  | Ethan

Запрос вернул документ, содержащий слово «Exploring», хотя в запросе использовалось слово «Explorations». Оператор LIKE вернул бы пустой результат.

 3: Оптимизация производительности поиска

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

В таблице news создайте новый столбец document.

ALTER TABLE news ADD "document" tsvector;

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

INSERT INTO news (id, title, content, author, document)
VALUES (4, 'Sleep deprivation curing depression', 'Clinicians have long known that there is a strong link between sleep, sunlight and mood.', 'Patel', to_tsvector('Sleep deprivation curing depression' || '. ' || 'Clinicians have long known that there is a strong link between sleep, sunlight and mood.'));

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

Чтобы заполнить столбец, используйте команду UPDATE:

UPDATE news SET document = to_tsvector(title || '. ' || content) WHERE document IS NULL;

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

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

В результате индексы помогают базам быстрее найти строки, используя специальные структуры данных и алгоритмы. PostgreSQL предлагает несколько типов индексов, которые подходят для определенных типов запросов. Наиболее важными в данном случае являются индексы GiST и GIN. Основное различие между ними заключается в том, как быстро они могут извлекать документы из таблицы. GIN медленнее собирается при добавлении новых данных, но быстрее обрабатывает запросы; GIST работает быстрее, но требует дополнительных данных.

Поскольку GiST извлекает данные примерно в 3 раза медленнее, чем GIN, лучше использовать GIN.

CREATE INDEX idx_fts_search ON news USING gin(document);

Теперь запрос SELECT будет немного проще:

SELECT title, content FROM news WHERE document @@ to_tsquery('Travel | Cure');
-[ RECORD 1 ]-----------------------------------------------------
title   | Sleep deprivation curing depression
content | Clinicians have long known that there is a strong link between sleep, sunlight and mood.
-[ RECORD 2 ]-----------------------------------------------------
title   | Pacific Northwest high-speed rail line
content | Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.

Чтобы закрыть консоль БД, введите:

\q

Заключение

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