KF Введение в полнотекстовый поиск в PostgreSQL

Введение в полнотекстовый поиск в PostgreSQL

Авторское неформальное описание полнотекстового поиска встроенного в PostgreSQL версии 8.3+, примеры и рекомендации по настройке. Также приведен справочник SQL команд для управления полнотекстовым поиском. Полное описание полнотекстового поиска доступно на сайте разработчиков [FTSBOOK].

Содержание

Введение

Полнотекстовый поиск в базах данных является одним из востребованных механизмов доступа к содержимому любой современной информационной системы, которые хранят метаинформацию, а зачастую, и сами документы, в базе данных. Современные веб-сайты, по сути, являются интерфейсом, способом организации доступа к базам данных. По мере накопления документов в системе неминуемо возникает проблема организации эффективной навигации по системе, чтобы посетитель сайта смог за минимальное количество кликов найти нужный документ. Помимо стандартной, зачастую ручной, навигации с использованием рубрикации (тематической, по типу материалов, категории пользователей и т.д.), полнотекстовый поиск является одним из самых эффективных методов навигации, особенно для новичков, незнакомых с устройством сайта.

Из нашего повседневного опыта мы понимаем, что хороший поиск - это поиск, который в ответ на наш запрос быстро найдет релевантные документы. И такие машины, казалось бы, существуют, например, широко известные поисковые машины как глобальные - "Google", так и наши российские - "Яндекс", "Рамблер". Более того, существует большое количество поисковиков, платных и бесплатных, которые позволяют индексировать всю вашу коллекцию документов и организовать вполне качественный поиск. Владельцу сайта остается только "скармливать" таким поисковикам контент по мере его появления. Это можно организовать несколькими способами - доступ через http-протокол, используя URL документа, как это делают большие внешние поисковики, или организация доступа к содержимому базы данных. В обоих случаях полнотекстовый индекс является внешним по отношению к базе данных. Часто такой подход оправдан и хорошо работает на многих сайтах, несмотря на некоторые недостатки, такие как неполная синхронизация содержимого БД, нетранзакционность, отсутствие доступа к метаданным и использование их для ограничения области поиска или, например, организации определенной политики доступа к документам, и т.д.

Мы не будем касаться таких поисковых машин, а будем рассматривать полнотекстовый поиск, который полностью интегрирован с СУБД. Очевидно, что подобный поиск обязан соответствовать архитектуре СУБД, что налагает определенные ограничения на алгоритмы и методы доступа к данным. Несмотря на то, что подобные ограничения могут влиять на производительность поиска, полный доступ ко всем метаданным базы данных дает возможность для реализации очень сложных поисков, просто невозможных для внешних поисковиков. Например, понятие документа в БД отличается от обычного восприятия как страница на сайте, которую можно сохранить, открыть, модифицировать, удалить. То, что пользователь или поисковый робот видит на сайте является результатом лишь одной комбинацией метаданных, полное множество которых практически недоступно для поисковых роботов. Существует даже понятие "скрытого веба" (Hidden Web), недоступного для поисковых машин и который во много раз превышает размеры видимого веба. Одним из компонентов этой "скрытой" части веба является содержимое баз данных.

Что такое документ в базе данных ? Это может быть произвольный текстовый атрибут или их комбинация. Атрибуты могут храниться в разных таблицах и тогда документ может являться результатом сложной "связки" нескольких таблиц. Более того, текстовые атрибуты могут быть на самом деле результатом работы программ-конвертеров, которые вытаскивают текстовую информацию из бинарных полей (.doc, .pdf, .ps, ...). В большинстве случаев, документ является результатом работы SQL команд и виртуальным по своей природе. Очевидно, что единственное требование для документа является наличие уникального ключа, по которому его можно идентифицировать. Для внешнего поисковика такой документ является просто набором слов ("bag of words"), без никакого понимания структуры, т.е. из каких атрибутов этот документ был составлен, какова важность того или иного документа. Вот пример документа, составленного из нескольких текстовых атрибутов. 

SELECT m.title ||' '|| m.author ||' '||  m.abstract ||' '|| d.body as document 
FROM messages m, docs d 
WHERE m.id = d.id and m.id = 12;
Интуитивно ясно, что не все части документа одинаково важны. Так, например, заголовок или абстракт обладают большей информативной плотностью, чем остальная часть документа.

Запрос имеет чисто иллюстративный характер, так как на самом деле, здесь надо было бы использовать функцию coalesce(), чтобы защититься от ситуации, когда один из атрибутов имеет значение NULL.

Как и обычный документ он состоит из слов, по которым его можно найти. Для этого документ надо уметь разбивать на эти слова, что также может быть не простой задачей, так как для разных задач понятие слова может быть разным. Мы используем термин "токен" для обозначения "слов", которые получаются после работы парсера, и термин "лексема" для обозначения того, что будет индексировано. Итак, парсер разбивает документ на токены, часть из которых индексируется. Каким образом токен становится лексемой - это определяется конкретной задачей, например, для поиска по цветам требуется индексировать не только обычные слова, обозначающие цвета красок, но и их различные эквиваленты, использующиеся в веб-технологиях, например, их шестнадцатеричные обозначения.

Полнотекстовый поиск в PostgreSQL

Как и многие современные СУБД, PostgreSQL [PGSQL] имеет встроенный механизм полнотекстового поиска. Отметим, что операторы поиска по текстовым данных существовали очень давно, это операторы LIKE, ILIKE, ~, ~*. Однако, они не годились для эффективного полнотекстового поиска, так как Для улучшения ситуации авторы этой статьи предложили и реализовали новый полнотекстовый поиск, существовавший как модуль расширения и интегрированный в PostgreSQL, начиная с версии 8.3.

Идея нового поиска состояла в том, чтобы затратить время на обработку документа один раз и сохранить время при поиске, использовать специальные программы-словари для нормализации слов, чтобы не заботиться, например, о формах слов, учитывать информацию о важности различных атрибутов документа и положения слова из запроса в документе для ранжирования найденных документов. Для этого, требовалось создать новые типы данных, соответствующие документу и запросу, и полнотекстовый оператор для сравнения документа и запроса, который возвращает TRUE, если запрос удовлетворяет запросу, и в противном случае - FALSE.

PostgreSQL предоставляет возможность как для создания новых типов данных, операторов, так и создания индексной поддержки для доступа к ним, причем с поддержкой конкурентности и восстановления после сбоев ! Однако, надо понимать, что индексы нужны только ускорения поиска, сам поиск обязан работать и без них.

Таким образом, были созданы новые типы данных - tsvector, который является хранилищем для лексем из документа, оптимизированного для поиска, и tsquery - для запроса с поддержкой логических операций, полнотекстовый оператор "две собаки" @@ и индексная поддержка для него с использованием [GiST] и [GIN]. tsvector помимо самих лексем может хранить информацию о положении лексемы в документе и ее весе (важности), которая потом может использоваться для вычисления ранжирующей информации. 

=# select 'cat & rat':: tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
 ----------
  t
=# select 'fat & cow':: tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
   ?column?
 ----------
  f
Кроме этого, были реализованы вспомогательные функции Для разбиения документа на токены используется парсер, который выдает токен и его тип, см. пример ниже. 
=# select "Alias","Token","Description" from ts_debug('12 cats');
 Alias | Token |   Description    
-------+-------+------------------
 uint  | 12    | Unsigned integer
 blank |       | Space symbols
 lword | cats  | Latin word
Каждому типу токена ставится в соответствие набор словарей, которые будут стараться распознать и "нормализовать" его. Порядок словарей фиксирован и важен, так как именно в этом порядке токен будет попадать на вход словарю, до тех пор, пока он не опознается одним из них. Если токен не распознался ни одним из словарей, или словарь опознал его как стоп-слово, то этот токен не индексируется. Таким образом, можно сказать, что для каждого типа токена существует правило обработки токена, которое описывает схему попадания токена в полнотекстовый индекс. 
=# select "Alias","Token","Dicts list","Lexized token" from ts_debug('as 12
 cats');
 Alias | Token |      Dicts list      |       Lexized token       
-------+-------+----------------------+---------------------------
 lword | as    | {pg_catalog.en_stem} | pg_catalog.en_stem: {}
 blank |       |                      | 
 uint  | 12    | {pg_catalog.simple}  | pg_catalog.simple: {12}
 blank |       |                      | 
 lword | cats  | {pg_catalog.en_stem} | pg_catalog.en_stem: {cat}
На этом примере мы видим, что токен 'as' обработался словарем pg_catalog.en_stem, распознался как стоп-слово и не попал в полнотекстовый индекс, в то время как токены '12' и 'cats' распознались словарями, нормализовались и попали в индекс.

