Использование полнотекстового индексирования и поиска в PostgreSQL

Всем привет,

Описываются базовые возможности, плюсы и минусы использования встроенного полнотекстового поиска СУБД PostgreSQL на основе опыта его практического использования.

При разработке приложений, особенно web-приложений, в 95% возникает задача выбрать системы для управления как структурированным контентом, так и неструктурированными (текстовая информация произвольной структуры), а также данными мультимедиа (выходит за рамки данной статьи).
Архитектор приложения задается вопросом: совместить эти данные под управлением одной СУБД, либо же взять отдельное специализированное средство для каждого вида информации.

Существуют проверенные временем инструменты для индексирования и поиска неструктурированных текстовых данных — Sphinx, Lucene, на Хабре есть хорошие авторские статьи на эту тему.
Преимущество в том, что это отдельная система и она приспособлена для своей задачи максимально хорошо.
Но есть и архитектурный минус такого решения — ведь структурная и описательная часть данных чаще всего связаны между собой, а следовательно придется сконструировать комбинированные запросы .

Рассмотрим на примере

Есть задача учета кандидатов-соискателей, которые присылают свои резюме в текстовом формате. Была изначально поставлена задача находить информацию по кандидатам на основании их навыков и практического опыта владения этими навыками.
Создаем реляционную модель (упрощенно):

Рекрутеры принимают резюме, изучают их, заполняют наши таблички, после чего запрос вида «дай мне список кандидатов с опытом C (но не C++) не менее 2 года и не старше 100 лет» задаются примерно следующим образом:

WHERE (year(candidate.dob) >
candidate_skill.experience > 2 AND candidate_skill.skill_id = skill.skill_id AND skill.name = "C" ORDER BY candidate_skill.experience DESC;

Всех находит, все довольны.

Затем случается неожиданность — заказчик из Тмутаракани требует специалиста с опытом работы на C, но чтобы опыт был обязательно в Тмутаракани.

У нас тексты резюме проиндексированы в системе Shpinx, например, так что мы можем быстро найти всех кандидатов с опытом работы в Тмутаракани, но при поиске по C уже возникнут сложности (попадут C++, C-Sharp и всякое другое C). Младшему рекрутеру светит вручную перелопатить многие тысячи кандидатов с опытом работы в Тмутаракани, чтобы найти у кого есть там опыт 2 года на C (сам такое видел не раз).

Но не обязательно — если изначально была благоразумно выбрана СУБД PostgreSQL, выручит текстовая колонка Resume: text в таблице Candidate — туда рекрутеры с самого начала тупо копипастили текст резюме, на всякий случай.

Надо заставить систему искать по текстовому полю. Что сделать:

1. PostgreSQL устанавливается без словарей русского языка, поэтому их нужно отдельно скачать например . Если база данных (что скорей всего) в uft-8 — придется еще переконвертировать словари из koi8-r в utf-8, вот так:
iconv -f koi8-r -t utf-8 < ru_RU.aff > russian.affix
iconv -f koi8-r -t utf-8 < ru_RU.dic > russian.dict
Получившиеся файлы скопировать в подпапку tsearch_data папки, где у вас установлена PostgreSQL.

2. создать словарь и конфигурацию для русского языка:
CREATE TEXT SEARCH DICTIONARY russian_ispell (Template = ispell, DictFile = russian, AffFile = russian, StopWords = russian);
CREATE TEXT SEARCH CONFIGURATION ru (Copy = russian);
Для полноты
ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR hword, hword_part, word WITH russian_ispell, russian_stem;
ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR asciihword, asciiword, hword_asciipart WITH english_ispell, english_stem;

После этого уже можно получить результат, доработав наш запрос

SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE (year(Candidate.DoB) > (year(now()) — 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience >
to_tsvector("ru",Candidate.Resume) @@ to_tsquery("ru","Тмутаракань")

3. Операция to_tsvector ресурсоемкая, и каждый раз при запросе конвертировать всё резюме для каждой строчки нерационально, создание индекса типа GIN по полю Resume, сконвертированному в тип данных tsvector, решит этот вопрос.
Для этого сначала создаем поле fulltext: tsvector в таблице Candidate, затем создаем триггер, который будет заполнять это поле на основании значения поля Resume при создании или изменении записи Candidate — не будем загромождать кодом, всё стандартно — в триггере присвоить fulltext:= to_tsvector("ru",NEW.resume)

Затем создаем индекс по полю Fulltext:
CREATE INDEX candidate_fulltext ON candidate USING gin(fulltext);

GIN — специальный тип индекса, для данных типа tsvector и для массивов (всего в PostgreSQL 9.3 уже 5 разных типов индексов, подробно можно узнать изучив доку PostgreSQL).

Запрос тогда принимает вид:
SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE (year(Candidate.DoB) > (year(now()) — 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience > 2 AND Candidate_Skill.Skill_id = Skill.Skill_id AND Skill.Name = "C" AND
Candidate.fulltext @@ to_tsquery("ru","Тмутаракань")
ORDER BY Candidate_Skill.Experience DESC;

Вот и всё — запрос выдаст точнейший из возможных результат по заданному критерию.

Плюсы и минусы

Главный плюс очевиден — компактность и точность запросов за счет комбинирования структурных и полнотекстовых критериев. Ради него и сыр-бор.

Еще один плюс — в установке и поддержке Sphinx уже нет необходимости.

Поиск по готовому индексу GIN очень быстрый, в сложных запросах PostgreSQL умеет его «склеивать» с другими индексами (хотя сам GIN составным быть не может).

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

На одной чаше весов у нас 1) простота, точность и скорость запросов SQL 2) минус 1 система в ландшафте,
на другой — 1) несколько дополнительных объектов в схеме БД, 2) замедление производительности запросов DML 3) неспециализированный движок.

