Как индекс ускоряет запрос — и когда он его замедляет

MAATRIX

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

Что такое индекс на самом деле

Таблица в базе данных — это куча строк (heap), физически лежащих в файле без всякого порядка относительно значений колонок. Когда вы делаете SELECT * FROM orders WHERE customer_id = 123 без индекса, база не знает, где искать нужные строки, и вынуждена прочитать каждую страницу таблицы целиком — это называется последовательное сканирование, Seq Scan в PostgreSQL или full table scan в MySQL. Для таблицы на несколько тысяч строк это незаметно, для таблицы на десятки миллионов — минуты вместо миллисекунд.

Индекс — это отдельная структура данных, которая хранит значения индексируемой колонки в отсортированном виде вместе со ссылкой на физическое место строки (в PostgreSQL это ctid, в MySQL InnoDB — обычно первичный ключ, если индекс вторичный). Самый распространённый тип — B-tree (сбалансированное дерево): корень, промежуточные узлы, листья. Листья хранят отсортированные ключи и указатели на строки, а сам поиск идёт сверху вниз по дереву.

Создаётся индекс просто:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

После этого для запроса выше планировщик может выбрать Index Scan или Bitmap Index Scan вместо Seq Scan — но выбирает он это не автоматически «потому что индекс есть», а по расчёту стоимости, о чём дальше.

Почему поиск и сортировка ускоряются

Ключевое свойство B-tree — логарифмическая глубина. Если в таблице миллион строк, дерево индекса обычно имеет глубину 3–4 уровня: чтобы найти нужный ключ, движку не нужно перебирать миллион записей, достаточно спуститься по 3–4 страницам дерева. Каждая страница — это блок на диске (в PostgreSQL по умолчанию 8 КБ), и вместо чтения тысяч блоков таблицы движок читает буквально несколько блоков индекса плюс блоки с самими найденными строками.

Индекс реально помогает в трёх сценариях:

• Точное совпадение: WHERE customer_id = 123 — дерево находит нужный ключ за несколько шагов.
• Диапазон и сортировка: WHERE created_at > '2026-08-01' или ORDER BY created_at — значения в B-tree лежат отсортированными, поэтому диапазонный запрос — это просто последовательное чтение части листьев дерева, а ORDER BY по индексированной колонке может вообще обойтись без отдельной сортировки результата.
• JOIN по внешнему ключу: индекс на колонке, по которой соединяются таблицы, превращает вложенный цикл в быстрый поиск вместо перебора.

Важный нюанс с составными (многоколоночными) индексами — действует правило leftmost prefix: индекс

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

хорошо работает для WHERE status = 'paid' AND created_at > '2026-08-01' и даже просто для WHERE status = 'paid', но не поможет запросу WHERE created_at > '2026-08-01' без условия на status — потому что порядок ключей в дереве определяется первой колонкой, и без неё дерево нельзя эффективно обойти по второй.

Почему индекс замедляет запись

Вот обратная сторона: индекс — это не бесплатная надстройка, а полноценная структура данных, которую нужно поддерживать в актуальном состоянии. Каждый INSERT в таблицу с пятью индексами — это не одна запись, а потенциально шесть: одна в heap-файл таблицы и по одной в каждый из пяти индексов, с рекурсивной перебалансировкой B-tree страниц, если страница переполнена.

С UPDATE в PostgreSQL ситуация ещё тоньше из-за MVCC: обновление строки — это не изменение на месте, а создание новой версии строки (новый tuple), а старая помечается устаревшей. Если вы обновили хотя бы одну из индексированных колонок, все индексы таблицы должны получить запись, указывающую на новую версию строки — даже если реально изменилось только одно поле. Есть оптимизация HOT (Heap-Only Tuple): если ни одна индексированная колонка не менялась и на той же странице heap есть свободное место, новая версия строки создаётся без обновления индексов вообще. Это одна из причин, почему «лишние» индексы на редко используемых для поиска колонках не только не помогают, но и убирают возможность HOT-обновлений для строк, где меняются именно эти колонки.

DELETE тоже не бесплатен: строка не удаляется физически сразу, а помечается как мёртвая (dead tuple), и все связанные с ней записи в индексах остаются до тех пор, пока VACUUM (в PostgreSQL) или аналогичный механизм не очистит их. Чем больше индексов — тем больше «мусора» накапливается между циклами очистки.

Практический эффект: массовая загрузка данных (COPY, batch INSERT) в таблицу с большим количеством индексов может быть в разы медленнее, чем в таблицу без индексов или с минимальным их набором. Частый приём при больших миграциях — временно удалить индексы, залить данные, создать индексы заново:

DROP INDEX idx_orders_customer_id;
-- массовая загрузка данных
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY строит индекс без блокировки таблицы на запись — дольше по времени, зато без простоя.

