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

2007 г

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

Олег Бартунов, Федор Сигаев

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

Мы приведем пример организации полнотекстового поиска, который каждый может повторить с версией 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,rank_cd(fts, q) from apod, 
to_tsquery('supernovae & x-ray') q 
where fts  @@ q order by rank_cd desc limit 5;
                     title                      | 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,rank_cd(fts, q) from apod,
to_tsquery('supernovae & x-ray') q
where fts  @@ q order by rank_cd desc limit 5;
                     title                      | 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

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

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

apod=# select title,rank_cd('{0.1,0.2,1.0,0.1}',fts, q) from apod,
to_tsquery('supernovae & x-ray') q
where fts  @@ q order by rank_cd desc limit 5;
                     title                      | 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)

Мы видим, как поменялись результаты. Отметим, что значения rank_cd не имеют особенного смысла, имеет значение только порядок. Однако, иногда хочется иметь нормированное значение и в таком случае можно использовать rank_cd/(rank_cd+1), например.

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

apod=# select headline(body,q,'StartSel=<,StopSel=>,MaxWords=10,MinWords=5'),
rank_cd(fts, q) from apod, to_tsquery('supernovae & x-ray') q 
where fts  @@ q order by rank_cd desc limit 5;
                               headline                               | 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: 39.525 ms

Здесь мы указали, что выделять найденные слова надо с помощью уголков и размер текста должен быть не меньше 5 слов, но не более 10. Мы видим, что время поиска значительно увеличилось ! Это связано не только с тем, что действительно функция headline не очень быстрая, но и с распространенной ошибкой в нашем запросе - мы вычисляем довольно медленную функцию headline для всех найденных документов, которых 36, а не для нужных 5 !

apod=# select count(*) from apod where  to_tsquery('supernovae & x-ray') @@ fts;
 count 
-------
    36

Правильный запрос использует subselect и мы получает тот же результат, но за гораздо меньшее время. Эффект может быть существенно выше, если общее количество найденных документов было бы не 36, а многие тысячи, например.

apod=# select headline(body,q, 'StartSel=<,StopSel=>,MaxWords=10,MinWords=5'),
rank from ( 
  select body,q, rank_cd(fts,q) as rank from apod, 
  to_tsquery('supernovae & x-ray') q where fts @@ q 
  order by rank desc limit 5
) as foo;
                               headline                               | 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: 6.700 ms

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

apod=# select title,rank_cd(fts, q) from apod, 
to_tsquery('supernovae & x-ray:b') q 
where fts  @@@ q order by rank_cd desc limit 5;
                     title                      | 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)

Обратите внимание, что мы использовали новый оператор "три собаки" @@@, вместо двух. Это связано с особенностью использования GIN индекса совместно с заданием весов в запросе. Если не использовать индекс или использовать GIST индекс, то можно воспользоваться привычными "двумя собаками" @@.

apod=# set enable_bitmapscan to off;
apod=# set enable_indexscan to off;
apod=# select title,rank_cd(fts, q) from apod, 
to_tsquery('supernovae & x-ray:b') q 
where fts  @@ q order by rank_cd desc limit 5;
                     title                      | 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 FULLTEXT CONFIGURATION public.pg  LOCALE  'ru_RU.UTF-8' 
       LIKE english WITH MAP;
ALTER  FULLTEXT CONFIGURATION public.pg SET AS DEFAULT;
CREATE FULLTEXT DICTIONARY pg_dict OPTION 'pg_dict.txt'  LIKE synonym;
CREATE FULLTEXT DICTIONARY en_ispell 
OPTION 'DictFile="english-utf8.dict",
        AffFile="english-utf8.aff",
        StopFile="english-utf8.stop"'
LIKE ispell_template;
ALTER FULLTEXT DICTIONARY en_stem SET OPTION 'english-utf8.stop';
ALTER FULLTEXT MAPPING ON pg FOR lword,lhword,lpart_hword
                             WITH pg_dict,en_ispell,en_stem;
DROP FULLTEXT 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]. Здесь

знак + используется для показа расширенной информации
{,d,p} - указывает информацию о каких объектах показывать. По умолчанию показывается информация о полнотекстовых конифгурациях.
PATTERN - необязательный параметр, задает имя объекта. Если имя не указано, то показывается информация о объекте, который представляет конфигурацию, парсер, словарь по умолчанию. 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 команды

CREATE FULLTEXT CONFIGURATION - создание полнотекстовой конфигурации

CREATE FULLTEXT CONFIGURATION cfgname 
PARSER  prsname  [ LOCALE localename] 
[AS DEFAULT];

