Многим при реализации поиска по сайту приходится делать запрос к базе данных, использующий оператор 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;