PostgreSQL: 100% CPU из-за отсутствующих индексов — диагностика через pg_stat_user_tables и EXPLAIN (ANALYZE, BUFFERS)

Типичный симптом: база «жжёт CPU», а приложение просто ждёт

Ситуация знакомая: нагрузка на PostgreSQL растёт, top показывает 100% CPU на одном или нескольких ядрах, а время ответа сайта/API увеличивается. Часто корень проблемы — не «слабое железо», а банальное: запросы читают слишком много строк, потому что им не хватает подходящих индексов.

На уровне планов это обычно выглядит как Seq Scan (последовательное чтение таблицы) или как чтение большого объёма heap-страниц при плохо подходящем индексе. Наша цель — быстро и без догадок ответить на три вопроса:

• какие таблицы чаще всего читаются «в лоб» (seq scan);
• какие именно запросы делают это (slow query high cpu);
• какой индекс реально поможет и как создать его без простоя (create index concurrently).

Шаг 0. Зафиксируйте контекст, чтобы не «лечить не то»

Перед тем как копать индексы, стоит подтвердить, что упор действительно в CPU и обработку данных, а не в блокировки или I/O. Минимальный чек:

• CPU: высокий us/sy в top/htop, процессы postgres на верхушке.
• Диск: нет тотального iowait (если wa огромный — индексы тоже могут помочь, но действовать надо аккуратнее).
• Блокировки: нет массового ожидания locks (иначе сначала разбираемся с ними).

Быстрый взгляд на активность в самой БД:

SELECT now(), pid, usename, application_name, client_addr, state, wait_event_type, wait_event,
       date_trunc('second', now() - query_start) AS runtime,
       left(query, 200) AS query_preview
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

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

Если есть сомнения по диску (высокий wa), полезно параллельно снять короткий профиль I/O и убедиться, что вы не упёрлись в хранение. Для этого у нас есть отдельный разбор про диагностику диска: iostat/iotop/fio и выбор scheduler.

Шаг 1. Найдите кандидатов на missing indexes через pg_stat_user_tables

Для старта нам не нужны конкретные запросы — нам нужны таблицы, которые PostgreSQL часто читает последовательно. Для этого подходит pg_stat_user_tables, где удобно смотреть на seq_scan/seq_tup_read и сравнивать с idx_scan.

Запрос «первая помощь»: таблицы с большим количеством прочитанных строк в seq scan:

SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_live_tup, n_dead_tup,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 30;

Как читать результат:

• seq_scan — сколько раз выполнялось последовательное сканирование таблицы.
• seq_tup_read — сколько строк прочитали при этих сканированиях (ключевой показатель для «сжигания CPU»).
• idx_scan — сколько раз использовались индексы для доступа.
• n_live_tup/n_dead_tup — приблизительная оценка живых/мёртвых строк (если мёртвых много, может быть отдельная история про vacuum/блоат).

Эвристика простая: таблица с большим seq_tup_read и маленьким idx_scan — кандидат №1. Но не пытайтесь «проиндексировать всё»: seq scan бывает нормальным (маленькие справочники, запросы, читающие большую часть таблицы).

Считаем «долю индексов» и отсеиваем маленькие таблицы

Чтобы уменьшить шум, оцените долю индексных сканов и отсеките мелкие таблицы:

SELECT schemaname, relname,
       pg_total_relation_size(relid) AS bytes,
       seq_scan, idx_scan,
       CASE WHEN (seq_scan + idx_scan) = 0 THEN 0
            ELSE round(100.0 * idx_scan / (seq_scan + idx_scan), 2)
       END AS idx_scan_pct,
       seq_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) > 128 * 1024 * 1024
ORDER BY seq_tup_read DESC
LIMIT 30;

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

Шаг 2. Свяжите «плохую таблицу» с конкретными запросами (pg_stat_statements)

pg_stat_user_tables показывает, где горит, но не показывает, какой запрос это делает. Для поиска конкретных SQL удобнее всего pg_stat_statements.

Проверяем, доступно ли расширение:

SELECT name, installed_version
FROM pg_available_extensions
WHERE name = 'pg_stat_statements';

Если расширение не подключено, обычно включают его через параметры PostgreSQL (потребуется рестарт из-за shared_preload_libraries). Общая схема выглядит так:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SELECT pg_reload_conf();

Важно: после изменения shared_preload_libraries нужен рестарт PostgreSQL. Планируйте окно обслуживания.

Дальше смотрим «топ по суммарному времени» и параллельно «топ по среднему времени». Первый ловит массовые запросы, второй — редкие, но тяжёлые:

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       shared_blks_read, shared_blks_hit,
       left(query, 200) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

На что смотреть при slow query high cpu:

• большой total_exec_time при большом calls — «дробовик»: запрос короткий, но стреляет часто и суммарно сжигает CPU;
• большой mean_exec_time — «снайпер»: запрос редкий, но каждый раз тяжёлый;
• высокие shared_blks_read относительно shared_blks_hit — добавляется дисковая составляющая, но индекс часто всё равно помогает за счёт снижения объёма чтения.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Шаг 3. Подтвердите гипотезу через EXPLAIN (ANALYZE, BUFFERS)

Теперь берём конкретный запрос из приложения (или из pg_stat_statements) и прогоняем план с выполнением. Ключевой инструмент — EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Как интерпретировать результат, если подозреваем missing index:

• Seq Scan on table + большое actual time и много rows — запрос читает слишком много строк.
• Filter: условие применяется после чтения — значит, индекса по условию нет или он не подходит.
• Buffers: большие значения shared read/hit на узле Seq Scan — подтверждение массового чтения страниц.
• Nested Loop с большим числом итераций — иногда missing index находится не на основной таблице, а на таблице в JOIN.