Каждый словарь по-своему понимает, что такое "нормализация", однако, интуитивно понятно, что в результате нормализации, группы слов, объединенные по тому или иному признаку, приводятся к одному слову. Это позволяет при поиске этого "нормализованного" слова найти все документы, содержащие слова из этой группы. Наиболее привычная нормализация для нас - это приведение существительного к единственному числу и именительному падежу, например, слово 'стол' является нормальной формой слов 'столы', 'столов', 'столами', 'столу' и т.д. Не менее естественным представляется приведение имен директорий '/usr/local/bin', '/usr/local/share/../bin', '/usr/local/./bin/' к к стандартному виду '/usr/local/bin'.

Комбинация парсера и правил обработки токенов определяет полнотекстовую конфигурацию, которых может быть произвольное количество. Большое количество конфигураций для 10 европейских языков и разных локалей уже встроено в PostgreSQL и хранится в системном каталоге, в схеме pg_catalog. Практически все функции поиска зависят от полнотекстовой конфигурации, которая является необязательным параметром. Необязательность определяет необходимость наличия способа выбора конфигурации по умолчанию. Этим способом является соответствие названия серверной локали, которую можно посмотреть с помощью команд show lc_ctype; и show lc_collate; и локали, приписанной к полнотекстовой конфигурации.

Сами парсеры и словари также хранятся в системе, их можно добавлять, изменять и удалять с помощью SQL команд.

Несмотря на богатые возможности по настраиванию полнотекстового поиска практически под любую задачу, возможности, предоставленные по умолчанию, вполне достаточны для организации полноценного поиска для широкого класса задач. Более того, для очень простого поиска, когда не требуется ранжирования документов, например, поиск по заголовкам новостей, когда есть естественный способ сортировки документов по времени, можно организовать с помощью всего одной команды. Для примера мы будем использовать таблицу apod, которая содержит архив известной Астрономической Картинки Дня [APOD]. 

=# \d apod
       Table "public.apod"
  Column  |   Type   | Modifiers 
----------+----------+-----------
 id       | integer  | not null
 title    | text     | 
 body     | text     | 
 sdate    | date     | 
 keywords | text     | 
Indexes:
    "apod_pkey" PRIMARY KEY, btree (id)
В этой таблице sdate - это дата документа, а атрибут keywords - строка с ключевыми словами через запятую, которые вручную редактор перевода присвоил документу. Создадим индекс по заголовкам: 
CREATE INDEX tit_idx ON apod USING gin(title);
После этого уже можно искать 
SELECT title FROM apod WHERE title @@ 'supernovae stars' ORDER by  sdate limit 10;
Чтобы понять, что на самом деле происходит при создании индекса, опишем все шаги.
  1. Определяется активная полнотекстовая конфигурация по серверной локали. Название конфигурации можно посмотреть с помощью show tsearch_conf_name;
  2. Атрибут title превращается в tsvector, по которому строится обратный индекс. При этом используется информация о парсерах и словарях, которая определяется полнотекстовой конфигурацией с именем tsearch_conf_name. Заметим, что так как tsvector не материализован как отдельный атрибут, а используется виртуально, то никакой информации о ранжировании недоступно. В силу текущего ограничения PostgreSQL, в индексе нельзя хранить никакую дополнительную информацию.

Полнофункциональный поиск требует создания нового атрибута для хранения tsvector, который оптимизирован для поиска и хранит позиционную информацию лексемы в документе и ее вес. Это можно сделать стандартными командами SQL 

=# UPDATE apod SET fts=
setweight( coalesce( to_tsvector(keywords),''),'A') || ' ' || 
setweight( coalesce( to_tsvector(title),''),'B') || ' ' ||
setweight( coalesce( to_tsvector(body),''),'D');
В этом примере мы добавили атрибут fts, который представляет собой конкатенацию текстовых полей keywords, title и body. При этом, с помощью функции setweight мы приписали разные веса лексемам из разных частей. Заметим, что мы приписали только "метки", не численные значения, которые будут приписаны этим самым меткам в момент поиска. Это позволяет настраивать поиск буквально налету, например, используя один и тот же полнотекстовый индекс можно организовывать поиск только по заголовкам и ключевым словам. 
=# select * from apod where fts @@ to_tsquery('supernovae:ab');

На этом мы закончим введение в полнотекстовый поиск в PostgreSQL и приведем список основных возможностей.

Еще раз напомним, что полное и исчерпывающее описание полнотекстового поиска в PostgreSQL приведено в [FTSBOOK] (технический английский), советы и рекомендации можно посмотреть в презентации [RIT2007].

Что надо знать о полнотекстовой конфигурации

1) FTS конфигурация объединяет все необходимое для организации полнотекстового поиска, а именно:

2) FTS конфигураций может быть много, они могут быть определены в разных схемах. Имя активной FTS конфигурации содержится в переменной default_text_search_config. По умолчанию, она выбирается из всех конфигураций, имеющих флаг DEFAULT, которые созданы для серверной локали, в соответствии с правилом видимости объектов в PostgreSQL, т.е. определяется переменной search_path. Здесь надо уточнить, что специальная схема pg_catalog неявно ставится первой в search_path, если только ее положение не указали явно. Так как встроенные FTS конфигурации определены в схеме pg_catalog, то они могут маскировать конфигурации, созданные в схеме по умолчанию, обычно public, совпадающие по имени, если search_path не содержит явно pg_catalog. Предположим, что мы имеем две конфигурации с именем russian определенные для локали ru_RU.UTF-8 и имеющие флаг DEFAULT. 

=# \dF *.russ*
            List of text search configurations
   Schema   |  Name   |            Description
------------+---------+------------------------------------
 pg_catalog | russian | configuration for russian language
 public     | russian |
(2 row)
В зависимости от search_path мы будем иметь разную активную FTS конфигурацию. 
=# show tsearch_conf_name;
    tsearch_conf_name
-------------------------
 pg_catalog.russian
(1 row)

=# set search_path=public, pg_catalog;
SET
=# show tsearch_conf_name;
  tsearch_conf_name
---------------------
 public.russian
Таким образом, чтобы не возникали разного рода конфузы мы рекомендуем:

3) FTS конфигурация как любой обычный объект базы данных имеет владельца, ее можно удалять, создавать, изменять только при наличии соответствующих прав.

4) Как правило, для успешного поиска требуется следить, чтобы использовалась одна и та же FTS конфигурация при индексировании и при поиске.

5) Рекомендуется явно использовать название полнотекстовой конфигурации в функциях to_tsquery(), to_tsvector(), чтобы избежать возможных накладных расходов.

Что надо знать о словарях

1) Словарь - это программа, которая принимает на вход слово, а на выходе

2) Надо следить, чтобы все данные, которые используют словари,были в server_encoding.

Встроенные словари включают:

3) Тестировать словари можно с помощью функции lexize 

=# select lexize('en_stem', 'stars');
 lexize
--------
 {star}

=# select lexize('en_stem', 'a');
 lexize
--------
 {}

4) Словари можно добавлять в систему, см. пример [FTSBOOKAPPC]

Что нужно знать об индексах

GIN индекс, или обобщенный обратный индекс - это структура данных, у которой ключом является лексема, а значением - сортированный список идентификаторов документов, которые содержат эту лексему. Отметим, что позиционная информация не хранится в индексе, что связано с ограничениями PostgreSQL. Так как в обратном индексе используется бинарное дерево для поиска ключей, то он слабо зависит от их количества и потому хорошо шкалируется. Этот индекс используется практически всеми большими поисковыми машинами, однако его использование в базах данных для индексирования изменяющихся документов затруднено, так как любые изменения (добавление нового документа, обновление или удаление) приводят к большому количеству обновлений индекса. Например, добавление нового документа, который содержит N уникальных лексем приводит к обновлению N записей в индексе. Поэтому этот индекс лучше всего подходит для неменяющихся коллекций документов. GIN индекс поддерживает групповое обновление индекса, которое является очень эффективным, поэтому иногда быстрее создать индекс заново, чем обновлять индекс при добавке каждого документа.

В тоже время, GiST индекс является "прямым" индексом, т.е. для каждого документа ставится в соответствие битовая сигнатура, в которой содержится информация о всех лексемах, которые содержаться в этом документе, поэтому добавление нового документа приводит к добавлению только одной сигнатуры. Для быстрого поиска сигнатуры хранятся в сигнатурном дереве RD-Tree (russian doll, матрешка), реализованная помощью GiST.

Сигнатура - это битовая строка фиксированной длины, в которой все биты изначально выставленны в '0'. С помощью хэш-функции слово отображается в определенный бит сигнатуры, который становится '1'. Сигнатура документа является наложением индивидуальных сигнатур всех слов. Такая техника называется superimposed coding и реализуется как bitwise OR, что является очень быстрой операцией. 
 word   signature
 ----------------
 w1 ->  01000000 
 w2 ->  00010000
 w3 ->  10000000
 ----------------------
        11010000
В этом примере, '11010000' является сигнатурой документа, состоящего из трех уникальных слов w1,w2,w3. Сигнатура является некоторым компактным представлением документа, что приводит к значительному уменьшению размера коллекции. Кроме того, фиксированный размер cигнатуры сильно облегчает операции сравнения. Все это делает использование сигнатур вместо документов привлекательным с точки зрения производительности.