Если есть сомнения, что это будет хорошо работать — не стоит тогда сразу сносить Sphinx. Но мы свой снесли уже очень давно, всё прекрасно без сбоев под нагрузкой работает.
Результат прекрасно себя оправдает, если запросы на полнотекстовый и комбинированный поиск составляют не более 30% от общего числа запросов. Внимательно изучите возможности полнотекстового поиска PostgreSQL — если потенциально вам недостаточно описанных в документации функций — тогда конечно надо рассматривать другие продукты, потому что, еще раз отмечу, PostgreSQL не является специализированной для полнотекстового индексирования и поиска системой и может уступать по возможностям более специализированным продуктам.

Часто, для подсобных нужд, бывает нужно использовать хоть плохенький но все-таки полнотекстовый поиск по БД. Понятно что, «серьезные пацаны» используют для этого всякие Sphinx и ElasticSearch , но у этих штук есть одна премерзкая особенность: их надо настраивать, загонять в них данные и вообще мониторить их потребности. Да и если у вас в базе всего полсотни тысяч записей — использовать эти «махины» не сильно то удобно. Особенно если не хотите выходить за пределы Django.

Выход есть и он достаточно простой. Любая современная база данных (Mongo — ага, lol) имеет в себе встроенный полнотекстовый поиск. Не шибко умный, зато «изкоробки». Так как MySQL последние несколько лет не работал (и слава богу) расскажу на примере PostgreSQL. Начнем с версии. Вообще для постгреса использование свежих версий не «прихоть админа», а реальная необходимость. Связано это с достаточно активной деятельностью направленной на улучшение работы и вводу новых фич в движек. Так что я советую проводить эксперименты, да и вообще работать с самой свежей версией СУБД.

Итак, приступим.

Допустим у нас есть какая то Django-модель:

