Аудит индексов Postgres с помощью pg_stat: как найти медленные и неиспользуемые индексы

Что такое аудит индексов Postgres (и чем он не является)

Аудит индексов Postgres — это проверка реальной картины по тому, что ваша база делает в продакшене. Он отвечает на практические вопросы: какие запросы медленные, какие индексы используются, какие индексы никогда не трогают и куда уходит время.

Цель — не собрать "всё, что может пригодиться". Цель — ускорить самые нагруженные пути и сократить избыточные расходы.

В разработке индексы часто кажутся в порядке, потому что трафик чистый: маленькие таблицы, предсказуемые запросы и несколько типичных путей. В продакшене всё сложнее. Таблицы растут, фильтры и сортировки меняются, проявляются пограничные случаи, и многие пользователи одновременно бьют по одним и тем же эндпоинтам. Запрос, который "достаточно быстрый" на 10 000 строк, может рухнуть на 10 миллионах, если начинает делать последовательные сканирования или большие сортировки.

Хороший аудит помогает:

• Определить конкретные медленные шаблоны запросов под реальной нагрузкой
• Подтвердить, какие индексы реально помогают сегодня
• Найти индексы, которые стоят места и замедляют записи без отдачи
• Решить, что добавить или изменить, и как вы будете доказывать эффективность

"Реальная статистика запросов" означает измеренное поведение живой нагрузки: как часто выполняется запрос, сколько времени занимает, сколько строк возвращает и бывают ли всплески. Гадать по схеме рискованно. Схема может подсказать, что можно проиндексировать, но не скажет, какие фильтры реально используют пользователи и какие запросы доминируют по времени работы базы.

Также важно понимать компромиссы. Индексы не бесплатны:

• Они ускоряют чтение (SELECT), когда соответствуют фильтрам, соединениям и порядку сортировки.
• Они могут замедлять записи (INSERT/UPDATE/DELETE), потому что каждая модификация должна обновлять индексы.
• Они увеличивают объём хранения и работу по обслуживанию (vacuum, bloat, бэкапы).

Поэтому аудит — это не "поставить индексы везде". Это «добавить правильные, удалить мусор и проверить результат по статистике». Такое часто встречается в кодовых базах, сгенерированных AI (например Cursor, Replit или v0), где запросы часто попадают в продакшен без тщательной индексации. В демо всё работает, но продакшен быстро показывает узкие места.

Перед началом: убедитесь, что статистика надёжна

Аудит индексов полезен только настолько, насколько достоверны статистики. Если база перезапускалась, происходил failover или статусы были сброшены, вы можете ошибочно добавить или удалить индекс.

Сначала проверьте, что вы можете читать нужные представления. Большая часть сигналов приходит из системных view, таких как pg_stat_*. На управляемом Postgres вы можете не быть суперпользователем, но обычно достаточно роли с правами чтения для общих pg_stat представлений.

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

• Убедитесь, что вы можете выполнить запросы к pg_stat_database, pg_stat_user_tables и pg_stat_user_indexes без ошибок.
• Проверьте, не сбрасывались ли статистики недавно (после рестарта, failover или ручного сброса).
• Подтвердите, что запросы вашего приложения не обходят Postgres в основном (слой кеширования может скрывать реальную нагрузку).
• Запускайте аудит на той же базе, которая обслуживает реальный трафик.

Если доступен pg_stat_statements, аудит становится гораздо надежнее — он агрегирует реальное поведение запросов во времени (вызовы, общее время, среднее время). Его нужно установить и включить заранее, и он начнёт отслеживать запросы только после включения.

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

Избегайте искажённого окна, если только само искажение не та проблема, которую вы исследуете. Не делайте аудит сразу после:

• Большого деплоя, который изменил шаблоны запросов
• Бэкапа/миграции, которая заливала базу
• Массового импорта, нехарактерного для обычного трафика
• Инцидента, когда система была деградирована

Одна классическая ловушка: новая фича случайно запускает N+1 цикл запросов. Тогда статистика будет требовать индексы, хотя реальное решение — остановить взрыв запросов.

Представления pg_stat, на которые вы будете опираться (быстрая карта)

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

Эти view покрывают большинство нужд:

• pg_stat_statements: табло нормализованных запросов с суммами и средними по времени, вызовам и возвращаемым строкам. Здесь вы находите шаблоны запросов, которые съедают больше всего латентности.
• pg_stat_user_tables: как доступаются таблицы. Ключевой сигнал — идёт ли трафик через индексы или через последовательные (full) сканирования.
• pg_stat_user_indexes: как часто используется каждый индекс. Полезно для поиска индексов, которые кажутся важными, но имеют почти нулевое использование.
• pg_statio_user_tables: читаются ли таблицы из кеша или с диска. Высокие чтения с диска часто коррелируют с видимой пользователю задержкой.
• pg_statio_user_indexes: то же самое для страниц индексов — кеш или диск.

