Postgres - статьи

Примеры использования GiST


Для того, чтобы использовать модуль в вашей БД, необходимо установить модуль, загрузить расширение в вашу БД. Установка модуля обычно заключается в последовательности команд (на примере tsearch2):

cd contrib/tsearch2 make && make install && make installcheck

Если все прошло нормально (все тесты прошли), то можно загрузить расширение в вашу БД, например: psql foodb < /usr/local/pgsql/share/contrib/tsearch2.sql

После этого вы можете использовать новые типы данных, предоставляемые модулем, операции, указанные в CREATE OPERATOR CLASS и функции. Например, для модуля tsearch2: create table fts ( id integer, title text, body text, ftsindex tsvector); create index fts_idx on fts using gist(ftsindex);

Здесь колонка ftsindex в таблице fts имеет тип tsvector, который и был предоставлен модулем tsearch2. Обратите внимание на указание метода (gist), который используется при построении индекса. Иногда, можно указать параметр opclass для метода, если требуется использовать оператор отличный от умолчания для данного типа колонки. Например, для модуля contrib/intarray можно указать opclass gist__intbig_ops для эффективной работы с большими массивами, в то время как по умолчанию используется gist__int_ops, достаточный для работы с небольшими массивами. Разница между gist__intbig_ops и gist__int_ops заключается в том, что первый opclass использует специальное представление массива битовой сигнатурой ( superimposed signature ) длиной 4096 бит и поэтому индекс является "lossy", в то время как во втором случае индекс является точным и не требует проверки найденных записей на соответствие запросу. -- default opclass, could be omitted CREATE INDEX message_rdtree_idx on message using gist ( sections gist__int_ops); -- opclass for large arrays CREATE INDEX message_rdtree_idx on message using gist ( sections gist__intbig_ops);

Более подробно см. документацию CREATE INDEX

Используя GiST, авторами были разработаны ряд популярных расширений, которые входят в дистрибутив PostgreSQL. Все модули реализуют типы данных, оптимизированных под конкретную задачу, хранилище, индексный доступ к нему и специализированные запросы. Ниже приводится очень краткий обзор использования этих расширений. Формальное описание содержится в документации к модулю, а примеры использования можно найти в архивах списков рассылок PostgreSQL и c помощью поисковой машины www.pgsql.ru.


tsearch2 - реализация полнотекстового поиска
Этот модуль предназначен для организации полнотекстового поиска в БД. Его отличительной особенностью является online-индекс и полная интеграция с БД, что дает возможность проводить полнотекстовый поиск с ограничениями по атрибутам. Например, искать по документам, в зависимости от уровня доступа клиента и дате публикации. Tsearch2 поддерживает использование словарей, предоставляет API для их создания. Поддержка словарей популярных форматов ispell (для приведения слов к их нормальной форме) и стемминга на основе snowball позволяет использовать tsearch2 со многими языками. Гибкость настройки tsearch2, конфигурация которого хранится в базе данных и доступна с помощью стандартных команд SQL, позволяет разрабатывать различные поиски ориентированные на разные задачи. Модуль предоставляет два вида ранжирующих функций, использующие координатную информацию, и которые можно использовать для сортировки результатов поиска по их релевантности запросу.
С модулем tsearch2 полнотекстовый поиск становится простой и рутинной задачей. Пример поиска документов, которые содержат слова 'собака', 'на', 'сене':
SELECT mid, title from messages where ftsindex @@ to_tsquery('собака & на & сене');
Аналогично, но ищутся 10 самых релевантных запросу документов: SELECT mid, title, rank(ftsindex,to_tsquery('собака & на & сене')) as rank from messages where ftsindex @@ to_tsquery('собака & на & сене') ORDER BY rank DESC LIMIT 10;
Модуль поддерживает структурность документа, т.е. словам из разных частей документа (всего можно использовать 4 части) можно задавать разные веса. Так, например, вес слова, входящего в название документа, можно увеличить, по сравнению с другими частями. Также, можно ограничивать поиск по различным частям документов, используя один и тот же индекс. В примере ниже, поле ftsindex включает поле title и тело документа.
UPDATE messages SET ftsindex=setweight( to_tsvector(title), 'A' ) to_tsvector(body);




