Как индекс GIN работает с полнотекстовым поиском в БД?

GIN индексы и полнотекстовый поиск в PostgreSQL

GIN (Generalized Inverted Index) — это тип индекса, который особенно эффективен для полнотекстового поиска в PostgreSQL. Он строит инвертированный индекс всех слов в тексте.

Как работает GIN индекс

Нормальный индекс (B-tree):

Документ: "Python is great"
Индекс: "Python is great" → ID документа

Поиск "great" → медленно! Нужно сканировать каждый документ

GIN индекс (Inverted Index):

Документ 1: "Python is great"
Документ 2: "I love Python"

Индекс (инвертированный):
"Python" → [Doc1, Doc2]
"is" → [Doc1]
"great" → [Doc1]
"I" → [Doc2]
"love" → [Doc2]

Поиск "Python" → сразу находим [Doc1, Doc2]! Очень быстро

Создание GIN индекса для полнотекстового поиска

-- 1. Создать таблицу
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Создать tsvector (Text Search Vector) — специальный тип для полнотекстового поиска
ALTER TABLE articles ADD COLUMN content_search TSVECTOR;

-- 3. Заполнить tsvector для всех существующих строк
UPDATE articles 
SET content_search = to_tsvector('english', title || ' ' || content);

-- 4. Создать GIN индекс на tsvector
CREATE INDEX idx_articles_content_search ON articles USING GIN(content_search);

-- 5. Создать триггер для автоматического обновления при добавлении/изменении
CREATE TRIGGER articles_content_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger(
    content_search,  -- Колонка tsvector
    'english',       -- Язык
    title, content   -- Колонки для индексирования
);

Использование полнотекстового поиска

-- Простой поиск (оператор @@)
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'Python');

-- Поиск с & (AND) — оба слова
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'Python & database');

-- Поиск с | (OR) — одно из слов
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'Python | Java');

-- Поиск с ! (NOT) — исключить слово
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'Python & !legacy');

-- Фразовый поиск (слова рядом)
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'fast <-> query');

-- С рангированием (релевантность)
SELECT id, title, ts_rank(content_search, query) AS rank
FROM articles, to_tsquery('english', 'Python') query
WHERE content_search @@ query
ORDER BY rank DESC;

Python с PostgreSQL и полнотекстовым поиском

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, func
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from datetime import datetime

Base = declarative_base()

class Article(Base):
    __tablename__ = "articles"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    content = Column(Text)
    content_search = Column(TSVECTOR)
    created_at = Column(DateTime, default=datetime.utcnow)

# 1. Создаём engine
engine = create_engine("postgresql://user:password@localhost/dbname")
Base.metadata.create_all(engine)

# 2. Добавляем статью
session = Session(engine)
article = Article(
    title="Python Programming",
    content="Python is a great language for web development"
)
session.add(article)
session.commit()

# 3. Полнотекстовый поиск
from sqlalchemy import and_

query_str = "Python"
results = session.query(Article).filter(
    Article.content_search.op("@@")(func.to_tsquery("english", query_str))
).all()

for article in results:
    print(f"{article.title}: {article.content}")

# 4. С рангированием (релевантностью)
from sqlalchemy import desc

query = func.to_tsquery("english", query_str)
rank = func.ts_rank(Article.content_search, query)

results = session.query(Article, rank.label("relevance")).filter(
    Article.content_search.op("@@")(query)
).order_by(desc("relevance")).all()

for article, relevance in results:
    print(f"{article.title} (relevance: {relevance})")

# 5. Сложные поиски
from sqlalchemy import or_

# AND
results = session.query(Article).filter(
    Article.content_search.op("@@")(func.to_tsquery("english", "Python & database"))
).all()

# OR
results = session.query(Article).filter(
    Article.content_search.op("@@")(func.to_tsquery("english", "Python | Java"))
).all()

Производительность GIN индекса

До индекса (полный скан):

-- Медленно! Сканирует все строки
SELECT * FROM articles
WHERE content ILIKE '%Python%';
-- Время: 1500 ms на 1 млн строк

После GIN индекса:

-- Быстро! Использует индекс
SELECT * FROM articles
WHERE content_search @@ to_tsquery('english', 'Python');
-- Время: 2 ms на 1 млн строк (в 750 раз быстрее!)

Тонкости и оптимизация

1. Выбор языка важен:

-- Английский: 'english', 'simple'
-- Русский: 'russian'
-- Немецкий: 'german'
-- Французский: 'french'

SELECT to_tsvector('russian', 'Добро пожаловать');

2. Исключение стоп-слов:

-- Стоп-слова ('the', 'a', 'is') исключаются автоматически
SELECT to_tsvector('english', 'The quick brown fox');
-- Результат: 'brown':3 'fox':4 'quick':2

3. Взвешивание слов (важность):

-- Придать разный вес словам
SELECT setweight(to_tsvector('english', 'Python'), 'A') ||
       setweight(to_tsvector('english', 'programming'), 'B');

-- Затем рангировать с учётом веса
SELECT ts_rank_cd(
    ARRAY[0.1, 0.2, 0.4, 1.0],  -- Веса для A, B, C, D
    content_search,
    query
) AS rank;

4. Полнотекстовый поиск по нескольким колонкам:

from sqlalchemy import func

# Объединяем несколько полей
results = session.query(Article).filter(
    func.to_tsvector('english', Article.title || ' ' || Article.content) \
        .op("@@")(func.to_tsquery('english', 'Python'))
).all()

Сравнение индексов

Вывод

GIN индекс — это мощный инструмент для полнотекстового поиска:

• Инвертированный индекс — каждому слову соответствуют документы, содержащие это слово
• Очень быстро — ускорение в 100-1000 раз для полнотекстовых запросов
• Поддерживает сложные запросы — AND, OR, NOT, фразовый поиск, рангирование
• Требует планирования — выбор языка, обновление индекса при изменениях
• Идеален для — поисковых систем, CMS, форумов, баз знаний

Для полнотекстового поиска в PostgreSQL GIN индекс — стандартный и рекомендуемый инструмент.