Астрономический поиск
Мы приведем пример организации полнотекстового поиска, который каждый может повторить с версией 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)
