12.2. Таблицы и индексы#
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;
Для ясности мы опустили вызовы функции coalesce,
которые нужны для поиска строк, содержащих NULL
в одном из двух полей.
Хотя эти запросы будут работать без индекса, большинство приложений найдут этот подход слишком медленным, за исключением, возможно, случайных поисков. Практическое использование текстового поиска обычно требует создания индекса.
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 не хранится явно.