12.2. Таблицы и индексы#

12.2. Таблицы и индексы

12.2. Таблицы и индексы #

Примеры в предыдущем разделе иллюстрировали полное сопоставление текста с использованием простых постоянных строк. В этом разделе показано, как искать данные в таблице, при необходимости используя индексы.

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 не хранится явно.