Что такое EXPLAIN ANALYZE и как читать BUFFERS в PostgreSQL?
EXPLAIN vs EXPLAIN ANALYZE
EXPLAIN показывает только план выполнения запроса и оценки планировщика (estimated rows, cost) — сам запрос не выполняется. EXPLAIN ANALYZE реально выполняет запрос (включая INSERT/UPDATE/DELETE!) и добавляет к плану фактические метрики: actual time, actual rows, loops. Это позволяет сравнить ожидания планировщика с реальностью — большое расхождение (например, ожидали 10 строк, получили 100000) обычно означает устаревшую статистику или неудачный запрос.
Важно: так как запрос выполняется по-настоящему, для модифицирующих запросов на проде нужно оборачивать в транзакцию с откатом:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'paid' WHERE id = 42;
ROLLBACK;
Опция BUFFERS
BUFFERS добавляет к каждому узлу плана статистику обращений к буферному кэшу PostgreSQL:
- shared hit — блоки найдены в shared buffers (кэш в памяти), быстро;
- shared read — блоки пришлось прочитать с диска (или из кэша ОС) — потенциально дорого;
- shared dirtied — блоки помечены как изменённые;
- shared written — блоки вытеснены из кэша на диск во время выполнения;
- temp read/written — использование временных файлов на диске, когда операции (сортировка, hash join, group by) не помещаются в
work_mem.
Пример вызова:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;
Как читать вывод
Index Scan using idx_orders_customer on orders
(cost=0.43..120.50 rows=50 width=200)
(actual time=0.05..1.20 rows=48 loops=1)
Buffers: shared hit=40 read=5
cost=0.43..120.50— оценка планировщика (условные единицы, не мс);actual time=0.05..1.20— реальное время старта первой строки и полного выполнения узла, в мс;rows=48vsrows=50— фактическое число строк близко к оценке, статистика актуальна;Buffers: shared hit=40 read=5— 40 блоков нашлись в кэше, 5 пришлось читать с диска. Cache hit ratio здесь = 40/(40+5) ≈ 89%.
На что смотреть в первую очередь
- Высокий read при частом выполнении запроса — данные не помещаются в shared_buffers или ОС-кэш вымывается другими запросами.
- temp read/written у Sort или HashAggregate — сигнал увеличить
work_memили переписать запрос. - Большое число loops у вложенных узлов (Nested Loop) — стоимость нужно умножать на loops, реальная суммарная стоимость может быть скрыта.
- Расхождение estimated vs actual rows в разы — устаревшая статистика (нужен
ANALYZEна таблице) или проблема с селективностью условия. - Sequential Scan с большим read на крупной таблице — возможно, отсутствует нужный индекс.
Дополнительно
Полезно комбинировать с FORMAT JSON для программного анализа, и с расширением pg_stat_statements/визуализаторами планов (explain.dalibo.com) для сложных запросов с множеством join.
Что хочет услышать интервьюер
Чётко объясняет разницу между EXPLAIN и EXPLAIN ANALYZE — оценка плана vs реальное выполнение
Знает, что BUFFERS показывает shared hit/read/dirtied/written и temp read/written
Умеет посчитать cache hit ratio из hit и read и объяснить, что означает низкий ratio
Понимает риск запуска EXPLAIN ANALYZE на изменяющих данные запросах и знает про обёртку в транзакцию с ROLLBACK
Может по расхождению estimated/actual rows и наличию temp-файлов диагностировать проблему (устаревшая статистика, нехватка work_mem, отсутствие индекса)
Пример: Безопасный запуск EXPLAIN ANALYZE для изменяющего запроса
BEGIN;
-- реально выполняет запрос, поэтому оборачиваем в транзакцию
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'paid'
WHERE id = 42;
-- откатываем, чтобы не изменить данные по-настоящему
ROLLBACK;
Пример: Пример чтения buffers в плане
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;
-- в выводе смотрим на каждый узел:
-- Buffers: shared hit=120 read=30 -- 30 блоков читались с диска, кэш прогрет не полностью
-- Buffers: temp read=200 written=200 -- сортировка не поместилась в work_mem, ушла на диск
Типичные ошибки
Запускают EXPLAIN ANALYZE на UPDATE/DELETE в проде без транзакции и отката, реально изменяя данные
Путают cost (условные единицы планировщика) с actual time (реальные миллисекунды)
Игнорируют секцию Buffers и делают выводы только по времени выполнения, упуская проблемы с диском
Не учитывают эффект холодного/тёплого кэша — сравнивают время первого и повторного запуска без пометки
Не понимают, что loops нужно учитывать при оценке суммарной стоимости вложенных узлов (Nested Loop)
AI-тренажеры
4.7
Практика в студии
