Використання повнотекстового індексування та пошуку в 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" %

І допустимо ми хотіли б зробити так, щоб по полях 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

і дещо змінюємо код нашої модельки:

З 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 title = models.CharField(max_length=200) опис = models.TextField(null=True, blank=True) article = models.CharField(max_length=200, null=True, blank=True) search_index = VectorField( 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) для кожного рядка БД.

Залишилося зрозуміти як цей повнотекстовий запит власне зробити у нашому коді. Тут простіше немає.

Всім привіт,

Описуються базові можливості, плюси та мінуси використання вбудованого повнотекстового пошуку СУБД 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 uk ALTER MAPPING FOR word, hword_part, word WITH russian_ispell, russian_stem;
ALTER TEXT SEARCH CONFIGURATION uk 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 не є спеціалізованою для повнотекстового індексування та пошуку системою і може поступатися можливостями більш спеціалізованим продуктам.

Всім привіт,

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

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

Існують перевірені часом інструменти для індексування та пошуку неструктурованих текстових даних – 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. Але ми свій знесли вже дуже давно, все чудово без збоїв працює під навантаженням.