LIKE, триграммы, полнотекстовый документ, ранжирование и качество выдачи
Фраза «нужен поиск» может означать разные задачи. Перед выбором индекса решите, что именно вводит человек и какие совпадения считает правильными.
LIKE 'post%' находит слова, начинающиеся с post.
При совместимых правилах сравнения его может ускорить B-tree.LIKE '%gres%' ищет фрагмент, а
сходство строк помогает находить близкое написание. Для этого подходит
расширение pg_trgm.Один индекс не обязан одинаково хорошо решать все три задачи.
Триграмма — последовательность из трёх соседних символов. Расширение
pg_trgm разбивает текст на такие части и умеет искать похожие строки и
фрагменты.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx
ON marketplace.products USING gin (name gin_trgm_ops);
SELECT id, name
FROM marketplace.products
WHERE name ILIKE '%keyboard%';GIN-индекс хранит триграммы названий. Он ускоряет чтение, но занимает место и удорожает вставки и изменения.
Очень короткий шаблон содержит мало триграмм, поэтому последовательное чтение
таблицы (Seq Scan) может оказаться дешевле. Решение планировщика проверяют на
данных, похожих на рабочие.
PostgreSQL использует два специальных типа:
tsvector — подготовленный набор лексем документа;tsquery — поисковое условие по этим лексемам.Лексема — нормализованная единица слова. Конкретная нормализация зависит от выбранной конфигурации языка.
Вместо повторного разбора текста при каждом чтении сохраним подготовленный документ в вычисляемом столбце:
ALTER TABLE marketplace.products
ADD COLUMN search_document tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
setweight(to_tsvector('simple', coalesce(description, '')), 'B')
) STORED;
CREATE INDEX products_search_document_idx
ON marketplace.products USING gin (search_document);setweight задаёт вес: совпадение в названии с весом A можно считать важнее
совпадения в описании с весом B. Конфигурация simple не применяет языковой
словарь; для русского поиска нужно отдельно проверить подходящую конфигурацию и
качество разбора.
websearch_to_tsquery принимает привычную пользовательскую строку и безопаснее
для сырого ввода, чем строгий to_tsquery:
WITH q AS (
SELECT websearch_to_tsquery('simple', :query) AS query
)
SELECT p.id, p.name, ts_rank_cd(p.search_document, q.query) AS rank
FROM marketplace.products AS p
CROSS JOIN q
WHERE p.search_document @@ q.query
ORDER BY rank DESC, p.id
LIMIT 20;Оператор @@ проверяет совпадение документа и запроса. ts_rank_cd вычисляет
оценку соответствия. Второй столбец сортировки p.id делает выдачу стабильной,
если оценки равны.
Релевантность нельзя оценить по одному красивому примеру. Соберите список реальных запросов и заранее отметьте ожидаемые результаты, затем сравнивайте варианты настройки на этом наборе.
PostgreSQL хорошо подходит для поиска внутри приложения с умеренным объёмом, если данные должны изменяться в одной транзакции с остальными таблицами.
Отдельная поисковая система может понадобиться для сложной лингвистики, распределённого индекса, исправления опечаток, большого числа фильтров и независимого масштабирования. Переход оправдывают измеримые требования к скорости и качеству, а не само слово «поиск».
В лаборатории 14 вы сравните поиск по началу, фрагменту и лексемам на одном наборе данных, а также планы до и после индексов.
Подробнее: полнотекстовый поиск, расширение pg_trgm.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.
Далее: Представления: запрос или сохранённый результат