Для каких значений лучше всего подходят разные типы индексов

Типы индексов в PostgreSQL и их применение

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

1. B-Tree (по умолчанию)

Когда использовать:

• Поиск по точному значению (WHERE id = 5)
• Диапазонные запросы (WHERE age > 18)
• Сортировка (ORDER BY)
• Условия IN, BETWEEN
• Foreign keys

# SQL пример
CREATE INDEX idx_user_age ON users(age);  -- B-Tree по умолчанию

# Запросы, которые ускоряет
SELECT * FROM users WHERE age = 25;        -- Быстро
SELECT * FROM users WHERE age > 18;        -- Быстро
SELECT * FROM users WHERE age IN (18,25,30);  -- Быстро
SELECT * FROM users ORDER BY age;          -- Быстро

# SQLAlchemy
from sqlalchemy import Index
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    age = Column(Integer)
    __table_args__ = (
        Index("idx_user_age", "age"),
    )

2. Hash индекс

Когда использовать:

• Только поиск по точному значению (WHERE status = 'active')
• Часто используемые колонки с частыми поисками
• НЕ подходит для диапазонов и сортировки

# SQL
CREATE INDEX idx_user_status ON users USING HASH(status);

# Быстро
SELECT * FROM users WHERE status = 'active';

# НЕ быстро (Hash не поддерживает диапазоны)
SELECT * FROM users WHERE status > 'a';  -- Полный скан

# SQLAlchemy
index = Index(
    "idx_user_status",
    "status",
    postgresql_using="hash"
)

3. GiST (Generalized Search Tree)

Когда использовать:

• Геопространственные запросы (PostGIS, расстояния)
• Полнотекстовый поиск
• Контейнер-запросы (массивы, диапазоны)
• Сложные типы данных

# SQL - пример с диапазонами
CREATE INDEX idx_date_range ON events USING GIST(date_range);

# Быстро: поиск пересечений
SELECT * FROM events 
WHERE date_range && tsrange('2024-01-01', '2024-12-31');

# Пример с массивами
CREATE INDEX idx_tags ON posts USING GIST(tags);
SELECT * FROM posts WHERE tags && ARRAY['python', 'django'];

4. GIN (Generalized Inverted Index)

Когда использовать:

• Поиск элементов внутри массивов
• Полнотекстовый поиск (tsvector)
• JSONB содержит операции
• Множество различных ключей

# SQL - пример с JSONB
CREATE INDEX idx_user_metadata ON users USING GIN(metadata);

# Быстро: поиск ключей в JSON
SELECT * FROM users 
WHERE metadata @> '{"premium": true}';

# Пример с полнотекстовым поиском
CREATE INDEX idx_article_text ON articles 
  USING GIN(to_tsvector('english', content));
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ plainto_tsquery('python');

# Пример с массивами
CREATE INDEX idx_categories ON products USING GIN(categories);
SELECT * FROM products WHERE 'electronics' = ANY(categories);

5. BRIN (Block Range Index)

Когда использовать:

• Очень большие таблицы (миллионы строк)
• Сортированные данные (datetime, ID)
• Плохая скорость вставки не критична
• Экономия места на диске

# SQL - пример
CREATE INDEX idx_created_at ON logs USING BRIN(created_at);

# Быстро и занимает минимум места
SELECT * FROM logs 
WHERE created_at > NOW() - INTERVAL '7 days';

# Для таблицы с 100 млн строк
-- B-Tree: займёт 500 MB
-- BRIN: займёт 1 MB

6. SP-GiST (Space-Partitioned GiST)

Когда использовать:

• Геопространственные данные
• IP адреса (inet/cidr типы)
• Структурированные данные

# SQL
CREATE INDEX idx_ip_address ON events USING SPGIST(ip);

SELECT * FROM events 
WHERE ip << '192.168.0.0/16'::inet;  -- Быстро

Сравнительная таблица

Практический пример для e-commerce

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    category = Column(String(100))
    price = Column(Numeric(10, 2))
    tags = Column(ARRAY(String))
    metadata = Column(JSON)
    created_at = Column(DateTime)
    
    __table_args__ = (
        # B-Tree для основных поисков
        Index("idx_product_category", "category"),
        Index("idx_product_price", "price"),
        Index("idx_product_created_at", "created_at"),
        
        # Hash для точных поисков
        Index("idx_product_status", "status", postgresql_using="hash"),
        
        # GIN для массивов и JSON
        Index("idx_product_tags", "tags", postgresql_using="gin"),
        Index("idx_product_metadata", "metadata", postgresql_using="gin"),
        
        # BRIN для больших таблиц
        Index("idx_product_created_brin", "created_at", postgresql_using="brin"),
    )

Как выбрать правильный индекс

Анализируй запросы — используй EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM products WHERE category = 'electronics';

Если видишь Sequential Scan — нужен индекс!

Рекомендации по размеру таблицы:

• Менее 1000 строк: индексы часто не нужны
• 1000-100000: B-Tree для основных колонок
• Более 1000000: рассмотри BRIN

Выбор по типам операций:

• Точный поиск (=): B-Tree или Hash
• Диапазон (>, <, BETWEEN): B-Tree или GiST
• Элементы массива: GIN
• Геоданные: GiST или SP-GiST

Баланс ресурсов:

• Быстрая вставка: BRIN или Hash
• Скорость запросов: B-Tree или GIN