Распространённое недоразумение: количество использований и влияние — не одно и то же.

• Счётчики использования (например, «сколько раз индекс сканировался») показывают популярность, а не экономию времени.
• Сигналы времени и I/O (из pg_stat_statements и pg_statio_*) показывают реальную боль.

Хороший рабочий процесс: найдите дорогие формы запросов в pg_stat_statements, затем используйте статистики по таблицам и индексам, чтобы понять, почему эти запросы медленные (последовательные сканирования, чтения с диска, плохая селективность и т. п.).

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

Пошагово: простой аудит индексов за 30–60 минут

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

Шаг 1: Вытащите самые медленные запросы (по общему и среднему времени).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

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

Шаг 2: Найдите таблицы с большим количеством последовательных чтений. Это обычные кандидаты на недостающий индекс или неправильную форму запроса.

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

Шаг 3: Перечислите индексы, которые неиспользуются или почти не используются. Большие индексы с низким idx_scan — главные подозреваемые.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Шаг 4: Найдите таблицы с высокой нагрузкой записи, где лишние индексы могут замедлять операции. Каждый дополнительный индекс заставляет вставки и обновления делать больше работы.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Шаг 5: Сохраняйте снимки со временными метками, чтобы сравнивать до и после. Сохраняйте результаты в одном месте (CSV или небольшая таблица) и делайте заметки о внесённых изменениях.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

Как находить недостающие индексы по реальным сигналам нагрузки

Недостающий индекс обычно проявляется как сочетание двух факторов:

• Большая таблица, которую сканируют повторно
• Небольшой набор шаблонов запросов, которые регулярно оказываются дорогими

Начните с сигналов на уровне таблиц. В pg_stat_user_tables высокий seq_scan (особенно в паре с высоким seq_tup_read) для большой таблицы — сильный намёк, что запросы фильтруются или соединяются без индекса. Это не доказательство, но направление для поиска.

Затем свяжите это с шаблонами из pg_stat_statements. Ищите повторяющиеся формы доступа:

• Частые WHERE-фильтры (например, user_id, created_at, tenant_id)
• Ключи JOIN (внешние ключи часто виноваты)
• Колонки в ORDER BY, которые приводят к большим сортировкам
• LIMIT-запросы, которые всё равно читают много, потому что не могут эффективно использовать индекс

Короткое практическое правило: индексируйте сначала селективные колонки. Селективная колонка имеет много разных значений и быстро сужает выборку. WHERE user_id = 42 обычно селективен. WHERE status = 'active' часто нет.

Композитные индексы могут быть правильным решением, но порядок колонок важен, потому что Postgres эффективнее использует левую часть btree-индекса. Если ваш обычный запрос такой:

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Индекс (user_id, created_at) соответствует такому шаблону. Обратный порядок (created_at, user_id) часто помогает меньше, потому что он не сузит результат под конкретного пользователя на раннем этапе.

Как найти неиспользуемые индексы (и когда их не удалять)

Неиспользуемые индексы коварны: они добавляют стоимость записей, занимают диск и увеличивают нагрузку на vacuum и бэкапы. Цель — не агрессивно удалять индексы, а убрать те, которые действительно не приносят пользы реальной нагрузке.

Начните с pg_stat_user_indexes. Базовая проверка:

• idx_scan = 0 с момента последнего сброса статистики (кандидат)
• Очень мало сканов по сравнению с объёмом записей в таблице (кандидат)

Затем выполните здравую проверку контекста. Некоторые индексы существуют ради корректности, а не скорости:

• Индексы, поддерживающие PRIMARY KEY или UNIQUE
• Индексы, важные для проверок внешних ключей
• Индексы, реализующие правила приложения (например, «только одна активная подписка на пользователя»)
• Индексы, нужные для редких, но важных задач (отчёты, экспорт, форензика)

Классическая ошибка: вы видите idx_scan = 0 у orders_created_at_idx, удаляете его, а потом обнаруживаете, что месячная сверка или отчёт использует этот индекс. Если вы смотрите только неделю статистики, можно принять неправильное решение.

Осторожный процесс, который работает:

1. Составьте короткий список кандидатов (таблица, индекс, почему кажется неиспользуемым).
2. Проверяйте снова после полного бизнес-цикла (обычно 2–4 недели).
3. Если индекс всё ещё не используется, удаляйте его в плановое окно и наблюдайте за задержками и временем записи.
4. Сохраняйте определение индекса так, чтобы можно было быстро восстановить его при необходимости.