При поиске, запрос можно аналогичным образом представить в виде сигнатуры и тогда процесс поиска будет заключаться в сравнении сигнатур. Если хотя бы одно положение '1' в сигнатурах не совпадает, то можно с уверенностью утверждать, что документ не содержит поисковый запрос. Однако, если все '1' поисковой сигнатура совпадают с '1' сигнатуры документа, то это означает лишь то, что поисковый запрос может содержаться в документе и это требует проверки с использованием самого документа, а не его сигнатуры. Вероятностный ответ связан с использованием хеширования и суперпозиции. Ниже приводятся несколько примеров поисковых сигнатур. 

 11010000  - сигнатура документа
 00000001  - сигнатура запроса Q1, точно не содержится в документе
 01000000  - сигнатура запроса Q2, возможно содержится в документе
 01010000  - cигнатура запроса Q3, возможно содержится в документе
Сигнатура Q2 является сигнатурой слова w1 и, таким образом, является правильным попаданием, в то время как сигнатура Q3 - ложным попаданием (false drop), несмотря на то, что она удовлетворяет сигнатуре документа. Ясно, что конечность размера сигнатуры и увеличение количества уникальных слов приводит к насыщению сигнатуры, т.е., когда все биты будут '1', что сильно уменьшает избирательность сигнатуры и ухудшает производительность поиска.

Существуют несколько структур данных для хранения сигнатур, такие как сигнатурный файл (signature file),но они не являются индексами, так как требует полного просмотра. Дерево RD-Tree является аналогом R-Tree, приспособленное к работе со множествами для решения задачи поиска всех множеств, которые содержат в себе некое подмножество, является индексной структурой и может сильно ускорять поиск. Подробнее о RD-Tree можно прочитать в оригинальной статье [RDTREE]

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

Из-за использования суперпозиции поиск по дереву может ответить однозначно только на то, что поисковая сигнатура точно не содержится в какой-либо сигнатуре, что позволяет не рассматривать не только эту сигнатуру, но и все поддерево под ней, что и приводит к значительному ускорению поиска. Например, для сигнатуры 11011000 правую ветку можно точно не рассматривать, однако она может находиться в левой ветке. 

   
                                         ROOT
                                       11011011
                                   
Internal nodes:           11011001                 10010011
                             |                        |
Leaves:          11010000, 11010001, 11011000   10010010,10010001
Очевидно, что чем больше глубина дерева, тем больше вероятность того, что сигнатура вырождается, т.е., начинает состоять из одних '1', а это приводит к тому, что приходится просматривать много веток и поиск замедляется. В предельном случае, когда сигнатура состоит из одних '1', она становится бесполезной, т.е., приходится просматривать все ветки, находящиеся под ней.

Найденные результаты приходится дополнительно проверять на наличие "false drops", т.е., проверять сами исходные документы, действительно ли они удовлетворяют поисковому запросу, что требует произвольного доступа к "heap" (таблице) и это сильно сказывается на производительности. Степень неоднозначности (lossiness), а следовательно и производительность GiST-индекса, зависит от кол-ва уникальных лексем и количества документов, что ограничивает применимость этого индекса для больших коллекций.

Это можно проиллюстрировать с помощью explain analyze на примере поиска по целочисленным массивам с помощью расширения intarray, в котором тоже используются сигнатуры и RD-Tree. 

 Bitmap Heap Scan on tt  (cost=29.27..405.87 rows=100 width=979) (actual time=68.714..6311.757 rows=678 loops=1)
   Filter: (a @> '{2}'::integer[])
   ->  Bitmap Index Scan on gist_idx  (cost=0.00..29.24 rows=100 width=0) (actual time=43.430..43.430 rows=10774 loops=1)
         Index Cond: (a @> '{2}'::integer[])
 Total runtime: 6312.807 ms
Видно, что GiST индекс (gist_idx) нашел 10774 записей из которых только 678 являются правильным ответом. Проверка 10774 записей и потребовала все время. GIN индекс в этом случае выполнил запрос всего за 11.892 ms. Как говорится, почувствуйте разницу ! 
Bitmap Heap Scan on tt  (cost=150.50..527.11 rows=100 width=982) (actual time=0.610..11.277 rows=678 loops=1)
   Recheck Cond: (a @> '{2}'::integer[])
   ->  Bitmap Index Scan on gin_idx  (cost=0.00..150.48 rows=100 width=0) (actual time=0.433..0.433 rows=678 loops=1)
         Index Cond: (a @> '{2}'::integer[])
 Total runtime: 11.892 ms

Но это не вся правда о GiST-индексе ! На самом деле, в листьях могут храниться не сигнатуры, а сами tsvector-а, если они не превышают TOAST_INDEX_TARGET байт, что-то около 512 байт. В этом случае попадание является точным и проверять ничего не надо. К сожалению, пока нет возможности индексу сказать какое было попадание, но в будущем, когда появится такая возможность, эта оптимизация может очень хорошо работать для новостных сайтов, где документы не очень большие. Чтобы изучить GiST-индекс, можно воспользоваться специальным модулем Gevel [GEVEL], который выдает полезную информацию об индексе. Вот пример такой выдачи для индекса gist_idx_50 для базы, которая содержит небольшие сообщения. Обратите внимание, что листья содержат как сами tsvector-а, так и сигнатуры, а внутренние ноды - только сигнатуры. 

arxiv=# select gist_stat('gist_idx_90');
                 gist_stat                  
--------------------------------------------
 Number of levels:          4
 Number of pages:           18296
 Number of leaf pages:      17496
 Number of tuples:          435661
 Number of invalid tuples:  0
 Number of leaf tuples:     417366
 Total size of tuples:      124776048 bytes
 Total size of leaf tuples: 119803816 bytes
 Total size of index:       149880832 bytes

-- leaf node
arxiv=# select * from gist_print('gist_idx_90') as 
        t(level int,valid bool, fts gtsvector) where level =4;
 level | valid |              fts               
-------+-------+--------------------------------
     4 | t     | 130 true bits, 1886 false bits
     4 | t     | 95 unique words
     4 | t     | 33 unique words
     4 | t     | 77 unique words
     4 | t     | 68 unique words
     4 | t     | 86 unique words
     4 | t     | 77 unique words
     4 | t     | 51 unique words
     4 | t     | 122 unique words
     4 | t     | 127 true bits, 1889 false bits
     4 | t     | 105 unique words
     4 | t     | 170 true bits, 1846 false bits
     4 | t     | 77 unique words
     4 | t     | 121 true bits, 1895 false bits
....................................
     4 | t     | 61 unique words
(417366 rows)

-- internal node
arxiv=# select * from gist_print('gist_idx_90') as 
        t(level int, valid bool, fts gtsvector) where level =3;

 level | valid |              fts               
-------+-------+--------------------------------
     3 | t     | 852 true bits, 1164 false bits
     3 | t     | 861 true bits, 1155 false bits
     3 | t     | 858 true bits, 1158 false bits
     3 | t     | 872 true bits, 1144 false bits
     3 | t     | 858 true bits, 1158 false bits
     3 | t     | 855 true bits, 1161 false bits
     3 | t     | 853 true bits, 1163 false bits
     3 | t     | 857 true bits, 1159 false bits
..................................................
     3 | t     | 782 true bits, 1234 false bits
     3 | t     | 773 true bits, 1243 false bits
(17496 rows)

Какой индекс использовать ?

После появления GIN-индекса, который хорошо шкалируется, может возникнуть ощущение, что GiST-индекс не нужен. Чтобы сравнить эти индексы мы взяли большую коллекцию абстрактов научных статей из arxiv.org (спасибо Сергею Карпову, который скачал и залил их в базу данных), которая содержит 459841 абстрактов. Вся база занимает чуть больше одного гигабайта. Подробнее можно прочитать на wiki [GINGIST], а здесь мы приведем только результаты (все времена приведены в миллисекундах). Тестировались три индекса - GiN-индекс и два GiST-индекса с разными факторами заполнения (fillfactor). GiN-индекс пока не поддерживате fillfactor. 

index           creation(ms)    size (b)        count(*)        rank query
-------------------------------------------------------------------------
GiN             532310.368      305864704       38.739          130.488
GIST100         189321.561      130465792       120.730         215.153
GIST50          164669.614      279306240       122.101         200.963
Здесь count(*) - это простой поисковый запрос, а rank query - это поисковый запрос с ранжированием. Обновление индекса проверялось для 95,1035,10546 записей. 
index (nlev)      95            1035            10546
-----------------------------------------------------------
GIN             3343.881        36337.733       217577.424
GIST50 (5)      238.101         2952.362        33984.443
GIST100 (4)     232.674         2460.621        27852.507
Выводы: Таким образом, GiST-индекс надо использовать для обновляемых данных, а GIN - для статичных архивов. Разбиение данных на обновляемую часть и архив и использование соответствующих индексов, позволяет получать производительный поиск на больших коллекциях с обновляемым контентом.

Синхронизация полнотекстового индекса

