Выбор индексов в базах данных для highload‑систем

Введение

Индексы — это «ускорители» доступа к данным в базах данных. Правильно выбранные индексы могут многократно ускорить запросы, что особенно критично в highload‑системах с большими объёмами данных и большим числом запросов. Однако за ускорение чтения приходится платить замедлением записи, дополнительным местом и дополнительной работой вакуума.

В этой статье мы разберём, как работают разные типы индексов, как выбирать индекс под конкретный запрос, обсудим подводные камни (блоат, переиндексация, избыточные индексы), затронем специфику MySQL и индексацию в NoSQL — MongoDB и Cassandra — и завершим чеклистом. Каждое число ниже получено замером, а не оценкой на глаз, и несколько распространённых рекомендаций эти замеры не пережили.

Сразу про границы. Все четыре стенда — одна машина, 4 CPU и 8 ГБ памяти:

СУБД	Конфигурация	Данные
PostgreSQL 17.11	shared_buffers=1GB, random_page_cost=4	payments, 20 млн строк (heap 2404 МиБ), отдельная таблица 2 млн строк для текстового поиска
MySQL 8.0.46	InnoDB, innodb_buffer_pool_size=128M	2 млн строк
MongoDB 7.0.40	WiredTiger cache 1 ГиБ	коллекция 1 млн документов, отдельная 200 тыс. для массивов
Cassandra 5.0.9	кластер из 3 узлов, heap 768 МиБ на узел, RF=1, CONSISTENCY ONE	200 тыс. строк в 20 тыс. партиций

Настройки памяти и объёмы данных у стендов разные намеренно: каждая СУБД проверяется против собственной базовой линии, а не против соседней. Поэтому сравнивать между строками разных СУБД нечего — там разные таблицы и разные запросы. Смысл несут соотношения внутри одного стенда. Буфер пула MySQL намеренно мал относительно данных: так видно работу с диском, а не скорость памяти.

Каждый запрос выполнялся 3–5 раз после прогрева (в Cassandra — 10–12), приводится медиана. Кэш тёплый, конкурентной нагрузки нет — переносить на своё железо стоит соотношения, а не абсолютные значения.

CREATE TABLE payments (
    id          bigserial PRIMARY KEY,
    user_id     integer       NOT NULL,
    type        text          NOT NULL,   -- purchase 70% / payout 20% / refund 5% / fee 5%
    status      text          NOT NULL,
    amount      numeric(12,2) NOT NULL,
    bucket      smallint      NOT NULL,   -- заполнена случайно, корреляция ~0
    created_at  timestamptz   NOT NULL,   -- растёт вместе с порядком вставки, корреляция 1.0
    tags        text[]        NOT NULL
);

Две последние колонки специально сделаны противоположными по физическому порядку. Дальше будет видно, что именно это, а не проценты отбора, решает судьбу индекса.

Типы индексов в реляционных СУБД и их влияние на производительность

Развилки — из замеров ниже: корреляция колонки решает спор B‑tree против BRIN, а GiST для триграмм проигрывает GIN и по размеру, и по сборке.

По умолчанию используется B‑tree — он покрывает большинство случаев. Но существуют и другие виды индексов.

B‑дерево (B‑Tree) — основной тип индекса. PostgreSQL использует B±дерево: данные лежат только в листьях, листья связаны в список. Свойства, ради которых его берут:

• Точечный поиск — несколько спусков по дереву.

• Диапазонный запрос — последовательный проход по связанным листьям.

• Сортировка — если порядок выборки совпадает с порядком индекса, Sort не нужен вовсе. Читать индекс можно в обе стороны, поэтому для простого ORDER BY x DESC отдельный DESC‑индекс не требуется — он нужен только для смешанных направлений вида ORDER BY a ASC, b DESC.

• min/max вырождаются в один спуск: SELECT max(created_at) FROM payments на 20 млн строк даёт 0.037 мс по индексу против 723 мс полным сканом.

• JOIN — при соединении индекс на внутренней таблице позволяет быстро находить соответствия, избегая полного сканирования.

С PostgreSQL 13 в B‑tree работает дедупликация повторяющихся ключей, и индексы по низкоселективным колонкам стали компактнее. Она не применяется к индексам с INCLUDE, а также к numeric, float, jsonb и контейнерным типам.

Хеш‑индекс поддерживает только операции равенства — для диапазонов и сортировки не годится. Компактнее ли он B‑tree — зависит от ширины ключа, причём в обе стороны. Замер на 5 млн строк:

Ключ	B‑tree	Hash	Разница	Поиск B‑tree	Поиск Hash
bigint	107.1 МиБ	128.0 МиБ	+19.5%	0.047 мс	0.053 мс
uuid	150.4 МиБ	128.0 МиБ	−14.9%	0.039 мс	0.043 мс
text, 110 байт	676.8 МиБ	128.0 МиБ	−81.1%	0.058 мс	0.043 мс

Хеш‑индекс хранит 4-байтовый хеш, а не сам ключ, поэтому его размер почти не зависит от ширины ключа — 128 МиБ во всех трёх случаях. На bigint он проигрывает B‑tree, на длинном тексте выигрывает в 5.3 раза. По скорости не выигрывает нигде: все точечные поиски укладываются в 0.04–0.06 мс. Ограничения: не бывает уникальным и многоколоночным, не даёт index‑only scan и сортировки, не хранит NULL. Пригоден для продакшена с PostgreSQL 10, когда стал WAL‑логируемым. Реальный аргумент за него — не размер, а то, что B‑tree отказывается индексировать ключи больше примерно трети страницы, а хешу ширина ключа безразлична.

GIN (Generalized Inverted Index) предназначен для случаев, когда одна запись содержит много значений: массив, JSON, документ для полнотекстового поиска. Размер и медленная вставка у него известны, но по highload сильнее бьёт другое. У GIN есть параметр fastupdate (по умолчанию включён): новые записи складываются в неотсортированный pending list и мержатся в дерево позже — при переполнении gin_pending_list_limit (по умолчанию 4 МБ) или при вакууме. Замер на таблице 2 млн строк, вставка 300 тыс. строк:

Режим	Вставка	строк/с	Поиск tags @> ARRAY['t5']
fastupdate = on (по умолчанию)	661 мс	453 957	40.2 мс
fastupdate = off	1 619 мс	185 353	39.2 мс
B‑tree по id — для масштаба	406 мс	738 189	-

Отключение pending list делает вставку в 2.45 раза дороже, а GIN даже в самом дешёвом режиме стоит 1.6 B‑tree. Но список — это отложенная работа: она не исчезает, а выстреливает позже, в момент мержа, и большой список замедляет ещё и поиск. Чистить список надо фоном или вручную через gin_clean_pending_list(), а форграундной чистки избегать увеличением gin_pending_list_limit.

GiST (Generalized Search Tree) — гибкая структура для геометрии, диапазонных типов (tsrange), запросов «ближайший сосед» и exclusion‑ограничений. Как альтернатива GIN для триграмм он на замере не равноценен. Таблица 2 млн строк, запрос body LIKE '%eclin%':

Индекс	Сборка	Размер	Использован планировщиком	Время
нет	-	-	-	126.2 мс
GIN gin_trgm_ops	22.9 с	304 МиБ	да	66.0 мс
GiST gist_trgm_ops	97.9 с	856 МиБ	нет — выбран seq scan	119.6 мс

Сборка в 4.3 раза дольше, размер в 2.8 раза больше, и индекс не был использован. Для триграммного поиска по тексту берите GIN.

BRIN (Block Range Index) — упрощённый индекс по блокам. Хранит сводку по диапазону страниц и потому крошечный. Условие применимости ровно одно: физическая корреляция колонки должна быть близка к 1. Замер на одинаковом объёме выборки (около 1% таблицы):

Колонка	Корреляция	Размер BRIN	Запрос по BRIN	Тот же запрос seq scan	Планировщик взял BRIN
created_at	+1.0	0.094 МиБ	30.5 мс	343.4 мс	да
bucket	≈ 0	0.070 МиБ	715.5 мс	315.8 мс	нет

На коррелированной колонке индекс размером 96 килобайт даёт ускорение в 11 раз — при том что B‑tree по той же колонке занимает 428 МиБ. На некоррелированной он в 2.3 раза медленнее полного скана. Ещё пара деталей. «Хранит min/max» верно только для опкласса minmax, а autosummarize по умолчанию выключен — несуммаризованный диапазон всегда считается подходящим и всегда читается.

Прочие: SP‑GiST для точек и префиксов текста, bloom‑фильтры для широких ad‑hoc фильтров. Применяются в узких случаях.

Модификаторы на практике решают больше, чем экзотические типы:

• частичный индекс — CREATE INDEX … WHERE условие. В примере ниже он оказался в 4.4 раза меньше полного аналога. Самый выгодный паттерн — WHERE колонка IS NOT NULL на разреженных колонках: в PostgreSQL B‑tree индексирует NULL, в отличие от Oracle, поэтому паттерн часто не замечают.

• индекс по выражению — CREATE INDEX … ((выражение)), выражение обязано быть IMMUTABLE.

• покрывающий — INCLUDE (...) с PostgreSQL 11.

• fillfactor — от него напрямую зависит, будет ли UPDATE трогать индексы.

Вывод по типам индексов: в большинстве ситуаций B‑Tree остаётся оптимальным выбором — он универсален и ускоряет равенство, диапазоны, сортировку и соединения. Специализированные индексы нужны для специфических запросов, а хеш‑индекс — это инструмент экономии места на длинных ключах, а не ускоритель.