Найдите худших по латентности по статистике запросов

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

Две модели для сравнения:

• Один очень медленный запрос (2 секунды), который выполняется дважды в сутки
• «Средний» запрос (40 мс), который выполняется 10 000 раз в сутки

Второй обычно приносит больше вреда.

Простой старт:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Затем повторите сортировку по среднему времени, чтобы заметить «единичные» медленные выражения:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Для каждого кандидата зафиксируйте несколько фактов перед изменением индексов:

• Текст запроса и queryid
• Вызовы, общее время, среднее время
• Возвращаемые строки (возвращает ли он гораздо больше, чем нужно приложению?)
• shared_blks_hit vs shared_blks_read (в основном кеш или много чтений с диска?)
• Быстрый EXPLAIN (ANALYZE, BUFFERS) в безопасной среде

Индексы не всегда ответ. Высокая латентность также может быть из-за неудачных JOIN, умножения строк, отсутствия пагинации, загрузки больших JSON-блоков или слишком большой работы внутри SQL.

Валидировать и проектировать правильный индекс (без предположений)

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Читайте план как историю:

• Seq Scan означает, что Postgres читает много таблицы и фильтрует уже после. Это нормально для маленьких таблиц, но часто медленно для больших.
• Index Scan или Index Only Scan означает, что Postgres может прыгнуть к нужным строкам.
• BUFFERS показывает, сколько работы было в памяти и сколько с диска — это часто объясняет, почему запрос то быстрый, то медленный при пиках.

При проектировании индекса подгоняйте его под то, как запрос фильтрует и сортирует:

• Индекс на одну колонку, когда одна колонка делает основную фильтрацию (например user_id).
• Композитный индекс, когда фильтруют сразу по нескольким колонкам (например (user_id, status)). Порядок колонок важен.
• Частичный индекс, когда вы всегда запрашиваете подмножество (например только status = 'open'). Меньше индекс, меньшие расходы на запись.
• Включение колонки сортировки, чтобы избежать большого сортирования (например (user_id, status, created_at)).

Делайте изменения небольшими и обратимыми:

1. Добавьте один индекс, таргетированный на один высокозатратный запрос.
2. Повторите EXPLAIN (ANALYZE, BUFFERS), чтобы подтвердить улучшение плана.
3. Измерьте снова в сопоставимом окне.
4. Если время записи или время блокировок ухудшилось — откатитесь.

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

Частые ошибки, которые портят аудит индексов

Аудиты идут не так, когда вы принимаете одну статистику за абсолютную истину и действуете слишком быстро.

Распространённые ошибки:

• Удаление индекса, который защищает корректность. Индексы, поддерживающие PRIMARY KEY или UNIQUE, важны даже если выглядят "неиспользуемыми". Некоторые индексы критичны для внешних ключей.
• Гонка за idx_scan без взгляда на время и количество строк. Низкое число сканов не значит автоматически "безопасно удалить", а высокий счётчик — не означает автоматически "нужно оставить".
• Добавление пересекающихся индексов под каждый слегка другой запрос. Это приводит к дублированию, росту расходов на запись и усложнению обслуживания.
• Обвинение индексов, когда истинная проблема — bloat или устаревшие статистики. Если autovacuum и analyze отстают, планы могут деградировать, и сканирования стать медленнее.
• Тестирование в тихие часы и предположение, что в пик будет лучше. Тёплость кеша, конкуренция и нагрузка на запись меняют поведение планов.

Делайте по одному изменению за раз, измеряйте до и после при похожей нагрузке и держите план отката.

Короткий чеклист и следующие шаги

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

Ваш 30-минутный повторяемый отчёт

• Захватите топ-запросов по общему времени и по среднему времени (держите небольшой набор, например по 20).
• Отметьте большие таблицы с высоким последовательным чтением.
• Держите короткий список индексов, которые выглядят неиспользуемыми, с однострочным объяснением.
• Предложите до трёх изменений (добавить, удалить или скорректировать) и привяжите каждое к конкретному запросу или таблице.
• Перезапустите тот же отчёт после деплоя и сравните время, вызовы, строки и сканирования.

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

Когда нужен второй взгляд

Если ваше приложение было сгенерировано инструментами вроде Replit, Cursor или v0, проблемы с производительностью часто возникают из сочетания недостающих индексов, неэффективных запросов и небезопасных паттернов. FixMyMess (fixmymess.ai) специализируется на диагностике и ремонте AI-сгенерированных кодовых баз, включая проблемы запросов и схем, которые проявляются только под реальной нагрузкой. Если застряли, целевой аудит может помочь выбрать минимальный набор изменений, который действительно снизит латентность.