Если ваша база данных хоть сколько-нибудь обновляется, то вам нужно будет следить за поддержанием полнотекстового индекс по мере добавление новых документов. PostgreSQL позволяет автоматизировать этот процесс с помощью определения триггера, который запускается после добавления новой строки или обновления существующих записей. Встроенный триггер tsearch() позволяет легко настроить обновление индекса, можно задать несколько текстовых колонок и имя функции для обработки соответствующей колонки. Вот пример использования функции для замены знака @ на знак пробела. 

CREATE FUNCTION dropatsymbol(text) RETURNS text 
AS 'select replace($1, ''@'', '' '');'
LANGUAGE SQL;

CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT 
ON tblMessages FOR EACH ROW EXECUTE PROCEDURE 
tsearch(tsvector_column,dropatsymbol, strMessage);

Для более сложного случая, когда документ состоит из нескольких частей с разными весами можно написать процедуру на языке plpgsql (не забудьте разрешить его использование с помощью команды createlang plpgsql DBNAME).

Создадим тестовую табличку со следующей структурой. 

CREATE TABLE aa ( 
	id integer primary key, 
	t1 text, 
	t2 text, 
	fts tsvector
);

=# create function my_update() returns trigger as 
$$
BEGIN
   NEW.fts=
   setweight( to_tsvector('english',NEW.t1),'A') || ' ' ||
   setweight( to_tsvector('english',NEW.t2),'B');
 RETURN NEW;
END;
$$ 
language plpgsql;
В этой функции мы для простоты опустили использование coalesce(). 
CREATE TRIGGER fts_update BEFORE INSERT OR UPDATE ON aa
FOR EACH ROW EXECUTE PROCEDURE my_update();

=# insert into aa (id, t1,t2) values(1,'12,15,789,3','500');
=# insert into aa (id, t1,t2) values(2,'-546,3','150');
=# select * from aa;
 id |     t1      | t2  |                   fts
----+-------------+-----+------------------------------------------
  1 | 12,15,789,3 | 500 | '3':4A '12':1A '15':2A '500':5B '789':3A
  2 | -546,3      | 150 | '3':2A '150':3B '-546':1A
(2 rows)

Как мы видим, вставка новых записей работает как и ожидалось. Проверим обновление. 

=# update aa set t1 = '1234567' where id=1;
=# select * from aa;
 id |   t1    | t2  |            fts
----+---------+-----+---------------------------
  2 | -546,3  | 150 | '3':2A '150':3B '-546':1A
  1 | 1234567 | 500 | '500':2B '1234567':1A
(2 rows)

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

If ( OLD.t1 <> NEW.t1 or OLD.t2 <> NEW.t2 ) Then
  -- получение fts
Endif

Тестирование настроек

Зачастую бывает необходимо потестировать свою полнотекстовую конфигурацию. Для этог существует встроенная функция ts_debug, которая наглядно показывает что происходит с текстом. Она подробно описана в документации [FTSBOOKDEBUG], мы приведем лишь пример: 

apod=# select * from ts_debug('the Supernovae stars');
 Alias |  Description  |   Token    |      Dicts list      |          Lexized token          
-------+---------------+------------+----------------------+---------------------------------
 lword | Latin word    | the        | {pg_catalog.en_stem} | pg_catalog.en_stem: {}
 blank | Space symbols |            |                      | 
 lword | Latin word    | Supernovae | {pg_catalog.en_stem} | pg_catalog.en_stem: {supernova}
 blank | Space symbols |            |                      | 
 lword | Latin word    | stars      | {pg_catalog.en_stem} | pg_catalog.en_stem: {star}
(5 rows)

Здесь заслуживает внимание последняя колонка, которая называется "Lexized token". В ней содержится имя словаря, который распознал токен и массив лексем, в который этот словарь преобразовал токен. Так как у нас настроен только один словарь pg_catalog.en_stem, который к тому же распознает любые слова, то все токены им и распознались. Токен the распознался как стоп-слово, поэтому мы получили пустой массив и оно не будет проиндексировано. Остальные токены были приведены к некоторому нормальному виду.

Можно указать явно название полнотекстовой конфигурации, что бы протестировать ее. 

apod=# select * from ts_debug('simple','the Supernovae stars');
 Alias |  Description  |   Token    |     Dicts list      |          Lexized token          
-------+---------------+------------+---------------------+---------------------------------
 lword | Latin word    | the        | {pg_catalog.simple} | pg_catalog.simple: {the}
 blank | Space symbols |            |                     | 
 lword | Latin word    | Supernovae | {pg_catalog.simple} | pg_catalog.simple: {supernovae}
 blank | Space symbols |            |                     | 
 lword | Latin word    | stars      | {pg_catalog.simple} | pg_catalog.simple: {stars}
(5 rows)

Как мы уже указывали выше, тестировать словари можно с помощью функции lexize.

Парсеры также можно тестировать использую функцию parse. 

=# select * from parse('default','123 - a number');
 tokid | token
-------+--------
    22 | 123
    12 |
    12 | -
     1 | a
    12 |
     1 | number
зедсь tokid - это id типа токена 
=# select * from token_type('default');
 tokid |    alias     |            description
-------+--------------+-----------------------------------
     1 | lword        | Latin word
     2 | nlword       | Non-latin word
     3 | word         | Word
     4 | email        | Email
     5 | url          | URL
     6 | host         | Host
     7 | sfloat       | Scientific notation
     8 | version      | VERSION
     9 | part_hword   | Part of hyphenated word
    10 | nlpart_hword | Non-latin part of hyphenated word
    11 | lpart_hword  | Latin part of hyphenated word
    12 | blank        | Space symbols
    13 | tag          | HTML Tag
    14 | protocol     | Protocol head
    15 | hword        | Hyphenated word
    16 | lhword       | Latin hyphenated word
    17 | nlhword      | Non-latin hyphenated word
    18 | uri          | URI
    19 | file         | File or path name
    20 | float        | Decimal notation
    21 | int          | Signed integer
    22 | uint         | Unsigned integer
    23 | entity       | HTML Entity

Пример: Астрономический поиск

Мы приведем пример организации полнотекстового поиска, который каждый может повторить с версией PostgreSQL 8.3+. Однако, большинство команд вполне должно работать и с PostgreSQL 8.2+, только вам для этого придется установить contrib/tsearch2 и загрузить в свою тестовую базу данных.

Исходные данные - архив [APOD]. 

> curl -O http://www.sai.msu.su/~megera/postgres/fts/apod.dump.gz
> createdb apod
(для PostgreSQL 8.2+ надо установить модуль contrib/tsearch2 и загрузить его
в БД apod)
> zcat apod.dump.gz | psql apod
> psql apod
Структура таблицы apod. Отметим, что поле keywords содержит ключевые слова, присвоенные экспертами вручную. 
apod=# \d apod
       Table "public.apod"
  Column  |   Type   | Modifiers 
----------+----------+-----------
 id       | integer  | not null
 title    | text     | 
 body     | text     | 
 sdate    | date     | 
 keywords | text     | 
Indexes:
    "apod_pkey" PRIMARY KEY, btree (id)

Текущая полнотекстовая конфигурация по умолчанию у нас pg_catalog.russian_utf8, так как наш кластер был создан командой с параметром --locale=ru_RU.UTF-8. 

apod=# \dF+ pg_catalog.russian_utf8
Configuration "pg_catalog.russian_utf8"
Parser name: "pg_catalog.default"
Locale: 'ru_RU.UTF-8' (default)
    Token     |      Dictionaries       
--------------+-------------------------
 email        | pg_catalog.simple
 file         | pg_catalog.simple
 float        | pg_catalog.simple
 host         | pg_catalog.simple
 hword        | pg_catalog.ru_stem_utf8
 int          | pg_catalog.simple
 lhword       | pg_catalog.en_stem
 lpart_hword  | pg_catalog.en_stem
 lword        | pg_catalog.en_stem
 nlhword      | pg_catalog.ru_stem_utf8
 nlpart_hword | pg_catalog.ru_stem_utf8
 nlword       | pg_catalog.ru_stem_utf8
 part_hword   | pg_catalog.simple
 sfloat       | pg_catalog.simple
 uint         | pg_catalog.simple
 uri          | pg_catalog.simple
 url          | pg_catalog.simple
 version      | pg_catalog.simple
 word         | pg_catalog.ru_stem_utf8

Выше, мы уже упоминали, что начиная с версии 8.3+ можно сделать простой полнотекстовый поиск в одну команду и приводили команду для добавления поля типа tsvector, чтобы получить полноценный поиск. 

apod=# UPDATE apod SET fts=
setweight( coalesce( to_tsvector(keywords),''),'A')|| ' ' ||
setweight( coalesce( to_tsvector(title),''),'B') || ' ' ||
setweight( coalesce( to_tsvector(body),''),'D');
apod=# \d apod
       Table "public.apod"
  Column  |   Type   | Modifiers
----------+----------+-----------
 id       | integer  | not null
 title    | text     |
 body     | text     |
 sdate    | date     |
 keywords | text     |
 fts      | tsvector |