Можно поискать только по названиям документов: SELECT mid, title FROM messages WHERE ftsindex @@ to_tsquery('собака:a & на & сене');
Для визуализации результатов поиска модуль предоставляет функцию headline, которая выдает релевантные части документа с подсветкой слов из запроса.
SELECT mid, headline(body, to_tsquery('собака & на & сене')), rank(fts_index,to_tsquery('собака & на & сене')) AS rank FROM messages WHERE ftsindex @@ to_tsquery('собака & на & сене') ORDER BY rank DESC LIMIT 10;
Отметим, что в этом запросе, функция headline вызывается для каждого найденного документа, что может существенно влиять на время исполнения запроса. Это связано с тем как в PostgreSQL реализован LIMIT. Оптимизированный запрос с использованием подзапроса (subselect) выглядит следующим образом: SELECT mid, headline(body, to_tsquery('собака & на & сене')) FROM (SELECT mid, body, rank(fts_index,to_tsquery('собака & на & сене')) AS rank FROM messages WHERE ftsindex @@ to_tsquery('собака & на & сене') ORDER BY rank DESC LIMIT 10) AS foo;
Здесь функция headline вызывается только нужное (максимум 10) количество раз.
ltree - поддержка данных с древовидной структурой
Стандартный способ работы с иерархическими данными, например, с каталогами, заключается в использовании таблиц связей (parent_id,child_id), что приводит ,так или иначе, к необходимости использования рекурсивных запросов. Идея модуля ltree состоит в том, чтобы хранить иерархию связей в специальном типе данных ltree и предоставлять индексную
поддержку для основных операций. Например, для данных изображенных на рисунке
TOP / | \ Science Hobbies Collections / | \ Astronomy Amateurs_Astronomy Pictures / \ | Astrophysics Cosmology Astronomy / | \ Galaxies Stars Astronauts
запрос на поиска всех потомков, например, узла 'Top.Science' выглядит: SELECT path FROM test WHERE path <@ 'Top.Science'; path ------------------------------------ Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology


Кроме работы со связями, ltree предоставляет возможность поиска с использованием регулярных выражений и модификаторов. Например, запрос Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain a) b) c) d) e)
означает, что узел должен:

  • a) - начинаться с узла с меткой 'Top'
  • b) - дальше могут идти вплоть до 2-х узлов с произвольной меткой
  • c) - после чего идет узел с именем начинающимся на 'sport' (маленькие и большие буквы не различаются)
  • d) - далее идет узел, имя которого не должно содержать 'footbal' или 'tennis'
  • e) - и кончаться на узел, начинающийся 'Russ' или 'Spain' (маленькие и большие буквы отличаются)

Пример: SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology
Также, можно использовать поиск по названиям узлов, например, найти все узлы, которые содержать слово 'Europe', слово, начинающееся с 'Russia' (case insensitive), и не содержащее слово 'Transportation': 'Europe & Russia*@ & !Transportation'
Пример: SELECT path FROM test WHERE path @ 'Astro*% & !pictures@'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Hobbies.Amateurs_Astronomy
Удобство использования этого модуля и большое количество полезных функций делает ltree очень полезным для решения типичных портальных задач.
intarray - индексная поддержка целочисленных массивов
Этот модуль часто используется в тех случаях, когда требуется денормализовать БД для повышения производительности. Например, типичная задача поиска документов из нескольких разделов. Классическая нормализованная схема предусматривает использование трех таблиц - messages, sections и message_section_map. Документ может принадлежать нескольким секциям, так что таблица message_section_map содержит связи многие-ко-многим. При этом, поиск всех документов из секций 1,2 будет содержать связку (join) двух таблиц messages и message_section_map, что влияет на производительность и в некоторых случаях просто неприемлемо. Денормализация приводит к тому, что в таблицу messages добавляется поле sections которое является массивом целых чисел - идентификаторов секций, к которым принадлежит данный документ. Однако, несмотря на то, что теперь не требуется вторая таблица, поиск будет все равно медленным из-за того, что операция поиск в массиве не использует индекс. Наш модуль intarray как раз и решает эту проблему - он обеспечивает индексную поддержку для операций над целочисленными массивами.
CREATE TABLE message (mid int not null,sections int[]); -- select some messages with section in 1 OR 2 - OVERLAP operator SELECT message.mid FROM messages WHERE messages.sections && '{1,2}';


-- select messages contains in sections 1 AND 2 - CONTAINS operator SELECT message.mid FROM messages WHERE messages.sections @ '{1,2}'; -- the same, CONTAINED operator SELECT message.mid FROM messages WHERE '{1,2}' ~ messages.sections;
Другой интересный пример использования массивов - это реализация генеалогического подхода для работы с древовидной структурой, т.е. для каждого узла хранить путь от него до корня дерева (пример сообщил Achilleus Mantzios). CREATE TABLE tree( id integer PRIMARY KEY, name text not null, parents integer[] ) CREATE INDEX tree_parents on tree using gist (parents gist__int_ops); INSERT INTO tree VALUES (1,'root',null); INSERT INTO tree VALUES (2,'kid1',intset(1)); INSERT INTO tree VALUES (3,'kid2',intset(1)); INSERT INTO tree VALUES (4,'kid1.1',intset(2)+'{1}'::int4[]); INSERT INTO tree VALUES (5,'kid1.2',intset(2)+'{1}'::int4[]);
Здесь функция intset преобразует integer в элемент массива, а оператор '+' соединяет два массива. Теперь мы имеем дерево следующего вида: (1,root,null) / \ / \ / \ (2,kid1,'{1}') (3,kid2,'{1}') / \ / \ / \ (4,kid1.1,'{2,1}') (5,kid1.2,'{2,1}')
Теперь мы можем найти прямых потомков узла id=1 (root) SELECT * FROM tree WHERE intset(1) ~ parents and icount(parents)=1;
Функция icount дает количество элементов в массиве или "глубину" узла в нашем примере. Чтобы найти всех потомков узла id: SELECT * FROM tree WHERE intset() ~ parents;
pg_trgm - поиск похожих строк на основе триграм
Этот модель не только позволяет быстро находить похожие строки, но еще и не зависит от языка, так как использует только статистику используемых триграмм. Триграмма - это последовательность из трех соседних букв. Например, слово 'собака' содержит триграммы 'соб', 'оба', 'бак', 'ака'. Используя pg_trgm, можно найти все слова, упорядоченные по похожести слову 'собака': CREATE INDEX trgm_idx ON foo USING gist (word gist_trgm_ops);
SELECT word, similarity(word, 'собака') AS sml FROM foo WHERE word % 'собака' ORDER BY sml DESC, word;


При этом, будет использоваться индекс trgm_idx, построенный по полю word, что обеспечивает хорошую производительность даже для большого количества слов.
Этот модуль можно использовать вместе с tsearch2 для полнотекстового поиска с коррекцией ошибок ввода.
rtree_gist - реализация R-tree с использованием GiST
Этот модуль позволяет эффективно работать с данными с пространственными атрибутами. Начиная с 8.1 этот модуль интегрирован в ядро PostgreSQL.
btree_gist - реализация B-tree с использованием GiST
Модуль поддерживает практические все основные типы данных, используемые в PostgreSQL и самостоятельной ценности не имеет, так как встроенный btree гораздо лучше. btree_gist применяется для создания композитных индексов, так как PostgreSQL не поддерживает композитные индексы, созданные с разными AM, например, gist и btree. Типичным примером использования является создание индекса по (ftsindex, ts), где ftsindex - колонка типа tsvector, а ts - timestamp. Такой индекс можно использовать не только для обычного полнотекстового поиска, но и для его ускорения поиска в определенном временном интервале.
CREATE INDEX fts_ts_idx ON foo USING gist(ftsindex,ts);
Здесь, по полю ts будет автоматически использоваться методы модуля btree_gist, а не btree.
gevel - набор функций для изучения GiST индекса
Этот модуль предназначен в первую очередь для разработчиков расширений с использованием GiST. Мы будем использовать модуль rtree_gist и данные, которые использовались для получения этой . в виде:
create table cities (id int4, b box); \copy cities from 'cities_mbr.copy' with delimiter as '|' rtree=# \d bix Index "public.bix" Column | Type --------+------ b | box gist, for table "public.cities"
Показать статистику об индексе: rtree=# select gist_stat('bix'); Number of levels: 2 Number of pages: 64 Number of leaf pages: 63 Number of tuples: 6782 Number of leaf tuples: 6719 Total size of tuples: 298408 bytes Total size of leaf tuples: 295636 bytes Total size of index: 524288 bytes


Вывести информацию о дереве, вплоть до уровня MAXLEVEL - gist_tree(INDEXNAME,MAXLEVEL) regression=# select gist_tree('pix',0); 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)
Здесь (слева направо):

  • 0 - page number
  • (l:0) - tree level
  • blk: 0 - block number
  • numTuple: 29 - the number of tuples
  • free: 6888b - free space in bytes
  • (15.63%) - occupied space in percents

Для визуализации дерева (смотри ) можно использовать функцию gist_print(INDEXNAME). Например, для визуализации разбиения на уровне 1, мы направляем вывод в файл: \pset tuples_only \o cities-l-1.leaf -- для версии PostgreSQL < 8.1 SELECT * FROM gist_print('bix') AS t(level int, a box) WHERE level = 1; -- для версии PostgreSQL начиная с 8.1 SELECT * FROM gist_print('bix') AS t(level int, valid bool, a box) WHERE level =1;
Обратите внимание на разницу в запросах ! Аналогично, можно получить данные для концевых узлов (уровень 2). Полученные данные использовались для получения .
Внимание: Функция gist_print(INDEXNAME) можно использовать только для объектов в индексе, которые имеют текстовое представление. Для этого необходимо написать функцию type_out для рассматриваемого типа объекта, например, tsvector_out для полнотекстового типа tsvector из модуля tsearch2. Функция box_out определена в ./backend/utils/adt/geo_ops.c
и для полноты приведем ее здесь:
/* box_out - convert a box to external form */ Datum box_out(PG_FUNCTION_ARGS) { BOX *box = PG_GETARG_BOX_P(0); PG_RETURN_CSTRING(path_encode(-1, 2, &(box->high))); }
Полный список расширений и документацию можно прочитать на странице разработчиков GiST.

Содержание раздела