Подбор индекса под конкретные запросы

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

У условия есть три разные роли:

• Access‑предикат сужает диапазон обхода: спуск по дереву сразу попадает в нужное место.

• Index‑фильтр проверяется на каждой прочитанной записи индекса — heap не трогается, но и обход не сужается.

• Table‑фильтр проверяется уже на строке, вытащенной из heap.

Здесь легко ошибиться, потому что Index Cond в плане — это не «то, по чему индекс спускается». Это все условия, протолкнутые внутрь индексного метода, то есть access‑предикаты и index‑фильтры вместе, а PostgreSQL их в выводе не различает. Таблица 1 млн строк, индекс (a, b, c):

-- WHERE a=1 AND b=42 AND c=142   (полный префикс: все три - access-предикаты)
Index Only Scan using ic_abc
  Index Cond: ((a = 1) AND (b = 42) AND (c = 142))
  Buffers: shared hit=2 read=1            <-- 3 буфера, 0.037 мс

-- WHERE a=1 AND c=42              (b пропущена: c - уже index-фильтр)
Index Only Scan using ic_abc
  Index Cond: ((a = 1) AND (c = 42))
  Buffers: shared read=386                <-- 386 буферов, 1.326 мс

Строки Index Cond выглядят одинаково, а разница — в 129 раз по буферам и в 36 раз по времени. А Filter появляется в плане только тогда, когда предикат нельзя проверить в индексе вообще:

-- WHERE a=1 AND c=42 AND pad LIKE 'zz%'   (колонки pad в индексе нет)
Index Scan using ic_abc
  Index Cond: ((a = 1) AND (c = 42))
  Filter: (pad ~~ 'zz%'::text)

Отличить access‑предикат от index‑фильтра можно, только сверив условие с определением индекса и посмотрев на Buffers. С PostgreSQL 18 появился прямой индикатор — счётчик Index Searches.

Колонки индекса образуют лексикографический порядок, поэтому access‑предикатом может быть цепочка равенств плюс один диапазон на её конце. Всё, что стоит в индексе после диапазона, обходу уже не помогает.

Индексы для фильтрации (WHERE)

Самый частый сценарий — ускорение фильтрации в WHERE. Правила выбора индекса:

Селективность решает не всё, и проценты — не тот критерий. Ходовые пороги выглядят «меньше 1–5% строк — индекс обязателен, 50% и больше — бесполезен». Они не учитывают главного: как данные лежат физически. Один и тот же запрос‑агрегат по двум колонкам с одинаковым процентом отбора, но противоположной корреляцией:

Доля таблицы	bucket: индекс	bucket: seq scan	кто быстрее	created_at: индекс	created_at: seq scan	кто быстрее
0.1%	8.0 мс	325.9 мс	индекс ×41	2.5 мс	334.9 мс	индекс ×132
0.5%	80.1 мс	336.0 мс	индекс ×4.2	12.5 мс	340.3 мс	индекс ×27
1%	300.4 мс	338.9 мс	индекс ×1.1	11.5 мс	344.7 мс	индекс ×30
2%	863.3 мс	332.6 мс	seq ×2.6	20.3 мс	338.3 мс	индекс ×17
5%	569.3 мс	327.7 мс	seq ×1.7	47.0 мс	331.7 мс	индекс ×7.1
10%	654.6 мс	349.2 мс	seq ×1.9	90.8 мс	350.8 мс	индекс ×3.9
20%	780.8 мс	402.3 мс	seq ×1.9	193.2 мс	399.4 мс	индекс ×2.1
30%	919.3 мс	452.2 мс	seq ×2.0	278.4 мс	452.6 мс	индекс ×1.6
50%	1 177.5 мс	560.1 мс	seq ×2.1	630.3 мс	559.9 мс	seq ×1.1

Посмотрите на строку «20%»: одна и та же доля таблицы, но по некоррелированной колонке индекс в 1.9 раза медленнее полного скана, а по коррелированной — в 2.1 раза быстрее. Процент отбора об этой разнице не знает ничего.

Правильный ориентир: по некоррелированной колонке перелом наступает между 1% и 2%, по коррелированной индекс выигрывает вплоть до 30% таблицы. Смотреть надо на correlation в pg_stats. С оговорками: это одно число на всю колонку, поэтому данные, сгруппированные внутри тенанта, но чередующиеся глобально, покажут корреляцию около нуля. И корреляцию можно создать — CLUSTER или pg_repack --order-by физически переупорядочивают таблицу.

Планировщик на этих данных систематически ошибается. На некоррелированной колонке он выбирал bitmap‑план на целом порядке селективности, хотя полный скан был быстрее:

Доля таблицы	Что выбрал планировщик	Его время	Время seq scan	Насколько хуже
2%	bitmap	861.8 мс	332.6 мс	×2.6
5%	bitmap	575.2 мс	327.7 мс	×1.8
10%	bitmap	654.3 мс	349.2 мс	×1.9
20%	bitmap	801.1 мс	402.3 мс	×2.0
30%	bitmap	915.8 мс	452.2 мс	×2.0
50%	seq scan	568.0 мс	560.1 мс	верно

Это практический аргумент против «раз индекс есть, значит план оптимальный»: от 2% до 30% отбора выбирался план вдвое медленнее оптимального.

Интересно и то, что время индексного плана немонотонно: на 2% — 863 мс, а на 5% — уже 569 мс, то есть больше строк отработалось быстрее. Объяснение видно в счётчике прочитанных блоков (в таблице примерно 307 700 страниц):

Доля таблицы	Прочитано блоков	Время
0.5%	0 — всё в кэше	80.1 мс
1%	46 529	300.4 мс
2%	222 588	863.3 мс
5%	297 282	569.3 мс
50%	316 129	1 177.5 мс

Bitmap Heap Scan читает страницы в физическом порядке. На 2% битмап покрывает 72% страниц вразнобой — это худший случай, много почти случайных чтений. На 5% и выше он покрывает уже почти все страницы, и чтение вырождается в фактически последовательное, которое дешевле в пересчёте на страницу. Худшая точка индексного плана — не максимум селективности, а её середина.

Про random_page_cost: снижение с 4 до 1.1 — типичная настройка для NVMe — точку переключения планировщика на этих данных не сдвинуло вовсе, переход на seq scan в обоих случаях произошёл только на 50%. Понижение этого параметра делает индексный план дешевле, то есть могло лишь оттянуть переход, а при effective_cache_size, сопоставимом с размером таблицы, планировщик и так считает, что почти всё лежит в кэше.

Если условие по одному столбцу (WHERE status = 'ACTIVE') — индексируем этот столбец. Если в условии есть функция или выражение над столбцом, обычный индекс не поможет:

Запрос	План	Время
WHERE (created_at AT TIME ZONE 'UTC')::date = '2024-12-13'	Seq Scan	598.2 мс
WHERE created_at >= '2024-12-13 00:00:00+00' AND created_at < '2024-12-14 00:00:00+00'	Index Scan	1.44 мс
индекс по выражению ((created_at AT TIME ZONE 'UTC')::date)	Index Scan	1.66 мс

Переписывание в диапазон даёт ×415 и не требует нового индекса — это почти всегда правильный первый ход. Смещение в литерале обязательно: created_at >= '2024-12-13' будет истолковано в таймзоне сессии, и вы тихо получите другие сутки. А наивный вариант индекса вообще не создастся:

CREATE INDEX ON payments ((created_at::date));
-- ERROR: functions in index expression must be marked IMMUTABLE

Приведение timestamptz → date зависит от TimeZone сессии, поэтому рабочая форма — с явной таймзоной.

Комбинация нескольких условий (AND/OR). Распространённое правило «один составной индекс всегда лучше двух отдельных» верно ровно наполовину:

Запрос	Два одноколоночных индекса	Один составной (bucket, user_id)
bucket = 7 AND user_id BETWEEN 1000 AND 200000	BitmapAnd, 80.8 мс	Index Only Scan, 0.31 мс
bucket = 7 OR user_id = 12345	BitmapOr, 15.3 мс	195.7 мс

На AND составной индекс быстрее в 261 раз. На OR — наоборот, два отдельных индекса быстрее в 12.8 раза: OR по разным колонкам это ровно тот случай, ради которого bitmap‑планы и существуют, и составной индекс его не покрывает.

Порядок столбцов в составном индексе имеет значение, и он не симметричен. Индексы (A,B) и (B,A) не взаимозаменяемы. Запрос WHERE bucket = 7 (19 831 строка из 20 млн):

Индекс	Размер	Время
(status, bucket)	133.1 МиБ	28.8 мс — полный проход по индексу
(bucket, status)	133.0 МиБ	1.4 мс
(bucket)	132.6 МиБ	1.36 мс
без индекса	-	329.7 мс

Разница в 20.5 раза на одном наборе колонок и практически одинаковом размере. При этом «индекс не будет использован» — неточная формулировка: PostgreSQL взял индекс с неподходящим ведущим столбцом и прошёл его целиком, что оказалось в 11 раз лучше seq scan и в 20 раз хуже правильного индекса. Такой полный проход планировщик выбирает, когда индекс существенно дешевле таблицы (здесь 133 МиБ против 2404 МиБ). По‑настоящему избыточная пара — это (bucket) при наличии (bucket, status): 1.36 против 1.4 мс, разница в пределах шума.

Правило левого префикса перестало быть абсолютным. Классическое «индекс (a, b) не поможет запросу только по b» верно до PostgreSQL 17 включительно. В PostgreSQL 18 B‑tree научился skip scan. Одинаковый скрипт, 5 млн строк, ведущая колонка с 5 различными значениями, запрос по второй колонке:

Версия	План	Буферов	Время
PostgreSQL 17.11	Parallel Seq Scan	46 729	46.1 мс
PostgreSQL 18.6	Index Only Scan, Index Searches: 7	22	0.018 мс
PG 17, отдельный индекс по второй колонке	Index Only Scan	-	0.038 мс
PG 18, отдельный индекс по второй колонке	Index Only Scan	-	0.040 мс

Skip scan практически догнал выделенный индекс, не создавая его. Работает, пока ведущая колонка низкоселективна: Index Searches: 7 показывает механизм — планировщик делает отдельный спуск на каждое её значение, и при тысячах различных значений приём вырождается. Правило левого префикса остаётся хорошим ориентиром, но при переходе на PG 18 часть «лишних» индексов действительно становится лишней, и это стоит перепроверить замером.

Поиск по шаблону — три разных случая, которые нельзя путать.

Префикс LIKE 'abc%': B‑tree подходит, но не в любой коллации. 3 млн строк, одинаковые данные в двух колонках:

Колонка и индекс	План	Время
COLLATE "en-US-x-icu", обычный btree	Seq Scan	57.6 мс
COLLATE "C", обычный btree	Index Only Scan	0.049 мс
COLLATE "en-US-x-icu" + text_pattern_ops	Index Only Scan	0.045 мс

Разница в 1280 раз, и вся она в классе операторов. Цена у этого есть: индекс с text_pattern_ops не обслуживает обычные <, > и ORDER BY в коллации колонки, поэтому колонке, которой нужны и префиксный поиск, и диапазоны, придётся держать два индекса. ILIKE 'p%' через него тоже не работает — нужен индекс по lower(col) text_pattern_ops.

Подстрока LIKE '%abc%': полнотекстовый индекс здесь не поможет никогда. GIN по to_tsvector, 2 млн строк:

Запрос	Индекс использован	Время
to_tsvector(body) @@ to_tsquery('declined')	да	47.8 мс
body LIKE '%eclin%'	нет	111.7 мс
body LIKE '%declined%' — целое слово	нет	107.4 мс

FTS‑индекс отвечает только на оператор @@ по tsvector и не используется для LIKE даже при поиске целого слова. Нужны триграммы — но и они не универсальны:

Шаблон	Совпадений	Seq Scan	GIN trgm	Ускорение
%e10adc3949%	1	129.7 мс	2.6 мс	×49
%eclin%	400 000	126.2 мс	66.1 мс	×1.9

Триграммный индекс окупается на селективных подстроках. Если шаблон находит пятую часть таблицы, вы платите 304 МиБ и 23 секунды сборки за двукратное ускорение. Шаблонам короче трёх символов он не помогает вовсе.

Покрывающие индексы. Если запрос выбирает только столбцы, присутствующие в индексе, СУБД может выполнить его, не обращаясь к основной таблице — это Index Only Scan. Но есть условие, о котором почти не пишут: PostgreSQL обязан убедиться, что строка видима, и это дёшево только если страница помечена all‑visible в visibility map. Карту проставляет VACUUM.

Состояние таблицы (5 млн строк)	План	Heap Fetches	Буферов	Время
после загрузки, до VACUUM	Bitmap Heap Scan	-	4 608	6.943 мс
после VACUUM	Index Only Scan	0	23	0.487 мс
после обновления 1% строк	Index Only Scan	5 112	5 135	7.397 мс
после повторного VACUUM	Index Only Scan	0	23	0.500 мс

Первые две строки показывают, что до вакуума планировщик вообще не выбирает index‑only scan — карта видимости не проставлена, и он идёт через heap.

Обновление одного процента строк замедлило запрос в 14.9 раза, не изменив ни план, ни индекс. Index‑only scan — не константа, а функция от того, успевает ли автовакуум. Настраивается это пер‑таблично: autovacuum_vacuum_scale_factor по умолчанию 0.2, то есть таблица на 20 млн строк ждёт около 4 млн мёртвых версий. Для append‑only таблиц с PostgreSQL 13 работают autovacuum_vacuum_insert_threshold и autovacuum_vacuum_insert_scale_factor.

Для покрытия используйте INCLUDE: эти колонки лежат только в листьях, не участвуют в сортировке и не входят в ключ уникальности. Оговорка: индекс с INCLUDE никогда не использует дедупликацию, поэтому на низкоселективном ключе (a) INCLUDE (b) может оказаться больше, чем (a, b).

Индексы для JOIN (соединений)

Для ускорения соединений следует индексировать колонки, по которым происходит соединение таблиц. Алгоритмы:

• Nested Loop Join: для каждой строки внешней таблицы ищется соответствие во внутренней. Индекс на внутренней таблице позволяет быстро находить нужные строки, иначе пришлось бы сканировать её целиком для каждой внешней записи.

• Merge Join: требует, чтобы обе входные последовательности были отсортированы по ключу соединения. Индексы могут обеспечить эту отсортированность без отдельной операции Sort.

• Hash Join: строит хеш‑таблицу по одной стороне соединения. Распространённое утверждение «hash join не использует индексы, поэтому лучше иметь индексы и позволять оптимизатору выбирать другие методы» неверно: hash join прекрасно читает вход из индекса, при нехватке work_mem не падает, а разбивается на порции со сбросом на диск, и на соединении «большая × большая» обычно и есть правильный выбор. Совет подталкивать оптимизатор к «другим методам» на больших объёмах даёт index nested loop, то есть худший план.

Про индексы на внешних ключах. Безусловное «всегда индексируйте FK» противоречит тезису «худший индекс — неиспользуемый». Индекс на FK‑колонке нужен по конкретной причине: либо вы выбираете дочерние строки по родителю, либо есть каскадные удаления и обновления — иначе каждое удаление родителя приводит к полному скану дочерней таблицы и держит блокировки. Если ни того, ни другого нет, это просто ещё одна статья расходов на запись.

Самая частая причина «индекс есть, а не используется» в соединениях — несовпадение типов. Обычно здесь приводят примеры «bigint против int» и «text против varchar», но для PostgreSQL они неверны: int2/int4/int8 живут в одном опсемействе и прекрасно используют индекс друг друга, а varchar и text бинарно совместимы. Реальные случаи другие: integer против numeric (разные опсемейства), text против citext, и приведение на стороне колонки вида WHERE varchar_col::int = 5.

Индексы для сортировки (ORDER BY) и группировки (GROUP BY)

Сортировка может быть дорогой операцией на больших наборах. Индексы помогают избежать явной сортировки, если порядок выборки совпадает с порядком индекса — с точностью до полного разворота, поскольку индекс читается в обе стороны. Не забывайте про NULLS FIRST/LAST: несовпадение ломает возможность обойтись без Sort.

Пример. Пусть есть таблица payments и запрос: вывести топ-10 самых крупных платежей типа refund за последний год.

SELECT id, amount, created_at
FROM payments
WHERE type = 'refund' AND created_at >= :from
ORDER BY amount DESC
LIMIT 10;

Запрос содержит равенство (type), диапазон (created_at), сортировку (amount) и ограничение. Напрашивается индекс (type, created_at, amount) — по порядку появления колонок в запросе, с расчётом на то, что СУБД мгновенно перейдёт к первым записям, уже упорядоченным по сумме. Проверим. В таблице 1 млн refund‑платежей, из них 336 410 за последний год и 920 за последние сутки:

Индекс	Размер	Сборка	Запрос за год	Запрос за сутки
нет	-	-	353.2 мс	320.0 мс
(type, created_at, amount DESC)	1049 МиБ	9.5 с	86.031 мс	0.358 мс
(type, amount DESC)	710 МиБ	14.4 с	0.092 мс	13.733 мс
(type, amount DESC) WHERE created_at >= '2023-12-23'	239 МиБ	4.4 с	0.061 мс	7.634 мс

На годовом диапазоне напрашивающийся индекс работает в 935 раз медленнее правильного. Причина видна в плане:

-- (type, created_at, amount DESC)
Limit
  ->  Gather Merge
        ->  Sort                                                <-- СОРТИРОВКА
              Sort Key: amount DESC
              ->  Parallel Bitmap Heap Scan on payments
                    ->  Bitmap Index Scan (actual rows=336410)   <-- 336 410 строк

-- (type, amount DESC)
Limit
  ->  Index Scan using idx on payments (actual rows=10)
        Index Cond: (type = 'refund'::text)
        Filter: (created_at >= '2023-12-23 23:06:40+00')
        Rows Removed by Filter: 15                               <-- прочитано 25 строк

После диапазонного предиката по created_at порядок по amount не сохраняется, поэтому база вычитывает все 336 410 подходящих записей и сортирует их. LIMIT 10 не спасает: чтобы узнать первую десятку, нужны все. Правильный порядок делает created_at фильтром — индекс идёт от самых крупных сумм вниз и останавливается, набрав 10 подходящих.

Отсюда правило ESR (Equality, Sort, Range): сначала поля из равенств, затем поле сортировки, затем диапазон. Часто встречающаяся формулировка «равенства впереди, сортировка и диапазон следом» склеивает S и R в один слот и потому обесценивает само правило — из неё и рождается ошибочный вариант выше.

Но ESR — эвристика, а не закон: она предполагает, что диапазон отбирает много строк. На суточном диапазоне картина переворачивается: (type, created_at, amount DESC) даёт 0.358 мс, а (type, amount DESC) — 13.7 мс, то есть в 38 раз хуже. Когда диапазон узкий, дешевле взять все 920 строк и отсортировать их, чем идти по миллиону refund‑ов от максимальной суммы вниз. Если оба паттерна реальны, держите оба индекса: планировщик сам выберет нужный.