Типичный портрет проблемы: запрос фильтрует по user_id и created_at, но таблица сканируется целиком, потому что нет составного индекса или выражение в WHERE «непригодно для индекса» (sargability ломает функция/приведение).

Частая ловушка: функция или приведение типов ломают использование индекса

Пример: WHERE date(created_at) = current_date. Такой запрос часто не использует индекс по created_at, потому что к колонке применена функция. Варианты лечения:

• переписать условие в диапазон: created_at >= current_date AND created_at < current_date + interval '1 day';
• или сделать функциональный индекс (если переписать нельзя).

Шаг 4. Подберите правильный индекс: что именно индексировать

Правильный индекс почти всегда следует из WHERE/JOIN/ORDER BY конкретного запроса. Практичные правила:

• Равенство слева, диапазон справа: для составного B-Tree индекса сначала колонки с =/IN, затем колонки с диапазоном (>, >=, BETWEEN).
• JOIN: индекс нужен на столбце условия соединения (часто на «дочерней» таблице).
• ORDER BY + LIMIT: индекс может отдавать строки уже в нужном порядке и резко снижать CPU и чтение.
• Частичные индексы: если запросы почти всегда работают по подмножеству (например, WHERE status = 'active'), partial index может быть идеальным.
• Поиск по подстроке: для LIKE '%...%' по тексту B-Tree не спасёт — обычно нужен GIN и триграммы (если это оправдано по данным и нагрузке).

Проверьте, что это не «ложный missing index»: статистика и селективность

Иногда индекс есть, но планировщик его не выбирает из-за неактуальной статистики или низкой селективности (условие выбирает слишком много строк). Перед созданием нового индекса полезно обновить статистику проблемной таблицы:

ANALYZE VERBOSE public.big_table;

А если у вас регулярно «распухают» большие таблицы и индексы из-за удаления/обновлений, имеет смысл держать под рукой рутину обслуживания и переиндексации. См. также: партиционирование и обслуживание индексов в PostgreSQL.

Шаг 5. Создаём индекс без простоя: CREATE INDEX CONCURRENTLY

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

CREATE INDEX CONCURRENTLY строит индекс в несколько фаз и минимизирует блокировки, но выполняется дольше и чувствителен к отменам/ошибкам: при прерывании индекс может остаться в состоянии invalid.

Пример составного индекса под WHERE:

CREATE INDEX CONCURRENTLY idx_big_table_user_created_at
ON public.big_table (user_id, created_at);

Пример partial index:

CREATE INDEX CONCURRENTLY idx_orders_active_user_created
ON public.orders (user_id, created_at)
WHERE status = 'active';

Практические нюансы:

• Команда не выполняется внутри транзакции — не оборачивайте в BEGIN/COMMIT.
• Планируйте место на диске: на время построения может потребоваться заметный объём.
• Если индекс создался invalid, часто проще удалить и создать заново после устранения причины прерывания.

Проверка состояния индексов:

SELECT i.relname AS index_name,
       ix.indisvalid, ix.indisready, ix.indislive
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
  AND t.relname = 'big_table'
ORDER BY i.relname;

Шаг 6. Перепроверьте план и нагрузку после индекса

После создания индекса заново прогоните EXPLAIN (ANALYZE, BUFFERS) для того же запроса и сравните:

• Seq Scan должен уйти в Index Scan или Bitmap Index Scan (зависит от селективности и структуры запроса).
• В BUFFERS обычно резко падает количество прочитанных страниц.
• Время выполнения и CPU уменьшаются (иногда в разы).

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

SELECT schemaname, relname, seq_scan, idx_scan, seq_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY seq_tup_read DESC
LIMIT 20;

Цель — увидеть, что у проблемной таблицы растёт idx_scan, а рост seq_tup_read становится адекватным для вашей нагрузки.

Что делать, если индекс «не помог»

Такое бывает. Частые причины:

• Запрос выбирает слишком много строк, и seq scan действительно дешевле (особенно если таблица небольшая или почти вся нужна).
• Неверный порядок колонок в составном индексе.
• Условие в запросе не использует индекс (функции, приведения типов, выражения).
• Проблема не в фильтре, а в JOIN (нужен индекс на другой таблице).
• Упор в сортировку/агрегацию: нужен индекс под ORDER BY или изменение запроса; иногда — настройка памяти (например, work_mem) под конкретный workload.

В таких случаях возвращайтесь к EXPLAIN (ANALYZE, BUFFERS) и ищите узел с максимальным вкладом по времени/буферам — оптимизировать нужно его, а не «таблицу вообще».

Мини-рутинка для админа: путь от 100% CPU до рабочего индекса

1. Снять активные запросы в pg_stat_activity и убедиться, что это не locks.
2. В pg_stat_user_tables найти топ по seq_tup_read, свериться с idx_scan.
3. Через pg_stat_statements определить 1–3 запроса, которые дают основной вклад во время.
4. Для каждого — EXPLAIN (ANALYZE, BUFFERS), найти Seq Scan или узел с максимальным вкладом.
5. Сформулировать индекс под WHERE/JOIN/ORDER BY.
6. Создать индекс через CREATE INDEX CONCURRENTLY.
7. Повторить EXPLAIN и контроль метрик (seq_tup_read, время запросов).

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Когда стоит остановиться и не плодить индексы

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

Если проблема повторяется регулярно (например, после релизов), заведите привычку: перед выкатыванием сложных запросов прогонять их на данных, близких к боевым, и смотреть планы. Это дешевле, чем тушить 100% CPU в пятницу вечером.

Если для проекта нужна предсказуемая производительность и отдельные ресурсы под базу, удобный путь — выделить PostgreSQL на VDS: проще контролировать CPU/RAM/диск и планировать обслуживание без сюрпризов соседей по серверу.