Какие мы можем создвать индексы в PostgresSQL?

Индексы в PostgreSQL

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

Основные типы индексов

1. B-Tree (B-дерево)

Стандартный индекс по умолчанию, который создается при использовании простого CREATE INDEX. Оптимален для операций сравнения и сортировки.

-- Создание B-Tree индекса
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at DESC);

Характеристики:

• Поддерживает операции: =, <, >, <=, >=, BETWEEN, IN
• Эффективен для сортировки (ORDER BY)
• Поддерживает уникальность (UNIQUE CONSTRAINT)
• Работает с NULL значениями

2. Hash (Хеш-индекс)

Специализирован для точного совпадения равенства (= оператор). В современных версиях PostgreSQL (10+) стал crash-safe и транзакционно безопасным.

-- Создание Hash индекса
CREATE INDEX idx_users_id_hash ON users USING HASH(id);

Особенности:

• Только для операций равенства (=)
• Обычно быстрее B-Tree для простых сравнений
• Не поддерживает сортировку или сравнение диапазонов

3. GiST (Generalized Search Tree)

Обобщенное дерево поиска - инфраструктура для реализации различных стратегий индексирования.

-- Индекс для полнотекстового поиска
CREATE INDEX idx_documents_content 
ON documents USING GIST(to_tsvector('russian', content));

-- Индекс для геометрических данных
CREATE INDEX idx_locations_geom 
ON locations USING GIST(geom);

Применение:

• Геометрические типы данных (точки, полигоны)
• Полнотекстовый поиск
• Поиск по диапазонам
• Иерархические данные (ltree)

4. GIN (Generalized Inverted Index)

Оптимизирован для индексирования составных значений, особенно массивов, JSONB и полнотекстового поиска.

-- Индекс для JSONB данных
CREATE INDEX idx_products_attributes 
ON products USING GIN(attributes);

-- Индекс для массивов
CREATE INDEX idx_articles_tags 
ON articles USING GIN(tags);

Преимущества:

• Эффективен для операций @> (содержит), <@ (содержится в)
• Быстрый поиск по элементам массивов
• Оптимален для JSONB поиска
• Поддерживает полнотекстовый поиск

5. SP-GiST (Space-Partitioned GiST)

Пространственно-разделенное GiST - эффективен для данных, которые можно разделить на непересекающиеся области.

-- Индекс для сетевых адресов
CREATE INDEX idx_devices_ip 
ON devices USING SPGIST(ip_address inet_ops);

Использование:

• Сетевые адреса (inet, cidr)
• Текст с шаблонами (например, LIKE 'abc%')
• Некоторые геометрические данные

6. BRIN (Block Range Index)

Индекс диапазонов блоков - компактный индекс для очень больших таблиц с естественной сортировкой данных.

-- BRIN индекс для временных рядов
CREATE INDEX idx_sensor_data_timestamp 
ON sensor_data USING BRIN(timestamp);

Особенности:

• Крайне малый размер (1-2% от размера таблицы)
• Эффективен для данных, сгруппированных физически (timestamps, последовательные ID)
• Поддерживает только определенные типы операций

Специализированные индексы

Частичные индексы (Partial Indexes)

Индексируют только часть данных таблицы по условию WHERE.

-- Индекс только для активных пользователей
CREATE INDEX idx_users_active_email 
ON users(email) 
WHERE status = 'active';

Составные индексы (Composite/Multi-column)

Индекс по нескольким столбцам с учетом порядка.

-- Составной индекс с порядком сортировки
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

Индексы по выражениям (Expression Indexes)

Индексируют результат выражения, а не значение столбца.

-- Индекс по нижнему регистру email
CREATE INDEX idx_users_lower_email 
ON users(LOWER(email));

-- Индекс по извлеченной дате
CREATE INDEX idx_logs_event_date 
ON logs(DATE(event_timestamp));

Выбор индекса: практические рекомендации

1. Для стандартных запросов - начинайте с B-Tree
2. Для JSONB/массивов - используйте GIN
3. Для геоданных - GiST или SP-GiST
4. Для временных рядов - рассмотрите BRIN
5. При ограниченной памяти - BRIN или частичные индексы
6. Для специализированных типов данных - используйте соответствующие операторные классы

-- Пример создания индекса с указанием класса операторов
CREATE INDEX idx_products_name_pattern 
ON products USING GIN(name gin_trgm_ops);

Мониторинг и обслуживание

-- Проверка использования индексов
SELECT * FROM pg_stat_user_indexes;

-- Анализ индексов
SELECT schemaname, tablename, indexname 
FROM pg_indexes 
WHERE tablename = 'your_table';

Ключевые принципы:

• Индексы ускоряют чтение, но замедляют запись
• Каждый индекс требует дискового пространства
• Не создавайте индексы "на всякий случай"
• Регулярно анализируйте использование индексов через EXPLAIN ANALYZE
• Используйте CONCURRENTLY для создания индексов на продакшене

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