Индексы в PostgreSQL: типы, применение и подводные камни

Введение

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

B-Tree индексы

B-Tree (сбалансированное дерево) — тип по умолчанию. Он покрывает большинство задач: сравнение на равенство, диапазонные запросы, сортировка.

-- Создание обычного B-Tree индекса
CREATE INDEX idx_users_email ON users (email);

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

Правила применения:

• Операции =, <, >, BETWEEN, LIKE 'prefix%'
• ORDER BY и GROUP BY
• Составные ключи — колонки с высокой селективностью ставьте первыми

Hash индексы

Hash индексы хранят хеш значения ключа и оптимизированы исключительно для проверки на равенство. С PostgreSQL 10 они стали WAL-логируемыми и надёжными.

-- Hash-индекс оправдан только при запросах с =
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

-- Запрос, который выигрывает от hash-индекса
SELECT * FROM sessions WHERE token = 'abc123xyz';

Правила применения:

• Только операции равенства (диапазоны не поддерживаются)
• Поля с высокой кардинальностью: UUID, токены, хеши
• Когда компактность важнее универсальности

GiST индексы

GiST (Generalized Search Tree) — расширяемая структура для сложных типов данных: геометрия, диапазоны, полнотекстовый поиск.

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

-- Поиск точек в радиусе 10 единиц
SELECT * FROM locations
WHERE coords <-> '(55.75, 37.62)'::point < 10;

-- Индекс для диапазонных типов
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

-- Поиск пересечений диапазонов дат
SELECT * FROM bookings
WHERE period && '[2024-01-01, 2024-01-31]'::daterange;

GIN индексы

GIN (Generalized Inverted Index) — инвертированный индекс для значений, содержащих множество элементов: массивы, JSONB, полнотекстовый поиск.

-- Индекс для JSONB-поля с атрибутами товаров
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Поиск всех товаров красного цвета через containment-оператор
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Индекс для полнотекстового поиска на русском языке
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('russian', body));

-- Полнотекстовый поиск с несколькими ключевыми словами
SELECT * FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'PostgreSQL & индекс');

BRIN индексы

BRIN (Block Range INdex) — сверхкомпактный индекс для больших таблиц с физической корреляцией между значением поля и расположением строки на диске.

-- BRIN для таблицы событий с монотонно возрастающей датой
CREATE INDEX idx_events_created ON events USING BRIN (created_at);

-- Запрос по диапазону — сканируются только нужные блоки диска
SELECT * FROM events
WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31';

BRIN в 100–1000 раз меньше B-Tree, но эффективен только при высокой корреляции: timeseries, автоинкрементные ID, данные с последовательной вставкой.

Частичные и функциональные индексы

Частичные индексы покрывают только часть строк таблицы — это уменьшает размер индекса и ускоряет его обновление.

-- Частичный индекс только по активным пользователям
CREATE INDEX idx_users_active_email ON users (email)
WHERE is_active = true;

-- Функциональный индекс на результат выражения
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- Теперь поиск без учёта регистра использует индекс
SELECT * FROM users WHERE lower(email) = 'user@example.com';

Частые ошибки

Индекс на поле с низкой кардинальностью. Индекс по булевому полю или полю со статусом из трёх значений почти бесполезен — планировщик предпочтёт seq scan. Используйте частичный индекс.

-- Плохо: кардинальность поля — 2 значения
CREATE INDEX idx_users_is_admin ON users (is_admin);

-- Лучше: частичный индекс только по нужному подмножеству
CREATE INDEX idx_users_admins ON users (id) WHERE is_admin = true;

Неправильный порядок колонок в составном индексе. B-Tree применяет колонки слева направо. Если запросы всегда фильтруют по status, он должен стоять первым.

-- Плохо: запрос фильтрует только по status, индекс не используется полностью
CREATE INDEX idx_orders_wrong ON orders (user_id, status);

-- Правильно: наиболее часто используемый фильтр — первым
CREATE INDEX idx_orders_right ON orders (status, user_id);

Индекс не используется из-за неявного приведения типов. Если тип аргумента в запросе не совпадает с типом колонки, PostgreSQL не сможет использовать индекс.

-- Плохо: phone хранится как VARCHAR, но передаётся число — приведение ломает индекс
SELECT * FROM users WHERE phone = 79001234567;

-- Правильно: тип аргумента совпадает с типом колонки
SELECT * FROM users WHERE phone = '79001234567';

Создание индексов без анализа реальных запросов. Перед созданием индекса всегда проверяйте план выполнения.

-- Полный анализ с реальным выполнением запроса и статистикой буферов
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

Заключение

Правило выбора типа индекса в PostgreSQL простое — смотрите на тип данных и характер запросов:

• B-Tree — универсальный выбор для сравнений и диапазонов
• Hash — только проверка на равенство с высокой кардинальностью
• GiST — геометрия, диапазоны, близость
• GIN — JSONB, массивы, полнотекстовый поиск
• BRIN — огромные таблицы с монотонными, последовательно вставляемыми данными

Каждый индекс — это компромисс между скоростью чтения и стоимостью записи. Используйте pg_stat_user_indexes для поиска неиспользуемых индексов и EXPLAIN ANALYZE для проверки эффекта от новых.