0Pricing
SQL Academy · Урок

Поиск по триграммам (pg_trgm) для нечёткого сопоставления

Находите похожие строки с расширением pg_trgm, триграммными индексами GiST/GIN и функцией similarity()

«Поиск по триграммам (pg_trgm) для нечёткого сопоставления» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Зачем нужны триграммы

LIKE %word% не может использовать индекс B-дерева. Триграммы («подстроки из 3 символов») вместе с GIN/GiST обеспечивают быстрый нечеткий поиск и поиск подстрок.

Подключение расширения

Расширение pg_trgm поставляется вместе с дополнительными модулями PostgreSQL:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Триграммы слова

Покажите триграммы строки:

SELECT show_trgm('database');
-- {dat, aba, atab, tab, abas, base, ase, ...}

Функция сходства

Вещественное число в диапазоне [0..1]:

SELECT similarity('database', 'datbase');     -- ~0.75
SELECT similarity('hello', 'world');             -- ~0.0

Оператор %

Возвращает true, если сходство выше порога (по умолчанию 0.3):

SELECT 'database' % 'datbase';      -- t
SET pg_trgm.similarity_threshold = 0.4;

Индекс для поиска подстрок

Триграммный индекс GIN ускоряет LIKE '%word%':

CREATE INDEX users_name_trgm ON users USING GIN (full_name gin_trgm_ops);

-- Now this is index-supported:
SELECT * FROM users WHERE full_name ILIKE '%ali%';

GiST и GIN

  • GIN — более быстрое чтение, более медленная запись, больший индекс
  • GiST — более быстрая запись, более медленное чтение, меньший индекс

Большинство команд выбирают GIN для нечеткого поиска.

Запросы для нечеткого сопоставления

Находите похожие строки даже при наличии опечаток:

SELECT full_name, similarity(full_name, 'datbase') AS sim
FROM users
WHERE full_name % 'datbase'
ORDER BY sim DESC
LIMIT 10;

Расстояние для ранжирования

Оператор <-> возвращает расстояние (1 - сходство), поэтому можно выполнить ORDER BY distance ASC:

SELECT full_name
FROM users
WHERE full_name % 'alice'
ORDER BY full_name <-> 'alice'
LIMIT 10;

Автодополнение с помощью триграмм

Объедините pg_trgm с префиксом LIKE для быстрого автодополнения:

SELECT name
FROM products
WHERE name % $1
   OR name ILIKE $1 || '%'
ORDER BY similarity(name, $1) DESC
LIMIT 8;

Ограничения триграмм

Триграммы работают с алфавитом входных данных. Для языков CJK нужна другая токенизация. Для полноценного поиска на естественном языке используйте tsvector и GIN.

Сочетание с другими индексами

Запрос с несколькими фильтрами может использовать триграммный индекс И индекс B-дерева посредством растровой операции AND:

EXPLAIN ANALYZE
SELECT * FROM users
WHERE full_name ILIKE '%ali%' AND country = 'US';

Итоги

pg_trgm устраняет проблему «начального шаблона с подстановочным символом».

  • Триграммный индекс GIN → быстрый поиск LIKE %x%
  • Оператор % и функция similarity() для нечеткого сопоставления
  • Расстояние <-> для ранжирования
  • Отлично подходит для автодополнения и поиска, устойчивого к опечаткам

Быстрая проверка

Почему обычный индекс B-tree не помогает при условии WHERE name LIKE '%alice%'?

Часто задаваемые вопросы

Урок «Поиск по триграммам (pg_trgm) для нечёткого сопоставления» бесплатный?

Да — полный текст урока «Поиск по триграммам (pg_trgm) для нечёткого сопоставления» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Поиск по триграммам (pg_trgm) для нечёткого сопоставления»?

Находите похожие строки с расширением pg_trgm, триграммными индексами GiST/GIN и функцией similarity() Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Поиск по триграммам (pg_trgm) для нечёткого сопоставления»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Поиск по триграммам (pg_trgm) для нечёткого сопоставления
  2. Полнотекстовый поиск с tsvector и GIN
  3. Геопространственная индексация с PostGIS
  4. Векторный поиск с pgvector
← Назад к SQL Academy