Почему база демонстративно игнорирует индекс, который вы для неё создали

MAATRIX

Вы честно создали индекс под запрос, который тормозил. EXPLAIN показывает Seq Scan вместо Index Scan, как будто индекса вообще не существует. Первая реакция — списать это на глюк планировщика или устаревшую статистику. Но чаще всего планировщик прав: он посчитал стоимость обоих путей и полный скан таблицы действительно оказался дешевле. Разберёмся, почему так происходит и как отличить правильное решение от настоящей проблемы.

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

Планировщик PostgreSQL (в MySQL с InnoDB логика похожая, хотя оптимизатор устроен проще) не смотрит на структуру запроса «на глаз» и не выбирает путь по привычке. Для каждого возможного плана выполнения он считает условную стоимость в абстрактных единицах — не в миллисекундах, а в величине, пропорциональной количеству операций ввода-вывода и процессорного времени. Эта стоимость складывается из двух базовых параметров: seq_page_cost — цена чтения одной страницы данных последовательно, по умолчанию 1.0, и random_page_cost — цена чтения одной страницы в случайном порядке, по умолчанию 4.0.

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

Для полного скана (Seq Scan) оценка простая: нужно прочитать все страницы таблицы последовательно. Для скана по индексу (Index Scan) оценка сложнее и складывается из двух частей:

• чтение страниц самого индекса (B-дерева) — это тоже не всегда последовательное чтение, но обычно индекс компактнее таблицы и частично лежит в кэше;
• для каждой найденной строки — отдельное обращение к таблице (heap fetch), чтобы получить актуальную версию строки со всеми столбцами. Именно это второе слагаемое и решает исход спора.

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

Что такое избирательность условия и почему она решает всё

Избирательность (selectivity) — это доля строк таблицы, которые удовлетворяют условию WHERE. Если из миллиона строк условию соответствуют 100 — избирательность высокая (0.0001), условие «избирательно» отсекает почти всё лишнее. Если условию соответствуют 400 000 строк — избирательность низкая, условие почти ничего не отсекает.

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

Наглядный пример на таблице заказов:

-- Высокая избирательность: конкретный заказ по уникальному номеру
SELECT * FROM orders WHERE order_number = 'A-2026-885421';
-- Индекс почти всегда выигрывает: одна строка из миллионов

-- Низкая избирательность: почти все заказы имеют статус 'completed'
SELECT * FROM orders WHERE status = 'completed';
-- Если 80% заказов имеют этот статус — планировщик пойдёт в Seq Scan,
-- и это будет верное решение, а не ошибка

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

Почему чтение через индекс — это россыпь случайных обращений

Важно понимать физику происходящего, а не просто верить цифре 4.0. Таблица на диске — это последовательность страниц фиксированного размера (в PostgreSQL обычно 8 КБ), и строки внутри неё физически лежат в порядке вставки (если только вы явно не переупорядочивали данные через CLUSTER). Индекс — это отдельная структура (обычно B-дерево), которая хранит отсортированные значения индексируемого столбца и указатели (TID) на физическое положение соответствующей строки в таблице.

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

На вращающемся диске такой скачущий доступ означает лишние перемещения головки на каждое обращение. На SSD физического перемещения нет, но случайный доступ всё равно медленнее последовательного — из-за размера операции ввода-вывода, накладных расходов на каждый отдельный запрос к контроллеру и меньшей эффективности предвыборки (prefetch), которая хорошо работает именно на последовательных паттернах. Разрыв сохраняется даже на NVMe, просто не в разы, как на HDD — точный коэффициент зависит от конкретного диска, и его правильнее измерить на своём железе, а не подгонять по умолчанию (ниже — как это сделать).

Есть исключение: если физический порядок строк сильно коррелирует с порядком индекса (например, id автоинкрементный и вы фильтруете по диапазону id), обращения к таблице после индексного поиска перестают быть случайными — они идут почти подряд. Планировщик видит эту корреляцию в статистике (столбец correlation в pg_stats) и снижает оценочную стоимость индексного скана. Поэтому один и тот же индекс на разных таблицах может вести себя по-разному.

Где проходит граница, после которой полный скан дешевле

У этой границы нет универсального процента вроде «после 20% строк индекс невыгоден» — такие цифры кочуют по интернету, но на практике зависят от нескольких факторов сразу: ширины строки (сколько строк помещается в одну страницу таблицы), того, насколько таблица и индекс уже в кэше (shared_buffers и файловый кэш ОС — планировщик учитывает это через параметр effective_cache_size, и если горячие данные в памяти, разница между случайным и последовательным доступом почти стирается), корреляции физического порядка строк с порядком индекса и реальной скорости случайного чтения именно на вашем диске — быстрый NVMe и медленный сетевой диск в облаке дают совершенно разное соотношение random_page_cost к seq_page_cost.

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