CREATE FULLTEXT 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 FULLTEXT 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 FULLTEXT CONFIGURATION - удалить FTS конфигурацию

DROP FULLTEXT CONFIGURATION [IF EXISTS]cfgname [ CASCADE | RESTRICT ];

IF EXISTS указывать не выдавать ошибку, если удаляемая конфигурация не существует.
CASCADE - автоматически удалить все FTS объекты, зависящие от удаляемой FTS конфигурации.
RESTRICT - не удалять FTS конфигурацию, если есть какие-либо FTS объекты, зависящие от нее. Этот режим используется по умолчанию.

ALTER FULLTEXT CONFIGURATION - изменить FTS конфигурацию

ALTER FULLTEXT CONFIGURATION cfgname RENAME TO newcfgname;

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

ALTER FULLTEXT CONFIGURATION cfgname { SET AS | DROP } DEFAULT;

Эта команда позволяет изменить параметры, задаваемые при ее создании.

CREATE FULLTEXT DICTIONARY - создать словарь

CREATE FULLTEXT DICTIONARY dictname 
    LEXIZE  lexize_function
    [INIT  init_function ]
    [OPTION  opt_text ]
;
  

CREATE FULLTEXT 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 FULLTEXT 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 FULLTEXT DICTIONARY - удаляет словарь

DROP FULLTEXT DICTIONARY [IF EXISTS]dictname [ CASCADE | RESTRICT ];

ALTER FULLTEXT DICTIONARY - изменяет параметры словаря

ALTER FULLTEXT DICTIONARY dictname RENAME TO newdictname;

ALTER FULLTEXT DICTIONARY dictname SET OPTION opt_text;

CREATE FULLTEXT MAPPING - создать правила обработки токенов словарями

CREATE FULLTEXT 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 FULLTEXT CONFIGURATION testcfg LOCALE  'testlocale'  LIKE russian_utf8;
=# CREATE FULLTEXT 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 FULLTEXT MAPPING - изменить правило обработки токенов

ALTER FULLTEXT MAPPING ON cfgname FOR tokentypename[, ...] WITH dictname1[, ...];

ALTER FULLTEXT MAPPING ON cfgname [FOR tokentypename[, ...] ] 
REPLACE olddictname TO newdictname;

Позволяет добавлять новые правила обработки токенов или изменять старые. Изменим правило для токена типа lword, см. предыдущий пример.

=# ALTER FULLTEXT 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 FULLTEXT MAPPING - удалить правило обработки токена

DROP FULLTEXT MAPPING [IF EXISTS] ON cfgname FOR tokentypename;

CREATE FULLTEXT PARSER - создать FTS парсер

CREATE FULLTEXT PARSER prsname 
    START= start_function
    GETTOKEN  gettoken_function
    END  end_function
    LEXTYPES  lextypes_function
    [  HEADLINE  headline_function ]
;

prsname - имя создаваемого парсера, может содержать название схемы, в которой он будет создан.
start_function - название функции, которая инициализирует парсер.
gettoken_function - название функции, которая возвращает токен.
end_function - название функции, которая вызывается после окончания работы парсера.
lextypes_function - название функции, которая возвращает массив, содержащий {id,alias,full descr} - идентификатор, краткое название токена и полное описание. Подробнее, смотри в src/include/utils/ts_public.h.
headline_function, - название функции, которая возвращает часть документа, содержащая запрос.

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

DROP FULLTEXT PARSER - удалить FTS парсер

DROP FULLTEXT PARSER [IF EXISTS] prsname [ CASCADE | RESTRICT ];

ALTER FULLTEXT PARSER - изменить имя FTS парсера

ALTER FULLTEXT PARSER prsname RENAME TO newprsname;

ALTER FULLTEXT ... OWNER - изменить владельца

ALTER FULLTEXT { PARSER|DICTIONARY|CONFIGURATION } name OWNER TO newowner;

По умолчанию, владельцем FTS объекта является тот, кто создал его. Команда ALTER FULLTEXT ... OWNER позволяет менять владельца.

COMMENT ON FULLTEXT - создать или изменить комментарий FTS объекта

COMMENT ON FULLTEXT { CONFIGURATION | DICTIONARY | PARSER }  objname IS text;

Комментарий виден при использовании + в команде psql, например, \dFd+ - показать расширенную информацию о словарях.
Для того чтобы убрать комментарий, надо задать NULL для параметра text.

=# COMMENT ON FULLTEXT DICTIONARY intdict IS 'Dictionary for integers';

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

Работа над созданием полнотекстового поиска в PostgreSQL поддерживалась Российским Фондом Фундаментальных Исследований, 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.

Назад Оглавление Вперёд