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