Отдельно стоит сказать про параметр random_page_cost. Значение 4.0 — это наследие эпохи вращающихся дисков, и на современных SSD-инстансах имеет смысл его снижать, ориентируясь на реальную разницу между последовательным и случайным чтением на своём накопителе (её стоит замерить, а не гадать):

-- Пример снижения для SSD-хранилища — конкретное значение подбирайте по своему диску
ALTER SYSTEM SET random_page_cost = 1.5;
SELECT pg_reload_conf();

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

Как проверить, что план на самом деле верный, а не планировщик ошибся

Прежде чем менять код или настройки, стоит убедиться, что дело именно в осознанном выборе, а не в устаревшей статистике или кривой оценке. Основной инструмент — EXPLAIN (ANALYZE, BUFFERS), который не только показывает выбранный план, но и реально выполняет запрос, сравнивая оценку с фактом:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..48500.00 rows=812000 width=120)
                    (actual time=0.015..410.223 rows=798452 loops=1)
  Filter: (status = 'completed'::text)
  Rows Removed by Filter: 201548
  Buffers: shared hit=32000 read=16500
Planning Time: 0.180 ms
Execution Time: 428.910 ms

Смотрите на две вещи. Первая — насколько оценка (rows=812000 в скобках cost) совпадает с фактом (actual ... rows=798452). Если расхождение в разы или на порядки — статистика устарела или собрана недостаточно детально. Если оценка близка к факту, как в примере выше — планировщик считал правильно, и Seq Scan обоснован тем, что условие действительно отсекает мало строк.

Вторая вещь — секция Buffers: сколько страниц взято из кэша (hit) и сколько реально прочитано с диска (read). Если почти всё из кэша, разница между последовательным и случайным доступом для этого запроса почти не имеет значения — тогда даже формально «правильный» индексный план не даст заметного выигрыша по времени.

Если расхождение оценки и факта большое, первым делом стоит обновить статистику вручную и, при необходимости, увеличить её детализацию:

-- Обновить статистику по таблице
ANALYZE orders;

-- Увеличить детализацию гистограммы для конкретного столбца
-- (по умолчанию 100, для сильно неравномерных данных иногда нужно больше)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

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

Что делать, если индекс всё равно нужен

Если после проверки оказалось, что планировщик прав и полный скан действительно дешевле для типичного запроса — это сигнал не бороться с планировщиком, а изменить сам подход к задаче.

Частичный индекс (partial index). Если вас интересует не «все заказы со статусом completed», а только редкий срез — например, заказы в статусе refunded, которых объективно мало — постройте индекс только по этому срезу:

CREATE INDEX idx_orders_refunded ON orders (created_at)
WHERE status = 'refunded';

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

Покрывающий индекс (covering index). Если проблема не в избирательности, а в лишних обращениях к таблице за столбцами, которых нет в индексе, добавьте их через INCLUDE — тогда для части запросов не понадобится идти в таблицу вообще (Index Only Scan), при условии что карта видимости актуальна:

CREATE INDEX idx_orders_status_covering ON orders (status)
INCLUDE (order_number, total_amount);

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

Переформулировать задачу. Если вам регулярно нужен весь список заказов по частому статусу — возможно, дело не в ускорении точечного SELECT, а в том, чтобы считать такие агрегаты заранее (материализованное представление, кэш на уровне приложения). Разница между тем, когда индекс действительно ускоряет запрос, а когда только добавляет накладные расходы, разобрана отдельно.

Если же вы уверены, что план ошибочный, а статистика свежая — можно временно отключить полный скан на уровне сессии и сравнить время выполнения:

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';
SET enable_seqscan = on; -- обязательно вернуть обратно

Это диагностический приём, а не постоянная настройка: если индексный план оказался быстрее на факте, значит стоимость в конфигурации сервера (random_page_cost, effective_cache_size) не соответствует реальному железу — правильнее поправить её, а не отключать enable_seqscan навсегда, это ударит по всем остальным запросам в базе. Общую настройку сервера под конкретное железо и нагрузку стоит свести в один проход, а не подбирать параметры по одному — этому посвящён отдельный разбор тюнинга PostgreSQL под конкретный сервер.

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