Использование Tsearch2 в PostgreSQL 8.3

Многим при реализации поиска по сайту приходится делать запрос к базе данных, использующий оператор LIKE или регулярные выражения.
С выходом PostgreSQL 8.3 модуль полнотекстового поиска tsearch2 был встроен в ядро системы (об этом ранее уже писалось на Хабре), и с его помощью можно реализовать поиск по базе данных, который будет более функционален. В принципе, в статье, указанной выше, приведен пример использования этого модуля, но хотелось бы поделиться опытом реализации полнотекстового поиска в реальном проекте.

Итак, есть таблица «news», содержащая поля: «title», «metaKeywords», «metaDescription» и «content».
Необходимо реализовать полнотекстовый поиск, который будет искать слово по всем полям таблицы, причем у каждого поля есть свой абсолютный приоритет.

Создадим базу данных в кодировке UTF8, и сделаем в ней тестовую таблицу:

CREATE DATABASE «tsearch2»
 WITH ENCODING = 'UTF8';
CREATE TABLE «news»
(
   «newsId» Serial NOT NULL,
   «title» Varchar(1024) NOT NULL,
   «metaKeywords» Varchar(2048),
   «metaDescription» Varchar(1024),
   «content» Text NOT NULL,
primary key («newsId»)
);

Далее добавим в эту таблицу какую-нибудь запись:

INSERT INTO «news» («title», «metaKeywords», «metaDescription», «content»)
VALUES ('Тестовая новость', 'новость, статья', 'Тестовая новость для поиска', 'Hello world');

Теперь необходимо создать конфигурацию полнотекстового поиска:

CREATE TEXT SEARCH DICTIONARY mydict_russian_ispell (
  TEMPLATE = ispell,
  DictFile = russian,
  AffFile = russian,
  StopWords = russian
);

CREATE TEXT SEARCH CONFIGURATION public.mydict_ru (PARSER = default);
COMMENT ON TEXT SEARCH CONFIGURATION public.mydict_ru IS 'conf. for mydict ru';

ALTER TEXT SEARCH CONFIGURATION mydict_ru ADD MAPPING
 FOR email, url, url_path, host, file, version,
  sfloat, float, int, uint,
  numword, hword_numpart, numhword
 WITH simple;
 
ALTER TEXT SEARCH CONFIGURATION mydict_ru ADD MAPPING
 FOR word, hword_part, hword
 WITH mydict_russian_ispell;

В этом скрипте мы создали свой ispell словарь mydict_russian_ispell. Потом создали свою конфигурацию mydict_ru, у которой указали правила использования словарей.
Стоит сказать, что в первом запросе используются ссылки на файлы, лежащие в postgres в директории по-умолчанию (\share\tsearch_data\).
Для создания своего словаря нам понадобились 3 файла: russian.affix, russian.stop и russian.dict. Первый содержит описание окончаний слов данного языка, второй — перечень стоп-слов, последний — сами слова. Важно помнить, что файлы должны быть в той же кодировке, что и сама база, т.е. в нашем случае — UTF8.

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

ALTER TABLE «news» ADD COLUMN fts_news tsvector;
UPDATE «news» SET fts_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');

CREATE INDEX news_fts_idx ON «news» USING gist(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 ) as rank
  FROM «news», plainto_tsquery('mydict_ru', 'новости' ) q
  WHERE fts_news @@ q
  ORDER BY rank DESC
) AS foo;

В результате получим строку Тестовая новость. Подсветку найденного слова реализует функция ts_headline; ранжирование запросов — функция ts_rank, по значению которой мы сортируем результаты.

Ну и конечно же надо не забыть сделать триггер, который будет у таблицы «news» обновлять поле «fts_news»:

CREATE OR REPLACE 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;

CREATE TRIGGER «newsFTSTrigger»
BEFORE INSERT OR UPDATE ON «news»
FOR EACH ROW EXECUTE PROCEDURE «updateNewsFTS»();

UPD

Производительность

Поскольку в комментариях попросили привести сведения о производительности, я решил сравнить два вида поиска — tsearch и поиск на регулярных выражениях.
Поиск проводится по VIEW, соединяющей данные из четырех таблиц (порядка 5400 записей в сумме).

Запрос, использующий tsearch:

SELECT
  «id»,
  «type»,          
  ts_headline('mydict_ru', «title», q) as «title»,
  ( CASE WHEN trim( «foreword» ) = '' THEN ts_headline('mydict_ru', «content», q)
    ELSE ts_headline('mydict_ru', «foreword», q) END ) as «body»,
  «resourceTypes»,  
  rank
FROM (
  SELECT
    «id»,
    «type»,
    «title»,
    «foreword»,
    «content»,
    «resourceTypes»,
    q,
    ts_rank( fts_vector, q ) as rank
  FROM «getSearchItems», plainto_tsquery('mydict_ru', 'физика' ) q
  WHERE fts_vector @@ q
  ORDER BY rank DESC
) AS foo;

Среднее время выполнения такого запроса у меня на ноутбуке составляет 2,35 секунды. Результат содержит 821 запись.

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

SELECT *
   , (SELECT position( lower('физика') in lower("search".«title») ) as «pos»
      EXCEPT SELECT 0 as «pos» ) as «titlePosition»
   , (select position( lower('физика') in lower("search".«metaKeywords») ) as «pos»
      EXCEPT SELECT 0 as «pos» ) as «metaKeywordsPosition»
   , (SELECT position( lower('физика') in lower("search".«metaDescription») ) as «pos»
      EXCEPT SELECT 0 as «pos» ) as «metaDescriptionPosition»
   , (SELECT position( lower('физика') in lower("search".«foreword») ) as «pos»
      EXCEPT SELECT 0 as «pos» ) as «forewordPosition»
   , (SELECT position( lower('физика') in lower("search".«content») ) as «pos»
      EXCEPT SELECT 0 as «pos» ) as «contentPosition»
FROM ( 
  SELECT
    «id»,
    «type»,
    «title»,
    «metaKeywords»,
    «metaDescription»,
    «foreword»,
    «content»
    FROM «getSearchItems»       
    WHERE ( lower(«title») ~ lower('(.*)'||'физика'||'(.*)')
         or lower( «metaKeywords» ) ~ lower('(.*)'||'физика'||'(.*)')
         or lower( «metaDescription» ) ~ lower('(.*)'||'физика'||'(.*)')
         or lower( «foreword» ) ~ lower('(.*)'||'физика'||'(.*)')
         or lower( «content» ) ~ lower('(.*)'||'физика'||'(.*)') )
     ) as "search"
ORDER BY «type» ASC
   , «titlePosition» ASC
   , «metaKeywordsPosition» ASC
   , «metaDescriptionPosition» ASC
   , «forewordPosition» ASC
   , «contentPosition» ASC;

Среднее время выполнения такого запроса у меня на ноутбуке составляет 1,5 секунды. Результат содержит 567 записей.
Таким образом, при использовании tsearch я получил время работы в 1,5 раза превышающее время работы «простого» поиска, но при этом я получил приблизительно в 1,5 раза больше записей, которые содержат различные формы слова физика и уже готовы к выводу в шаблон.

P.S.

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

CREATE TEXT SEARCH DICTIONARY english_ispell (
  TEMPLATE = ispell,
  DictFile = english,
  AffFile = english,
  StopWords = english
);

ALTER TEXT SEARCH CONFIGURATION mydict_ru ADD MAPPING
 FOR asciiword
 WITH english_ispell;

Ссылки по теме:
Архив с файлами русских ispell словарей (UTF8)
Оффициальная страница tsearch2
Пример использования tsearch2 в postgresql-8.2