Отдельного внимания заслуживает колонка «Сборка», потому что результат в ней контринтуитивен: больший индекс строится быстрее меньшего — 1049 МиБ за 9.5 с против 710 МиБ за 14.4 с. Разброс между тремя независимыми сборками каждого меньше 1%, так что это не шум. Чтобы понять причину, соберём три двухколоночных индекса, отличающихся только типом второй колонки:

Индекс	Тип второй колонки	Размер	Сборка
(type, created_at)	timestamptz, физически упорядочен	722.7 МиБ	8.5 с
(type, user_id)	integer, случайный	282.4 МиБ	9.8 с
(type, amount)	numeric, случайный	710.2 МиБ	13.7 с

Работают два независимых фактора, и размер — ни один из них. Первый: предупорядоченность входа. created_at уже лежит в физическом порядке, поэтому сортировка при сборке почти бесплатна — и индекс на 723 МиБ строится быстрее индекса на 282 МиБ со случайным integer. Второй: стоимость сравнения типа. numeric сравнивается заметно дороже, и (type, amount) при том же объёме проигрывает (type, created_at) 62% времени. Планируя окно на выкатку, ориентируйтесь на типы колонок, а не на ожидаемый размер индекса.

Частичный индекс на широком диапазоне оказался и самым быстрым, и в 4.4 раза меньше. Но у него две ловушки. Предикат обязан быть IMMUTABLE, поэтому WHERE created_at >= now() - interval '1 year' не создастся — дату придётся захардкодить, и индекс начнёт устаревать: через год он покрывает два года, потом всю таблицу. И планировщик применит его только если сможет доказать, что предикат запроса влечёт предикат индекса: с литералом докажет, с bind‑параметром из приложения — нет.

Пагинация. Для highload это важнее половины рассуждений про типы индексов. Индекс (created_at, id), страница 20 строк:

Смещение	OFFSET	буферов	Keyset	буферов	Разница
0	0.031 мс	4	0.038 мс	4	одинаково
1 000	0.100 мс	7	0.036 мс	4	×2.8
100 000	7.00 мс	387	0.041 мс	4	×171
1 000 000	68.56 мс	3 835	0.045 мс	4	×1 524
5 000 000	345.21 мс	19 163	0.048 мс	4	×7 192

OFFSET читает и выбрасывает все пропускаемые строки, поэтому его стоимость линейно растёт с номером страницы. Keyset не зависит от глубины вовсе — 4 буфера на любой странице:

SELECT id, created_at FROM payments
WHERE (created_at, id) > ('2023-06-01 10:00:00+00'::timestamptz, 8123456)
ORDER BY created_at, id
LIMIT 20;

Три условия, на которых keyset ломается: сравнение кортежей работает только при одинаковом направлении сортировки всех колонок. NULL в ключе пагинации молча выбрасывает строки, поэтому ключ обязан быть NOT NULL. И перейти сразу на страницу N он не умеет.

Группировка (GROUP BY) схожа с сортировкой. Индекс помогает, когда даёт агрегацию без сортировки или существенно сокращает вход, но выигрыш здесь менее прямой, чем при WHERE и ORDER BY: группировка часто требует просмотра всех строк после фильтра. Для тяжёлых агрегатов обычно выгоднее материализованная агрегация.

Подводные камни индексации в высоконагруженных системах

Замедление записей. Главный компромисс индексации: ускоряя чтение, мы замедляем запись. Оценку «каждый индекс добавляет ~5-10% к вставке, десяток лишних её удвоит» легко проверить. Замер на таблице с PK и добавляемыми по одному индексами (bulk‑вставка 1 млн строк и 50 тыс. построчных вставок):

Индексов	Bulk	Δ	Построчно	Δ	WAL	Δ WAL	Размер индексов
1 (только PK)	2 309 мс	-	346 мс	-	205.9 МБ	-	22.5 МиБ
2	3 418 мс	+48.0%	433 мс	+25.2%	282.1 МБ	+37.0%	47.8 МиБ
3	4 398 мс	+90.5%	457 мс	+32.1%	362.8 МБ	+76.2%	86.5 МиБ
5	8 114 мс	+251.3%	702 мс	+102.8%	572.3 МБ	+178.0%	190.7 МиБ
7	11 358 мс	+391.8%	892 мс	+157.9%	766.9 МБ	+272.5%	284.2 МиБ
9	15 194 мс	+557.9%	1 201 мс	+247.2%	1 118.8 МБ	+443.4%	429.2 МиБ

Восемь дополнительных индексов — это не удвоение, а 6.6× на bulk‑загрузке и 3.5× на построчной вставке, плюс 5.4× объёма WAL (а WAL — это ещё и трафик репликации, и время восстановления). В пересчёте на один индекс выходит около 17% при компаундировании, но пошаговые приросты гуляют от 5.5% до 25%, так что единого коэффициента просто не существует. И в этой таблице нет ещё одной статьи расходов: VACUUM обязан пройти по каждому индексу таблицы, поэтому лишние индексы умножают и время вакуума — от которого, как показано выше, зависит бюджет латентности index‑only scan.

Форма первичного ключа — самый недооценённый источник этой цены. Замер на 3 млн строк, ключи материализованы заранее, чтобы мерить работу индекса, а не генерацию значений:

PK	Вставка	WAL	Размер PK	Плотность листьев	Фрагментация
bigint последовательный	2 340 мс	487.8 МБ	64.3 МиБ	90.09%	0%
uuid v7, упорядоченный по времени	2 782 мс	539.4 МБ	90.3 МиБ	90.03%	0%
uuid v4, случайный	5 663 мс	585.9 МБ	120.6 МиБ	67.53%	49.81%

Сравните два UUID между собой: ширина ключа одинаковая, 16 байт, отличается только порядок — и вставка в 2 раза медленнее, индекс на 33.6% больше, плотность листьев 67.5% против 90.0%. Последовательные ключи всегда пишутся в самую правую страницу, которую PostgreSQL расщепляет в пропорции 90/10, а случайные бьют во все страницы дерева, вызывая расщепления пополам. Если бизнес требует UUID — берите v7 или ULID, а не v4. В PostgreSQL 18 есть встроенный uuidv7().

Избыточные индексы (overindexing). Иногда разработчики создают «на всякий случай» много индексов или дублирующие. Худший индекс — неиспользуемый индекс: он съедает диск и память, замедляет все модификации и вакуум, а пользы не приносит. Признак проблемы — idx_scan близкий к нулю, но удалять по одному этому признаку опасно (см. следующий раздел).

Фрагментация и раздувание индексов. Со временем в индексах накапливается пустое пространство — блоат. Базовая причина роста — расщепления страниц от вставки версий вразнобой. Но настоящая продовая беда в том, что удерживаемый xmin делает этот рост неустранимым. Одна и та же нагрузка, три прохода UPDATE по 3 млн строк с вакуумом между ними, отличие одно — во втором случае в соседней сессии открыта транзакция, выполнившая запрос:

Сценарий	Рост индекса	Мёртвых версий после VACUUM
без открытой транзакции	+99.9%	0
с открытым снапшотом	+299.6%	9 000 000

Открытый снапшот утроил рост индекса и сделал 9 миллионов мёртвых версий неудаляемыми. В проде эту роль играют долгие аналитические запросы, hot_standby_feedback на репликах, replication slots, подготовленные транзакции (переживают разрыв соединения и не видны в pg_stat_activity) и незакрытые транзакции в пуле приложения:

SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start, query
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xmin_age DESC LIMIT 5;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;

Индекс раздувается на UPDATE не всегда — механизм называется HOT. UPDATE не трогает индексы, если выполнены оба условия: не изменена ни одна проиндексированная колонка (включая колонки в индексных выражениях и предикатах частичных индексов) и новая версия строки помещается на ту же страницу. За второе отвечает fillfactor, по умолчанию равный 100 — то есть свободного места нет. Один проход UPDATE по 2 млн строк:

fillfactor	Что обновляем	Доля HOT	Рост индекса	Рост heap	Время UPDATE
100	не входящую в индекс колонку	0.0%	+99.9%	+100.0%	8.5 с
100	проиндексированную колонку	0.0%	+99.9%	+100.0%	5.7 с
90	не входящую в индекс колонку	10.8%	+99.9%	+89.2%	7.8 с
90	проиндексированную колонку	0.0%	+99.9%	+89.2%	5.8 с
70	не входящую в индекс колонку	43.4%	+99.8%	+56.6%	6.2 с
70	проиндексированную колонку	0.0%	+99.9%	+56.6%	5.7 с

Обновление проиндексированной колонки — всегда не‑HOT, при любом fillfactor. Но и обновление обычной колонки при fillfactor = 100 тоже не‑HOT: на плотно заполненной таблице новой версии просто некуда лечь. Значение 90 почти не помогает, заметный эффект даёт 70 — при трёх проходах доля HOT дорастает до 66%, а время UPDATE падает вдвое.

Обратите внимание, что индекс вырос примерно на 100% во всех конфигурациях, включая ту, где 43% обновлений прошли по HOT‑пути: оставшиеся 57% всё равно вставляют записи, а вставка миллиона ключей вразнобой вызывает расщепления страниц. После VACUUM файл индекса не сжимается — место переиспользуется, но операционной системе не возвращается.

