12.2. Таблицы и индексы #
В предыдущем разделе приводились примеры, которые показывали, как можно выполнить сопоставление с простыми текстовыми константами. В этом разделе показывается, как находить текст в таблице, возможно с применением индексов.
12.2.1. Поиск в таблице #
Полнотекстовый поиск можно выполнить, не применяя индекс. Следующий простой запрос выводит заголовок (title
) каждой строки, содержащей слово friend
в поле body
:
SELECT title FROM pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
При этом также будут найдены связанные слова, такие как friends
и friendly
, так как все они сводятся к одной нормализованной лексеме.
В показанном выше примере для разбора и нормализации строки явно выбирается конфигурация english
. Хотя параметры, задающие конфигурацию, можно опустить:
SELECT title FROM pgweb WHERE to_tsvector(body) @@ to_tsquery('friend');
Такой запрос будет использовать конфигурацию, заданную в параметре default_text_search_config.
В следующем более сложном примере выбираются десять документов, изменённых последними, со словами create
и table
в полях title
или body
:
SELECT title FROM pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10;
Чтобы найти строки, содержащие NULL
в одном из полей, нужно воспользоваться функцией coalesce
, но здесь мы опустили её вызовы для краткости.
Хотя такие запросы будут работать и без индекса, для большинства приложений скорость будет неприемлемой; этот подход рекомендуется только для нерегулярного поиска и динамического содержимого. Для практического применения полнотекстового поиска обычно создаются индексы.
12.2.2. Создание индексов #
Для ускорения текстового поиска мы можем создать индекс GIN (см. Раздел 12.9):
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));
Заметьте, что здесь используется функция to_tsvector
с двумя аргументами. В выражениях, определяющих индексы, можно использовать только функции, в которых явно задаётся имя конфигурации текстового поиска (см. Раздел 11.7). Это объясняется тем, что содержимое индекса не должно зависеть от значения параметра default_text_search_config. В противном случае содержимое индекса может быть неактуальным, если разные его элементы tsvector
будут создаваться с разными конфигурациями текстового поиска и нельзя будет понять, какую именно использовать. Выгрузить и восстановить такой индекс будет невозможно.
Так как при создании индекса использовалась версия to_tsvector
с двумя аргументами, этот индекс будет использоваться только в запросах, где to_tsvector
вызывается с двумя аргументами и во втором передаётся имя той же конфигурации. То есть, WHERE to_tsvector('english', body) @@ 'a & b'
сможет использовать этот индекс, а WHERE to_tsvector(body) @@ 'a & b'
— нет. Это гарантирует, что индекс будет использоваться только с той конфигурацией, с которой создавались его элементы.
Индекс можно создать более сложным образом, определив для него имя конфигурации в другом столбце таблицы, например:
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector(config_name, body));
где config_name
— имя столбца в таблице pgweb
. Так можно сохранить имя конфигурации, связанной с элементом индекса, и, таким образом, иметь в одном индексе элементы с разными конфигурациями. Это может быть полезно, например, когда в коллекции документов хранятся документы на разных языках. И в этом случае в запросах должен использоваться тот же индекс (с таким же образом задаваемой конфигурацией), например, так: WHERE to_tsvector(config_name, body) @@ 'a & b'
.
Индексы могут создаваться даже по объединению столбцов:
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));
Ещё один вариант — создать отдельный столбец tsvector
, в котором сохранить результат to_tsvector
. Чтобы этот столбец автоматически синхронизировался с исходными данными, он создаётся как сохранённый генерируемый столбец. Следующий пример показывает, как можно подготовить для индексации объединённое содержимое столбцов title
и body
, применив функцию coalesce
для получения желаемого результата, даже когда один из столбцов NULL
:
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
Затем мы создаём индекс GIN для ускорения поиска:
CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);
Теперь мы можем быстро выполнять полнотекстовый поиск:
SELECT title FROM pgweb WHERE textsearchable_index_col @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10;
Хранение вычисленного выражения индекса в отдельном столбце даёт ряд преимуществ. Во-первых, для использования индекса в запросах не нужно явно указывать имя конфигурации текстового поиска. Как показано в вышеприведённом примере, в этом случае запрос может зависеть от default_text_search_config
. Во-вторых, поиск выполняется быстрее, так как для проверки соответствия данных индексу не нужно повторно выполнять to_tsvector
. (Это актуально больше для индексов GiST, чем для GIN; см. Раздел 12.9.) С другой стороны, схему с индексом по выражению проще реализовать и она позволяет сэкономить место на диске, так как представление tsvector
не хранится явно.