Использование Tsearch2 в PostgreSQL 8.4
Полнотекстовый поиск по базам данных с использованием Tsearch2 в PostgreSQL 8.4
Многим при реализации поиска по сайту приходится делать запрос к базе данных, использующий оператор LIKE или регулярные выражения.
С выходом PostgreSQL 8.4 модуль полнотекстового поиска tsearch2 был встроен в ядро системы, и с его помощью можно реализовать поиск по базе данных, который будет более функционален. В принципе, в статье, указанной выше, приведен пример использования этого модуля, но хотелось бы поделиться опытом реализации полнотекстового поиска в реальном проекте.
Итак, естьтаблица"news", содержащаяполя: "title", "metaKeywords", "metaDescription" и"content".
Необходимо реализовать полнотекстовый поиск, который будет искать слово по всем полям таблицы, причем у каждого поля есть свой абсолютный приоритет.
Создадим базу данных в кодировке UTF8, и сделаем в ней тестовую таблицу:
CREATEDATABASE"tsearch2"
WITHENCODING = 'UTF8';
CREATETABLE"news"
(
"newsId" Serial NOTNULL,
"title" Varchar(1024) NOTNULL,
"metaKeywords" Varchar(2048),
"metaDescription" Varchar(1024),
"content" Text NOTNULL,
primarykey("newsId")
);
Далее добавим в эту таблицу какую-нибудь запись:
INSERTINTO"news" ("title", "metaKeywords", "metaDescription", "content")
VALUES('Тестовая новость', 'новость, статья', 'Тестовая новость для поиска', 'Hello world');
Теперь необходимо создать конфигурацию полнотекстового поиска:
CREATETEXT SEARCHDICTIONARYmydict_russian_ispell (
TEMPLATE = ispell,
DictFile = russian,
AffFile = russian,
StopWords = russian
);
CREATETEXT SEARCHCONFIGURATION public.mydict_ru (PARSER = default);
COMMENT ONTEXT SEARCHCONFIGURATION public.mydict_ru IS'conf. for mydict ru';
ALTERTEXT SEARCHCONFIGURATION mydict_ru ADDMAPPING
FORemail, url, url_path, host, file, version,
sfloat, float, int, uint,
numword, hword_numpart, numhword
WITHsimple;
ALTERTEXT SEARCHCONFIGURATION mydict_ru ADDMAPPING
FORword, hword_part, hword
WITHmydict_russian_ispell;
В этом скрипте мы создали свой ispell словарь mydict_russian_ispell. Потом создали свою конфигурацию mydict_ru, у которой указали правила использования словарей.
Стоит сказать, что в первом запросе используются ссылки на файлы, лежащие в postgres в директории по-умолчанию (\share\tsearch_data\).
Для создания своего словаря нам понадобились 3 файла: russian.affix, russian.stop и russian.dict. Первый содержит описание окончаний слов данного языка, второй - перечень стоп-слов, последний - сами слова. Важно помнить, что файлы должны быть в той же кодировке, что и сама база, т.е. в нашем случае - UTF8.
При реализации поиска мы будем обращаться не к полям базы, а к специальному индексу, который будет содержать информацию о содержимом таблицы.
ALTERTABLE"news" ADDCOLUMNfts_news tsvector;
UPDATE"news" SETfts_news=
setweight( coalesce( to_tsvector('mydict_ru', "title"),''),'A') ||
setweight( coalesce( to_tsvector('mydict_ru', "metaKeywords"),''),'B') ||
setweight( coalesce( to_tsvector('mydict_ru', "metaDescription"),''),'C') ||
setweight( coalesce( to_tsvector('mydict_ru', "content"),''),'D');
CREATEINDEXnews_fts_idx ON"news" USINGgist(fts_news);
vacuum analyze "news";
Этим скриптом мы создали в таблице еще одно поле типа tsvector, которое содежит информацию об указанных четырех полях таблицы, причем поле "title" берется с самым высоким приоритетом A, а поле "content" - с самым низким D. Затем создали GIST индекс и сделали обновление индекса.
В результате нашей тестовой записи таблицы соответствует следущий tsvector - 'поиск':8C 'статья':4B 'новость':2A,3B,6C 'тестовый':1A,5C.
Ну вот теперь и пришло время для теста нашего поиска. Выполнимследущийзапрос:
SELECT
"newsId",
ts_headline('mydict_ru', "title", q) as"title",
rank
FROM(
SELECT
"newsId",
"title",
q,
ts_rank( fts_news, q ) asrank
FROM"news", plainto_tsquery('mydict_ru', 'новости') q
WHEREfts_news @@ q
ORDERBYrank DESC
) ASfoo;
В результате получим строку Тестовая новость. Подсветку найденного слова реализует функция ts_headline; ранжирование запросов - функция ts_rank, по значению которой мы сортируем результаты.
Ну и конечно же надо не забыть сделать триггер, который будет у таблицы "news" обновлять поле "fts_news":
CREATEORREPLACE FUNCTION"updateNewsFTS"() RETURNS"trigger" AS'
DECLARE bUpdate boolean;
BEGIN
bUpdate = false;
IF (TG_OP = ''INSERT'') THEN
bUpdate := true;
ELSEIF (TG_OP = ''UPDATE'') THEN
IF NEW.title != OLD.title OR NEW.content != OLD.content OR NEW."metaKeywords" != OLD."metaKeywords" OR NEW."metaDescription" != OLD."metaDescription" THEN
bUpdate := true;
END IF;
END IF;
IF bUpdate = TRUE THEN
RAISE NOTICE ''UPDATE '';
new.fts_news:=setweight( coalesce( to_tsvector(''mydict_ru'', new.title),''''),''A'') ||
setweight( coalesce( to_tsvector(''mydict_ru'', new."metaKeywords"),''''),''B'') ||
setweight( coalesce( to_tsvector(''mydict_ru'', new."metaDescription"),''''),''C'') ||
setweight( coalesce( to_tsvector(''mydict_ru'', new.content),''''),''D'');
END IF;
RETURN NEW;
END;
'LANGUAGE'plpgsql'VOLATILE;
CREATETRIGGER"newsFTSTrigger"
BEFOREINSERTORUPDATEON"news"
FOREACHROWEXECUTEPROCEDURE"updateNewsFTS"();
UPD
Производительность
Поскольку в комментариях попросили привести сведения о производительности, я решил сравнить два вида поиска - tsearch и поиск на регулярных выражениях.
Поиск проводится по VIEW, соединяющей данные из четырех таблиц (порядка 5400 записей в сумме).
Запрос, использующийtsearch:
SELECT
"id",
"type",
ts_headline('mydict_ru', "title", q) as"title",
( CASEWHENtrim( "foreword" ) = '' THENts_headline('mydict_ru', "content", q)
ELSEts_headline('mydict_ru', "foreword", q) END) as"body",
"resourceTypes",
rank
FROM(
SELECT
"id",
"type",
"title",
"foreword",
"content",
"resourceTypes",
q,
ts_rank( fts_vector, q ) asrank
FROM"getSearchItems", plainto_tsquery('mydict_ru', 'физика') q
WHEREfts_vector @@ q
ORDERBYrank DESC
) ASfoo;
Среднее время выполнения такого запроса у меня на ноутбуке составляет 2,35 секунды. Результат содержит 821 запись.
Запрос, использующий регулярные выражения и реализующий подобие ранжирования результатов:
SELECT*
, (SELECTposition( lower('физика') in lower("search"."title") ) as"pos"
EXCEPTSELECT0 as"pos" ) as"titlePosition"
, (selectposition( lower('физика') in lower("search"."metaKeywords") ) as"pos"
EXCEPTSELECT0 as"pos" ) as"metaKeywordsPosition"
, (SELECTposition( lower('физика') in lower("search"."metaDescription") ) as"pos"
EXCEPTSELECT0 as"pos" ) as"metaDescriptionPosition"
, (SELECTposition( lower('физика') in lower("search"."foreword") ) as"pos"
EXCEPTSELECT0 as"pos" ) as"forewordPosition"
, (SELECTposition( lower('физика') in lower("search"."content") ) as"pos"
EXCEPTSELECT0 as"pos" ) as"contentPosition"
FROM(
SELECT
"id",
"type",
"title",
"metaKeywords",
"metaDescription",
"foreword",
"content"
FROM"getSearchItems"
WHERE( lower("title") ~ lower('(.*)'||'физика'||'(.*)')
orlower( "metaKeywords" ) ~ lower('(.*)'||'физика'||'(.*)')
orlower( "metaDescription" ) ~ lower('(.*)'||'физика'||'(.*)')
orlower( "foreword" ) ~ lower('(.*)'||'физика'||'(.*)')
orlower( "content" ) ~ lower('(.*)'||'физика'||'(.*)') )
) as"search"
ORDERBY"type" ASC
, "titlePosition" ASC
, "metaKeywordsPosition" ASC
, "metaDescriptionPosition" ASC
, "forewordPosition" ASC
, "contentPosition" ASC;
Среднее время выполнения такого запроса у меня на ноутбуке составляет 1,5 секунды. Результат содержит 567 записей.
Таким образом, при использовании tsearch я получил время работы в 1,5 раза превышающее время работы "простого" поиска, но при этом я получил приблизительно в 1,5 раза больше записей, которые содержат различные формы слова физика и уже готовы к выводу в шаблон.
P.S.
Данная реализация поиска предоставляет полнотекстовый поиск по русским словам, находящимся в базе.
При такой конфигурации английские слова не были проиндексированы. Чтобы это исправить, необходимо создавать другую конфигурацию поиска, я лишь предоставил ту, которою использовал сам.
Пока что самым простым и доступным мне кажется такой вид конфигурации, добавляющий анлийский ispell словарь и индексацию английских слов:
CREATETEXT SEARCHDICTIONARYenglish_ispell (
TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
ALTERTEXT SEARCHCONFIGURATION mydict_ru ADDMAPPING
FORasciiword
WITHenglish_ispell;