Напрашивается мысль, что делу помогут короткие транзакции: с PostgreSQL 14 в nbtree есть bottom‑up index deletion, который чистит «мусорные» версии в листовой странице ровно в момент, когда она собирается расщепиться. Рассчитан он именно на этот сценарий. Проверка на том же объёме работы, разбитом по‑разному:

Сценарий	Рост индекса	Время
одна транзакция на все 2 млн строк	+99.8%	8.5 с
2000 коротких транзакций по 1000 строк	+99.8%	8.6 с

Разницы нет. Вероятная причина в том, что нагрузка не попадает в условия срабатывания механизма: на один ключ здесь приходится около двадцати версий, размазанных по большому индексу, а bottom‑up deletion рассчитан на плотную концентрацию дубликатов одного ключа в пределах страницы. Так что рассчитывать на него как на средство от роста индекса не стоит.

Лечение остаётся двойным: не индексировать часто обновляемые колонки (счётчики, балансы, updated_at, флаги) и понижать fillfactor на UPDATE‑интенсивных таблицах. Частичный индекс, которым это иногда предлагают лечить, к делу не относится. Понять, нужно ли вам понижать fillfactor, помогает метрика pg_stat_all_tables.n_tup_newpage_upd из PostgreSQL 16 — сколько обновлений пришлось положить на новую страницу.

Переиндексация и блокировки. Совет «REINDEX блокирует, используйте pg_repack» устарел с 2019 года: с PostgreSQL 12 есть REINDEX INDEX CONCURRENTLY, который не блокирует чтения и записи. Индекс, раздутый до 43 МиБ:

Метрика	До	После REINDEX INDEX CONCURRENTLY
Размер	43.0 МиБ	21.5 МиБ, −50%
Плотность листьев	52.54%	91.21%
Фрагментация	49.99%	0%
Время операции	-	701.9 мс

Ограничения, без которых это не рецепт для прода: не работает для индексов exclusion‑ограничений и системных каталогов, требует примерно двойного места на время операции, а при падении оставляет невалидный индекс с суффиксом ccnew, который надо удалять руками. pgrepack остаётся нужен для перестроения самой таблицы — CLUSTER и VACUUM FULL берут ACCESS EXCLUSIVE и в highload неприменимы.

Выкатка индекса на живой системе. CREATE INDEX CONCURRENTLY делает два прохода по таблице, не выполняется внутри транзакционного блока (а большинство миграционных фреймворков оборачивают миграции в транзакцию), ждёт завершения конкурирующих транзакций и может упасть, оставив INVALID‑индекс, который занимает место и поддерживается на записи, но не используется на чтении:

SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;

Любая DDL‑команда, ожидающая блокировку, встаёт в очередь и блокирует всех, кто пришёл после неё, включая читателей. Поэтому DDL под нагрузкой выполняют с lock_timeout и ретраями. Отдельно: для партиционированных таблиц CREATE INDEX CONCURRENTLY не поддерживается — там строят индекс по каждой партиции, затем CREATE INDEX ON ONLY parent и ATTACH PARTITION. Глобальных индексов по партиционированной таблице в PostgreSQL нет, поэтому уникальность обязана включать ключ партиционирования.

Смена версии коллации — единственный случай, когда индекс даёт неверный ответ, а не медленный. Порядок сортировки текста задаётся glibc или ICU, и при обновлении ОС он меняется (канонический пример — переход на glibc 2.28). Существующие btree‑индексы по тексту остаются построенными по старому порядку: запрос перестаёт находить существующую строку, UNIQUE пропускает дубликат. Сигнал — WARNING: collation has version mismatch, диагностика — pg_collation_actual_version() против pg_collation.collversion, лечение — REINDEX затронутых индексов и затем ALTER COLLATION … REFRESH VERSION. Проверить целостность помогает расширение amcheck. Это же одна из причин держать технические колонки (идентификаторы, хеши, коды) в COLLATE "C": у него нет версии, которая могла бы поменяться.

Рост размера индексов. Индексы могут занимать больше места, чем данные таблицы. Это увеличивает требования к памяти: в замере выше девять индексов заняли 429 МиБ там, где один занимал 22.5 МиБ. Если рабочий набор индексов не умещается в shared_buffers, эффективность падает.

Анализ необходимости индексации: инструменты и подходы

EXPLAIN / План запроса — базовый инструмент оптимизации. Минимально полезная форма — не голый EXPLAIN, а EXPLAIN (ANALYZE, BUFFERS): счётчики shared hit против shared read отвечают на вопрос «читали из памяти или с диска», без которого сравнивать времена бессмысленно. С PostgreSQL 18 BUFFERS включён по умолчанию. На что смотреть:

Сигнал	Что значит
оценка rows= и actual rows= расходятся в разы	статистика врёт: ANALYZE, default_statistics_target, CREATE STATISTICS для коррелированных колонок
Rows Removed by Filter велико относительно возвращённых строк	предикат работает фильтром по heap, хотя при LIMIT это бывает и правильным решением
Index Cond есть, а Buffers неожиданно велики	условие в индексе не сужает обход
Heap Fetches больше нуля в Index Only Scan	вакуум не поспевает
Heap Blocks: lossy	битмап не влез в work_mem, идёт перепроверка на уровне страниц
Sort рядом с LIMIT	кандидат на переупорядочивание колонок индекса

Осторожно: EXPLAIN ANALYZE реально выполняет запрос, поэтому UPDATE и DELETE анализируют только внутри транзакции с откатом. Для продакшена есть auto_explain, логирующий планы медленных запросов без ручного воспроизведения.

Статистика использования индексов. PostgreSQL ведёт её в pg_stat_user_indexes: idx_scan, idx_tup_read, idx_tup_fetch. Совет «удаляйте индексы с idx_scan = 0» в таком виде опасен. Проверим на таблице с PK, UNIQUE‑ограничением и обычным индексом при нагрузке, которая не использует ни один из двух последних:

 indexrelname    | idx_scan
-----------------+----------
 cons_email_uniq |        0
 cons_org        |        0
 cons_t_pkey     |        6

DROP INDEX cons_email_uniq;
-- ERROR: cannot drop index cons_email_uniq because constraint cons_email_uniq
--        on table cons_t requires it

Нулевой счётчик сам по себе не является основанием для удаления, и вот почему. Индекс может обслуживать ограничение — он не сканируется, но обеспечивает инвариант данных. Счётчики сбрасываются при pg_stat_reset() и восстановлении после сбоя, поэтому окно наблюдения может оказаться длиной в час. С PostgreSQL 16 есть более надёжный last_idx_scan — дата последнего использования. И главное: счётчики локальны для узла, а читающие реплики обслуживают другой профиль запросов, поэтому индекс с нулём на праймари может быть горячим на реплике.

Безопасная процедура: собрать статистику со всех узлов за период, покрывающий все периодические джобы (месячные отчёты!), проверить pg_constraint на зависимости, зафиксировать DDL для быстрого восстановления и удалять через DROP INDEX CONCURRENTLY.

Slow query log. PostgreSQL умеет логировать запросы дольше заданного порога через log_min_duration_statement. Анализируя эти логи, вы обнаружите самые «тяжёлые» запросы, разбирать их удобно через pgbadger. Подход прежний: найти медленный запрос → проанализировать план → добавить индекс → проверить улучшение.

Мониторинг производительности и профайлинг. pg_stat_statements собирает статистику частоты и стоимости запросов. Ранжировать надо по total_exec_time и calls, а не по среднему времени: запрос на 5 мс, вызываемый 10 000 раз в секунду, важнее запроса на 3 секунды раз в час.

Гипотетические индексы и советники. Расширение HypoPG позволяет создать «виртуальный» индекс и посмотреть план без затрат на сборку — незаменимо на больших таблицах, где сборка ради проверки гипотезы стоит часы. Рядом стоят pg_qualstats (по каким колонкам реально фильтруют) и dexter (автоподбор индексов поверх HypoPG). Полагаться на них вслепую не стоит: советники оценивают отдельный запрос, а платите вы за общую рабочую нагрузку, включая стоимость записи.

Резюме: регулярно профилируйте систему. Картина запросов меняется — добавляются фичи, меняется распределение данных. Индекс, нужный вчера, сегодня может быть неактуальным, и наоборот. Оптимизация — непрерывный процесс: меряем — оптимизируем — проверяем.

Специфика MySQL

Всё выше — про PostgreSQL. MySQL заслуживает отдельного разбора, потому что несколько ключевых вещей там устроены принципиально иначе. Замеры ниже — со стенда MySQL 8.0.46: InnoDB, буфер пула 128 МиБ, таблица на 2 млн строк.

Кластерный первичный ключ. Главное отличие InnoDB: таблица физически хранится в порядке первичного ключа, а вторичные индексы хранят не указатель на строку, а сам PK. В PostgreSQL из этого ничего не следует, а в InnoDB следует два раза. Выбор PK — это выбор физического порядка всей таблицы, и случайный UUID означает вставки в случайные места самой таблицы, а не только индекса. И длина PK умножается на число вторичных индексов — 16-байтовый UUID против 8-байтового bigint раздувает их все разом.

Одна и та же таблица на 2 млн строк с двумя вторичными индексами, отличается только тип PK:

PK	Кластерный индекс	k_user	k_status_created	Сумма вторичных
bigint	87.6 МиБ	63.6 МиБ	39.6 МиБ	103.2 МиБ
BINARY(16), UUIDv7	104.7 МиБ	75.7 МиБ	55.7 МиБ	131.4 МиБ
BINARY(16), UUIDv4	104.7 МиБ	75.7 МиБ	55.7 МиБ	131.4 МиБ