Class Element(models.Model): class Meta: db_table = "products" parent = models.ForeignKey(Section, null=True, blank=True) title = models.CharField(max_length=200) description = models.TextField(null=True, blank=True) article = models.CharField(max_length=200, null=True, blank=True) def __unicode__(self): return "%s/%s" % (self.parent.title, self.title)

И допустим мы хотели бы сделать так, чтобы по полям title и description происходил полнотекстовый поиск.

Для начала разберем как это делается на чистом SQL:

SELECT *, ts_rank(to_tsvector("russian", title||desctription), to_tsquery("russian", "шампунь|против|перхоти")) as rank FROM "Products" WHERE to_tsvector("russian", title||desctription) @@ to_tsquery("russian", "шампунь|против|перхоти") ORDER BY rank DESC LIMIT 20 OFFSET 0;

В этом запросе главными командами являются to_tsvector и to_tsquery. Первая команда переводит строки которые лежат в БД в дикт вида: слово:вес,…

Наглядно:

Функция to_tsquery нормализует введенные слова и приводит их к типу tsquery. Так-же есть функция plainto_tsquery, которая принимает на вход просто строку и приводит ее так-же как to_tsquery, но без необходимости заранее разбивать фразу. Надо заметить, что если в случае с to_tsquery можно самим указать логическое условие, я данном случае ИЛИ (|), то plainto_tsquery разбивает строку с условием И (&).

Так-же в запросе участвует оператор @@ которые как раз сравнивает tsvector и tsquery. Вообще есть достаточно много функций и операторов для полнотекстового поиска, так что курим .

Все это будет работать из коробки, даже с не настроенными индексами (про них ниже). Но. Мы то ленивые, писать сырые запросы не хотим и вообще считаем это «низкосистемным злом» (sarcasm).

Добрый дядя Джангонафт облегчил задачу интеграции FTS в Django. Для этого устанавливаем модуль

Pip install djorm-ext-pgfulltext

и несколько изменяем код нашей модельки:

From djorm_pgfulltext.models import SearchManager from djorm_pgfulltext.fields import VectorField from django.db import models class Element(models.Model): class Meta: db_table = "products" parent = models.ForeignKey(GroupSection, null=True, blank=True) title = models.CharField(max_length=200) description = models.TextField(null=True, blank=True) article = models.CharField(max_length=200, null=True, blank=True) search_index = VectorField() def __unicode__(self): return "%s/%s" % (self.parent.title, self.title) search_manager = SearchManager(fields=("title", "description"), config="pg_catalog.russian", search_field="search_index", auto_update_search_field=True)

По сути все изменение заключается в том, что мы добавили search_index — которое является тем самым tsvector для записи в БД и добавили новый менеджер запросов в конструктор которого передали следующие параметры:

fields — массив полей из которых будет строиться tsvector,
config — указывает postgresql с каким словарем мы хотим работать,
search_field — поле в которм у нас лежат данные которые являются уже подготовленным tsveсtor, собранный из указанных в fields полей,
auto_update_search_field — флаг который заставляет пересоздаваться search_field при изменении записи.

Если взглянуть на структуру таблицы, то мы увидим одно дополнительное поле — search_index, в котором уже лежит tsvector. Это сделано для оптимизации, Postgres умеет работать с уже подготовленными векторами и не тратить в пустую ресурсы на выполнение to_tsvector(‘russian’, title||desctription) для каждой строки БД.

Осталось понять как этот полнотекстовый запрос собственно сделать в нашем коде. Тут проще не бывает.

Всем привет,

Описываются базовые возможности, плюсы и минусы использования встроенного полнотекстового поиска СУБД Postgre на основе опыта его практического использования.

Существуют проверенные временем инструменты для индексирования и поиска неструктурированных текстовых данных - Django, Sphinx, Lucene, на Хабре есть хорошие авторские статьи на эту тему.
Преимущество в том, что это отдельная система и она приспособлена для своей задачи максимально хорошо.
Но есть и архитектурный минус такого решения - ведь структурная и описательная часть данных чаще всего связаны между собой, а следовательно придется сконструировать комбинированные запросы .

