Проектирование индекса под нагрузку

Держи в голове одну картинку: B-tree-индекс - это данные, отсортированные по ключу, плюс ссылки на строки. Из этой картинки следует всё остальное.

Отсортированный список даёт три вещи бесплатно: быстрый поиск по значению (бинарный), быстрый поиск по диапазону (соседние записи рядом) и готовый порядок для ORDER BY (данные уже упорядочены). Чего он не даёт: искать по тому, что не в начале ключа. Если список отсортирован по фамилии, найти человека по имени он не помогает - имена разбросаны по всему списку.

Эта модель объясняет и составные индексы, и ORDER BY-оптимизацию, и sargability. Дальше просто применяем её к случаям.

Индекс (a, b, c) сортирует записи сначала по a, при равенстве - по b, при равенстве - по c. Как телефонный справочник: сначала фамилия, потом имя, потом отчество.

Отсюда правило префикса: индекс помогает запросам, которые фиксируют ключ слева направо без пропусков:

• WHERE a = 1 - да (префикс длины 1);
• WHERE a = 1 AND b = 2 - да (префикс длины 2);
• WHERE a = 1 AND b = 2 AND c = 3 - да (полный ключ);
• WHERE b = 2 - нет: b не в начале, записи с b = 2 разбросаны по всему индексу;
• WHERE a = 1 AND c = 3 - частично: поможет по a, c отфильтрует уже прочитанное.

Поэтому порядок колонок - главное решение в составном индексе. Колонки с равенством ставят раньше, колонку с диапазоном или сортировкой - в конец. Подробнее - в index-design.

Раз индекс отсортирован, он может отдать строки сразу в нужном порядке - без отдельного шага Sort. Это особенно ценно с LIMIT:

-- частый паттерн: последние записи в ветке

SELECT * FROM repos

WHERE forked_from_id = 42

ORDER BY updated_at DESC

LIMIT 10;

Индекс (forked_from_id, updated_at DESC) обслуживает это идеально: по forked_from_id = 42 он находит нужный участок, а внутри него записи уже отсортированы по updated_at DESC - бери первые десять и стоп. Без такого индекса план - Seq Scan плюс Sort всей выборки, и его стоимость на порядки выше. Это типичный кейс «было 2.8M cost, стало 80»: добавили правильный составной индекс - и сортировка исчезла.

Два приёма, сокращающих работу ещё сильнее.

Покрывающий индекс кладёт в лист недостающие колонки через INCLUDE, чтобы запрос обслуживался Index-Only Scan без обращения к таблице (см. главу 32):

CREATE INDEX ON repos (forked_from_id, updated_at DESC) INCLUDE (name);

Частичный индекс индексирует не всю таблицу, а только строки, проходящие условие:

CREATE INDEX ON tasks (created_at) WHERE status = 'active';

Если 99% задач завершены, а запросы всегда про активные, частичный индекс в разы меньше полного и быстрее - в нём только нужные строки. Он же снижает цену записи: строки вне условия индекс не трогают.

Когда запрос фильтрует по результату функции, обычный индекс по колонке не подходит (условие не sargable). Решение - индекс по тому же выражению:

-- запрос: WHERE lower(email) = 'a@b.c'

CREATE INDEX ON users (lower(email));

Теперь выражение lower(email) проиндексировано, и запрос его использует. Индекс по выражению вычисляет функцию при вставке и хранит результат - поэтому функция должна быть IMMUTABLE (давать один и тот же результат для одних входов). Тот же приём - для (created_at::date), json-извлечений, конкатенаций. Связь с sargability - в sargability.

У индексов есть обратная сторона: каждый из них обновляется на каждый INSERT, и на UPDATE неиндексируемых колонок тоже (если это не HOT-update). Пять индексов на таблице - это пятикратная работа на запись. Индекс, который не используется ни одним запросом, - чистый убыток: занимает место, тормозит запись, ничего не ускоряет.

Найти такие легко:

SELECT relname, indexrelname, idx_scan

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 за разумный период работы значит «индексом ни разу не воспользовались». Такие индексы - кандидаты на удаление. То же с дублирующими: два индекса с одинаковым набором колонок в одном порядке - один лишний.

Соблазн «навешать индексов на всякий случай» наказывается дважды. Первый раз - записью: больше индексов, медленнее INSERT/UPDATE. Второй раз - планированием. Планировщик рассматривает все применимые индексы, и на таблице с десятками индексов (особенно частичных) время планирования заметно растёт - он перебирает больше вариантов на каждый запрос.

Крайний случай - «тысяча частичных индексов» под каждый частный запрос: запись еле ползёт, а планирование само по себе становится узким местом. Правило простое: индекс заводят под доказанную потребность (запрос в плане берёт Seq Scan там, где должен брать индекс), а не впрок. Меньше индексов, но точных, почти всегда лучше, чем много на всякий случай.

Собираем часть VII в порядок решений при создании индекса.

1. Какой оператор в условии? Это задаёт тип индекса через operator class: =/диапазон/ORDER BY - B-tree; @>/полнотекст - GIN; пересечение/гео - GiST; огромный append-only по диапазону - BRIN.
2. Условие sargable? Колонка не должна быть под функцией; иначе индекс по выражению.
3. Несколько колонок? Порядок: равенство раньше, диапазон/сортировка в конец; правило префикса.
4. Можно покрыть запрос? INCLUDE под Index-Only Scan.
5. Запрос всегда по подмножеству? Частичный индекс с WHERE.
6. Не во вред ли записи? Проверь, что индекс реально используется (pg_stat_user_indexes), удали неиспользуемые и дубли.

Этот чеклист - прикладной итог всей части. В капстоуне он соберётся с диагностикой планов и статистики в единый алгоритм оптимизации.