Привет, Хабр!

PostgreSQL, одна из самых мощных и гибких реляционных СУБД, предлагает нам свой модуль pg_trgm, чтобы решить сложную задачу полнотекстового поиска.

Когда речь идет о поиске, просто LIKE запросы больше не всегда могут удовлетворить технические требования. Полнотекстовый поиск подразумевает не только поиск точных соответствий, но и учет схожести слов, учет морфологии, а также поддержку более сложных запросов. PostgreSQL, конечно, предоставляет средства для выполнения таких задач, и модуль pg_trgm - один из инструментов, с помощью которого это можно сделать.

Итак, что такое pg_trgm? Этот модуль PostgreSQL предоставляет набор функций и операторов, которые позволяют работать с трехграммами (триграммами) - это последовательности из трех символов. Для понимания, давайте взглянем на пример:

SELECT show_trgm('example');

Результатом будет:

{"  e", " ex", " exa", "amp", "e  ", "e x", "exa", "ple", "xam"}

Текст "example" разбивается на трехграммы, и это дает нам основу для сравнения с другими строками. Какие плюсы присутствуют в таком методе? Во-первых, это позволяет находить похожие строки, даже если они не совпадают буквенно. Это полезно, например, при исправлении опечаток или при поиске синонимов. Трехграммы также учитывают порядок символов и поддерживают операторы для сравнения, что делает их мощным инструментом для полнотекстового поиска.

Основы полнотекстового поиска в PostgreSQL

Полнотекстовый поиск предоставляет собой удобный и эффективный способ поиска информации. PostgreSQL предоставляет набор инструментов для реализации полнотекстового поиска, и понимание его основ - это важное знание для все разрабов, а так же аналитиков.

1. Текстовый тип данных в PostgreSQL

Прежде чем начать работу с полнотекстовым поиском, необходимо понимать, как PostgreSQL хранит и обрабатывает текст. В PostgreSQL есть несколько типов данных для хранения текста, но основными из них являются TEXT и VARCHAR. Например, для создания таблицы с полем для текста, мы можем использовать следующий SQL-запрос:

CREATE TABLE articles (
    id serial PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

В данном примере title и content - это поля для хранения текста, которые будут использоваться в поиске. Различные типы данных могут быть более или менее подходящими в зависимости от требований к проекту и производительности.

2. Оператор LIKE

Одним из способов выполнения поиска в текстовых полях в PostgreSQL является использование оператора LIKE. Этот оператор позволяет находить строки, которые содержат определенную подстроку. Например, чтобы найти все статьи, в заголовке которых содержится слово "PostgreSQL," можно использовать следующий SQL-запрос:

SELECT * FROM articles WHERE title LIKE '%PostgreSQL%';

Этот запрос вернет все строки, в которых поле title содержит слово "PostgreSQL." Однако оператор LIKE не поддерживает сложные операции поиска, такие как учет морфологии или синонимов.

3. Оператор ILIKE

Оператор ILIKE работает аналогично LIKE, но он не учитывает регистр букв. Это полезно при поиске без учета регистра. Например:

SELECT * FROM articles WHERE title ILIKE '%postgresql%';

Этот запрос найдет строки с любым регистром букв в слове "PostgreSQL."

4. Оператор @@

Для более гибкого полнотекстового поиска в PostgreSQL, мы можем использовать оператор @@. Он работает с операторами для поиска текста с использованием векторов весов. Например:

SELECT * FROM articles WHERE to_tsvector('english', title) @@ to_tsquery('english', 'PostgreSQL');

Этот запрос использует функции to_tsvector и to_tsquery, чтобы выполнить полнотекстовый поиск с учетом языка (в данном случае - английского). Это позволяет более точно находить соответствия в тексте и учитывать разные формы слов.

5. Оператор %

Оператор % в комбинации с функцией to_tsquery позволяет находить строки, содержащие схожие слова. Например:

SELECT * FROM articles WHERE to_tsvector('english', title) @@ to_tsquery('english', 'PostgreSQL%');

Этот запрос найдет строки, где в поле title есть слова, начинающиеся с "PostgreSQL," даже если они различаются по окончаниям или формам.

6. Пример с учетом ранжирования

Одной из мощных функциональностей PostgreSQL в области полнотекстового поиска является ранжирование результатов. Мы можем использовать функцию ts_rank для определения степени схожести строк:

SELECT id, title, ts_rank(to_tsvector('english', title), to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

Этот запрос вернет статьи, отсортированные по степени схожести с запросом, что позволяет предоставить более релевантные результаты.

Модуль pg_trgm

1. Установка и настройка модуля pg_trgm

Перед тем как начать использовать модуль pg_trgm, необходимо удостовериться, что он установлен в вашей базе данных PostgreSQL. Вы можете проверить наличие модуля с помощью следующей команды:

SELECT name FROM pg_extension WHERE extname='pg_trgm';

Если модуль не установлен, вы можете выполнить следующую команду, чтобы установить его:

CREATE EXTENSION pg_trgm;

После установки модуля, вам нужно будет создать индекс для таблицы и полей, которые вы планируете использовать для полнотекстового поиска с pg_trgm. Давайте создадим простую таблицу для примера:

CREATE TABLE products (
    id serial PRIMARY KEY,
    name TEXT
);

INSERT INTO products (name) VALUES
    ('PostgreSQL Database'),
    ('Data Management System'),
    ('Relational Database');

CREATE INDEX trgm_name_idx ON products USING gist (name gist_trgm_ops);

2. Простейший поиск с pg_trgm

Теперь, когда модуль pg_trgm настроен, мы можем начать выполнять поисковые запросы. Одной из ключевых функций модуля pg_trgm является pg_trgm.similarity, которая позволяет определить степень схожести между двумя строками. Например:

SELECT name, pg_trgm.similarity(name, 'PostgreSQL') AS similarity
FROM products
ORDER BY similarity DESC;

Этот запрос вернет имена продуктов, отсортированные по степени схожести с фразой "PostgreSQL." Чем ближе значение схожести к 1.0, тем ближе строка к заданной фразе.

3. Поиск по близким словам с использованием pg_trgm

Другой полезной функцией модуля pg_trgm является pg_trgm.word_similarity, которая позволяет находить близкие слова к заданному запросу. Например:

SELECT name
FROM products
WHERE pg_trgm.word_similarity(name, 'PostgreSQL') > 0.4;

Этот запрос найдет все продукты, имена которых содержат слова, близкие к "PostgreSQL."

4. Расширенный поиск с pg_trgm

Модуль pg_trgm также предоставляет возможность выполнять более сложные запросы с использованием операторов. Например:

SELECT name
FROM products
WHERE name % 'Postgres' OR name % 'Database';

Этот запрос найдет все продукты, имена которых содержат либо "Postgres," либо "Database."

5. Пример: поиск с использованием pg_trgm и оператора @@

Модуль pg_trgm может быть эффективно использован в сочетании с оператором @@, который мы рассмотрели в предыдущем разделе. Это позволяет создавать более точные и гибкие запросы. Например:

SELECT name
FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'PostgreSQL');

Этот запрос выполнит полнотекстовый поиск с использованием pg_trgm и оператора @@, учитывая язык текста.

Создание и настройка индексов с использованием pg_trgm

При использовании полнотекстового поиска, создание индексов является важной частью процесса, так как оно позволяет значительно ускорить запросы.

1. B-деревянные индексы: B-дерево - это один из наиболее распространенных типов индексов в PostgreSQL. Он используется для ускорения поиска значений в столбце, и для текстовых данных он может быть не таким эффективным, как индексы, созданные с использованием модуля pg_trgm.

2. Операторы индексирования: При создании индексов в PostgreSQL, вы можете выбирать оператор индексирования. Для полнотекстового поиска с использованием pg_trgm, мы будем использовать gist_trgm_ops или gin_trgm_ops, которые оптимизированы для трехграмм.

Применение pg_trgm для создания индексов полнотекстового поиска

Теперь перейдем к тому, как создать индексы полнотекстового поиска с использованием модуля pg_trgm. Допустим, у нас есть таблица products, и мы хотим создать индекс для поля name, чтобы ускорить поиск по названиям продуктов.

Для создания такого индекса используем оператор CREATE INDEX:

CREATE INDEX trgm_name_idx ON products USING gist (name gist_trgm_ops);

В этом запросе мы указываем, что хотим создать индекс с именем trgm_name_idx для поля name в таблице products. Мы используем USING gist для указания типа индекса (GIST - обобщенный индекс с поддержкой полнотекстового поиска). И, естественно, мы применяем оператор gist_trgm_ops для использования pg_trgm в качестве механизма индексции.

При создании индексов с использованием pg_trgm, есть несколько параметров конфигурации, которые могут повлиять на их производительность. Например, параметр pg_trgm.similarity_threshold позволяет настроить порог схожести между строками, при котором они считаются совпадающими.

Можно настроить этот параметр, изменяя значение по умолчанию (которое равно 0.3) следующим образом:

SET pg_trgm.similarity_threshold = 0.5;

Изменение порога схожести может влиять на результаты поиска, поэтому это важно учитывать при настройке индексов.

Посмотрим на пример создания индекса в контексте нашей таблицы products. Пусть у нас есть следующая таблица и данные:

CREATE TABLE products (
    id serial PRIMARY KEY,
    name TEXT
);

INSERT INTO products (name) VALUES
    ('PostgreSQL Database'),
    ('Data Management System'),
    ('Relational Database');

Для создания индекса на поле name, используем оператор CREATE INDEX:

CREATE INDEX trgm_name_idx ON products USING gist (name gist_trgm_ops);

Теперь, когда индекс создан, запросы, связанные с полнотекстовым поиском в поле name, будут выполняться намного быстрее благодаря индексированию трехграмм.

Важно помнить, что создание индексов может потребовать дополнительного места на диске и немного увеличить время выполнения операций вставки, обновления и удаления записей.

Оптимизация запросов с использованием индексов pg_trgm

Модуль pg_trgm предоставляет нам множество операторов и функций, которые мы можем использовать для оптимизации запросов полнотекстового поиска:

1. % - Оператор схожести: Этот оператор позволяет нам находить строки, схожие с заданной фразой. Например:

   SELECT name FROM products WHERE name % 'PostgreSQL';

   Этот запрос найдет все продукты, чьи имена схожи с "PostgreSQL."

2. <-> - Оператор близости: Этот оператор позволяет находить строки, схожие с заданной фразой в порядке близости. Например:

   SELECT name FROM products WHERE name <-> 'PostgreSQL' ORDER BY name <-> 'PostgreSQL';

   Этот запрос вернет продукты, отсортированные по степени близости к "PostgreSQL."

3. pg_trgm.similarity - Функция схожести: Эта функция позволяет определить степень схожести между строками. Например:

   SELECT name, pg_trgm.similarity(name, 'PostgreSQL') AS similarity FROM products ORDER BY similarity DESC;

   Этот запрос вернет продукты, отсортированные по степени схожести с "PostgreSQL."

4. pg_trgm.word_similarity - Функция близости слов: Эта функция находит близкие слова к заданной фразе. Например:

   SELECT name FROM products WHERE pg_trgm.word_similarity(name, 'PostgreSQL') > 0.4;

   Этот запрос найдет продукты, имена которых содержат слова, близкие к "PostgreSQL."

Некоторые примеры:

1. Оптимизация запроса с использованием оператора %:

   Допустим, у нас есть запрос, который ищет продукты, чьи имена схожи с заданной фразой:

   SELECT name FROM products WHERE name % 'PostgreSQL';

   Мы можем оптимизировать этот запрос, используя индекс pg_trgm:

   SELECT name FROM products WHERE name % 'PostgreSQL' LIMIT 10;

   Добавление LIMIT 10 ограничивает количество возвращаемых результатов, что может значительно ускорить запрос.

2. Оптимизация запроса с использованием функции схожести:

   Предположим, мы ищем продукты, схожие с "PostgreSQL," и хотим отсортировать результаты по степени схожести. Мы можем оптимизировать запрос следующим образом:

   SELECT name, pg_trgm.similarity(name, 'PostgreSQL') AS similarity
   FROM products
   WHERE pg_trgm.similarity(name, 'PostgreSQL') > 0.5
   ORDER BY similarity DESC;

   Добавление условия pg_trgm.similarity(name, 'PostgreSQL') > 0.5 позволяет нам отсеять менее схожие результаты, что улучшает производительность.

3. Оптимизация запроса с использованием оператора <->:

   Если мы ищем продукты, близкие к "PostgreSQL" в порядке близости, мы можем оптимизировать запрос так:

   SELECT name FROM products WHERE name <-> 'PostgreSQL' ORDER BY name <-> 'PostgreSQL' LIMIT 10;

   Это ограничивает количество возвращаемых результатов и ускоряет запрос.

А что насчет других методов индексирования?

1. Сравнение с tsvector/tsquery

Модуль pg_trgm и модуль tsvector/tsquery предоставляют разные методы полнотекстового поиска в PostgreSQL.

• pg_trgm использует трехграммы для индексации и поиска текстовой информации. Он подходит для поиска схожих фраз и слов, учитывая опечатки и разнообразные вариации текста. Этот метод хорошо работает для поиска похожих слов, но может потребовать более высокого порога схожести для более точных результатов.

• tsvector/tsquery предоставляет структуры данных, которые позволяют создавать индексы для текстовой информации и выполнять поиск с учетом лексем и лексемных запросов. Этот метод подходит для точного и гибкого полнотекстового поиска, но не учитывает схожие слова и опечатки так же, как pg_trgm.

2. Сравнение с pg_bigm

Расширение pg_bigm предназначено для выполнения более точного и гибкого полнотекстового поиска в PostgreSQL. Оно позволяет создавать индексы для больших объемов текста и обеспечивает более точные результаты, чем pg_trgm.

Однако, pg_bigm может быть более ресурсоемким и медленным в сравнении с pg_trgm, особенно при работе с большими объемами данных. Выбор между этими методами зависит от конкретных потребностей вашего приложения. Если вам нужна высокая точность и гибкость, pg_bigm может быть предпочтительным вариантом.

3. Сравнение с расширением fuzzystrmatch

Расширение fuzzystrmatch предоставляет функции для сравнения строк с учетом схожести и опечаток. Оно подходит для поиска строк, которые похожи, но не обязательно идентичны. Это может быть полезно при поиске схожих слов и фраз, но не обеспечивает индексацию и полнотекстовый поиск на уровне, который предлагает pg_trgm.

Конечно, нужно побольше примеров использования pg_trgm:

Пример 1: Поиск похожих фраз

Задача: Найти все фразы в базе данных, похожие на заданную фразу "Полнотекстовый поиск в PostgreSQL".

SELECT phrase FROM phrases
WHERE phrase % 'Полнотекстовый поиск в PostgreSQL';

Пример 2: Поиск близких слов

Задача: Найти все слова в базе данных, близкие к заданному слову "оптимизация".

SELECT word FROM words
WHERE pg_trgm.word_similarity(word, 'оптимизация') > 0.4;

Пример 3: Сортировка результатов по близости

Задача: Найти и отсортировать продукты по степени близости их названий к "PostgreSQL".

SELECT name, pg_trgm.similarity(name, 'PostgreSQL') AS similarity
FROM products
ORDER BY similarity DESC;

Пример 4: Поиск с учетом опечаток

Задача: Найти все имена пользователей, схожие с "Otus Otusovich" с учетом возможных опечаток.

SELECT name FROM users
WHERE name % 'Otus Otusovich' OR name % 'Otus Otusovich';

Пример 5: Поиск с использованием оператора близости

Задача: Найти и отсортировать все продукты по близости их названий к "PostgreSQL".

SELECT name FROM products
WHERE name <-> 'PostgreSQL'
ORDER BY name <-> 'PostgreSQL';

Пример 6: Ограничение количества результатов

Задача: Найти и отобразить первые 10 продуктов, ближайших к "PostgreSQL".

SELECT name FROM products
WHERE name <-> 'PostgreSQL'
ORDER BY name <-> 'PostgreSQL'
LIMIT 10;

Пример 7: Поиск с использованием индексов

Задача: Использовать индекс pg_trgm для поиска схожих слов в большой таблице "documents".

CREATE INDEX trgm_document_idx ON documents USING gin (content gin_trgm_ops);

SELECT content FROM documents
WHERE content % 'индексирование полнотекстовых данных';

Пример 8: Поиск близких фамилий в базе данных клиентов

Задача: Найти клиентов с фамилией, близкой к "Smith".

SELECT last_name FROM customers
WHERE pg_trgm.similarity(last_name, 'Smith') > 0.6;

Пример 9: Поиск с использованием функции схожести слов

Задача: Найти и отобразить слова из словаря, схожие с "программирование", и оценить степень схожести.

SELECT word, pg_trgm.similarity(word, 'программирование') AS similarity
FROM dictionary
ORDER BY similarity DESC;

Пример 10: Поиск с использованием оператора схожести и логического оператора OR

Задача: Найти фразы, схожие с "полезные советы" или "лучшие практики".

SELECT phrase FROM phrases
WHERE phrase % 'полезные советы' OR phrase % 'лучшие практики';

Пример 11: Поиск по сокращенному названию компании

Задача: Найти компании по их сокращенному названию (например, "IBM" для "International Business Machines").

SELECT company_name FROM companies
WHERE pg_trgm.similarity(abbreviation, 'IBM') > 0.5;

Пример 12: Поиск с использованием частичного ввода пользователя

Задача: Поиск и отображение результатов по частичному вводу пользователя, например, когда пользователь вводит "программ".

SELECT term FROM glossary
WHERE term % 'программ';

Пример 13: Поиск с учетом регистра

Задача: Найти и отобразить имена пользователей, схожие с "Johnny Cage ", но с учетом регистра.

SELECT name FROM users
WHERE name % 'Johnny Cage' COLLATE "C";

Пример 14: Поиск с использованием буквенных символов

Задача: Найти и отобразить слова, схожие с "Отус", используя только буквенные символы.

SELECT word FROM dictionary
WHERE pg_trgm.similarity(word, 'Отус') > 0.4 AND word ~ '^[а-яА-Я]+$';

Пример 15: Поиск с использованием дополнительных параметров

Задача: Найти и отобразить компании с близкими названиями, учитывая порог схожести.

SET pg_trgm.similarity_threshold = 0.6;
SELECT company_name FROM companies
WHERE company_name % 'Google';

Заключение

Модуль pg_trgm является мощным инструментом для реализации полнотекстового поиска в PostgreSQL. Его способность учитывать схожие слова и опечатки делает его полезным инструментом для работы с текстовой информацией.

В преддверии старта курса PostgreSQL для администраторов баз данных и разработчиков хочу порекомендовать вам парочку бесплатных вебинаров, которые будут интересны всем, кто интересуется PostgreSQL:

• Postgresql в Docker - все ли мы знаем?

• Синхронный VS Асинхронный PostgreSQL