Indexes:
    "apod_pkey" PRIMARY KEY, btree (id)

После этого мы уже можем искать и ранжировать результаты поиска. 

apod=# select title,ts_rank_cd(fts, q) from apod, 
to_tsquery('supernovae & x-ray') q 
where fts  @@ q order by ts_rank_cd desc limit 5;
                     title                      | ts_rank_cd 
------------------------------------------------+---------
 Supernova Remnant E0102-72 from Radio to X-Ray | 1.59087
 An X-ray Hot Supernova in M81                  | 1.47733
 X-ray Hot Supernova Remnant in the SMC         | 1.34823
 Tycho's Supernova Remnant in X-ray             | 1.14318
 Supernova Remnant and Neutron Star             | 1.08116
(5 rows)

Time: 11.948 ms

Заметим, что никаких индексов не было создано, полнотекстовый поиск обязан работать и без них. Для ускорения поиска мы можем создать индекс и повторить запрос. 

apod=# create index fts_idx on apod using gin (fts);
apod=# select title,ts_rank_cd(fts, to_tsquery('supernovae & x-ray')) from apod
where fts  @@ to_tsquery('supernovae & x-ray') order by ts_rank_cd desc limit 5;
                     title                      | ts_rank_cd
------------------------------------------------+---------
 Supernova Remnant E0102-72 from Radio to X-Ray | 1.59087
 An X-ray Hot Supernova in M81                  | 1.47733
 X-ray Hot Supernova Remnant in the SMC         | 1.34823
 Tycho's Supernova Remnant in X-ray             | 1.14318
 Supernova Remnant and Neutron Star             | 1.08116
(5 rows)

Time: 1.998 ms
Видно, что результаты не изменились, как и должно быть, но время исполнения запросы уменьшилось на порядок. Что мы и хотели получить.

В запросе мы использовали функцию ts_rank_cd, которая возвращает ранк документа относительно запроса. В нашем случае документ - это fts, а запрос - to_tsquery('supernovae & x-ray'). fts мы создавали из нескольких текстовых атрибутов, которым были присвоены разные веса. Их численные значения могут быть заданы в функции ts_rank_cd, которые по умолчанию имеет следующие значения 0.1, 0.2, 0.4, 1.0, что соответствует D,C,B,A. Мы можем явно указать новые значения, например, подняв важность слов в заголовках, а важность ключевых слов сильно понизив, считая экспертов не очень компетентными. 

apod=# select title,ts_rank_cd('{0.1,0.2,1.0,0.1}',fts, to_tsquery('supernovae & x-ray')) from apod
where fts  @@ to_tsquery('supernovae & x-ray') order by ts_rank_cd desc limit 5;
                     title                      | ts_rank_cd
------------------------------------------------+----------
 An X-ray Hot Supernova in M81                  | 0.708395
 X-ray Hot Supernova Remnant in the SMC         | 0.646742
 Supernova Remnant N132D in X-Rays              | 0.577618
 Cas A Supernova Remnant in X-Rays              | 0.458009
 Supernova Remnant E0102-72 from Radio to X-Ray |  0.44515
(5 rows)
Мы видим, как поменялись результаты. Отметим, что значения ts_rank_cd не имеют особенного смысла, имеет значение только порядок. Однако, иногда хочется иметь нормированное значение и в таком случае можно использовать ts_rank_cd/(ts_rank_cd+1), например.

Если мы хотим показать в результатах поиска выдержки из текста, то можно воспользоваться функцией ts_headline. 

apod=# select ts_headline(body,to_tsquery('supernovae & x-ray'),'StartSel=<,StopSel=>,MaxWords=10,MinWords=5'),
ts_rank_cd(fts, to_tsquery('supernovae & x-ray')) from apod
where fts  @@ to_tsquery('supernovae & x-ray') order by ts_rank_cd desc limit 5;
                               ts_headline                               | ts_rank_cd 
----------------------------------------------------------------------+---------
 <supernova> remnant  E0102-72, however, is giving astronomers a clue | 1.59087
 <supernova> explosion. The picture was taken in  <X>-<rays>          | 1.47733
  <X>-<ray> glow is produced by  multi-million degree                 | 1.34823
  <X>-<rays> emitted by this shockwave made by a telescope            | 1.14318
  <X>-<ray> glow. Pictured is the <supernova>                         | 1.08116
(5 rows)

Time: 2.191 ms
Здесь мы указали, что выделять найденные слова надо с помощью уголков и размер текста должен быть не меньше 5 слов, но не более 10. Для версий PostgreSQL 9.5- рекомендуется использовать subselect, чтобы не вычислять лишний раз ts_headline, например так: 
apod=# select ts_headline(body, to_tsquery('supernovae & x-ray'), 'StartSel=<,StopSel=>,MaxWords=10,MinWords=5'),
rank from ( 
  select body, ts_rank_cd(fts,to_tsquery('supernovae & x-ray')) as rank from apod
  where fts @@ to_tsquery('supernovae & x-ray') 
  order by rank desc limit 5
) as foo;
                               ts_headline                               | ts_rank_cd 
----------------------------------------------------------------------+---------
 <supernova> remnant  E0102-72, however, is giving astronomers a clue | 1.59087
 <supernova> explosion. The picture was taken in  <X>-<rays>          | 1.47733
  <X>-<ray> glow is produced by  multi-million degree                 | 1.34823
  <X>-<rays> emitted by this shockwave made by a telescope            | 1.14318
  <X>-<ray> glow. Pictured is the <supernova>                         | 1.08116
(5 rows)

Time: 2.096 ms

Используя один и тот же полнотекстовый индекс fts мы можем искать по частям документа или их комбинациям. Например, можно потребовать, чтобы слово x-ray встречалось в заголовках документов. 

apod=# select title,ts_rank_cd(fts, to_tsquery('supernovae & x-ray:b')) from apod
where fts  @@ to_tsquery('supernovae & x-ray:b')  order by ts_rank_cd desc limit 5;
                     title                      | ts_rank_cd  
------------------------------------------------+----------
 Supernova Remnant E0102-72 from Radio to X-Ray |  1.59087
 An X-ray Hot Supernova in M81                  |  1.47733
 X-ray Hot Supernova Remnant in the SMC         |  1.34823
 Tycho's Supernova Remnant in X-ray             |  1.14318
 Vela Supernova Remnant in X-ray                | 0.703056
(5 rows)

Пример: FTS конфигурация для www.postgresql.org

На сайтах postgresql.org вы можете увидеть полнотекстовый поиск в жизни. Объем индексированных документов - это около 600,0000 постингов в архивах рассылок и более 20,000 документов на сайте www.postgresql.org. Создадим полнотекстовую конфигурацию для такого поиска. Так как мы создаем тематический поиск, мы должны создать словарь синонимов pg_dict.txt, который содержит, например, все названия базы данных PostgreSQL и положим его в директорию $PGROOT/share/dicts_data. 

postgres postgresql
pgsql    postgresql
postgres postgresql

Теперь можно создать нашу конфигурацию public.pg используя стандартную конфигурацию для английского языка english. Конечно, все делаем в транзакции, чтобы не оставалось "мусора", если где-то возникла проблема. 

BEGIN;
CREATE TEXT SEARCH CONFIGURATION public.pg  LOCALE  'ru_RU.UTF-8' 
       LIKE english WITH MAP AS DEFAULT;
CREATE TEXT SEARCH DICTIONARY pg_dict OPTION 'pg_dict.txt'  LIKE synonym;
CREATE TEXT SEARCH DICTIONARY en_ispell 
OPTION 'DictFile="english-utf8.dict",
        AffFile="english-utf8.aff",
        StopFile="english-utf8.stop"'
LIKE ispell_template;
ALTER TEXT SEARCH DICTIONARY en_stem SET OPTION 'english-utf8.stop';
ALTER TEXT SEARCH MAPPING ON pg FOR lword,lhword,lpart_hword
                             WITH pg_dict,en_ispell,en_stem;
DROP TEXT SEARCH MAPPING ON pg FOR email, url, sfloat, uri, float;
END;
Мы создали словарь на основе словаря ispell. Так как мы используем UTF-8, то мы используем конвертированные в UTF-8 файлы ispell. Далее, мы указали словарям en_ispell, en_stem использовать стоп-слова для английского языка в директории $PGROOT/share/dicts_data. Затем, мы задали, что токены типа lword,lhword,lpart_hword, обозначающие английские слова, должны обрабатываться словарями pg_dict,en_ispell,en_stem и именно в таком порядке. И напоследок, мы удалили правила для токенов, которые нас не интересуют - это email, url, sfloat, uri, float.

Более подробно можно прочитать в [FTSBOOKAPPA].

Поддержка в psql

Информацию о полнотекстовых объектах можно получить в psql с помощью команд \dF{,d,p}[+] [PATTERN]. Здесь 
=# \dF *fts*
       List of fulltext configurations
 Schema |  Name   |   Locale    | Description
--------+---------+-------------+-------------
 public | fts_cfg | ru_RU.UTF-8 |