Вторичные индексы прибавили 27.3%, сама таблица — 20%. На двух индексах это 28 МиБ на 2 млн строк. При десятке индексов и сотнях миллионов строк множитель тот же, а абсолютные цифры уже другие.

Строки для v7 и v4 совпадают до десятых — разница здесь чисто в ширине ключа, порядок вставки на размер не влияет. Он влияет на плотность страниц, и это видно в замере PostgreSQL выше: 90% против 67.53% заполнения листьев. Вывод про UUIDv7 из раздела про форму ключа здесь работает с удвоенной силой: в InnoDB случайный ключ портит не только индекс, но и физический порядок таблицы.

Prefix‑индексы. В MySQL можно индексировать первые N символов колонки:

CREATE INDEX idx_url ON links (url(64));

В PostgreSQL такого нет вовсе. Это штатный ответ на проблему «индексы занимают больше места, чем данные»: на длинных строковых колонках префиксный индекс сокращает размер ценой частичной селективности — покрывающим он быть перестаёт, и за значением придётся идти в таблицу.

Колонка url длиной около 110 символов, 2 млн строк:

Индекс	Размер	Сборка
(url) целиком	224.0 МиБ	6240 мс
url(64)	166.0 МиБ	5708 мс
url(32)	93.8 МиБ	4261 мс

Длину префикса выбирают не на глаз, а по числу различных значений:

Префикс	Различных значений из 2 000 000
LEFT(url, 16)	1
LEFT(url, 32)	1
LEFT(url, 48)	996 898
LEFT(url, 64)	2 000 000

У этих данных общий домен и общий путь, поэтому первые 32 символа одинаковы у всех строк: url(32) экономит 58% места и не отбирает ничего — планировщик получит все 2 млн строк и отфильтрует их сам. url(64) разделяет все строки до единой и стоит на 26% дешевле полного индекса. Между 48 и 64 символами селективность прыгает с половины до полной — шаг в 16 символов меняет индекс из бесполезного в исчерпывающий, и угадать эту границу без запроса SELECT COUNT(DISTINCT LEFT(col, N)) нельзя.

Ограничение InnoDB на длину ключа — 3072 байта при формате строки DYNAMIC.

Descending indexes. В MySQL 5.7 и ниже индекс работал для сортировки только при полном совпадении порядка, и смешивать ASC/DESC было нельзя — такой запрос всегда упирался в filesort. С MySQL 8.0 ограничение снято:

CREATE INDEX idx_events ON events (user_id ASC, created_at DESC);

Разница видна на запросе, где направления действительно смешаны — ORDER BY user_id ASC, created DESC по 2 млн строк без предиката:

Индекс	План	Стоимость	Время
(user_id ASC, created ASC)	сортировка 2 млн строк	201 953	266.9 мс
(user_id ASC, created DESC)	без сортировки	0.024	41.3 мс

Шесть с половиной раз по времени и восемь порядков по оценке стоимости. Оговорка про «действительно смешаны» существенна: если добавить WHERE user_id = 42, равенство фиксирует первую колонку, и обратный проход по обычному индексу обслуживает оба варианта одинаково — разница исчезает. Descending‑индекс окупается там, где по одной колонке идёт сортировка, а не отбор.

Invisible indexes — то, чего не хватает в PostgreSQL. Индекс можно скрыть от планировщика, оставив на диске:

ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;

План того же запроса до и после переключения:

Состояние	План	Стоимость
VISIBLE	Covering index lookup on ev using ix (user_id=42)	3.04
INVISIBLE	Filter: (ev.user_id = 42) над полным сканом	201 898

Индекс при этом остаётся на диске — все 49.6 МиБ. Откат стоит одну команду вместо многочасового перестроения. Это правильный способ проверить гипотезу «индекс не нужен» перед удалением: сутки под реальной нагрузкой с невидимым индексом отвечают на вопрос точнее, чем любой анализ статистики.

Ревизия индексов через sys‑схему (с MySQL 5.7, включена по умолчанию) закрывает задачу почти без усилий:

-- индексы, которыми ни разу не пользовались с момента старта сервера
SELECT * FROM sys.schema_unused_indexes;

-- избыточные и дублирующие, с готовой командой на удаление
SELECT table_schema, table_name, redundant_index_name, sql_drop_index
FROM sys.schema_redundant_indexes;

-- запросы, которые ходят полным сканом
SELECT query, exec_count, no_index_used_count
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC LIMIT 20;

Ранжировать нагрузку надо по sys.statement_analysis, отсортированной по total_latency — это аналог pg_stat_statements и то же соображение: частый быстрый запрос съедает больше, чем редкий медленный. Данные берутся из performance_schema, поэтому оговорки те же: счётчики обнуляются при рестарте, смотреть надо на всех узлах.

Для более глубокого анализа есть pt-index-usage из percona‑toolkit — он прогоняет slow log через EXPLAIN и показывает, какие индексы реально задействуются, и pt-duplicate-key-checker для избыточных по префиксу.

Онлайн‑DDL. Аналог CREATE INDEX CONCURRENTLY — ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE. Там, где онлайн‑режим недоступен или реплики не выдерживают лага, применяют gh-ost или pt-online-schema-change.

Полнотекстовый индекс. FULLTEXT в InnoDB — инвертированный список слов, прямой аналог GIN, обслуживает MATCH() ... AGAINST(). Параллель с GIN стоит довести до конца, потому что она объясняет две практические вещи: есть innodb_ft_cache_size — отложенная вставка через кеш в памяти, прямой аналог pending list со всеми его всплесками при сбросе. И есть innodb_ft_min_token_size, из‑за которого слова короче порога молча не индексируются — типовая причина «поиск ничего не находит, хотя индекс есть».

Index Merge — аналог BitmapAnd/BitmapOr, но выбирается планировщиком заметно реже, а управляется через optimizer_switch. Как и в PostgreSQL: если пара условий встречается постоянно, правильный ответ — составной индекс, а не расчёт на слияние двух одиночных.

Индексация в NoSQL: MongoDB и Cassandra

Замеры — с двух оставшихся стендов: MongoDB 7.0.40 с кешем WiredTiger 1 ГиБ на коллекции в 1 млн документов и Cassandra 5.0.9 кластером из трёх узлов на 200 тыс. строк.

Индексы в MongoDB

Пересечения индексов не произошло: планировщик взял один индекс, второе условие проверил постфильтром по документам

MongoDB — документоориентированная БД, но по части индексов очень схожа с реляционными: под капотом B‑tree, индекс по умолчанию на _id, обычные индексы на поля, включая вложенные через точечную нотацию.

Single field и compound. MongoDB умеет index intersection начиная с 2.6, а запросы с $or могут использовать разные индексы для разных ветвей. Проверим на find({city: "Boston", age: 25}) — 1 млн документов, 8 городов, 60 значений возраста, поля независимы, под условие подходят 2065 документов:

Индексы	totalKeysExamined	totalDocsExamined	nReturned	Время
нет (COLLSCAN)	0	1 000 000	2065	154 мс
два одиночных {city} и {age}	16 532	16 532	2065	15 мс
… принудительно {city: 1}	124 738	124 738	2065	55 мс
… принудительно {age: 1}	16 532	16 532	2065	10 мс
составной {city: 1, age: 1}	2065	2065	2065	4 мс

Пересечения не произошло. При двух одиночных индексах выигравший план — FETCH filter{city} ← IXSCAN age_1: планировщик взял один индекс, более селективный, а второе условие проверил постфильтром уже по документам. Отсюда и 16 532 прочитанных документа ради 2065 нужных — восьмикратный перебор.

Составной индекс читает ровно столько ключей и документов, сколько возвращает: 2065 при 2065. Это тот самый ориентир из диагностики ниже — totalKeysExamined равен nReturned, лишней работы нет. Как и в реляционных СУБД: один правильно спроектированный составной индекс лучше, чем расчёт на пересечение двух одиночных. Порядок полей выбирается по тому же принципу ESR.

Multikey‑индексы создаются автоматически при индексировании поля‑массива: каждый элемент индексируется отдельно, что близко к GIN. Ограничение — в одном compound‑индексе только одно поле может быть массивом.

Цена отдельной записи на элемент меньше, чем кажется. Коллекция на 200 тыс. документов, в массиве tags в среднем 4.50 элемента, всего 899 748 записей:

Индекс	Записей	Размер
{n: 1} по скаляру	200 000	1.9 МиБ
{tags: 1} multikey	899 748	3.6 МиБ

Записей в 4.5 раза больше, размер — в 1.9 раза: одинаковые термы делят префикс, и сжатие WiredTiger снимает большую часть повторов. Множитель по размеру ближе к корню от множителя по числу элементов, чем к нему самому.

Запрос {tags: {$all: ["index", "shard"]}} просмотрел 71 935 ключей и вернул 26 952 документа: MongoDB сканирует по одному элементу массива, а остальные проверяет постфильтром. Для сравнения, {tags: "index"} дал 71 662 ключа при 71 662 возвращённых — точное попадание. Чем больше элементов в $all, тем сильнее расходятся просмотренное и возвращённое.

Функциональных индексов в MongoDB нет — аналога CREATE INDEX ON t (lower(x)) не существует ни в одной версии. Обходятся тремя способами: вычисляемым полем, которое приложение поддерживает при записи, и обычным индексом по нему, partial index с partialFilterExpression, если нужно проиндексировать подмножество документов, wildcard index (с 4.2), если структура документов заранее неизвестна.

Partial index экономит ровно свою долю. Индекс {status: 1, created: 1} на миллионе документов, где status: "paid" у 20.0%:

Индекс	Размер
полный	10.5 МиБ
partialFilterExpression: {status: "paid"}	2.1 МиБ (20.3% от полного)

Линейная зависимость от доли документов — никаких скрытых накладных расходов на условие.

Ограничение на размер ключа в 1024 байта действовало до MongoDB 4.0 включительно. Начиная с 4.2 (при featureCompatibilityVersion 4.2 и выше) Index Key Limit снят.

Покрывающие запросы работают и здесь: если все запрошенные поля есть в индексе, MongoDB не идёт за документами. При индексе {city: 1, age: 1} и запросе по city:

Проекция	totalDocsExamined	Время
{_id: 0, city: 1, age: 1}	0	31 мс
{_id: 0, city: 1, age: 1, amount: 1}	124 738	132 мс

Одно лишнее поле в проекции — и запрос перестаёт быть index‑only, добавляя 124 738 чтений документов и четырёхкратное время. id: 0 в проекции обязателен: id возвращается по умолчанию, а его в индексе нет.

Где хранятся индексы. На диске, а WiredTiger кеширует их страницы в своём cache. Следствие то же, что и в PostgreSQL: рабочее множество индексов должно помещаться в кеш, иначе каждый поиск превращается в дисковое чтение. Формулировка «хранятся в памяти» задаёт неверную модель.

Диагностика. Актуальные метрики — totalKeysExamined (сколько ключей индекса просмотрено) и totalDocsExamined (сколько документов прочитано), через db.collection.explain("executionStats"). Ориентир: totalKeysExamined близко к nReturned — индекс подобран хорошо, totalDocsExamined сильно больше nReturned — индекс не покрывает запрос и база ходит за документами зря. Метрики nscanned / nscannedObjects из документации до 3.0 больше не используются.

TTL‑индексы удаляют документы по истечении срока, а фоновый процесс просыпается раз в 60 секунд. Из этой периодичности следует то, что надо закладывать в приложение.

Документы не исчезают в момент истечения. 50 тыс. документов с меткой на час в прошлом и expireAfterSeconds: 1 продержались в коллекции 52 секунды после создания индекса — то есть до срабатывания фонового процесса, а не до наступления срока. Под нагрузкой ожидание больше: процесс конкурирует за те же ресурсы, что и рабочие запросы. Полагаться на TTL как на гарантию нельзя, фильтр по дате в запросе всё равно нужен. TTL — механизм уборки, а не контроля доступа.

TTL‑удаления реплицируются. Это обычные delete, они попадают в oplog и проигрываются на всех вторичных узлах. Если единовременно истекает большой объём, например суточная партия логов, вы получаете всплеск записи в oplog и лаг реплик на ровном месте. Лечится размазыванием срока жизни: случайный джиттер к expireAfterSeconds при записи документа. Это ровно та же проблема, что описана ниже для Cassandra, только проявляется она через oplog.

Под нагрузкой индекс на репликасете добавляют rolling index build — по одной ноде за раз, выводя её из набора. Это единственный безопасный способ. Полезные мелочи: hint() для принудительного выбора индекса при отладке, sparse‑индексы для полей, которых нет у большинства документов, hidden indexes (db.collection.hideIndex(), с 4.4) — прямой аналог INVISIBLE в MySQL, для проверки гипотезы перед удалением.

Индексы в Apache Cassandra

Число узлов взято из system_traces.events, время из system_traces.sessions, кластер из трёх узлов

Cassandra построена на идеях Amazon Dynamo (статья 2007 года — распределение и репликация) и Google BigTable (модель данных). Не путать с DynamoDB: это коммерческий сервис AWS, вышедший в 2012 году, то есть уже после Cassandra.

B‑tree там нет. Cassandra построена на LSM‑дереве — записи сначала попадают в memtable в памяти, затем сбрасываются на диск неизменяемыми SSTable, которые периодически сливаются компакцией. Чтение по ключу — это проверка bloom‑фильтра каждой SSTable, обращение к partition index и summary, и только потом чтение данных. Запись поэтому дёшева и последовательна, чтение по partition key дёшево, а любая операция, требующая обхода данных без ключа, дорога принципиально — она не может опереться на упорядоченную структуру, которой просто нет. Вторичный индекс здесь — по сути ещё одна таблица, локальная для узла, со всеми свойствами LSM, включая накопление tombstones.

Primary Key против вторичного индекса. Каждая таблица имеет Primary Key из Partition Key и опциональных Clustering columns. Partition Key определяет, на каком узле лежит запись, clustering columns задают сортировку внутри партиции. Основной способ эффективного доступа — через Primary Key.

Вторичные индексы локальны для узла. Запрос по такому индексу координатор рассылает всем узлам, и при большом кластере это превращается практически в полный скан. Это не абстракция — трассировка показывает разлёт напрямую. Кластер из трёх узлов, 200 тыс. строк в 20 тыс. партиций, время взято из system_traces.sessions, число узлов — из уникальных source в system_traces.events:

Запрос	Узлов из 3	Время	Строк
WHERE user_id = 42 (ключ партиции)	2	2.3 мс	10
WHERE email = '…' (индекс, 200 тыс. значений)	3	23.0 мс	1
WHERE email = 'нет@…' (промах)	3	17.3 мс	0
WHERE status = 'paid' LIMIT 1000 (5 значений)	3	19.2 мс	1000

Два узла в первой строке — это координатор и владелец партиции, больше запрос не трогает никого. Как только ключа партиции в запросе нет, участвуют все три.

Промах стоит почти столько же, сколько попадание: 17.3 против 23.0 мс. За ноль строк платится полная цена обхода кластера — узнать об отсутствии значения можно только опросив всех. Масштабируется это в худшую сторону: на трёх узлах разница с поиском по ключу партиции десятикратная, на тридцати координатор будет ждать тридцать ответов ради той же одной строки.

Документация не рекомендует индексы на колонках с очень высокой и очень низкой кардинальностью, и обе границы работают по‑разному. При высокой в индексе почти столько же точек, сколько строк, и запрос обращается ко всем узлам ради одного результата. При низкой индекс отбирает слишком много, и каждый узел возвращает горы данных — впрочем, при плотных совпадениях и небольшом LIMIT обход обрывается раньше: тот же status = 'paid' с LIMIT 100 уложился в два узла, потому что нужное нашлось сразу.

SASI и SAI — две похожие аббревиатуры, которые часто путают.

SASI (SSTable Attached Secondary Index) появился в Cassandra 3.4 и добавил к обычным вторичным индексам диапазонные запросы и префиксный поиск. Но он всегда был помечен как экспериментальный, в Cassandra 5.0 объявлен deprecated, а к версии 6.0 планируется к удалению. Начинать новый проект с SASI сегодня не стоит.

SAI (Storage Attached Index) — то, ради чего стоит смотреть на Cassandra 5.0 (вышла в сентябре 2024). SAI разработан в DataStax, передан в Apache и заменяет собой и обычные вторичные индексы (2i), и SASI. Он существенно компактнее на диске, потому что в отличие от SASI не строит n‑граммы для каждого терма, и заметно дешевле по латентности и позволяет строить несколько индексов на таблице без пропорционального роста накладных расходов. Ограничение о локальности никуда не делось, и это стоит проверить прежде, чем закладываться на SAI в проектировании: все замеры в таблице выше сделаны именно на SAI, и все три строки без ключа партиции — по три узла из трёх. SAI меняет структуру индекса на диске, а не распределение данных по кластеру. Цена запроса стала ниже, часть сценариев, где раньше безальтернативно делали отдельную таблицу, теперь закрывается индексом, но запрос без partition key как обходил кластер, так и обходит.

На 2025 год расклад такой: на Cassandra 5.0+ вторичные индексы — это SAI, SASI не рассматриваем, а денормализация в отдельную таблицу остаётся правильным выбором для горячих путей.

Tombstones. Удаление — это запись маркера, который живёт gc_grace_seconds и только потом убирается компакцией. Одна партиция на 20 тыс. строк, gc_grace_seconds = 3600, чтение первых 100 живых строк:

Состояние	Время	Надгробий в трассе
до удалений	3.3 мс	0
удалено 19 900 из 20 000	27.6 мс	19 900
после major compaction	21.2 мс	19 900
gc_grace_seconds = 0 + compaction	12.9 мс	0

Восьмикратная деградация на ровном месте: чтобы отдать 100 строк, узел просматривает 19 900 маркеров. Третья строка — главная: major compaction надгробия не выбросил, потому что gc_grace_seconds не истёк. Компакция здесь не спасение, а ожидание — маркер обязан прожить свой срок, иначе удалённая строка воскреснет с узла, который пропустил удаление. Убрать надгробия раньше срока можно только осознанно снизив gc_grace_seconds, и это размен на риск воскрешения данных.

Массовое истечение TTL даёт всплеск tombstones на ровном месте, и разгребать его придётся не компакцией, а временем. Планируйте объём единовременно истекающих данных.

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

Query‑based modeling. Правильный подход — проектировать таблицу под запрос, а не запрос под таблицу. Денормализация и вторая таблица под второй паттерн доступа здесь норма, а не костыль.

Materialized views помечены экспериментальными начиная с 3.0.x и официально не рекомендованы к продакшену — известны проблемы с рассинхронизацией базовой таблицы и представления. Ручное ведение второй таблицы на уровне приложения многословнее, но предсказуемее.

Типичные ошибки и анти‑паттерны индексирования

Каждая ветка ведёт к строке из таблицы ниже. Правая нижняя — единственная, где виноват не запрос, а планировщик

