Индексирование полнотекстовых данных в PostgreSQL с использованием модуля pg_trgm
Привет, Хабр!
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 предоставляет набор инструментов для реализации полнотекстового поиска, и понимание его основ - это важное знание для все разрабов, а так же аналитиков.
Текстовый тип данных в PostgreSQL
Прежде чем начать работу с полнотекстовым поиском, необходимо понимать, как PostgreSQL хранит и обрабатывает текст. В PostgreSQL есть несколько типов данных для хранения текста, но основными из них являются TEXT
и VARCHAR
. Например, для создания таблицы с полем для текста, мы можем использовать следующий SQL-запрос:
CREATE TABLE articles (
id serial PRIMARY KEY,
title VARCHAR(255),
content TEXT
);
В данном примере title
и content
- это поля для хранения текста, которые будут использоваться в поиске. Различные типы данных могут быть более или менее подходящими в зависимости от требований к проекту и производительности.
Оператор
LIKE
Одним из способов выполнения поиска в текстовых полях в PostgreSQL является использование оператора LIKE
. Этот оператор позволяет находить строки, которые содержат определенную подстроку. Например, чтобы найти все статьи, в заголовке которых содержится слово "PostgreSQL," можно использовать следующий SQL-запрос:
SELECT * FROM articles WHERE title LIKE '%PostgreSQL%';
Этот запрос вернет все строки, в которых поле title
содержит слово "PostgreSQL." Однако оператор LIKE
не поддерживает сложные операции поиска, такие как учет морфологии или синонимов.
Оператор
ILIKE
Оператор ILIKE
работает аналогично LIKE
, но он не учитывает регистр букв. Это полезно при поиске без учета регистра. Например:
SELECT * FROM articles WHERE title ILIKE '%postgresql%';
Этот запрос найдет строки с любым регистром букв в слове "PostgreSQL."
Оператор
@@
Для более гибкого полнотекстового поиска в PostgreSQL, мы можем использовать оператор @@
. Он работает с операторами для поиска текста с использованием векторов весов. Например:
SELECT * FROM articles WHERE to_tsvector('english', title) @@ to_tsquery('english', 'PostgreSQL');
Этот запрос использует функции to_tsvector
и to_tsquery
, чтобы выполнить полнотекстовый поиск с учетом языка (в данном случае - английского). Это позволяет более точно находить соответствия в тексте и учитывать разные формы слов.
Оператор
%
Оператор %
в комбинации с функцией to_tsquery
позволяет находить строки, содержащие схожие слова. Например:
SELECT * FROM articles WHERE to_tsvector('english', title) @@ to_tsquery('english', 'PostgreSQL%');
Этот запрос найдет строки, где в поле title
есть слова, начинающиеся с "PostgreSQL," даже если они различаются по окончаниям или формам.
Пример с учетом ранжирования
Одной из мощных функциональностей 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
При использовании полнотекстового поиска, создание индексов является важной частью процесса, так как оно позволяет значительно ускорить запросы.
B-деревянные индексы: B-дерево - это один из наиболее распространенных типов индексов в PostgreSQL. Он используется для ускорения поиска значений в столбце, и для текстовых данных он может быть не таким эффективным, как индексы, созданные с использованием модуля pg_trgm.
Операторы индексирования: При создании индексов в 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 предоставляет нам множество операторов и функций, которые мы можем использовать для оптимизации запросов полнотекстового поиска:
%
- Оператор схожести: Этот оператор позволяет нам находить строки, схожие с заданной фразой. Например:SELECT name FROM products WHERE name % 'PostgreSQL';
Этот запрос найдет все продукты, чьи имена схожи с "PostgreSQL."
<->
- Оператор близости: Этот оператор позволяет находить строки, схожие с заданной фразой в порядке близости. Например:SELECT name FROM products WHERE name <-> 'PostgreSQL' ORDER BY name <-> 'PostgreSQL';
Этот запрос вернет продукты, отсортированные по степени близости к "PostgreSQL."
pg_trgm.similarity
- Функция схожести: Эта функция позволяет определить степень схожести между строками. Например:SELECT name, pg_trgm.similarity(name, 'PostgreSQL') AS similarity FROM products ORDER BY similarity DESC;
Этот запрос вернет продукты, отсортированные по степени схожести с "PostgreSQL."
pg_trgm.word_similarity
- Функция близости слов: Эта функция находит близкие слова к заданной фразе. Например:SELECT name FROM products WHERE pg_trgm.word_similarity(name, 'PostgreSQL') > 0.4;
Этот запрос найдет продукты, имена которых содержат слова, близкие к "PostgreSQL."
Некоторые примеры:
Оптимизация запроса с использованием оператора
%
:Допустим, у нас есть запрос, который ищет продукты, чьи имена схожи с заданной фразой:
SELECT name FROM products WHERE name % 'PostgreSQL';
Мы можем оптимизировать этот запрос, используя индекс pg_trgm:
SELECT name FROM products WHERE name % 'PostgreSQL' LIMIT 10;
Добавление
LIMIT 10
ограничивает количество возвращаемых результатов, что может значительно ускорить запрос.Оптимизация запроса с использованием функции схожести:
Предположим, мы ищем продукты, схожие с "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
позволяет нам отсеять менее схожие результаты, что улучшает производительность.Оптимизация запроса с использованием оператора
<->
:Если мы ищем продукты, близкие к "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: