Индексы в PostgreSQL: как найти и ускорить медленные запросы

Медленный запрос в PostgreSQL почти всегда ускоряется одним правильным индексом, но угадывать его не нужно: план выполнения из EXPLAIN ANALYZE показывает, где база читает лишние строки. Порядок простой — найти медленные запросы через pg_stat_statements, посмотреть план, добавить индекс под фильтр и сортировку и проверить результат. Лишние индексы при этом замедляют запись.

Разберём этот порядок по шагам, с примерами запросов и типичными ошибками.

Почему запросы в PostgreSQL становятся медленными?

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

Типичные причины:

• нет индекса под условие WHERE или под сортировку ORDER BY;
• индекс есть, но условие написано так, что он не используется, — например, функция над столбцом;
• приложение делает сотни мелких запросов вместо одного (проблема N+1);
• устаревшая статистика, из-за которой планировщик выбирает неудачный план;
• запрос тянет лишние столбцы или все строки, когда нужна одна страница.

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

Как найти медленные запросы?

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

-- Топ запросов по суммарному времени выполнения
SELECT
    round(total_exec_time::numeric / 1000, 1) AS total_sec,
    calls,
    round(mean_exec_time::numeric, 1)          AS avg_ms,
    left(query, 120)                           AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Расширение нужно включить в shared_preload_libraries и создать командой CREATE EXTENSION pg_stat_statements. В управляемых облачных базах оно обычно уже доступно. Дополнительно полезен параметр log_min_duration_statement, который пишет в лог все запросы дольше заданного порога.

Как читать план выполнения запроса?

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

На что смотреть в первую очередь:

1. Seq Scan по большой таблице — база читает её целиком; часто это признак отсутствия индекса.
2. Rows Removed by Filter — сколько строк прочитано и выброшено; чем больше, тем хуже.
3. Sort с большим числом строк — сортировка не использует индекс.
4. Разница между rows в оценке и фактическим числом строк — признак устаревшей статистики.

Фото: Unsplash

Какой индекс создать под запрос?

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

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
    ON orders (customer_id, status, created_at DESC);

CONCURRENTLY позволяет создать индекс, не блокируя запись в таблицу, — на продакшене это обязательно. Какие индексы выбирать под разные задачи:

Тип индекса	Для чего подходит	Пример
B-tree (по умолчанию)	Равенство, диапазоны, сортировка	Заказы клиента по дате
Составной	Несколько условий и сортировка сразу	(customer_id, status, created_at)
Частичный	Условие по небольшой части таблицы	Только активные заказы: WHERE status = 'new'
По выражению	Условие с функцией над столбцом	lower(email) для поиска без учёта регистра
GIN	JSONB, массивы, полнотекстовый поиск	Поиск по полям в JSONB
BRIN	Огромные таблицы, данные упорядочены по времени	Логи и события по дате

Когда индекс не используется

Индекс не поможет, если условие не совпадает с тем, как он построен. WHERE lower(email) = ... не использует индекс по email — нужен индекс по выражению. Составной индекс (customer_id, status) не поможет запросу, который фильтрует только по status. А если условие выбирает большую часть таблицы, полный перебор действительно быстрее, и планировщик прав.

Почему лишние индексы вредят?

Лишние индексы вредят, потому что каждый из них обновляется при каждой вставке и изменении строки, занимает место на диске и в памяти. Таблица с десятком индексов, половина из которых не используется, медленнее принимает данные и дольше восстанавливается из бэкапа.

Как держать индексы в порядке:

• проверяйте неиспользуемые индексы по pg_stat_user_indexes: столбец idx_scan у них равен нулю;
• удаляйте дубликаты — индекс (a) не нужен, если есть (a, b);
• не создавайте индекс «на всякий случай» — только под реальный запрос из статистики;
• после массовых изменений данных запускайте ANALYZE, чтобы обновить статистику.

Если вы ещё выбираете базу для проекта, возможности индексов — один из доводов в пользу PostgreSQL, которые мы разбирали в статье PostgreSQL или MySQL.

Коротко

• Ищите медленные запросы через pg_stat_statements, сортируя по суммарному времени.
• Читайте план через EXPLAIN (ANALYZE, BUFFERS): Seq Scan и много отброшенных строк — сигнал к индексу.
• Составной индекс строится так: сначала столбцы равенства, потом сортировка или диапазон.
• На продакшене создавайте индексы с CONCURRENTLY, чтобы не блокировать запись.
• Удаляйте неиспользуемые индексы — они замедляют запись и занимают память.