Пошук уроків, статей та іншого контенту
Налаштуєте пошук за текстом через tsvector і tsquery, словники, ранжування та відповідні індекси.
Повнотекстовий пошук призначений для пошуку слів і фраз у великих текстових полях. На відміну від LIKE, він:
розбирає текст на окремі лексеми;
може приводити слова до нормальної форми;
ігнорує стоп-слова;
підтримує логічні умови та пошук фраз;
уміє ранжувати результати за релевантністю;
ефективно працює з індексами.
У PostgreSQL для цього використовуються два основні типи:
tsvector — підготовлений пошуковий документ;
tsquery — пошуковий запит.
Оператор @@ перевіряє, чи відповідає tsvector заданому tsquery.
SELECT
to_tsvector('simple', 'PostgreSQL підтримує повнотекстовий пошук')
@@
to_tsquery('simple', 'пошук') AS matches;Результат:
matches
---------
ttsvectortsvector містить лексеми тексту, зазвичай у нормалізованому вигляді. Позиції слів також можуть зберігатися для пошуку фраз і ранжування.
SELECT to_tsvector(
'simple',
'PostgreSQL підтримує повнотекстовий пошук'
);Приклад результату може виглядати так:
'підтримує':2 'повнотекстовий':3 'пошук':4 'postgresql':1Числа після лексем — це позиції слів у початковому тексті.
Для англійської конфігурації слова можуть бути приведені до спільної основи:
SELECT to_tsvector(
'english',
'PostgreSQL supports searching, searched documents and search indexes'
);Конкретний результат залежить від словника, але слова на кшталт searching, searched і search можуть бути нормалізовані до однієї лексеми.
Перший аргумент to_tsvector — це конфігурація повнотекстового пошуку. Вона визначає:
правила розбору тексту;
словники;
нормалізацію слів;
список стоп-слів.
Переглянути доступні конфігурації можна так:
SELECT cfgname
FROM pg_ts_config
ORDER BY cfgname;Для тексту українською часто використовують конфігурацію simple, якщо потрібне точне зіставлення слів без спеціального стемінгу:
SELECT to_tsvector('simple', 'розробник розробники розробкою');Конфігурацію потрібно вибирати відповідно до мови та вимог пошуку. Важливо використовувати одну й ту саму конфігурацію під час створення tsvector, побудови tsquery та створення індексу.
tsquerytsquery описує умови пошуку. Його можна створити кількома функціями.
to_tsqueryto_tsquery підтримує спеціальний синтаксис повнотекстового пошуку:
SELECT to_tsquery('simple', 'postgresql & пошук');Основні оператори:
& — логічне «і»;
| — логічне «або»;
! — заперечення;
<-> — слова розташовані поруч;
:* — префіксний пошук.
Наприклад:
SELECT to_tsquery('simple', 'postgresql & (пошук | індекс)');Запит відповідає тексту, у якому є postgresql і хоча б одне зі слів — пошук або індекс.
Префіксний пошук:
SELECT to_tsquery('simple', 'програм:*');Такий запит може відповідати лексемам, які починаються з програм.
to_tsquery очікує правильний синтаксис. Якщо передати в нього довільний текст користувача, запит може завершитися помилкою.
plainto_tsqueryplainto_tsquery приймає звичайний текст і сам перетворює слова на умову з оператором AND:
SELECT plainto_tsquery(
'simple',
'PostgreSQL повнотекстовий пошук'
);Це зручно, коли користувач вводить звичайний пошуковий рядок і не повинен керувати синтаксисом tsquery.
websearch_to_tsquerywebsearch_to_tsquery підтримує звичний для пошукових систем формат:
слова без операторів поєднуються через AND;
фрази можна взяти в подвійні лапки;
OR означає логічне «або»;
мінус перед словом означає заперечення.
SELECT websearch_to_tsquery(
'simple',
'"повнотекстовий пошук" OR індекс -помилка'
);Для пошуку за текстом, який надходить від користувача, зазвичай краще використовувати plainto_tsquery або websearch_to_tsquery, а не передавати введення безпосередньо в to_tsquery.
@@Оператор @@ перевіряє відповідність документа пошуковому запиту:
SELECT
to_tsvector('simple', 'PostgreSQL підтримує повнотекстовий пошук')
@@
plainto_tsquery('simple', 'повнотекстовий пошук') AS matches;У запитах до таблиці ліворуч зазвичай розташований стовпець типу tsvector, а праворуч — tsquery.
Створимо таблицю статей. Пошуковий вектор зберігатиметься в окремому стовпці.
DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector(
'simple'::regconfig,
coalesce(title, '') || ' ' || coalesce(body, '')
)
) STORED
);
INSERT INTO articles (title, body)
VALUES
(
'Повнотекстовий пошук у PostgreSQL',
'PostgreSQL використовує tsvector і tsquery для пошуку за текстом.'
),
(
'Індекси бази даних',
'GIN індекс прискорює пошук за підготовленим текстовим документом.'
),
(
'Робота з транзакціями',
'Транзакції гарантують цілісність даних у базі.'
);
SELECT id, title, search_vector
FROM articles;Пошук статей, які містять слово пошук:
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук');Пошук за кількома словами:
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery(
'simple',
'PostgreSQL текст'
);У цьому випадку обидві лексеми повинні бути присутніми в документі.
Пошук із логічними операторами:
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery(
'simple',
'пошук | індекс'
);Пошук фрази:
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery(
'simple',
'пошук <-> за'
);Оператор <-> перевіряє, чи розташовані лексеми поруч у заданому порядку. Для точного пошуку фрази в реальних текстах результат залежить від того, як конфігурація обробляє стоп-слова та позиції.
Заголовок зазвичай важливіший за основний текст. PostgreSQL дозволяє призначати частинам документа ваги:
A;
B;
C;
D.
Вага A має найвищий пріоритет.
Для цього потрібно окремо обробити заголовок і тіло за допомогою setweight:
SELECT
setweight(
to_tsvector('simple', 'Повнотекстовий пошук у PostgreSQL'),
'A'
)
||
setweight(
to_tsvector('simple', 'Пошук можна прискорити за допомогою GIN індексу.'),
'B'
) AS document;Оператор || об’єднує два tsvector.
Якщо ваги потрібні в таблиці, стовпець можна визначити так:
DROP TABLE IF EXISTS weighted_articles;
CREATE TABLE weighted_articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
search_vector tsvector
GENERATED ALWAYS AS (
setweight(
to_tsvector('simple'::regconfig, coalesce(title, '')),
'A'
)
||
setweight(
to_tsvector('simple'::regconfig, coalesce(body, '')),
'B'
)
) STORED
);Ваги A, B, C, D не означають абсолютні оцінки. Вони лише впливають на обчислення релевантності.
Сам оператор @@ повертає тільки true або false. Щоб розташувати результати від найбільш релевантних до найменш релевантних, використовують функції ранжування.
ts_rankts_rank оцінює відповідність документа запиту, враховуючи частоту та ваги лексем.
SELECT
id,
title,
ts_rank(
search_vector,
plainto_tsquery('simple', 'пошук')
) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук')
ORDER BY rank DESC;Щоб не створювати tsquery кілька разів, його можна винести в CTE:
WITH query AS (
SELECT plainto_tsquery('simple', 'пошук PostgreSQL') AS search_query
)
SELECT
a.id,
a.title,
ts_rank(a.search_vector, query.search_query) AS rank
FROM articles AS a
CROSS JOIN query
WHERE a.search_vector @@ query.search_query
ORDER BY rank DESC, a.id;ts_rank_cdts_rank_cd — варіант ранжування, який враховує близькість термінів у документі. Він може бути корисним, коли документи з лексемами, розташованими ближче одна до одної, повинні мати вищий рейтинг.
SELECT
id,
title,
ts_rank_cd(
search_vector,
plainto_tsquery('simple', 'пошук PostgreSQL')
) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук PostgreSQL')
ORDER BY rank DESC;Для повнотекстового пошуку зазвичай спочатку застосовують умову @@, а потім сортують лише знайдені рядки за рейтингом.
Без індексу PostgreSQL може перевіряти кожен рядок таблиці. Для великих таблиць це повільно.
Найчастіше для tsvector використовують індекс GIN:
CREATE INDEX articles_search_vector_gin_idx
ON articles
USING GIN (search_vector);Після цього запит такого виду може використовувати індекс:
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук');GIN добре підходить для пошуку за складеними документами, оскільки індекс зберігає відповідність між лексемами та рядками таблиці.
Також PostgreSQL підтримує GiST:
CREATE INDEX articles_search_vector_gist_idx
ON articles
USING GIST (search_vector);Для типових задач повнотекстового пошуку часто починають із GIN. Вибір індексу потрібно перевіряти на реальних даних та запитах.
Якщо окремого стовпця tsvector немає, можна створити індекс на виразі:
CREATE INDEX articles_text_search_gin_idx
ON articles
USING GIN (
to_tsvector(
'simple'::regconfig,
coalesce(title, '') || ' ' || coalesce(body, '')
)
);Умова запиту повинна використовувати такий самий вираз:
SELECT id, title
FROM articles
WHERE to_tsvector(
'simple'::regconfig,
coalesce(title, '') || ' ' || coalesce(body, '')
) @@ plainto_tsquery('simple', 'пошук');Якщо вираз в індексі та вираз у запиті відрізняються, PostgreSQL може не використати цей індекс.
Для аналізу плану виконання використовуйте EXPLAIN:
EXPLAIN
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук');На маленькій таблиці PostgreSQL може вибрати послідовне сканування навіть за наявності індексу. Це нормально: для кількох рядків послідовне читання може бути дешевшим.
Перевіряти індекс надійніше на таблиці з реальним обсягом даних і за допомогою:
EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'пошук');Повнотекстовий пошук PostgreSQL обробляє текст у кілька етапів:
парсер розділяє текст на токени;
конфігурація визначає тип кожного токена;
словники перетворюють слова на лексеми;
стоп-слова можуть бути відкинуті;
з лексем формується tsvector або tsquery.
Перевірити, як конфігурація розбирає конкретний текст, можна через ts_debug:
SELECT *
FROM ts_debug(
'english',
'PostgreSQL supports full-text searching'
);Результат покаже тип токена, словник і лексему, яку було отримано після обробки.
Словник може:
відкидати стоп-слова;
приводити слова до основи;
розпізнавати синоніми;
працювати з тезаурусом;
повертати кілька варіантів лексем.
Конфігурацію пошуку слід обирати однаково для індексу та запиту. Наприклад, якщо search_vector створено з конфігурацією english, пошуковий запит також має бути побудований з english:
WHERE search_vector @@ plainto_tsquery('english', $1)Зміна конфігурації може змінити лексеми, тому після зміни правил обробки тексту індекс або збережені пошукові вектори потрібно перебудувати.
У прикладному коді пошуковий текст потрібно передавати як параметр запиту, а не конкатенувати зі SQL-рядком.
SELECT
id,
title,
ts_rank(search_vector, query.search_query) AS rank
FROM articles
CROSS JOIN LATERAL (
SELECT plainto_tsquery('simple', $1) AS search_query
) AS query
WHERE search_vector @@ query.search_query
ORDER BY rank DESC, id;Параметр $1 містить звичайний текст, введений користувачем. plainto_tsquery перетворює його на безпечний пошуковий запит без необхідності вручну екранувати оператори tsquery.
LIKE замість повнотекстового пошукуЗапит:
WHERE body ILIKE '%пошук%'може знайти підрядок, але не розуміє лексем, стоп-слів, словоформ і релевантності. Для повнотекстового пошуку використовуйте tsvector, tsquery та оператор @@.
Ці вирази можуть працювати по-різному:
to_tsvector('english', body)
plainto_tsquery('simple', 'search')Одна й та сама конфігурація повинна використовуватися на обох етапах.
to_tsqueryto_tsquery очікує спеціальний синтаксис. Рядок користувача може містити символи, які спричинять помилку або матимуть несподіване значення.
Для звичайного пошуку використовуйте:
plainto_tsquery('simple', $1)або:
websearch_to_tsquery('simple', $1)Побудова tsvector під час кожного запиту без індексу може бути повільною на великих таблицях. Для підготовленого стовпця зазвичай створюють індекс:
CREATE INDEX articles_search_vector_gin_idx
ON articles USING GIN (search_vector);@@ts_rank має сенс для документів, які справді відповідають запиту. Тому зазвичай потрібно поєднувати ранжування з умовою:
WHERE search_vector @@ search_query
ORDER BY ts_rank(search_vector, search_query) DESCtsvector — підготовлений для пошуку текстовий документ.
tsquery — умова, за якою виконується пошук.
Оператор @@ перевіряє відповідність документа запиту.
to_tsquery підтримує оператори &, |, !, <-> і префіксний пошук.
plainto_tsquery та websearch_to_tsquery зручніші для введення користувача.
Конфігурація визначає парсер, словники, стоп-слова та нормалізацію.
setweight дозволяє зробити заголовок важливішим за основний текст.
ts_rank і ts_rank_cd використовують для сортування за релевантністю.
Для tsvector найчастіше створюють індекс GIN.
Конфігурація під час створення індексу, tsvector і tsquery має бути узгодженою.