Индекс был, но планировщик его не брал: статистика устарела на два месяца

MAATRIX

Отчёт по заказам в админке работал за 40-60 мс годами, а потом за одну ночь стал занимать 10-12 секунд и класть под собой соединения к базе. Индекс на нужных колонках стоял на месте, \d его прекрасно показывал, REINDEX ничего не менял — а планировщик PostgreSQL всё равно упорно шёл в Seq Scan по таблице на десятки миллионов строк. Разбираемся, как «живой» индекс может годами простаивать без дела из-за статистики, которую никто не обновил.

Что сломалось: отчёт за 40 мс превратился в отчёт за 12 секунд

Таблица orders в проде — около 42 млн строк, стандартная модель заказов интернет-магазина: id, status, created_at, customer_id, ещё десяток служебных полей. Под самый частый запрос админки — «показать заказы в статусе X за последние N дней» — давно стоял составной индекс:

CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

Запрос простой:

SELECT id, customer_id, created_at, total
FROM orders
WHERE status = 'processing'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 200;

Он годами укладывался в десятки миллисекунд. Проблема проявилась не сразу и не резко — сначала отчёт стал «иногда подвисать», через неделю подвисания стали регулярными, а ещё через пару дней страница отчёта в рабочие часы стабильно отваливалась по таймауту 10 секунд на стороне бэкенда. Одновременно с этим на графике CPU сервера базы появились ровные плато — не пики, а именно плато на 15-20 минут, совпадающие по времени с всплесками обращений к отчёту. На сервере PostgreSQL 16 крутится на выделенном сервере с NVMe — то есть узкое место было явно не в диске.

Первая реакция была стандартной: посмотреть, не «потерялся» ли индекс. \d orders в psql показывал индекс на месте, pg_indexes — то же самое, размер индекса на диске разумный, никаких признаков bloat выше нормы. Индекс был жив. А вот EXPLAIN по тому же запросу говорил обратное тому, что ожидалось.

Что показали логи и метрики

Первым делом подняли pg_stat_statements, отсортировав по mean_exec_time за последние сутки:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%FROM orders%status%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Ровно тот запрос отчёта был на первом месте с ростом mean_exec_time примерно в 150-200 раз по сравнению с записями недельной давности в архиве метрик мониторинга. Дальше — очевидный шаг, EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at, total
FROM orders
WHERE status = 'processing'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 200;

План показал:

Limit  (cost=... rows=200 ...) (actual time=... rows=200 loops=1)
  ->  Sort  (cost=... )
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..1 830 442.00 rows=... width=...)
              Filter: (status = 'processing'::text AND created_at >= ...)
              Rows Removed by Filter: 41 ...
Planning Time: 0.6 ms
Execution Time: 11 842.3 ms

Планировщик честно перебирал почти всю таблицу построчно вместо того, чтобы взять готовый индекс по (status, created_at). При этом Buffers: shared hit и read показывали, что читались десятки тысяч страниц — ровно то, чего индекс должен был не допустить.

Одновременно в pg_stat_activity в моменты пиков стало видно по несколько параллельных сессий с этим же запросом в состоянии active, каждая держит по одному ядру CPU на полные 10-12 секунд — отсюда и плато на графике нагрузки: не аномальный всплеск, а обычный набор параллельных пользовательских запросов, каждый из которых внезапно стал в сто с лишним раз дороже.

Какие гипотезы отбросили

По порядку проверили пять версий, прежде чем добрались до настоящей причины.

1. Индекс повреждён. Сделали REINDEX INDEX CONCURRENTLY idx_orders_status_created на реплике для проверки — план запроса не изменился ни на йоту. Индекс был совершенно здоровым, просто планировщик решил его не использовать.

2. Диск не успевает, I/O bottleneck. Проверили iostat -x 1 и pg_stat_bgwriter — утилизация диска в норме, задержки чтения в единицах миллисекунд, никакого признака деградации хранилища. NVMe отрабатывал штатно.

3. Долгие транзакции держат блокировки и мешают использовать индекс. Посмотрели pg_locks и pg_stat_activity на предмет зависших транзакций и idle in transaction — ничего подозрительного, блокировок на orders не было вообще, только обычные короткие RowShareLock от самих select-запросов.

4. Индекс собран не на тех колонках или не в том порядке. Перепроверили условие в WHERE и ORDER BY против определения индекса — status первым, created_at вторым, ровно как в запросе. Индекс подходил идеально, CREATE INDEX был скопирован без ошибок из миграции.

5. Autovacuum вообще не работает на сервере. Проверили pg_stat_progress_vacuum и логи — autovacuum активно работал, воркеры регулярно проходили по другим таблицам базы. Значит, дело не в том, что процесс сломан целиком — что-то происходило конкретно с этой таблицей.

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

Как нашли реальную причину: статистика «застряла» в прошлом

Ключевая проверка — когда таблица последний раз анализировалась:

SELECT relname, last_analyze, last_autoanalyze,
       n_mod_since_analyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Результат объяснил всё сразу: last_autoanalyze — дата почти двухмесячной давности, а n_mod_since_analyze — несколько миллионов строк, изменённых с того момента без единого автоматического ANALYZE. Статистика планировщика по факту описывала таблицу такой, какой она была два месяца назад, а не такой, какая она сейчас.

За эти два месяца в таблицу заливались данные из миграции старого бэкенда заказов — крупными пакетами по 300-500 тысяч строк за раз, в основном со статусом imported_legacy, которого раньше почти не существовало. Распределение значений в колонке status за эти два месяца заметно сместилось, но pg_stats по-прежнему хранил гистограмму и n_distinct из состояния «до миграции»:

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

По устаревшей статистике status = 'processing' выглядел как значение, дающее очень большую долю строк таблицы — планировщик оценивал selectivity фильтра так, будто под условие попадёт заметная часть всех 42 млн строк, и при такой (ошибочной) оценке Seq Scan действительно дешевле по его формуле стоимости, чем чтение индекса вперемешку с обращениями к таблице. EXPLAIN без ANALYZE показывал плановую оценку rows= в разы больше, чем EXPLAIN ANALYZE показывал по факту — классический разрыв между estimated и actual rows, который в 9 случаях из 10 и есть диагностика проблемы со статистикой.

Дальше — вопрос, почему autovacuum два месяца не запускал ANALYZE сам. Ответ в формуле порога:

порог = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

С настройками по умолчанию (threshold = 50, scale_factor = 0.1) для таблицы в 42 млн строк порог получается около 4.2 млн изменённых строк. Пакеты миграции по 300-500 тысяч строк накапливались медленно и предсказуемо не добивали до этой отметки внутри одного цикла между более крупными операциями — а как только n_mod_since_analyze всё же переваливал порог, autovacuum и правда запускал ANALYZE, просто существенно позже, чем менялось реальное распределение данных, значимое для конкретного запроса. Для таблицы такого размера настройки по умолчанию, рассчитанные на небольшие и средние таблицы, попросту не годятся.

Подробнее о том, как устроен индекс и когда он вместо ускорения запроса становится лишней нагрузкой, разобрано в статье про то, как индекс ускоряет запрос и когда замедляет — там же объясняется, почему сам факт наличия индекса ничего не гарантирует без адекватной статистики.

Что изменили после инцидента

Первое и самое быстрое — ручной ANALYZE прямо во время инцидента:

ANALYZE VERBOSE orders;

План запроса поменялся сразу после команды — планировщик вернулся к Index Scan по idx_orders_status_created, время выполнения упало обратно к десяткам миллисекунд. ANALYZE не блокирует чтение и запись в таблицу (в отличие от VACUUM FULL), только собирает статистику, поэтому его можно смело гонять на проде в рабочее время.

Дальше — три постоянных изменения, чтобы не наступать на эти же грабли снова.

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

ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 1000
);

Для таблицы с 42 млн строк это опускает порог примерно до 840 тысяч изменённых строк вместо 4.2 млн — статистика обновляется в разы чаще именно там, где это действительно нужно, без лишней нагрузки на мелкие и средние таблицы базы, для которых настройки по умолчанию и так работают нормально. То же самое имеет смысл сделать для других крупных таблиц с активным потоком изменений — как это выбирать по размеру и характеру нагрузки, в деталях разобрано в статье про тюнинг PostgreSQL и типичные ошибки.

Добавили обязательный ANALYZE в скрипт миграций и пакетных загрузок. Любой скрипт, который вставляет или массово обновляет больше условной сотни тысяч строк в таблице, теперь завершается явным ANALYZE по затронутой таблице — не полагаясь на то, что autovacuum успеет среагировать до того, как этими данными начнут пользоваться реальные запросы.

Настроили мониторинг возраста статистики. Простой запрос раз в час проверяет разрыв между текущим моментом и last_analyze/last_autoanalyze, а также долю изменённых строк:

SELECT relname,
       greatest(last_analyze, last_autoanalyze) AS last_stats,
       now() - greatest(last_analyze, last_autoanalyze) AS stats_age,
       n_mod_since_analyze,
       round(100.0 * n_mod_since_analyze / greatest(n_live_tup, 1), 1) AS modified_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000000
ORDER BY stats_age DESC NULLS FIRST;

Алерт срабатывает при stats_age больше нескольких дней или при modified_pct выше нескольких процентов на крупных таблицах — раньше это никто не отслеживал вообще, полагаясь на то, что autovacuum «сам разберётся». В целом за состоянием базы и её метриками удобнее следить не разовыми запросами, а постоянным дашбордом — как это собрать, описано в статье про мониторинг баз данных через Grafana.

Отдельно проговорили с командой: если запрос внезапно и без изменений в коде стал работать сильно медленнее, а нагрузка на диск и CPU при этом в норме — первым делом смотреть не на индексы и не на блокировки, а на pg_stat_user_tables и разрыв между оценкой планировщика и фактическим числом строк в EXPLAIN ANALYZE. Это самая частая и самая недооценённая причина деградации именно точечных, «внезапных» запросов на больших таблицах.

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