Когда планировщик сам игнорирует индекс

Наличие индекса не гарантирует, что он будет использован — и это нормальное, ожидаемое поведение, а не баг. PostgreSQL использует cost-based планировщик: он оценивает стоимость разных вариантов выполнения запроса (в условных единицах, а не в секундах) на основе статистики, собранной ANALYZE (гистограммы значений, количество уникальных значений, распределение), и выбирает вариант с наименьшей расчётной стоимостью.

Две частые причины, почему план идёт через Seq Scan, хотя индекс существует:

Низкая селективность условия. Если колонка status принимает всего три значения (new, paid, cancelled) и вы фильтруете по status = 'paid', а таких строк 40% от таблицы — чтение через индекс означает 40% случайных обращений к heap-файлу вперемешку с чтением индекса, что на вращающихся дисках и даже на части SSD-конфигураций дороже, чем один проход по таблице подряд. Планировщик это учитывает и честно выбирает Seq Scan.

Маленькая таблица. Если вся таблица помещается в несколько страниц (условно — тысячи строк, а не миллионы), разница между чтением через индекс и последовательным сканированием пренебрежимо мала, а у Seq Scan меньше накладных расходов на служебные операции. Планировщик снова предпочтёт его.

Проверить реальную селективность несложно:

ANALYZE orders;
SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

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

EXPLAIN ANALYZE: как увидеть реальный план

Гадать, использует ли конкретный запрос индекс, не нужно — это можно увидеть напрямую:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN без ANALYZE показывает только оценку планировщика (сколько строк и по какой цене он *ожидает* обработать), не выполняя запрос. EXPLAIN ANALYZE реально выполняет запрос и добавляет фактическое время и число строк — из-за этого для UPDATE/DELETE стоит оборачивать проверку в транзакцию с откатом:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'paid' WHERE id = 42;
ROLLBACK;

В выводе смотрите на:

• Тип узла — Seq Scan, Index Scan, Index Only Scan (данные найдены прямо в индексе, без похода в heap), Bitmap Heap Scan + Bitmap Index Scan (комбинация для нескольких условий или средней селективности).
• cost= — расчётная стоимость планировщика (startup..total), в условных единицах, не в миллисекундах.
• actual time= — реальное время выполнения этого узла в миллисекундах, и rows= — сколько строк реально обработано, в сравнении с оценкой планировщика в скобках. Большое расхождение (план ожидал 10 строк, а обработал 100 000) — сигнал, что статистика устарела и нужен ANALYZE.
• Buffers: shared hit/read — сколько страниц взято из кэша (hit) и сколько реально прочитано с диска (read); много read при повторном запуске того же запроса означает, что данные не помещаются в shared_buffers.

Отдельно полезно pg_stat_user_indexes — счётчик idx_scan показывает, сколько раз индекс реально использовался с момента последнего сброса статистики:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Индексы с idx_scan = 0 на таблице, живущей уже недели — кандидаты на удаление, если только это не индекс под уникальное ограничение или внешний ключ, которые нужны не для скорости чтения, а для целостности данных.

Типичная ошибка: «проиндексировали всё подряд»

Частый сценарий: после жалоб на медленные запросы кто-то в команде добавляет индекс на каждую колонку, которая встречается хоть в одном WHERE, ORM генерирует индекс на каждый внешний ключ автоматически, а через полгода никто уже не помнит, зачем нужен конкретный индекс. Результат вылезает не сразу, а когда объём записи растёт: batch-загрузка данных, которая раньше шла 10 минут, начинает идти 40; autovacuum не успевает чистить мёртвые строки, потому что ему приходится обходить не только heap, но и десяток индексов на каждой таблице; WAL (журнал упреждающей записи) растёт быстрее, потому что каждое изменение логируется вместе со всеми затронутыми индексами.

Как это находится на практике:

-- неиспользуемые индексы
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY pg_relation_size(indexrelid) DESC;

-- дублирующиеся индексы на одних и тех же колонках
SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS duplicate_indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

Дублирующиеся или перекрывающиеся составные индексы (например, отдельный индекс на (status) и ещё один на (status, created_at), где первый почти всегда избыточен благодаря leftmost prefix) можно смело удалять — планировщик и так использует более широкий индекс для запросов, покрываемых узким.

Удаление лучше делать без блокировки таблицы:

DROP INDEX CONCURRENTLY idx_orders_status;

Правильный порядок работы — не «проиндексировать заранее на всякий случай», а сначала собрать реальные медленные запросы (через EXPLAIN ANALYZE, лог log_min_duration_statement или расширение pg_stat_statements), увидеть по плану, где действительно не хватает индекса, и добавлять точечно под конкретный паттерн запроса — а не под колонку вообще.

Частые вопросы