Рассмотрим на примере

рекрутеры принимают резюме, изучают их, заполняют наши таблички, после чего запрос вида «дай мне список кандидатов с опытом C (но не C++) не менее 2 года и не старше 100 лет» задаются примерно следующим образом:

SELECT candidate.name, skill.name, candidate_skill.expericence, candidate.phone, candidate.email FROM candidate, candidate_skill, skill
WHERE (year(candidate.dob) >
candidate_skill.experience > 2 AND candidate_skill.skill_id = skill.skill_id AND skill.name = "C" ORDER BY candidate_skill.experience DESC;

Всех находит, все довольны.

Затем случается неожиданность - заказчик из Тмутаракани требует специалиста с опытом работы на C, но чтобы опыт был обязательно в Тмутаракани.

Но не обязательно - если изначально была благоразумно выбрана СУБД Postgre, выручит текстовая колонка Resume: text в таблице Candidate - туда рекрутеры с самого начала тупо копипастили текст резюме, на всякий случай.

Надо заставить систему искать по текстовому полю. Что сделать:

1. Postgre устанавливается без словарей русского языка, поэтому их нужно отдельно скачать например . Если база данных (что скорей всего) в UTF-8 - придется еще переконвертировать в utf-8, вот так:
iconv -f koi8-r -t utf-8 < ru_RU.aff > russian.affix
iconv -f koi8-r -t utf-8 < ru_RU.dic > russian.dict
Получившиеся файлы скопировать в подпапку tsearch_data папки, где у вас установлена Postgre.

После этого уже можно получить результат, доработав наш запрос

SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE (year(Candidate.DoB) > (year(now()) - 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience >
to_tsvector("ru",Candidate.Resume) @@ to_tsquery("ru","Тмутаракань")

3. Операция to_tsvector трудоемкая, и каждый раз при запросе конвертировать всё резюме для каждой строчки нерационально, создание индекса типа GIN по полю Resume, сконвертированному в тип данных tsvector, решит этот вопрос.
Для этого сначала создаем поле fulltext: tsvector в таблице Candidate, затем создаем триггер, который будет заполнять это поле на основании значения поля Resume при создании или изменении записи Candidate - не будем загромождать кодом, всё стандартно - в триггере присвоить fulltext:= to_tsvector("ru",NEW.resume)

Затем создаем индекс по полю Fulltext:
CREATE INDEX candidate_fulltext ON candidate USING gin(fulltext);

GIN - специальный тип индекса, для данных типа tsvector и для массивов (всего в Postgre 9.3 уже 5 разных типов индексов, подробно можно узнать изучив доку Postgre).

Запрос тогда принимает вид:
SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE (year(Candidate.DoB) > (year(now()) - 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience > 2 AND Candidate_Skill.Skill_id = Skill.Skill_id AND Skill.Name = "C" AND
Candidate.fulltext @@ to_tsquery("ru","Тмутаракань")
ORDER BY Candidate_Skill.Experience DESC;

Вот и всё - запрос выдаст точнейший из возможных результат по заданному критерию.

Плюсы и минусы

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

Еще один плюс - в установке и поддержке Sphinx уже нет необходимости.

Поиск по готовому индексу GIN очень быстрый, в сложных запросах Postgre умеет его «склеивать» с другими индексами (хотя сам GIN составным быть не может).

Минусы тоже очевидны
- необходимо содержать дополнительное поле tsvector (немаленькое), нужен триггер, нужен дополнительный индекс. Потери производительности при вставке и обновлении записей будут довольно ощутимыми.

На одной чаше весов у нас 1) простота, точность и скорость запросов SQL 2) минус 1 система в ландшафте,
на другой - 1) несколько дополнительных объектов в схеме БД, 2) замедление производительности запросов DML.

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