Анти‑паттерн	Почему плохо	Что делать
Выбор индекса по проценту отбора без учёта корреляции	На 20% таблицы решение переворачивается: ×2.1 в пользу индекса либо ×1.9 против	Смотреть pg_stats.correlation, а не долю строк
Диапазон в индексе перед колонкой сортировки	Порядок теряется, появляется Sort по всей выборке: 86.0 против 0.092 мс	E → S → R как дефолт, с проверкой на узком диапазоне
Расчёт на то, что (A,B) заменит (B,A)	Не заменяет: 28.8 против 1.4 мс	Ведущей ставить колонку из access‑предиката
Дублирующий (A) при наличии (A,B)	Избыточен, но платится на каждой записи	Удалить короткий — после проверки pg_constraint
«Составной индекс всегда лучше двух»	Неверно для OR: 15.3 против 195.7 мс	На OR — отдельные индексы и BitmapOr
Функция или приведение над колонкой в WHERE	Предикат несаргабельный: 598 против 1.44 мс	Переписать в диапазон с явным смещением, иначе IMMUTABLE‑индекс по выражению
LIKE 'префикс%' в не‑C коллации без text_pattern_ops	Обычный btree не применяется: 57.6 против 0.045 мс	text_pattern_ops или COLLATE "C"
Полнотекстовый индекс в расчёте на LIKE '%...%'	FTS отвечает только на @@ и не используется даже для целого слова	Триграммы gin_trgm_ops
Триграммы под неселективный шаблон	304 МиБ и 23 с сборки ради ускорения в 1.9 раза	Проверить селективность шаблона заранее
OFFSET для глубокой пагинации	Стоимость линейна по глубине: ×7 192 на пятимиллионном смещении	Keyset — с оговорками про NULL и направления сортировки
Индекс по часто обновляемой колонке	Ломает HOT: доля HOT нулевая при любом fillfactor	Не индексировать счётчики и updated_at
fillfactor = 100 на UPDATE‑интенсивной таблице	HOT невозможен даже без индексных колонок	Понижать до 70, на 90 эффекта почти нет
Длинные транзакции при UPDATE‑нагрузке	Блокируют вакуум: рост индекса +300% вместо +100%	Короткие транзакции, мониторинг backend_xmin
Случайный UUID как первичный ключ	Расщепления страниц, плотность 67%, вставка вдвое медленнее	UUIDv7, ULID или bigint
Индексы «на всякий случай»	Восемь лишних индексов — это 6.6× на вставке и 5.4× WAL	Индексировать под конкретный запрос
Удаление индексов по idx_scan = 0	Констрейнты, сброс счётчиков, разные профили на репликах	Процедура из раздела «Анализ необходимости индексации»
Расчёт на Index Only Scan без вакуума	Обновление 1% строк даёт деградацию в 14.9 раза	Мониторить Heap Fetches, настраивать автовакуум пер‑таблично
«Раз индекс есть — план оптимальный»	Планировщик ошибался вдвое на целом порядке селективности	Проверять планы, а не предполагать
BRIN на некоррелированной колонке	В 2.3 раза медленнее полного скана	BRIN только при корреляции около 1, не забыть autosummarize
Апгрейд ОС без переиндексации текстовых индексов	Индекс даёт неверный ответ	REINDEX + REFRESH VERSION, проверка через amcheck

Альтернативы индексу

Индекс — не единственный и часто не самый дешёвый ответ на «запрос медленный». Девятый индекс, как мы уже видели, обходится в 5.6 раза дороже на вставке. Прежде чем добавлять ещё один, стоит проверить, не подходит ли что‑то из этого списка лучше.

• Предрасчёт и инкрементальные счётчики. Тяжёлый агрегат, который считается на каждый запрос, дешевле пересчитывать при записи. Особенно это касается GROUP BY: индекс помогает ему только при совпадении порядка, во всех остальных случаях планировщик возьмёт Hash Aggregate, и индекс на группировку не повлияет.

• Материализованные представления — тот же предрасчёт средствами СУБД, с явным контролем момента обновления.

• Кеш на уровне приложения — для данных, которые меняются реже, чем читаются.

• Вынос полнотекстового и фасетного поиска в Elasticsearch или OpenSearch вместо наращивания GIN‑индексов: мы видели, что триграммный индекс стоит 304 МиБ и 23 секунды сборки ради ускорения в 1.9 раза.

• Read‑реплики для аналитических запросов, чтобы не держать на праймари индексы, нужные раз в сутки для отчёта. Помните, что профиль использования индексов на реплике свой, и idx_scan там считается отдельно.

• Партиционирование — снижает объём работы без индекса вообще: partition pruning отсекает целые секции по ключу партиционирования. Глобальных индексов в PostgreSQL нет, только локальные по секциям, а уникальный индекс обязан включать ключ партиционирования.

• Денормализация — продублировать поле, чтобы убрать соединение целиком. В Cassandra это вообще единственный правильный ответ, но и в реляционных базах на горячем пути он рабочий.

Рекомендации по проектированию схемы с учётом индексов

Таблица выше отвечает на вопрос «что не так с существующим индексом». Здесь — то, что решается на этапе проектирования схемы и в эту таблицу не попадает.

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

Комбинируйте условия в составных индексах, но не переусердствуйте: индекс на 5 колонок, из которых реально фильтруются 2, это лишняя трата ресурсов. И помните, что на OR составной индекс проигрывает двум отдельным.

Учитывайте частоту запросов. Индекс для месячного отчёта, возможно, не нужен. Индекс для API, которое дёргается 1000 раз в секунду, жизненно необходим.

Следите за ростом данных. Архитектура, прекрасно работавшая на 100 тыс. записей, может упереться в потолок на 100 млн. Может оказаться полезным партиционирование — но помните, что глобальных индексов по партиционированной таблице нет и уникальность обязана включать ключ партиционирования.

Тестируйте и профилируйте. Прогоняйте нагрузочные тесты с профилированием запросов и смотрите на p99, а не на среднее: лучше поймать отсутствие индекса на этапе теста, чем получать алерт в бою.

Документируйте решения. Индексы должны жить в миграциях под контролем версий, с понятными именами. Отдельно предусмотрите, что CREATE INDEX CONCURRENTLY не выполняется внутри транзакции — это требует специальной обвязки в большинстве миграционных фреймворков.

Чеклист выбора индекса под задачу

1. Что за запрос мы ускоряем? Выпишите пример запроса со всеми его частями: WHERE, JOIN, ORDER BY, GROUP BY, LIMIT.

2. Какие предикаты станут access‑предикатами, а какие — фильтрами? Access‑предикат — цепочка равенств плюс один диапазон на её конце. Всё, что стоит после диапазона, обход не сужает. По тексту плана это не различить: сверяйтесь с определением индекса и смотрите на Buffers.

3. Какова корреляция колонки? SELECT attname, correlation FROM pg_stats WHERE tablename = '…'. Это важнее доли отбираемых строк.

4. Подходит ли тип индекса под операторы? Равенство и диапазон — B‑tree. LIKE 'префикс%' — B‑tree плюс text_pattern_ops в не‑C коллации. LIKE '%подстрока%' — только триграммы, и только если шаблон селективен. Массивы, JSON, полнотекстовый поиск — GIN. Гео, диапазонные типы, ближайшие соседи — GiST. Огромная append‑only таблица — BRIN.

5. Нужен ли составной индекс и в каком порядке? Равенства впереди, затем колонка сортировки, затем диапазон. При узком диапазоне проверьте обратный порядок — он может выиграть на порядок.

6. Не покроет ли задачу частичный индекс? Он бывает кратно меньше. Но предикат обязан быть IMMUTABLE, дата в нём устаревает, и с bind‑параметром индекс не применится.

7. Будет ли индекс покрывающим — и переживёт ли это вакуум? INCLUDE даёт Index Only Scan, но отключает дедупликацию и деградирует при активной записи.

8. Какой существующий индекс новый делает избыточным? Добавили (A,B) — проверьте, не пора ли убрать (A).

9. Каковы побочные эффекты для записи? Оцените частоту вставок и обновлений. Каждый индекс — это 17–25% скорости построчной вставки, рост WAL и дополнительная работа вакуума.

10. Как вы его выкатите и откатите? CREATE INDEX CONCURRENTLY вне транзакционного блока, с lock_timeout и ретраями, с проверкой на INVALID после. Метрика и порог отката фиксируются до выкатки, а не после. Индекс приедет на все реплики — считайте место для всего кластера.

11. Что будет на репликах и с партиционированием? Индекс приедет на все узлы физической репликации — место и время сборки считайте для всего кластера. Если таблица партиционирована, CREATE INDEX CONCURRENTLY для неё не работает, а уникальность обязана включать ключ партиционирования.

12. Что будет при апгрейде операционной системы? Текстовые индексы после смены версии коллации требуют REINDEX — иначе они начнут давать неверные ответы, а не медленные.

13. Влезает ли рабочий набор в память? Индексы конкурируют с данными за shared_buffers: девять индексов в замере выше заняли 429 МиБ там, где один занимал 22.5 МиБ.

14. Протестируйте с EXPLAIN. Создайте индекс в тестовой среде и посмотрите план. Используется ли он? Ушёл ли Sort? Сколько буферов читается? Если индекс не используется из‑за неверной оценки, поможет ANALYZE.

15. Мониторьте в бою. После выхода в продакшен следите: ушли ли проблемы с медленным запросом, не выросло ли время вставки, не появились ли блокировки. Через месяц проверьте last_idx_scan на всех узлах — с оглядкой на stats_reset и на констрейнты.

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