=# \dF *.fts*
       List of fulltext configurations
 Schema |  Name   |   Locale    | Description
--------+---------+-------------+-------------
 fts    | fts_cfg | ru_RU.UTF-8 |
 public | fts_cfg | ru_RU.UTF-8 |

SQL команды

Внимание: Окончательный синтаксис SQL комманд еще не утвержден !
CREATE TEXT SEARCH CONFIGURATION - создание полнотекстовой конфигурации
CREATE TEXT SEARCH CONFIGURATION cfgname 
PARSER  prsname  [ LOCALE localename] 
[AS DEFAULT];

CREATE TEXT SEARCH CONFIGURATION cfgname 
[{  PARSER  prsname | LOCALE  localename  }  [ ...]] 
LIKE template_cfg [WITH MAP] 
[AS DEFAULT];
  • FTS конфигурация принадлежит пользователю, который создал ее
  • Имя конфигурации cfgname может содержать название схемы, тогда она будет создана в этой схеме, иначе конфигурация будет создана в текущей схеме.
  • PARSER prsname задает парсер, который который используется для разбивания текста на токены. Имя парсера также может содержать название схемы.
  • LOCALE localename - задает название серверной локале, для которой эта конфигурация будет выбираться по умолчанию, если задана опция AS DEFAULT.
  • LIKE template_cfg указывает, что в качестве шаблона используется существующая FTS конфигурация template_cfg.
  • WITH MAP используется с LIKE template_cfg и означает, что также копируются правила обработки токенов словарями.
Примеры: 
=# CREATE TEXT SEARCH CONFIGURATION test LIKE pg_catalog.russian_utf8 AS DEFAULT;
=# \dF public.test
           List of fulltext configurations
 Schema | Name |   Locale    | Default | Description
--------+------+-------------+---------+-------------
 public | test | ru_RU.UTF-8 | Y       |
DROP TEXT SEARCH CONFIGURATION - удалить FTS конфигурацию
DROP TEXT SEARCH CONFIGURATION [IF EXISTS]cfgname [ CASCADE | RESTRICT ];
  • IF EXISTS указывать не выдавать ошибку, если удаляемая конфигурация не существует.
  • CASCADE - автоматически удалить все FTS объекты, зависящие от удаляемой FTS конфигурации.
  • RESTRICT - не удалять FTS конфигурацию, если есть какие-либо FTS объекты, зависящие от нее. Этот режим используется по умолчанию.
ALTER TEXT SEARCH CONFIGURATION - изменить FTS конфигурацию
ALTER TEXT SEARCH CONFIGURATION cfgname RENAME TO newcfgname;

ALTER TEXT SEARCH CONFIGURATION cfgname 
SET { LOCALE localename | PARSER prsname } [, ...];

ALTER TEXT SEARCH CONFIGURATION cfgname { SET AS | DROP } DEFAULT;
Эта команда позволяет изменить параметры, задаваемые при ее создании.
CREATE TEXT SEARCH DICTIONARY - создать словарь
CREATE TEXT SEARCH DICTIONARY dictname 
    LEXIZE  lexize_function
    [INIT  init_function ]
    [OPTION  opt_text ]
;
  

CREATE TEXT SEARCH DICTIONARY dictname 
[    {   INIT  init_function 
        | LEXIZE  lexize_function 
        | OPTION opt_text } 
[ ... ]] LIKE template_dictname;
  • Название словаря dictname может содержать название схемы, в которой он будет создан, например, public.english.
  • LEXIZE lexize_function - название функции, которая занимается преобразованием токена в лексему.
  • INIT init_function - название функции, которая инициализирует словарь
  • OPTION opt_text - задает текстовую строку, которая доступна словарю. Обычно, ее используют для указания файлов, используемых словарем. Относительные пути для словарных файлов интерпретируются относительно директории $PGROOT/share/dicts_data.
  • LIKE template_dictname - задает словарь-шаблон, используемый для создания словаря. При этом, значения параметров INIT, LEXIZE, OPTION, если заданы, перекрывают значения по умолчанию.
Для примера, создадим словарь my_simple, который будет аналогичен встроенному словарю simple, но способен различать стоп-слова английского языка. 
=# CREATE TEXT SEARCH DICTIONARY public.my_simple OPTION 'english.stop' 
LIKE pg_catalog.simple;
=# select lexize('public.my_simple','YeS');
 lexize
--------
 {yes}
=# select lexize('public.my_simple','The');
 lexize
--------
 {}
Пример создания нового словаря можно посмотреть в Приложении.
DROP TEXT SEARCH DICTIONARY - удаляет словарь
DROP TEXT SEARCH DICTIONARY [IF EXISTS]dictname [ CASCADE | RESTRICT ];
ALTER TEXT SEARCH DICTIONARY - изменяет параметры словаря
ALTER TEXT SEARCH DICTIONARY dictname RENAME TO newdictname;

ALTER TEXT SEARCH DICTIONARY dictname SET OPTION opt_text;
CREATE TEXT SEARCH MAPPING - создать правила обработки токенов словарями
CREATE TEXT SEARCH MAPPING ON cfgname FOR tokentypename[, ...] WITH dictname1[, ...];
Для FTS конфигурации cfgname задается соответствие между списком tokentypename1,tokentypename2,... и словарями, через которые эти токены этих типов будут проходить.
  • tokentypename[, ...] - список типов токенов, например, lword,lhword,lpart_hword.
  • dictname1[, ...] - список словарей, которые будут пытаться опознать токены. Порядок словарей важен.
Например, создадим тестовую конфигурацию testcfg на основе шаблона russian_utf8 и зададим правила обработки английских слов lhword,lpart_hword,lword. 
=# CREATE TEXT SEARCH CONFIGURATION testcfg LOCALE  'testlocale'  LIKE russian_utf8;
=# CREATE TEXT SEARCH MAPPING ON testcfg FOR lword,lhword,lpart_hword 
WITH simple,en_stem;
=# \dF+ testcfg
Configuration 'testcfg'
Parser name: 'default'
Locale: 'testlocale'
     Token   |  Dictionaries
-------------+----------------
 lhword      | simple,en_stem
 lpart_hword | simple,en_stem
 lword       | simple,en_stem
ALTER TEXT SEARCH MAPPING - изменить правило обработки токенов
ALTER TEXT SEARCH MAPPING ON cfgname FOR tokentypename[, ...] WITH dictname1[, ...];

ALTER TEXT SEARCH MAPPING ON cfgname [FOR tokentypename[, ...] ] 
REPLACE olddictname TO newdictname;
Позволяет добавлять новые правила обработки токенов или изменять старые. Изменим правило для токена типа lword, см. предыдущий пример. 
=# ALTER TEXT SEARCH MAPPING ON testcfg FOR lhword WITH simple;
=# \dF+ testcfg
 Configuration 'testcfg'
Parser name: 'default'
Locale: 'testlocale'
 Token  |  Dictionaries
--------+----------------
 lhword      | simple,en_stem
 lpart_hword | simple,en_stem
 lword       | simple
DROP TEXT SEARCH MAPPING - удалить правило обработки токена
DROP TEXT SEARCH MAPPING [IF EXISTS] ON cfgname FOR tokentypename;
CREATE TEXT SEARCH PARSER - создать FTS парсер
CREATE TEXT SEARCH PARSER prsname 
    START= start_function
    GETTOKEN  gettoken_function
    END  end_function
    LEXTYPES  lextypes_function
    [  ts_headline  ts_headline_function ]
;
  • prsname - имя создаваемого парсера, может содержать название схемы, в которой он будет создан.
  • start_function - название функции, которая инициализирует парсер.
  • gettoken_function - название функции, которая возвращает токен.
  • end_function - название функции, которая вызывается после окончания работы парсера.
  • lextypes_function - название функции, которая возвращает массив, содержащий {id,alias,full descr} - идентификатор, краткое название токена и полное описание. Подробнее, смотри в src/include/utils/ts_public.h.
  • ts_headline_function, - название функции, которая возвращает часть документа, содержащая запрос.
Встроенный парсер называется default и распознает 23 типа токенов, список которых можно получить с помощью функции token_type(prsname). Пример создания нового парсера можно посмотреть в Приложении
DROP TEXT SEARCH PARSER - удалить FTS парсер
DROP TEXT SEARCH PARSER [IF EXISTS] prsname [ CASCADE | RESTRICT ];
ALTER TEXT SEARCH PARSER - изменить имя FTS парсера
ALTER TEXT SEARCH PARSER prsname RENAME TO newprsname;
ALTER TEXT SEARCH ... OWNER - изменить владельца
ALTER TEXT SEARCH { PARSER|DICTIONARY|CONFIGURATION } name OWNER TO newowner;
По умолчанию, владельцем FTS объекта является тот, кто создал его. Команда ALTER TEXT SEARCH ... OWNER позволяет менять владельца.
COMMENT ON TEXT SEARCH - создать или изменить комментарий FTS объекта
COMMENT ON TEXT SEARCH { CONFIGURATION | DICTIONARY | PARSER }  objname IS text;
  • Комментарий виден при использовании + в команде psql, например, \dFd+ - показать расширенную информацию о словарях.
  • Для того чтобы убрать комментарий, надо задать NULL для параметра text. 
