К содержимому журнала

Инфраструктура

Медленные запросы в PostgreSQL: путь от симптома до плана выполнения

В пятницу вечером дежурному прилетает тикет: страница заказов грузится четыре секунды. Через час таких тикетов три. Это не происшествие, а классический маршрут от симптома в мониторинге до виноватого оператора в плане выполнения — и полезно заранее знать все его станции.

Энвер Джапаровобновлено 24.09.20254 мин чтения

Слои горизонтальных линий, сгущающихся к центру, как строки плана запроса
Содержание

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

Симптом: страница заказов грузится четыре секунды #

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

slow_query.sqlSQL
-- Отчёт по последним заказам клиента: в продакшене занимает 4,2 секунды
SELECT o.id,
       o.created_at,
       o.total_amount,
       count(oi.id) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'user@example.com'
  AND o.created_at >= now() - interval '90 days'
  AND o.status <> 'draft'
ORDER BY o.created_at DESC
LIMIT 20;

Запрос выглядит безобидно: фильтр по клиенту, ограничение по датам, счётчик позиций. Ни одного очевидного преступления — и именно поэтому «на глаз» его не чинят. Первое правило: медленный запрос диагностируется планом, а не чтением SQL. Интуиция ошибается в половине случаев, а цена ошибки — индекс, который ничего не ускорит, но замедлит каждую запись.

Диагностика: план вместо догадок #

Инструмент один — EXPLAIN (ANALYZE, BUFFERS) . ANALYZE действительно исполняет запрос и показывает фактическое время и фактическое число строк на каждом операторе, BUFFERS добавляет главное: сколько страниц база взяла из кэша, а сколько прочла с диска. Смотрите три вещи: где оператор с наибольшим фактическим временем, где оценки планировщика разошлись с реальностью в разы и где buffers read существенно больше buffers hit.

explain_output.txtSQL
Limit (actual time=4187.431..4187.437 rows=20 loops=1)
  Buffers: shared hit=121842 read=8421
  ->  Sort (actual time=4187.428..4187.431 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 29kB
        ->  Hash Join (actual time=14.322..3944.180 rows=1284312 loops=1)
              Hash Cond: (oi.order_id = o.id)
              Buffers: shared hit=121842 read=8421
              ->  Seq Scan on order_items oi
                    (actual time=0.021..1912.445 rows=8941450 loops=1)
                    Buffers: shared hit=104980 read=7320
              ->  Hash (actual time=13.855..13.856 rows=2412 loops=1)
                    ->  Nested Loop (actual time=0.081..12.902 rows=2412 loops=1)
                          ->  Index Scan on customers c
                                (actual time=0.031..0.540 rows=1 loops=1)
                                Index Cond: (email = 'user@example.com')
                          ->  Index Scan on orders o
                                (actual time=0.041..12.201 rows=2412 loops=1)

План рассказывает историю целиком. Nested Loop по клиентам отработал мгновенно: единица строк, индексы на месте. А дальше планировщик взял девять миллионов строк позиций полным сканированием и присоединил их к двум тысячам четырём сотням заказов клиента, получив миллион двести тысяч строк. Затем отсортировал этот миллион, чтобы отдать двадцать записей. Восемь тысяч страниц пришли с диска — кэш на таком объёме уже не спасает.

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

Лечение: индекс под реальный запрос #

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

fix_index.sqlSQL
-- Индекс под конкретный фильтр и сортировку
CREATE INDEX CONCURRENTLY orders_customer_recent_idx
  ON orders (customer_id, created_at DESC)
  INCLUDE (total_amount, status)
  WHERE status <> 'draft';

-- После массовых загрузок не забываем освежить статистику
ANALYZE orders;

Разберём каждую часть рецепта. Составное начало (customer_id, created_at DESC) совпадает и с фильтром, и с сортировкой — сортировка исчезает из плана, LIMIT отдаёт первые двадцать записей и останавливается. Частичное условие WHERE status <> 'draft' выкидывает черновики из индекса ещё на записи: индекс меньше, покрывает ровно то, что нужно запросу. INCLUDE добавляет суммы и статусы прямо в индекс — дополнительные столбцы не участвуют в поиске, но позволяют закрыть выборку без обращения к таблице.

Результат: 4,2 секунды против двадцати восьми миллисекунд, план — по индексу, без сканирований и без сортировки. Плата скромная: чуть более дорогая запись в orders и один лишний индекс в схеме. CONCURRENTLY не блокирует запись при построении — на живой таблице это единственный допустимый способ.

Чтение плана: типовые находки
Оператор планаТипичная причинаЛечение
Seq Scan по большой таблицеНет индекса под фильтр или планировщик счёл его дорожеПроверить актуальность статистики (ANALYZE), добавить индекс под реальные предикаты
Sort по миллионам строкORDER BY без подходящего индексаСоставной индекс с нужным порядком столбцов и направлением сортировки
Nested Loop с миллионами итерацийОценка строк занижена: планировщик ждёт сотни, а получает миллионыОсвежить статистику, при необходимости повысить её детализацию
Hash: batches с выгрузкой на дискwork_mem слишком мала для хеш-таблицыПоднять work_mem для сессии или уменьшить объём данных до соединения

Чему учит этот случай #

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

Последнее особенно важно: выигрыш в сто пятьдесят раз на одном запросе ничего не говорит о системе в целом — и об этом мы подробно писали в материале об измерении производительности . Один бенчмарк — не доказательство; доказательство — p95 эндпоинта на продакшене через неделю после релиза. Именно поэтому каждый такой маршрут от симптома до плана стоит заканчивать не только индексом, но и записью в базе знаний команды: следующая деградация почти никогда не повторяет предыдущую, но всегда повторяет её логику.

Все статьи