Оптимизация медленных SQL-запросов PostgreSQL

Оптимизация медленных SQL-запросов PostgreSQL

Вы ждёте 20 секунд на загрузку отчёта. Пользователи уходят, база данных — узкое горло. Типичная ситуация: один запрос к PostgreSQL выполняется 5 секунд, а таких запросов сотни в минуту. Мы разбираемся, почему это происходит, и устраняем проблему: снижаем время ответа БД в 3–5 раз на нагрузке. Диагностика занимает день, оптимизация — ещё пару дней. Все изменения документируются, замеры до и после обязательны.

Медленные запросы — основная причина плохого UX. 95% проблем производительности БД решаются одним из четырёх методов: добавлением индекса, переписыванием запроса, денормализацией или кешированием. Опыт показывает, что грамотная оптимизация 10–15 самых тяжёлых запросов может высвободить до 40% ресурсов сервера. Разберём, как диагностировать и исправлять медленные запросы в PostgreSQL.

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

pg_stat_statements — первое расширение, которое нужно включить на продакшне. Оно собирает статистику по каждому запросу: суммарное и среднее время, количество вызовов, стандартное отклонение. Коэффициент вариации (coeff_var) помогает выявить запросы с нестабильным планом.

Процесс диагностики в пять шагов:

1. Включите расширение pg_stat_statements (если отключено) и соберите статистику за несколько часов.
2. Выполните запрос топ-20 по total_exec_time.
3. Для каждого подозрительного запроса получите план через EXPLAIN (ANALYZE, BUFFERS).
4. Определите узлы плана: Seq Scan, Nested Loop, Hash Join с Batches > 1.
5. Примените соответствующую оптимизацию: добавьте индекс, перепишите запрос, настройте work_mem.

-- Включение pg_stat_statements shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all -- Топ-20 по суммарному времени SELECT round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, calls, round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct, left(query, 120) AS query FROM pg_stat_statements WHERE calls > 100 ORDER BY total_exec_time DESC LIMIT 20; 

coeff_var_pct — коэффициент вариации: высокий процент говорит о нестабильном плане (разные параметры дают кардинально разное время). Далее каждый подозрительный запрос прогоняем через EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT p.*, c.name AS category_name FROM products p JOIN categories c ON c.id = p.category_id WHERE p.status = 'published' AND p.created_at > NOW() - INTERVAL '30 days' ORDER BY p.created_at DESC LIMIT 50; 

Ключевые узлы в плане:

• Seq Scan на большой таблице — нет индекса или планировщик решил, что индекс не выгоден.
• Nested Loop с большим количеством итераций — N+1 на уровне SQL.
• Hash Join с Batches > 1 — не хватает work_mem.
• Sort без Index Scan на ORDER BY колонке — нет подходящего индекса.

Почему индексы не используются?

Даже при наличии индекса планировщик может его проигнорировать. Основные причины:

• Функция на колонке в WHERE (например, DATE(created_at))
• Низкая селективность (индекс на булевой колонке со смещённым распределением)
• Сортировка, не совпадающая с порядком индекса

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

Какие антипаттерны встречаются чаще всего?

-- Плохо: SELECT * тянет лишние колонки; OFFSET увеличивает нагрузку; OR не использует индекс; функция на колонке; NOT IN с NULL SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000; SELECT * FROM users WHERE email = $1 OR phone = $1; SELECT * FROM orders WHERE DATE(created_at) = $1; SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items); -- Хорошо: только нужные поля; keyset pagination; UNION ALL; range condition; NOT EXISTS SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50; SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1; SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2; SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id); 

Сравним методы пагинации:

Оптимизация JOIN: составные индексы

-- Добавляем составной индекс для типичного фильтра CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC); -- Запрос использует index scan без Sort SELECT id, total, status, created_at FROM orders WHERE user_id = $1 AND status = 'completed' ORDER BY created_at DESC LIMIT 10; 

Порядок колонок в индексе: сначала equality conditions (user_id = $1, status = 'completed'), затем range/sort (created_at DESC).

Настройка work_mem и LATERAL

Если в EXPLAIN ANALYZE видим external merge (Disk: ...) при Sort — увеличиваем work_mem для сессии:

SET work_mem = '64MB'; -- Выполняем тяжёлый аналитический запрос 

В postgresql.conf лучше оставить work_mem низким (4-8MB по умолчанию) и поднимать для конкретных запросов через SET LOCAL work_mem.

-- LATERAL: для row-dependent subqueries SELECT u.id, u.email, recent.total FROM users u CROSS JOIN LATERAL ( SELECT SUM(total) AS total FROM orders o WHERE o.user_id = u.id AND o.created_at > NOW() - INTERVAL '30 days' ) AS recent; 

LATERAL часто даёт лучший план, чем JOIN на агрегированный CTE.

Объём работ по оптимизации

• Аудит 10–15 самых тяжёлых запросов через pg_stat_statements и EXPLAIN ANALYZE
• Переписывание запросов с устранением антипаттернов
• Добавление и настройка индексов (включая составные и частичные)
• Настройка параметров PostgreSQL (shared_buffers, work_mem, effective_cache_size)
• Предоставление отчёта с замерами до/после и рекомендациями для команды
• Дополнительно: обучение разработчиков работе с планами запросов

В одном из проектов мы сократили время выполнения запросов с 4 секунд до 200 мс — это снизило нагрузку на сервер и позволило избежать upgrade инфраструктуры. Оптимизация 15 медленных запросов может высвободить значительную часть ресурсов сервера.

Сроки и стоимость

Диагностика и оптимизация 10–15 медленных запросов — 2–3 дня. Глубокий аудит схемы и запросов для высоконагруженного приложения — 3–5 дней. Стоимость рассчитывается индивидуально после оценки объёма.

Для старта проекта свяжитесь с нами в Telegram или по почте — мы проведём бесплатный анализ первых двух запросов. Закажите диагностику — и получите отчёт с замерами до/после. Мы гарантируем измеримое снижение времени запросов не менее чем на 30% — результат фиксируем до и после оптимизации.