=# COMMENT ON TEXT SEARCH DICTIONARY intdict IS 'Dictionary for integers';

Благодарности

Работа над созданием полнотекстового поиска в PostgreSQL поддерживалась Российским Фондом Фундаментальных Исследований (05-07-90225), EnterprizeDB PostgreSQL Development Fund, Mannheim University, jfg:networks, Georgia Public Library Service, Рамблер.

Ссылки

Авторы

Олег Бартунов и Федор Сигаев являются членами PostgreSQL Global Development Group (поддержка и развитие GiST в PostgreSQL), авторами информационно-поисковой системы по PostgreSQL ресурсам и занимаются продвижением PostgreSQL в России. Они являются авторами полнотекстового поиска в PostgreSQL и целого ряда популярных расширений PostgreSQL, в том числе, поддержка иерархических типов данных ltree, работа с целочисленными массивами intarray. Более подробная информация доступна на странице PostgreSQL GiST development.

Приложение

Поиск с очепятками

Часто полнотекстовый поиск используется совместно с модулем contrib/pg_trgm, который на основе статистики триграмм позволяет находить слова, наиболее близкие к запросу. 

=# select show_trgm('supyrnova');
                    show_trgm                    
-------------------------------------------------
 {"  s"," su",nov,ova,pyr,rno,sup,upy,"va ",yrn}

С помощью функции ts_stat мы собираем информацию о всех индексируемых словах и затем строим триграммный индекс. 

=# select * into apod_words from ts_stat('select fts from apod') order by ndoc desc, 
     nentry desc,word;

=# \d apod_words
  Table "public.apod_words"
 Column |  Type   | Modifiers 
--------+---------+-----------
 word   | text    | 
 ndoc   | integer | 
 nentry | integer |

=# create index trgm_idx on apod_words using gist(word gist_trgm_ops);
Теперь мы можем быстро искать слова-кандидаты используя функцию similarity, которая подсчитывает похожесть слова используя количество общих триграмм. 
=# select word, similarity(word, 'supyrnova') AS sml 
from apod_words where word % 'supyrnova' order by sml desc, word;
     word      |   sml    
---------------+----------
 supernova     | 0.538462
 pre-supernova | 0.411765
(2 rows)
Из соображений производительности, слова, у которых похожесть не превышает некоторый порог, отбрасываются. Посмотреть значение порога и изменить его можно с помощью функций show_limit() и set_limit(real). По умолчанию используется значение 0.3.

Советы по повышению производительности

Если ваша коллекция документов очень большая и непрерывно пополняется, то может возникнуть ситуация, когда скорость вставки в базу и поиск станут не удовлетворять вас. PostgreSQL предоставляет много возможностей по оптимизации, но мы кратко коснемся сегментирования и распределения данных.

Сегментирование данных

Сегментирование данных можно организовать с помощью наследования (TABLE INHERITANCE) и CE (CONSTRAINT EXCLUSION). Идея состоит в том, чтобы иметь родительскую таблицу (класс), которая определяет основной набор атрибутов и таблицы, которые наследуют структуру родительской таблицы, но имеющие определенные ограничения на параметр, по которому проводится сегментирование. Механизм наследования в PostgreSQL обеспечивает выполнение запроса по всем таблицам автоматически, при этом наличие CE позволяет просматривать только те таблицы, которые удовлетворяют условию на параметр. Типичная ситуация, когда сегментирование идет по времени, например, для хранение журналов веб-серверов.

В нашем примере мы создаем таблицу apod_class и две таблицы, которые наследуют ее. Эти таблицы наследуют структуру родительской таблицы, но при этом могут иметь свои специфические атрибуты. Таблица apod_new предназначена для новых сообщений, а apod_archive для неизменяющихся архивных документов. Заметим, что для новых сообщений мы создали GiST индекс, который очень хорошо обновляется, а для архивной таблицы создали GIN индекс, который очень хорошо шкалируется, но обновление, как и для всех обратных индексов, происходит очень медленно. 

CREATE TABLE apod_class (
        id integer,
        title text,
        body text,
        sdate date,
        keywords text,
        fts tsvector
);
CREATE TABLE apod_new ( CHECK ( sdate >2001-08-08 ) ) INHERITS (apod_class);
CREATE INDEX gist_idx ON apod_new USING gist(fts);

CREATE TABLE apod_archive ( CHECK ( sdate ≤2001-08-08 ) ) INHERITS (apod_class);
CREATE INDEX gist_idx ON apod_new USING gin(fts);
PostgreSQL позволяет искать как по всей коллекции, указав таблицу apod_class, так и по отдельным частям. В зависимости от задачи, сегментировать данные можно и по большему количеству таблиц, например, распределять документы по годам, месяцам. Оптимизатор PostgreSQL автоматически выбирает только те таблицы, которые удовлетворяют условию CHECK, что очень благоприятно сказывается на производительности запросов. Например, для запроса 
apod=# select title,ts_rank_cd(fts, q) from apod_class, to_tsquery('stars') q 
where fts  @@ q order by ts_rank_cd desc limit 5;
будут просматриваться две таблицы, а для запроса 
apod=# select title,ts_rank_cd(fts, q) from apod_class, to_tsquery('stars') q 
where fts  @@ q and sdate > 2001-08-08 order by ts_rank_cd desc limit 5;
будет использоваться только таблица apod_new. Отметим, что для этого необходимо включить CONSTRAINT EXCLUSION 
SET constraint_exclusion TO on;

Распределение данных

Если сегментирование данных по таблицам недостаточно, то можно распределять данные по серверам. В этом случае, с помощью модуля contrib/dblink можно исполнять поисковые запросы на разных серверах, получать результаты, объединять их и выбирать необходимые документы, например, топ-10 самых релевантных документов. Вот пример запроса по коллекции, которая распределена по двум сервера по диапазонам идентификатора документов. 

