Индексы PostgreSQL: типы, стратегии и оптимизация

PostgreSQL поддерживает шесть типов индексов: B-tree, Hash, GIN, GiST, BRIN и SP-GiST, каждый оптимизирован для своего класса задач. Частичные индексы с условием WHERE и покрывающие индексы с INCLUDE дают дополнительную гибкость для специфических паттернов запросов. Правильный выбор типа и структуры индекса — разница между 10ms и 10 секундами для одного запроса. Неправильные индексы не просто бесполезны — они замедляют вставку и занимают место.

Почему это важно: Индексирование — это самый высокодоходный инструмент оптимизации PostgreSQL. Правильно построенный составной индекс может превратить 8-секундный запрос в 12-миллисекундный без изменений в коде приложения. При этом каждый лишний индекс замедляет каждую операцию INSERT, UPDATE, DELETE на таблице — PostgreSQL должен обновлять все индексы при каждом изменении строки. На таблицах с высокой нагрузкой записи (заказы, события, логи) 10 индексов вместо правильных 3 могут снизить throughput вставки в 3-4 раза.

Главная идея

B-tree — универсальный индекс для сравнений (=, <, >, BETWEEN) и сортировки. Это дерево с балансировкой, высотой O(log N), размером ~10-30% от данных столбца. 99% всех индексов в типичном приложении — B-tree. GIN (Generalized Inverted Index) — инвертированный индекс для составных типов. Хранит mapping от каждого элемента к списку строк, которые его содержат. Идеален для JSONB (@>, ?), массивов, tsvector. Медленнее при UPDATE (нужно обновить записи для всех изменённых элементов), но очень быстрый поиск. BRIN (Block Range INdex) — компактный индекс для огромных таблиц с физически упорядоченными данными. Хранит min/max значения для диапазонов блоков (128 блоков по умолчанию = ~1MB данных). Размер BRIN-индекса на таблице в 1TB — несколько мегабайт. Работает только если данные физически коррелируют со значением столбца (временные ряды, append-only таблицы).

Как это выглядит на практике

Создай B-tree индекс для часто используемого foreign key: CREATE INDEX idx_orders_user_id ON orders(user_id);. PostgreSQL не создаёт индексы на foreign key автоматически — нужно делать явно. Без этого индекса каждый JOIN orders с users делает seq scan таблицы orders.
Составной индекс для многоколонного WHERE: CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) WHERE status != 'archived';. Этот индекс покрывает WHERE status = 'pending' ORDER BY created_at DESC и при этом не индексирует архивные заказы.
Покрывающий индекс с INCLUDE для Index-Only Scan: CREATE INDEX idx_orders_user_covering ON orders(user_id, status) INCLUDE (total, created_at);. Запрос SELECT total, created_at FROM orders WHERE user_id = 42 AND status = 'paid' читает данные прямо из индекса без обращения к heap-таблице.
Частичный индекс для фильтра по NULL: CREATE INDEX idx_users_unconfirmed_email ON users(email) WHERE confirmed_at IS NULL;. Если 90% пользователей подтверждены, этот индекс в 10 раз меньше полного и быстрее для поиска неподтверждённых.
GIN для JSONB: CREATE INDEX idx_products_attributes ON products USING GIN (attributes);. После этого WHERE attributes @> '{"color": "red"}' использует GIN вместо seq scan. Для индексирования конкретного ключа: CREATE INDEX idx_products_price ON products ((attributes->>'price')::numeric);.
BRIN для временного ряда событий: CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at) WITH (pages_per_range = 64);. На таблице 500GB этот индекс занимает ~50MB против 50GB для B-tree, при этом эффективен для WHERE created_at BETWEEN '2024-01-01' AND '2024-02-01'.
Создание индекса в продакшене без блокировки: CREATE INDEX CONCURRENTLY idx_payments_user_id ON payments(user_id);. CONCURRENTLY строит индекс в фоне, не блокируя DML на таблице. Занимает дольше (2-3x), но безопасно.
Проверь что индекс не стал INVALID: SELECT indexname, indisvalid FROM pg_indexes JOIN pg_class ON pg_class.relname = indexname WHERE tablename = 'payments';. После CONCURRENTLY indisvalid = false означает что индекс не используется — нужно дропнуть и пересоздать.
Найди неиспользуемые индексы: SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexname NOT LIKE '%_pkey' ORDER BY pg_relation_size(indexrelid) DESC;. Индексы с idx_scan = 0 со времени последнего pg_stat_reset — кандидаты на удаление.
Найди дублирующиеся индексы: SELECT a.indexname AS idx1, b.indexname AS idx2, a.tablename FROM pg_indexes a JOIN pg_indexes b ON a.tablename = b.tablename AND a.indexname < b.indexname AND a.indexdef LIKE b.indexdef || '%' WHERE a.tablename NOT IN ('pg_class', 'pg_index');. Дубли возникают после добавления новых индексов без проверки существующих.
Индекс на выражение для case-insensitive поиска: CREATE INDEX idx_users_email_lower ON users (lower(email));. После этого WHERE lower(email) = lower('User@Example.com') использует индекс. Без этого индекса поиск без учёта регистра — всегда seq scan.
Контролируй размер индексов: SELECT tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;. Индексы больше основной таблицы или невостребованные индексы — первые кандидаты на анализ.

Примеры кода

Составной и покрывающий индекс для типичного OLTP-запроса

-- Типичный запрос: заказы пользователя в определённом статусе
-- SELECT id, total, created_at FROM orders
-- WHERE user_id = 42 AND status = 'paid'
-- ORDER BY created_at DESC LIMIT 20;

-- Плохо: три отдельных индекса
CREATE INDEX idx_orders_user_id  ON orders(user_id);
CREATE INDEX idx_orders_status   ON orders(status);
CREATE INDEX idx_orders_created  ON orders(created_at);

-- Хорошо: один составной покрывающий индекс
-- user_id первым (=), status вторым (=), created_at для ORDER BY
CREATE INDEX idx_orders_user_status_created
  ON orders(user_id, status, created_at DESC)
  INCLUDE (total);

-- Этот индекс покрывает весь запрос через Index-Only Scan:
-- WHERE user_id = 42 → первый ключ
-- AND status = 'paid' → второй ключ
-- ORDER BY created_at DESC → уже отсортировано
-- SELECT total → в INCLUDE, нет обращения к heap

Составной индекс (user_id, status, created_at DESC) покрывает все условия одним index scan. INCLUDE (total) превращает его в покрывающий — PostgreSQL читает данные прямо из индекса. Три отдельных индекса работают хуже: планировщик должен делать Bitmap Index Scan + Bitmap Heap Scan + Sort.

Частичные индексы для типичных фильтров

-- Индекс только на активных пользователей (90% уже deleted)
CREATE INDEX idx_users_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Индекс только на открытые тикеты поддержки
CREATE INDEX idx_tickets_open_created
  ON support_tickets(created_at DESC, priority DESC)
  WHERE status IN ('open', 'in_progress');

-- Индекс для фоновых задач в очереди
CREATE INDEX idx_jobs_pending_scheduled
  ON background_jobs(scheduled_at ASC)
  WHERE status = 'pending';

-- Проверить что планировщик использует частичный индекс:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users
WHERE email = 'user@example.com'
  AND deleted_at IS NULL;
-- Ожидаем: Index Scan using idx_users_active_email

Частичный индекс в 5-10 раз меньше полного если условие WHERE охватывает небольшое подмножество строк. Планировщик использует частичный индекс только когда WHERE запроса совместимо с WHERE индекса. Важно: условие WHERE в запросе должно быть идентично условию в индексе.

BRIN-индекс для временных рядов

-- Таблица событий: 1 миллиард строк, ~500GB
-- Данные вставляются только в конец (append-only)
-- created_at монотонно возрастает

-- B-tree на created_at: ~50GB (10% от данных)
-- BRIN на created_at: ~5MB (!) при pages_per_range=128

CREATE INDEX idx_events_created_brin
  ON events USING BRIN (created_at)
  WITH (pages_per_range = 64);

-- Эффективен для range-запросов
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(amount)
FROM events
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';
-- Ожидаем: Bitmap Index Scan on idx_events_created_brin

-- BRIN НЕ эффективен для точечных запросов
-- WHERE created_at = '2024-01-15 10:30:00' → seq scan лучше BRIN

BRIN хранит min/max для каждого диапазона pages_per_range блоков. При запросе PostgreSQL проверяет, может ли искомое значение находиться в данном диапазоне. Работает только для append-only таблиц где данные физически упорядочены по столбцу. Если вставки хаотичны, BRIN не работает.

Индексы на выражения

-- Case-insensitive поиск по email
CREATE INDEX idx_users_email_ci ON users (lower(email));

