Когда индекс перестаёт помогать: ищем точку, где база выбирает полное сканирование

MAATRIX

Разработчик добавляет индекс, ждёт ускорения — а EXPLAIN как ни в чём не бывало показывает Seq Scan (в MySQL — type: ALL) по многомиллионной таблице. Первая мысль обычно «база сломалась» или «индекс не подхватился». В большей части таких случаев планировщик в порядке: он честно посчитал, что читать индекс дороже, чем прочитать таблицу целиком. Разберёмся, где проходит граница между «индекс ещё выгоден» и «индекс уже мешает», и что с этим делать.

Почему это решение, а не баг

И PostgreSQL, и MySQL (с InnoDB) используют cost-based optimizer — планировщик, который не следует жёстким правилам вида «если есть индекс на колонке в WHERE — используй его», а на каждый запрос строит несколько альтернативных планов и выбирает план с минимальной расчётной стоимостью. Стоимость — не время в миллисекундах, а условные единицы, собранные из ожидаемого числа операций чтения страниц с диска (или из буферного кеша), сравнений строк и сортировок.

У индексного доступа есть встроенная невыгодная сторона: чтение по B-tree индексу почти всегда означает случайный доступ к страницам таблицы (random I/O) — для каждой найденной по индексу строки нужен отдельный поход в heap (кучу) за остальными колонками, если это не покрывающий индекс. Последовательное сканирование (Seq Scan), наоборот, читает страницы подряд — последовательный I/O, который на вращающихся дисках был в разы дешевле random-чтения, да и на SSD/NVMe всё ещё дешевле за счёт предсказуемого доступа и меньшего числа обращений к странице.

Отсюда правило, о котором чаще всего забывают: индекс выгоден, когда он отсекает заметную часть строк. Если запрос всё равно вернёт 30-50% таблицы, поход по индексу — это 30-50% случайных чтений плюс накладные расходы на сам индекс, что почти всегда дороже одного последовательного прохода. Планировщик именно это и считает, и в большинстве случаев его арифметика верна — проблема чаще в исходных данных для расчёта, а не в самой логике.

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

Селективность: ключевая метрика, которую не показывают в интерфейсе

Селективность индекса — это доля уникальных значений, которую условие в WHERE отсекает от всей таблицы. Формально для равенства WHERE column = value селективность оценивается как 1 / n_distinct (число различных значений в колонке), а для диапазонов и LIKE — по гистограмме распределения значений, которую собирает ANALYZE.

Пример на пальцах для таблицы orders из 10 млн строк:

• status с 5 возможными значениями — почти нет селективности. WHERE status = 'paid' в среднем отсекает лишь долю строк порядка 1/5 от таблицы. Индекс по одному status планировщик почти всегда проигнорирует в пользу Seq Scan — читать миллионы строк по случайным адресам дороже, чем прочитать всю таблицу подряд.
• customer_id с 500 тыс. уникальных значений — высокая селективность. WHERE customer_id = 12345 отсекает почти всю таблицу, оставляя единицы или десятки строк. Здесь индекс почти всегда выигрывает.
• created_at >= now() - interval '7 days' на таблице с историей за несколько лет — селективность зависит от распределения: при равномерном накоплении данных доля будет небольшой, и индекс выгоден. Но если основная масса заказов создана за последний месяц (активный рост бизнеса), тот же диапазон охватывает куда большую долю — и выгода индекса резко падает.

Грубое практическое правило (ориентир, а не строгий порог): если запрос отбирает больше нескольких процентов строк таблицы, по умолчанию стоит считать, что Seq Scan может оказаться дешевле, и проверять это через EXPLAIN, а не полагаться на наличие индекса как на гарантию.

Как читать план запроса: EXPLAIN ANALYZE в PostgreSQL

EXPLAIN без ANALYZE показывает только расчётный план — что планировщик собирается делать и сколько это, по его мнению, будет стоить, без реального выполнения. EXPLAIN ANALYZE реально выполняет запрос и добавляет к плану фактические цифры — это меняет данные, если запрос пишущий, поэтому для INSERT/UPDATE/DELETE оборачивайте его в транзакцию с ROLLBACK, либо используйте флаг ANALYZE, TIMING OFF без выполнения в проде на горячих таблицах в часы пиковой нагрузки.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total
FROM orders
WHERE status = 'paid'
  AND created_at >= now() - interval '30 days';

Типичный вывод для случая, когда планировщик осознанно выбрал Seq Scan:

Seq Scan on orders  (cost=0.00..215432.00 rows=612044 width=24)
                     (actual time=0.012..891.334 rows=598211 loops=1)
  Filter: (status = 'paid'::text AND created_at >= (now() - '30 days'::interval))
  Rows Removed by Filter: 9401789
  Buffers: shared hit=12044 read=203388
Planning Time: 0.211 ms
Execution Time: 934.552 ms

На что смотреть построчно:

• cost=0.00..215432.00 — расчётная стоимость: стартовая и полная. Сравнивайте её с cost альтернативного плана — часто полезно временно отключить Seq Scan (SET enable_seqscan = off; в текущей сессии) и посмотреть, какую стоимость даёт индексный план.
• rows (планируемое) vs actual rows — если эти числа расходятся на порядок, статистика устарела и надо делать ANALYZE orders, а не менять индексы.
• Rows Removed by Filter — сколько строк было прочитано и отброшено. Большое число здесь при Seq Scan — нормально. Но то же самое поле на Index Scan сигналит о плохой селективности индекса.
• Buffers: shared hit / read — сколько страниц взято из кеша (hit), а сколько реально читалось с диска (read). Большая доля read при регулярно повторяющемся запросе — повод посмотреть на объём shared_buffers, это разобрано в статье про тюнинг PostgreSQL на выделенном сервере.
• Planning Time vs Execution Time — если планирование само по себе занимает заметное время на фоне выполнения, это чаще сигнал о сложном запросе с множеством join, а не о проблеме индекса.

Более наглядно план читается через визуализацию — вставьте вывод EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) в explain.dalibo.com или depesz.com/explain, оба подсвечивают самые дорогие узлы плана цветом.

Как читать план запроса: EXPLAIN в MySQL

MySQL традиционно даёт менее подробный вывод, чем PostgreSQL, но с версии 8.0 поддерживает EXPLAIN ANALYZE, которая тоже выполняет запрос и показывает фактические цифры в древовидном формате.

EXPLAIN
SELECT id, customer_id, total
FROM orders
WHERE status = 'paid'
  AND created_at >= NOW() - INTERVAL 30 DAY;

Классический табличный вывод:

+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | orders | ALL  | idx_status    | NULL | NULL    | NULL | 9812433 |    18.20 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+

Ключевые поля:

• type: ALL — это и есть полное сканирование таблицы, аналог Seq Scan. possible_keys показывает, что индекс idx_status теоретически применим, но колонка key пустая — оптимизатор его не выбрал.
• rows — оценка числа строк для просмотра, а filtered — процент от них, который реально пройдёт фильтр. Низкий filtered означает, что даже после индексного поиска пришлось бы читать заметную долю таблицы — оптимизатор посчитал это невыгодным.
• Extra: Using where — фильтрация происходит после чтения строки, без помощи индекса.

Для более точной диагностики полезен EXPLAIN ANALYZE (доступен начиная с MySQL 8.0.18), который показывает не только план, но и фактическое число итераций и время на каждый узел — аналог ANALYZE в PostgreSQL, только в собственном древовидном формате.

Типичные причины неожиданного full/seq scan

Когда план с полным сканированием появляется внезапно — там, где раньше был Index Scan, — почти всегда виновата одна из следующих причин.

Устаревшая статистика. PostgreSQL хранит статистику распределения данных в pg_statistic, обновляемую через ANALYZE (обычно автоматически через autovacuum по порогу изменённых строк). После массовой загрузки, миграции или удаления большого объёма данных статистика может не успеть обновиться, и планировщик считает по устаревшим числам. Ручной ANALYZE table_name; — первое, что стоит попробовать при внезапном Seq Scan. В MySQL аналог — ANALYZE TABLE table_name;.

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

Функция или приведение типа поверх колонки. WHERE lower(email) = 'a@b.com' не воспользуется обычным индексом на email, потому что индексируется значение колонки, а не результат функции. Тот же эффект даёт неявное приведение типов. Решение — функциональный индекс (CREATE INDEX ON table (lower(email))) либо явные касты в запросе.

Неудачный порядок колонок в составном индексе. Индекс (a, b, c) эффективен для условий по a, по a и b, по a, b и c — но почти бесполезен для запроса, где в WHERE есть только b или только c, без a. Подробнее — в статье о работе B-дерева внутри индекса и его пределах.