select dblink_connect('pgweb','dbname=pgweb hostaddr='XXX.XXX.XXX.XXX');

select * from dblink('pgweb',
'select  tid, title, ts_rank_cd(fts_index, q) as rank from pgweb, 
to_tsquery(''table'') q  
where q @@ fts_index and tid >= 6000 order by rank desc limit 10' )
as t1 (tid integer, title text, rank real)

union all

select tid, title,  ts_rank_cd(fts_index, q) as rank from pgweb,
to_tsquery('table') q   
where q @@ fts_index and tid < 6000 and tid > 0 order by rank desc limit 10

) as foo
order by rank desc limit 10;
Отметим, что ранжирующая функция требует только локальной информации, что облегчает реализацию.

Словарь для целых чисел

В качестве примера нового словаря для полнотекстового поиска мы рассмотрим словарь для целых чисел intdict, который "обрезает" целые числа, так что общее количество уникальных слов уменьшается, что в целом благоприятно сказывается на производительности поиска. У словаря есть два параметра MAXLEN, который контролирует максимальную длину числа, и REJECTLONG, который указывает считать ли длинное целое число стоп-словом или нет. По умолчанию MAXLEN=6,REJECTLONG=false.

Для создания словаря необходимо написать две функции, имена которых потом будут использованы в команде CREATE TEXT SEARCH DICTIONARY ниже. Функция init_intdict инициализирует словарь - задает значения параметров по умолчанию и принимает их новые значения, функция dlexize_intdict делает всю основную работу - возвращает NULL, если слово неопознанно, пустой массив, если словарь решил, что входная строка это стоп-слово, или массив лексем, в противном случае.

Словарь просто обрезает длинные целые числа. 

=# select lexize('intdict', 11234567890);
  lexize
----------
 {112345}

Теперь будем трактовать длинные целые числа как стоп-слово. 

=# ALTER TEXT SEARCH DICTIONARY intdict SET OPTION 'MAXLEN=6, REJECTLONG=TRUE';
=# select lexize('intdict', 11234567890);
 lexize
--------
 {}

Файлы dict_tmpl.c,Makefile,dict_intdict.sql.in надо положить в директорию contrib/dict_intdict. После этого надо установить словарь и загрузить словарь в базу DBNAME. 

make && make install
psql DBNAME < dict_intdict.sql

Файл dict_tmpl.c: 

#include "postgres.h"
#include "utils/builtins.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

#include "utils/ts_locale.h"
#include "utils/ts_public.h"
#include "utils/ts_utils.h"

 typedef struct {
        int     maxlen;
        bool    rejectlong;
 } DictInt;


 PG_FUNCTION_INFO_V1(dinit_intdict);
 Datum dinit_intdict(PG_FUNCTION_ARGS);

 Datum
 dinit_intdict(PG_FUNCTION_ARGS) {
        DictInt *d = (DictInt*)malloc( sizeof(DictInt) );
        Map *cfg, *pcfg;
        text *in;

        if ( !d )
                elog(ERROR, "No memory");
        memset(d,0,sizeof(DictInt));

        /* Your INIT code */
/* defaults */
        d->maxlen = 6;
        d->rejectlong = false;

if ( PG_ARGISNULL(0) || PG_GETARG_POINTER(0) == NULL ) { /* no options */
        PG_RETURN_POINTER(d);
}
        in = PG_GETARG_TEXT_P(0);
        parse_keyvalpairs(in,&cfg);
        PG_FREE_IF_COPY(in, 0);
        pcfg=cfg;

        while (pcfg->key) {
                if ( strcasecmp("MAXLEN", pcfg->key) == 0 ) {
                        d->maxlen=atoi(pcfg->value);
                } else if ( strcasecmp("REJECTLONG", pcfg->key) == 0 ) {
                        if ( strcasecmp("true", pcfg->value) == 0 ) {
                                d->rejectlong=true;
                        } else if ( strcasecmp("false", pcfg->value) == 0 ) {
                                d->rejectlong=false;
                        } else {
                                elog(ERROR,"Unknown value: %s => %s", pcfg->key,
 pcfg->value);
                        }
                } else {
                        elog(ERROR,"Unknown option: %s => %s", pcfg->key, pcfg->
value);
                }
                pfree(pcfg->key);
                pfree(pcfg->value);
                pcfg++;
        }
        pfree(cfg);

        PG_RETURN_POINTER(d);
 }

PG_FUNCTION_INFO_V1(dlexize_intdict);
Datum dlexize_intdict(PG_FUNCTION_ARGS);
Datum
dlexize_intdict(PG_FUNCTION_ARGS) {
        DictInt *d = (DictInt*)PG_GETARG_POINTER(0);
        char       *in = (char*)PG_GETARG_POINTER(1);
        char *txt = pnstrdup(in, PG_GETARG_INT32(2));
        TSLexeme *res=palloc(sizeof(TSLexeme)*2);

        /* Your INIT dictionary code */
        res[1].lexeme = NULL;
        if  ( PG_GETARG_INT32(2) > d->maxlen ) {
                if ( d->rejectlong ) {          /* stop, return void array */
                        pfree(txt);
                        res[0].lexeme = NULL;
                } else {                        /* cut integer */
                        txt[d->maxlen] = '\0';
                       res[0].lexeme = txt;
                }
        } else {
                res[0].lexeme = txt;
        }

        PG_RETURN_POINTER(res);
}

Файл Makefile: 

subdir = contrib/dict_intdict
top_builddir = ../..
include $(top_builddir)/src/Makefile.global

MODULE_big = dict_intdict
OBJS =  dict_tmpl.o
DATA_built = dict_intdict.sql
DOCS =

include $(top_srcdir)/contrib/contrib-global.mk

Файл dict_intdict.sql.in: 

SET search_path = public;
BEGIN;

CREATE OR REPLACE FUNCTION dinit_intdict(internal)
         returns internal
         as 'MODULE_PATHNAME'
         language 'C';

CREATE OR REPLACE FUNCTION dlexize_intdict(internal,internal,internal,internal)
        returns internal
        as 'MODULE_PATHNAME'
        language 'C'
        with (isstrict);

CREATE TEXT SEARCH DICTIONARY intdict
        LEXIZE  'dlexize_intdict' INIT  'dinit_intdict'
        OPTION  'MAXLEN=6,REJECTLONG=false'
;
END;

Очень простой парсер

Предположим, что мы хотим создать свой парсер, который выделяет только один тип токена - слово (3,word,Word) и подключить его к полнотекстовому поиску. Для этого нам нужен еще один тип токена - это разделитель (12, blank,Space symbols). Идентификаторы типов (3,12) выбраны таким образом, чтобы можно было использовать стандартную функцию ts_headline.

Поместите файлы test_parser.c, Makefile, test_parser.sql.in в директорию contrib/test_parser, затем загрузите парсер в базу данных (в данном примере regression). 

make 
make install
psql regression < test_parser.sql

Мы создали тестовую FTS конфигурацию testcfg, для которой определен парсер testparser.

Для написания своего парсера необходимо разработать как-минимум 4 функции, см. SQL команду CREATE TEXT SEARCH PARSER. 

=# SELECT * FROM parse('testparser','That''s my first own parser');
 tokid | token
-------+--------
     3 | That's
    12 |
     3 | my
    12 |
     3 | first
    12 |
     3 | own
    12 |
     3 | parser
=# SELECT to_tsvector('testcfg','That''s my first own parser');
                   to_tsvector
-------------------------------------------------
 'my':2 'own':4 'first':3 'parser':5 'that''s':1
=# SELECT ts_headline('testcfg','Supernovae stars are the brightest phenomena in galaxies', to_tsquery('testcfg', 'star'));
                            ts_headline
-----------------------------------------------------------------
 Supernovae stars are the brightest phenomena in galaxies

Файл test_parser.c 

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/*
 * types
 */

/* self-defined type */
typedef struct {
  char *  buffer; /* text to parse */
  int     len;    /* length of the text in buffer */
  int     pos;    /* position of the parser */
} ParserState;

/* copy-paste from wparser.h of tsearch2 */
typedef struct {
  int     lexid;
  char    *alias;
  char    *descr;
} LexDescr;

/*
 * prototypes
 */
PG_FUNCTION_INFO_V1(testprs_start);
Datum testprs_start(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(testprs_getlexeme);
Datum testprs_getlexeme(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(testprs_end);
Datum testprs_end(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(testprs_lextype);
Datum testprs_lextype(PG_FUNCTION_ARGS);

/*
 * functions
 */
Datum testprs_start(PG_FUNCTION_ARGS)
{
  ParserState *pst = (ParserState *) palloc(sizeof(ParserState));
  pst->buffer = (char *) PG_GETARG_POINTER(0);
  pst->len = PG_GETARG_INT32(1);
  pst->pos = 0;

  PG_RETURN_POINTER(pst);
}

Datum testprs_getlexeme(PG_FUNCTION_ARGS)
{
  ParserState *pst   = (ParserState *) PG_GETARG_POINTER(0);
  char        **t    = (char **) PG_GETARG_POINTER(1);
  int         *tlen  = (int *) PG_GETARG_POINTER(2);
  int         type;

  *tlen = pst->pos;
  *t = pst->buffer +  pst->pos;

  if ((pst->buffer)[pst->pos] == ' ') {
    /* blank type */
    type = 12;
    /* go to the next non-white-space character */
    while (((pst->buffer)[pst->pos] == ' ') && (pst->pos < pst->len)) {
      (pst->pos)++;
    }
  } else {
    /* word type */
    type = 3;
    /* go to the next white-space character */
    while (((pst->buffer)[pst->pos] != ' ') && (pst->pos < pst->len)) {
      (pst->pos)++;
    }
  }

  *tlen = pst->pos - *tlen;

  /* we are finished if (*tlen == 0) */
  if (*tlen == 0) type=0;

  PG_RETURN_INT32(type);
}
Datum testprs_end(PG_FUNCTION_ARGS)
{
  ParserState *pst = (ParserState *) PG_GETARG_POINTER(0);
  pfree(pst);
  PG_RETURN_VOID();
}

Datum testprs_lextype(PG_FUNCTION_ARGS)
{
  /*
    Remarks:
    - we have to return the blanks for ts_headline reason
    - we use the same lexids like Teodor in the default
      word parser; in this way we can reuse the ts_headline
      function of the default word parser.
  */
  LexDescr *descr = (LexDescr *) palloc(sizeof(LexDescr) * (2+1));

  /* there are only two types in this parser */
  descr[0].lexid = 3;
  descr[0].alias = pstrdup("word");
  descr[0].descr = pstrdup("Word");
  descr[1].lexid = 12;
  descr[1].alias = pstrdup("blank");
  descr[1].descr = pstrdup("Space symbols");
  descr[2].lexid = 0;

  PG_RETURN_POINTER(descr);
}

Файл Makefile 

override CPPFLAGS := -I. $(CPPFLAGS)

MODULE_big = test_parser
OBJS = test_parser.o

DATA_built = test_parser.sql
DATA =
DOCS = README.test_parser
REGRESS = test_parser


ifdef USE_PGXS
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/test_parser
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

Файл test_parser.sql.in 

SET search_path = public;

BEGIN;

CREATE FUNCTION testprs_start(internal,int4)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict);

CREATE FUNCTION testprs_getlexeme(internal,internal,internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict);

CREATE FUNCTION testprs_end(internal)
RETURNS void
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict);

CREATE FUNCTION testprs_lextype(internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict);


CREATE TEXT SEARCH PARSER testparser 
        START     'testprs_start'
        GETTOKEN  'testprs_getlexeme'
        END       'testprs_end'
        LEXTYPES  'testprs_lextype'
;

CREATE TEXT SEARCH CONFIGURATION  testcfg  PARSER  'testparser' LOCALE  NULL;
CREATE TEXT SEARCH MAPPING ON testcfg FOR word WITH simple;

END;