Прямой ответ
Индекс — отсортированная копия ключевых колонок, по которой Postgres находит строки без полного сканирования таблицы; базовый тип B-tree закрывает 90% задач (равенство, диапазоны, сортировка) и создаётся одной командой: CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id) (CONCURRENTLY — без блокировки записи на живой базе). Три решения, которые разработчик принимает осознанно: какие колонки — индексируем то, что реально встречается в WHERE/JOIN/ORDER BY (не «на всякий случай»: каждый индекс замедляет INSERT/UPDATE и ест диск); в каком порядке в составном индексе — сначала колонки равенства, потом диапазона ((status, created_at) обслуживает status='new' AND created_at > ..., обратный порядок — плохо); какого типа — GIN для JSONB/массивов/полнотекста, частичный индекс для редких статусов (WHERE status='new' — индекс на 5% таблицы вместо 100%), функциональный для выражений (lower(email)). И обязательная гигиена: pg_stat_user_indexes показывает реально используемые индексы — нечитаемые месяцами удаляются (планы запросов объясняют, почему планировщик их игнорирует).
Как работает B-tree и когда индекс не спасает
B-tree хранит значения отсортированными со ссылками на строки: поиск равенства — спуск по дереву за log(N), диапазон — спуск к границе и последовательное чтение соседей (отсюда «индекс помогает и равенству, и >, <, BETWEEN, ORDER BY»). Когда индекс не используется (видно в EXPLAIN): выборка слишком большая (нужно 40% таблицы — последовательное сканирование дешевле случайных чтений по индексу); колонка неселективна (пол status с двумя значениями 50/50 — деревом не отсеять); выражение над колонкой (WHERE year(created_at)=2026 — b-tree знает значения created_at, не года — нужен функциональный индекс или переписывание на диапазон); приведение типов (WHERE phone = 123 при текстовой колонке — неявный cast убивает индекс). Отдельный класс — покрытие нужд сортировки: ORDER BY created_at DESC LIMIT 20 с индексом (created_at DESC) читает 20 готовых строк вместо сортировки миллиона (сортировки в планах); пагинация OFFSET 10000 при этом всё равно медленна (пропуск тысяч строк) — лечится keyset-пагинацией (WHERE id > last_id ORDER BY id LIMIT 20 — индекс делает каждый шаг одинаково дешёвым).
Составные индексы: порядок колонок решает
Составной индекс (a, b, c) — сортировка по a, внутри a — по b, внутри b — по c; запрос может использовать префикс: (a), (a, b), (a, b, c) — но не «хвост» (b, c) без a. Практические правила: равенство раньше диапазона — (tenant_id, status, created_at) обслуживает tenant=X AND status='new' AND created_at>... полностью, а (tenant_id, created_at, status) — только до диапазона; селективность — колонка, по которой фильтруют почти всегда (tenant_id), первой; сортировка учитывается (ORDER BY created_at использует индекс, если колонки равенства фиксированы константами). Сколько индексов на таблицу — разумный коридор 3–6: каждый пишется при каждом INSERT/UPDATE (таблица заказов с 12 индексами вставляет заметно медленнее), место и кеш-память не бесконечны. Дубли и перекрытия ловятся взглядов на список (\d таблица): (user_id) при существующем (user_id, created_at) — лишний (префикс уже покрыт) — удаление экономит запись без потери чтения.
Специальные виды: частичные, функциональные, GIN
Частичный индекс — индекс с условием: CREATE INDEX ON orders (created_at) WHERE status='new' — содержит только «новые» заказы (5% строк): мал, быстр, идеален для рабочих очередей и «активных» срезов; условие в запросе должно совпадать (планировщик сопоставляет предикат). Функциональный (expression) индекс — по значению выражения: CREATE INDEX ON users (lower(email)) — запрос с тем же выражением использует индекс; типовой набор — lower для регистронезависимого поиска, date(created_at) для группировки по дням (хотя чаще лучше диапазонный WHERE). GIN — inverted index для составных значений: JSONB (CREATE INDEX ON events USING gin (data jsonb_path_ops) — запросы @>, ?), массивы (ANY/&&), полнотекстовый tsvector (поиск); GIN дороже при записи и не обслуживает диапазоны/сортировки — инструмент для «содержит», не для «больше». UNIQUE — ограничение целостности, которое одновременно индекс (бизнес-уникальность живёт в БД, а не только в коде приложения — гонка двух параллельных вставок ловится только там — идемпотентность). BRIN — нишевый: огромные append-only таблицы с физически упорядоченными данными (логи по времени) — индекс в тысячи раз меньше b-tree.
Практический сценарий: лента заказов, которая тормозила
Симптом: страница «заказы клиента» (фильтр по статусу + сортировка по дате, LIMIT 50) на 4M строк грузится 4 секунды. Разбор: (1) EXPLAIN ANALYZE — Seq Scan + Sort external merge — индекса нет вовсе; (2) наивный индекс (user_id) — 0.9 с: строки клиента нашлись быстро, но сортировка по created_at осталась (сотни заказов на клиента сортируются в памяти — уже терпимо, но можно лучше); (3) составной (user_id, created_at DESC) — 40 мс: индекс отдаёт строки клиента сразу отсортированными, LIMIT 50 читает 50 позиций дерева; (4) добавляем «активные заказы» — частичный (user_id, created_at DESC) WHERE status IN ('new','processing') — рабочий экран большинства пользователей читает маленький индекс; (5) проверка записи: INSERT замедлился на ~10% (три индекса вместо одного) — приемлемо для read-heavy экрана; (6) гигиена через месяц: pg_stat_user_indexes — idx_scan наивного (user_id) = 0 (составной его заменил) — удалён. Миграция индексов — через alembic с CREATE INDEX CONCURRENTLY в отдельной нетранзакционной миграции (CONCURRENTLY не работает внутри транзакции — типовой грабель). Итог: 100x на главном экране ценой двух строк DDL — но только после чтения плана, а не по наитию.
FAQ
Сколько индексов — слишком много?
Сигналы не в количестве, а в эффекте: INSERT/UPDATE деградирует (замерьте до/после), pg_stat_user_indexes показывает нулевые idx_scan месяцами, дубли-префиксы не удалены; 10+ индексов на горячей таблице — повод для ревизии, 3–6 рабочих — норма.
Когда индекс замедляет запрос, а не ускоряет? Планировщик может выбрать индексный путь ошибочно (устаревшая статистика — расхождение оценок и факта в плане — EXPLAIN-разбор); лечится ANALYZE таблицы, а не удалением индекса; принудительные хинты в Postgres отсутствуют намеренно — исправляют данные для планировщика, не план.
Поможет ли индекс для LIKE '%текст%'?
B-tree — нет (префиксный поиск LIKE 'текст%' — да, с text_pattern_ops для не-C-локалей); для произвольных подстрок — GIN с pg_trgm (триграммный индекс) — детали в статье про полнотекст.
Нужны ли индексы на внешних ключах? Postgres не создаёт их автоматически (в отличие от некоторых СУБД), а нужны они часто: JOIN по FK и каскадные удаления ищут дочерние строки — без индекса каждое удаление родителя сканирует дочернюю таблицу; правило: FK-колонка, по которой идут JOIN'ы или каскады, — с индексом.
Источники: документация PostgreSQL (index types, partial indexes), Use The Index Luke — ссылки в шапке.