-- Теперь этот запрос использует индекс:
SELECT id, name FROM users
WHERE lower(email) = lower('User@Example.COM');

-- Индекс на вычисляемое значение из JSONB
CREATE INDEX idx_products_price_numeric
  ON products ((attributes->>'price')::numeric);

-- Запрос использует индекс:
SELECT name, attributes->>'price' AS price
FROM products
WHERE (attributes->>'price')::numeric > 10000
ORDER BY (attributes->>'price')::numeric DESC;

-- Индекс на year(created_at) для группировки по году
CREATE INDEX idx_orders_year
  ON orders (date_part('year', created_at));

Индекс на выражение создаётся для конкретного вычисления. PostgreSQL использует его когда в WHERE или ORDER BY есть точно такое же выражение. Выражение в запросе должно быть идентично выражению в индексе — lower(email) != Email != LOWER(email) (последнее другой синтаксис).

Диагностика: неиспользуемые и дублирующиеся индексы

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'
  AND indexname NOT LIKE '%_unique'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Отношение seq scan к index scan (много seq scan = возможно нужен индекс)
SELECT
  relname,
  seq_scan,
  idx_scan,
  round(idx_scan * 100.0 / NULLIF(seq_scan + idx_scan, 0), 1) AS idx_pct
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_scan DESC
LIMIT 15;

pg_stat_user_indexes.idx_scan считает сколько раз индекс использовался с момента последнего pg_stat_reset(). idx_scan = 0 после нескольких дней работы под нагрузкой означает, что индекс не нужен. Перед удалением проверь, не является ли он UNIQUE или PK constraint.

Что происходит под капотом

B-tree структура: сбалансированное дерево с высотой O(log N). Каждый узел — страница PostgreSQL (8KB). Листовые узлы содержат значения ключей и heap TID (tuple ID = страница + offset). Для таблицы с 100M строк высота B-tree ~5 уровней → 5 страничных операций на поиск. Размер ~10-30% от данных индексируемого столбца.
Составной B-tree индекс (a, b, c): данные отсортированы сначала по a, затем по b внутри каждого a, затем по c. Правило leftmost prefix: WHERE a=1 использует индекс, WHERE a=1 AND b=2 — использует, WHERE b=2 — не использует (PostgreSQL не может найти нужное место без значения a). WHERE a=1 AND c=3 — использует индекс по a, но не по c.
Hash-индекс: хеш-таблица для equality (=). O(1) для поиска точного совпадения. Не поддерживает: >, <, BETWEEN, LIKE, ORDER BY, IS NULL. PostgreSQL 10+ сделал Hash WAL-safe (раньше не восстанавливался после crash). В большинстве случаев B-tree быстрее Hash из-за page cache locality.
GIN внутри: для каждого 'термина' (ключ JSONB, элемент массива, лексема tsvector) хранит список row ID (posting list). При поиске пересекает posting lists для всех терминов запроса. Медленный UPDATE: при изменении JSONB нужно обновить posting lists для всех изменённых ключей. Для смягчения: gin_pending_list_limit — накапливает изменения в pending list и применяет батчами.
BRIN (Block Range Index): делит таблицу на диапазоны по pages_per_range блоков (дефолт 128). Для каждого диапазона хранит min и max индексируемого столбца. При запросе WHERE created_at > '2024-01-01' PostgreSQL проверяет каждый диапазон: если min > '2024-01-01' или max < '2024-01-01' — диапазон пропускается, иначе — читается. Эффективность зависит от физической корреляции данных с диапазоном — autovacuum вычисляет correlation.
Index-Only Scan: когда все нужные столбцы есть в индексе (ключи + INCLUDE), PostgreSQL не обращается к heap. Но нужно проверить visibility map: если страница помечена как all-visible (все строки видны всем транзакциям), Index-Only Scan безопасен без обращения к heap. Если не помечена — PostgreSQL всё равно идёт в heap для проверки видимости. VACUUM поддерживает visibility map актуальной.
CONCURRENTLY механизм: обычный CREATE INDEX блокирует таблицу на ShareLock (не блокирует SELECT, но блокирует INSERT/UPDATE/DELETE). CREATE INDEX CONCURRENTLY делает два прохода: первый строит индекс по существующим данным, второй добивает изменения, произошедшие во время первого прохода. Не блокирует DML, но занимает 2-3x дольше и более чувствителен к ошибкам транзакций.
Bloat индексов: при UPDATE PostgreSQL помечает старую версию строки как dead и вставляет новую. В индексе появляется запись для новой версии, старая помечается как dead. VACUUM убирает dead tuples из heap и индексов. При интенсивном UPDATE индексы раздуваются (bloat) — занимают больше места, замедляются. REINDEX CONCURRENTLY пересоздаёт индекс без bloat.
GiST (Generalized Search Tree): расширяемый фреймворк для пространственных и нестандартных данных. Используется для: геометрических типов (PostGIS), диапазонных типов, full-text search. Менее эффективен для стандартных типов чем B-tree или GIN, но единственный выбор для геопространственных запросов.
Стоимость обновления индексов: каждый INSERT в таблицу требует вставки в все индексы таблицы. Для таблицы с 10 индексами каждая вставка — 10 дополнительных операций на B-tree. При bulk INSERT (ETL, импорт данных) рекомендуется: DROP нефункциональных индексов, INSERT данные, REINDEX. Это быстрее, чем поддерживать 10 индексов при каждой вставке.