LIKE с ведущим wildcard. WHERE name LIKE '%смит%' не может использовать обычный B-tree индекс — движку негде найти точку входа в дерево без известного префикса. Нужен полнотекстовый индекс (pg_trgm с GIN в PostgreSQL, FULLTEXT в MySQL) или внешний поисковый движок.

OR между условиями на разных колонках. WHERE status = 'paid' OR customer_id = 12345 планировщик PostgreSQL иногда умеет разложить на Bitmap Index Scan с объединением, но не всегда — если один из индексов отсутствует или устарела статистика по одной из веток, весь план скатывается в Seq Scan.

Индекс раздут (bloat). После частых UPDATE/DELETE без своевременного VACUUM индекс в PostgreSQL может занимать в разы больше места из-за мёртвых записей — его чтение перестаёт быть дешевле скана таблицы даже при хорошей селективности. Разбор — в статье про медленный VACUUM в PostgreSQL.

Что делать: пошаговая диагностика

1. Снять план через EXPLAIN ANALYZE (PostgreSQL) или EXPLAIN / EXPLAIN ANALYZE (MySQL) — без плана любые действия наугад. Сохраните вывод, чтобы сравнивать до/после изменений.
2. Сравнить планируемое и фактическое число строк. Расхождение в разы или на порядок — почти всегда сигнал устаревшей статистики. ANALYZE table; в PostgreSQL или ANALYZE TABLE table; в MySQL — дешёвая операция, стоит запускать первой.
3. Прикинуть селективность условия вручную, не полагаясь на интуицию:

   SELECT count(*) FILTER (WHERE status = 'paid') AS matched,
          count(*) AS total
   FROM orders;

Если matched / total больше нескольких процентов — вероятно, Seq Scan обоснован, и нужно менять не индекс, а сам подход к запросу.

1. Проверить, действительно ли применим существующий индекс — нет ли функции поверх колонки, совпадает ли порядок колонок в составном индексе с условиями WHERE, не мешает ли неявное приведение типа.
2. Для пограничных случаев временно отключить Seq Scan и сравнить cost. SET enable_seqscan = off; в отдельной сессии (не в конфиге прод-инстанса на постоянной основе) покажет, насколько план по индексу дороже или дешевле. Если разница минимальна — планировщик прав.
3. Если индекс объективно нужен, но не подходит по структуре — рассмотреть покрывающий индекс (INCLUDE в PostgreSQL, чтобы избежать похода в heap), частичный индекс (условие вроде WHERE status = 'paid' прямо в определении индекса, если запросы всегда фильтруют по конкретному значению) или партиционирование таблицы по диапазону дат — тогда Seq Scan будет идти не по всей таблице, а по одной секции.
4. Проверить железо, если Seq Scan неизбежен по логике запроса. Раз последовательное сканирование — легитимный план, имеет смысл убедиться, что оно быстрое: таблица должна помещаться в буферный кеш или читаться с NVMe, а не с медленного сетевого диска. На выделенном сервере с NVMe и достаточным RAM под shared_buffers полный скан большой таблицы проходит заметно быстрее, чем на переподписанной VPS с сетевым хранилищем.

Общая логика планировщика запросов и то, как он выбирает между несколькими альтернативными планами (не только Seq Scan vs Index Scan, но и порядок join, выбор Hash Join против Nested Loop), подробнее разобрана в статье как планировщик запросов выбирает план и где ошибается.

Когда не нужно бороться с Seq Scan

Не всякий Seq Scan — проблема, которую нужно устранять любой ценой. Есть ситуации, где полное сканирование объективно правильный выбор, и попытка «заставить» базу использовать индекс только ухудшит производительность:

• Аналитические запросы, читающие большую часть таблицы (отчёты, агрегации по всей истории) — там Seq Scan часто быстрее, особенно если добавить pg_prewarm для прогрева кеша перед регулярным batch-отчётом, либо вынести такие запросы в отдельную аналитическую БД, не нагружая транзакционную.
• Небольшие таблицы-справочники — планировщик почти всегда выберет Seq Scan независимо от наличия индекса, потому что прочитать одну-две страницы таблицы дешевле, чем идти через индекс. Это нормально.
• Разовые миграционные или служебные запросы вне горячего пути приложения — оптимизация под них обычно не окупает усложнение схемы индексов.

Стоит бороться с Seq Scan только тогда, когда он либо неожиданно заменил ранее работавший Index Scan (ищите причину — статистика, bloat, изменившееся распределение данных), либо когда он реально создаёт узкое место в горячем пути при объективно высокой селективности условия.

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