Типичные ошибки и заблуждения

Ошибка: индекс на каждый столбец в таблице — хорошая практика для безопасности. Каждый индекс — дополнительная нагрузка на каждый INSERT, UPDATE, DELETE. Таблица с 15 индексами вместо правильных 5 может иметь вдвое меньший throughput вставки. Индексируй только столбцы, действительно используемые в запросах.
Ошибка: составной индекс (user_id, status) работает для запросов WHERE status = 'paid'. B-tree составной индекс эффективен только если запрос использует leftmost prefix — первый столбец. WHERE status = 'paid' без user_id не использует индекс (user_id, status). Нужен отдельный индекс (status) или поменять порядок.
Ошибка: CREATE INDEX CONCURRENTLY гарантированно безопасен и всегда успешен. CONCURRENTLY может завершиться с ошибкой при конфликте транзакций или уже существующем индексе с тем же именем. В этом случае создаётся INVALID индекс, который не используется, но занимает место. Всегда проверяй: SELECT indexname, indisvalid FROM pg_indexes WHERE tablename = 'your_table'.
Ошибка: большой индекс = медленный индекс. Размер индекса влияет на попадание в кеш, но не напрямую на скорость поиска. B-tree с 1 миллиардом записей имеет высоту ~6, то есть 6 страничных операций на поиск. Проблема больших индексов — не скорость поиска, а объём памяти (shared buffers) для кеширования.
Ошибка: EXPLAIN показывает Seq Scan — значит нет подходящего индекса. Seq Scan может быть правильным выбором: при выборке > 10-20% строк таблицы, для маленьких таблиц (< 1000 строк), при низкой selectivity (одно значение для 80% строк). Планировщик выбирает Seq Scan осознанно на основе статистики — это не всегда баг.
Ошибка: Index Scan всегда быстрее Bitmap Index Scan. Index Scan работает лучше для маленького числа строк (< ~100). Bitmap Index Scan эффективен для среднего числа строк — строит битовую карту совпадений, затем читает heap страницами в физическом порядке (лучше locality). При очень большом числе строк — Seq Scan быстрее обоих.
Ошибка: BRIN-индекс подходит для любой большой таблицы. BRIN работает только если данные физически упорядочены по индексируемому столбцу. Таблица с хаотичными вставками по status или user_id — BRIN будет иметь correlation ~0 и не исключит ни один диапазон блоков, фактически не работая.
Ошибка: удаление индекса требует перезапуска или обслуживания. DROP INDEX CONCURRENTLY удаляет индекс без блокировки DML. Неиспользуемые индексы безопасно удалять в prodакшене командой DROP INDEX CONCURRENTLY idx_name.

Ключевые выводы

B-tree — универсальный выбор для equality, range и sort; GIN — для JSONB, массивов, FTS; BRIN — для append-only временных рядов объёмом TB.
Составной индекс (a, b, c) работает для запросов по prefix (a), (a, b), (a, b, c) — но не по (b) или (c) без a.
INCLUDE превращает индекс в покрывающий: PostgreSQL читает данные из индекса без обращения к heap через Index-Only Scan.
Частичный индекс с WHERE уменьшает размер в 10-100 раз для типичных фильтров (deleted_at IS NULL, status = 'active').
CREATE INDEX CONCURRENTLY — всегда для продакшена; после завершения проверяй indisvalid через pg_indexes.
Неиспользуемые индексы (idx_scan = 0 в pg_stat_user_indexes) — удаляй командой DROP INDEX CONCURRENTLY; они замедляют вставку без пользы.
Индекс на выражение (lower(email), (attributes->>'price')::numeric) покрывает запросы с точно таким же выражением в WHERE.

Термины урока

B-tree: сбалансированное дерево с высотой O(log N); универсальный тип индекса для equality, range queries и сортировки.

GIN (Generalized Inverted Index): инвертированный индекс, хранящий mapping элемент→список строк; оптимален для JSONB, массивов, tsvector.

BRIN (Block Range INdex): компактный индекс, хранящий min/max для диапазонов блоков; эффективен только для физически упорядоченных данных.

Partial index: индекс с условием WHERE, охватывающий подмножество строк таблицы; меньший размер и более точный для типичных запросов.

Covering index (INCLUDE): индекс с дополнительными столбцами в INCLUDE для Index-Only Scan без обращения к heap.

Index-Only Scan: чтение данных прямо из индекса без обращения к heap, возможно если все нужные столбцы в индексе и visibility map помечает страницы как all-visible.

CONCURRENTLY: режим создания/удаления индекса без ShareLock на таблице; DML не блокируется, занимает 2-3x дольше.

Leftmost prefix rule: составной B-tree индекс (a, b, c) используется только если запрос содержит условие на a (первый столбец).

Index bloat: накопление мёртвых записей в индексе после UPDATE/DELETE; устраняется REINDEX CONCURRENTLY.

Functional index: индекс на выражение или функцию от столбца, используемый при точно таком же выражении в запросе.

Связь с работой backend-разработчика

Чеклист правильной индексации при разработке новой таблицы: (1) индексы на все foreign key столбцы — PostgreSQL не создаёт их автоматически; (2) составной индекс на (user_id, created_at DESC) для пагинированных запросов пользователя; (3) частичный индекс WHERE deleted_at IS NULL или WHERE status != 'archived' если soft delete; (4) GIN для JSONB если используешь @> оператор; (5) INCLUDE для часто запрашиваемых столбцов которые не в условиях WHERE. Для продакшен-оптимизации: ежемесячно смотри pg_stat_user_indexes на idx_scan = 0 и удаляй неиспользуемые индексы. После больших нагрузочных тестов запускай REINDEX CONCURRENTLY для таблиц с высоким UPDATE-rate — это убирает bloat и восстанавливает компактность индексов. При добавлении нового индекса в продакшене: ALWAYS использовать CONCURRENTLY, после завершения проверять indisvalid = true через pg_indexes, запускать EXPLAIN ANALYZE для целевых запросов чтобы убедиться что индекс используется.

Мини-разбор реальной ситуации

Таблица orders с 50M строк. Частый запрос в дашборде: SELECT id, total, status, created_at FROM orders WHERE user_id = ? AND status IN ('paid', 'shipped') ORDER BY created_at DESC LIMIT 20. Время: 4.2 секунды. Seq scan таблицы 50M строк. Анализ через EXPLAIN ANALYZE: нет подходящего индекса, планировщик делает Seq Scan + Filter + Sort. Решение 1: CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status, created_at DESC). Время: 8ms, Index Scan, никакой сортировки (уже в нужном порядке). Дополнительно обнаружили: старый индекс idx_orders_created_at (одиночный на created_at) не использовался 3 месяца — idx_scan = 0. DROP INDEX CONCURRENTLY idx_orders_created_at — освободили 12GB. Итог: запрос в 525x быстрее + 12GB свободного места + ускорение вставки новых заказов.

Что запомнить

Leftmost prefix: составной индекс (a, b, c) работает для (a), (a, b), (a, b, c) — не для (b) или (b, c).
INCLUDE даёт Index-Only Scan — нет обращения к heap; добавляй столбцы из SELECT в INCLUDE составных индексов.
Всегда CONCURRENTLY при создании индексов в продакшене; после — проверяй indisvalid = true.
Регулярно проверяй pg_stat_user_indexes на idx_scan = 0; удаляй неиспользуемые индексы через DROP INDEX CONCURRENTLY.
Частичный индекс с WHERE — в 10-100 раз меньше полного для типичных фильтров; используй для soft delete и статусных полей.
BRIN только для append-only таблиц с физически упорядоченными данными (временные ряды, логи); иначе неэффективен.

Итог

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