Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 20 · Раздел 1

Что такое explain plan?

4. Hash Left Join → соединил 5. HashAggregate → сгруппировал

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 Junior Level

EXPLAIN — команда PostgreSQL, которая показывает, как база данных планирует выполнить запрос, не выполняя его.

Простая аналогия: Перед поездкой GPS показывает маршрут: какие дороги, сколько поворотов, примерное время. EXPLAIN — это GPS для запроса: показывает, какие индексы, какие таблицы, сколько времени займёт.

Пример:

-- Показать план запроса
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=100)
--   Index Cond: (email = 'test@example.com'::text)

Что означают числа:

  • cost=0.42..8.44 — стоимость: начало..получение всех строк
  • rows=1 — сколько строк ожидает база
  • width=100 — средний размер строки в байтах

Когда использовать:

  • Запрос работает медленно
  • Хотите проверить, используется ли индекс
  • Перед созданием индекса — поможет ли он

🟡 Middle Level

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN: только план, НЕ выполняет запрос
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE: выполняет запрос + показывает реальное время
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=100)
--   (actual time=0.035..0.037 rows=1 loops=1)
--                     ↑ реальное время в ms
-- Planning Time: 0.123 ms
-- Execution Time: 0.058 ms

Ключевые флаги

-- Полный набор для анализа
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- Флаги:
-- ANALYZE  → выполнить запрос + реальное время
-- BUFFERS  → показать чтение из кэша/диска
-- COSTS    → показать стоимость (по умолчанию on)
-- ROWS     → показать ожидаемое кол-во строк
-- WIDTH    → показать размер строки
-- FORMAT   → TEXT (по умолчанию), JSON, YAML, XML

Чтение плана

EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, COUNT(o.id) as orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Moscow'
GROUP BY u.name;

-- План:
-- HashAggregate  (cost=... rows=... actual time=15.2..15.3 rows=500 loops=1)
--   Buffers: shared hit=250 read=50
--   → Hash Left Join  (cost=... actual time=10.1..12.5 rows=2000 loops=1)
--        Buffers: shared hit=200 read=40
--        Hash Cond: (o.user_id = u.id)
--        → Seq Scan on orders o  (actual time=0.01..5.2 rows=10000 loops=1)
--             Buffers: shared hit=150
--        → Hash  (cost=... actual time=3.1..3.1 rows=500 loops=1)
--             Buffers: shared hit=50 read=40
--             → Seq Scan on users u  (actual time=0.02..2.8 rows=500 loops=1)
--                  Filter: (city = 'Moscow')
--                  Rows Removed by Filter: 999500
-- Planning Time: 0.5 ms
-- Execution Time: 16.0 ms

Читаем снизу вверх:

  1. Seq Scan on users → отфильтровал 500 из 1 млн
  2. Hash → построил хэш для JOIN
  3. Seq Scan on orders → прочитал 10,000 заказов
  4. Hash Left Join → соединил
  5. HashAggregate → сгруппировал

Типы сканирования

Тип Когда Скорость
Seq Scan Чтение всей таблицы Медленно для больших, быстро для маленьких
Index Scan Поиск по индексу → чтение строки Быстро для малых выборок
Index Only Scan Все данные в индексе Очень быстро
Bitmap Index Scan Индекс → битовая карта → строки Средние выборки (100-10,000 строк)

Bitmap Index Scan: собирает ВСЕ matching TID из индекса в битовую карту, потом читает строки. Выгоднее Index Scan для 100-10,000 строк из разных мест — меньше случайных I/O.

Типичные проблемы

  1. Seq Scan на большой таблице 
    EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Seq Scan on users  ← плохо!
--   Filter: (email = ...)
--   Rows Removed by Filter: 999999
   
-- Решение: создать индекс
CREATE INDEX idx_users_email ON users(email);
  2. Оценка не совпадает с реальностью 
    -- rows=1000 actual rows=1
-- Планировщик ошибся в 1000 раз!
   
-- Причина: устаревшая статистика
-- Решение: ANALYZE users;
  3. Spill на диск 
    EXPLAIN (ANALYZE) SELECT ... GROUP BY ...;
-- HashAggregate (actual time=... rows=...)
--   Disk: 5GB  ← плохо! work_mem не хватило
   
-- Решение: SET work_mem = '256MB';

Практические примеры

-- Проверка использования индекса
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- Index Scan using idx_orders_user_id → отлично!

-- Проверка JOIN
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- Hash Join → нормально для больших таблиц
-- Nested Loop → отлично, если есть индекс

-- Проверка сортировки
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Limit → Index Scan Backward → отлично!
-- Limit → Sort → можно оптимизировать индексом

🔴 Senior Level

Анатомия узла Costs

Sort (cost=150.10..155.10 rows=2000 width=40)
         ↑         ↑
    Startup    Total
    Cost       Cost

Startup Cost: Стоимость получения первой строки

  • Критично для LIMIT 1, WHERE EXISTS
  • Планировщик может выбрать план с огромным Total Cost, но низким Startup Cost

Total Cost: Стоимость получения всех строк

  • Критично для полной выборки

Арбитражные единицы:

  • 1 unit ≈ чтение одной 8KB страницы с диска
  • cost=100 ≈ чтение 100 страниц

BUFFERS: анализ I/O

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Buffers: shared hit=250 read=50 written=10
--               ↑       ↑         ↑
--            из кэша  с диска   записано

-- shared hit: 250 / (250+50) = 83% Cache Hit Rate
-- read=50: 50 × 8KB = 400KB прочитано с диска
-- written=10: 10 × 8KB = 80KB записано (temp files?)

Интерпретация:

  • shared hit » shared read → отлично (всё в кэше)
  • shared read » shared hit → проблема (много Disk I/O)
  • written > 0 → spill на диск или запись temp файлов

Generic vs Custom Plans

Проблема Prepared Statements:

-- Custom Plan (знает конкретное значение)
EXPLAIN SELECT * FROM users WHERE id = 5;
-- Plan: Index Scan (идеально для id=5)

-- Generic Plan (универсальный для любого $1)
PREPARE stmt(int) AS SELECT * FROM users WHERE id = $1;
EXPLAIN EXECUTE stmt(5);
-- Plan: Seq Scan (универсальный, "средний" для всех id)

Почему Generic Plan может быть хуже:

Данные skewed:
  id=5 → 1 строка (Index Scan идеален)
  id=100 → 500,000 строк (Seq Scan идеален)
  
Generic Plan: "средний" → Seq Scan
→ Для id=5 катастрофически медленный!

Планировщик «усредняет» стоимость: для id=5 нужен Index Scan (1 строка),
для id=100 — Seq Scan (500,000 строк). Seq Scan «безопаснее» в среднем,
но для конкретных значений с малой выборкой — катастрофа.

Решение:

-- 1. plan_cache_mode
SET plan_cache_mode = 'force_custom_plan';  -- Всегда Custom
SET plan_cache_mode = 'force_generic_plan'; -- Всегда Generic
SET plan_cache_mode = 'auto';               -- По умолчанию (5 раз Custom, потом Generic)

-- 2. В JDBC/Hibernate:
-- Проверьте: prepareThreshold=0 (отключить Prepared Statements)

JIT-компиляция

JIT (Just-In-Time) — PostgreSQL компилирует части запроса в машинный код через LLVM. Как если бы база «переписала» часть запроса на C.

Ускоряет:

  1. Тяжёлые выражения (a * b + c / d) на миллионах строк
  2. Агрегаты (SUM, AVG) с большим количеством группировок
  3. Оценки кортежей при Hash Join

Замедляет: простые запросы (SELECT * FROM t WHERE id = 1) — компиляция > выполнения!

-- В EXPLAIN ANALYZE может появиться блок JIT
-- JIT:
--   Functions: 5
--   Options: Inlining, Optimization
--   Timing: Min 2.1 ms, Max 2.5 ms

-- Плюсы: ускоряет сложные выражения и агрегаты
-- Минусы: на коротких запросах компиляция > выполнения!

-- Отключение для OLTP
SET jit = off;

-- Проверка
SHOW jit;

Parallel Query в планах

EXPLAIN SELECT COUNT(*) FROM large_table;

-- Gather
--   Workers Planned: 4
--   → Parallel Seq Scan on large_table
--        Workers Launched: 4

-- Важные метрики:
-- Workers Planned: сколько запланировал оптимизатор
-- Workers Launched: сколько реально запустилось
-- Если Launched < Planned → проверьте:
--   max_parallel_workers_per_gather
--   max_parallel_workers
--   Загрузки сервера

Incremental Sort (PG 13+)

EXPLAIN SELECT * FROM orders ORDER BY user_id, created_at;

-- Incremental Sort  ← PG 13+!
--   Sort Key: user_id, created_at
--   Presorted Key: user_id  ← данные уже отсортированы по user_id
--   → Sort только по created_at внутри каждого user_id
--   → В 5-10 раз быстрее полной сортировки!

На PG 12 и ниже — полная сортировка всех колонок, даже если часть уже отсортирована.

Edge Cases

  1. EXPLAIN ANALYZE выполняет запрос! 
    -- ⚠️ DML запрос будет выполнен!
EXPLAIN ANALYZE DELETE FROM users WHERE status = 'INACTIVE';
-- → Все неактивные пользователи УДАЛЕНЫ!
   
-- ✅ Безопасный способ:
BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE status = 'INACTIVE';
ROLLBACK;  -- Отменяем изменения
  2. Первый запуск EXPLAIN ANALYZE может врать 
    Данные могут быть не в кэше → первый запуск медленнее.
Запустите 2-3 раза и смотрите на второй/третий результат.
Или: DISCARD ALL; — очистка кэша.
  3. WAL генерация (PG 13+) 
    EXPLAIN (ANALYZE, WAL) INSERT INTO orders SELECT ...;
   
-- WAL:
--   Records: 50000
--   Bytes: 10MB
--   FPI: 100 (Full Page Images — полные копии 8KB страниц в WAL при
--   первом изменении страницы после checkpoint. Большое число FPI =
--   высокая нагрузка на WAL и репликацию)
   
-- Критично для оценки нагрузки на репликацию

На PG 12 и ниже флаг WAL в EXPLAIN недоступен. Оценивайте нагрузку на репликацию через pg_stat_wal или логи.

  1. Timing overhead 
    -- EXPLAIN ANALYZE добавляет overhead на замеры времени
-- Для микро-оптимизаций отключите TIMING
   
EXPLAIN (ANALYZE, TIMING off) SELECT ...;
-- Быстрее, но без детальных замеров каждого узла

Production Experience

Реальный сценарий #1: Generic Plan убивает API

  • Приложение: JDBC Prepared Statement для поиска по email
  • В консоли: 5ms (Custom Plan, Index Scan)
  • В приложении: 500ms (Generic Plan, Seq Scan)
  • Причина: email skewed (у некоторых пользователей 1000 записей)
  • Решение: plan_cache_mode = 'force_custom_plan'
  • Результат: 5ms стабильно

Реальный сценарий #2: JIT замедляет OLTP

  • REST API: простые запросы < 1ms
  • EXPLAIN ANALYZE: JIT Timing: 8ms
  • Причина: JIT включён по умолчанию, компиляция > выполнения
  • Решение: SET jit = off для сессии
  • Результат: 0.5ms вместо 8.5ms (ускорение в 17 раз)

Monitoring

-- 1. Поиск медленных запросов
SELECT query, mean_exec_time, calls, shared_blks_read
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 2. Проверка конкретного запроса
EXPLAIN (ANALYZE, BUFFERS, WAL) 
SELECT ...;

-- 3. Поиск запросов с Generic Plan проблемами
-- (в логах приложения: медленно, в консоли: быстро)

-- 4. Проверка JIT
EXPLAIN (ANALYZE) SELECT ...;
-- Ищите блок "JIT" и Timing

-- 5. Parallel Query эффективность
EXPLAIN (ANALYZE) SELECT ...;
-- Workers Launched vs Planned

Best Practices

  1. Всегда используйте EXPLAIN (ANALYZE, BUFFERS)
  2. Сравнивайте Estimated vs Actual rows → расхождение = проблема статистики
  3. Проверяйте Cache Hit Rate через Buffers
  4. Generic vs Custom — тестируйте Prepared Statements
  5. JIT отключайте для OLTP, включайте для OLAP
  6. DML + EXPLAIN ANALYZE → только в транзакции с ROLLBACK
  7. WAL (PG 13+) для оценки нагрузки на репликацию
  8. SETTINGS (PG 12+) — EXPLAIN (ANALYZE, SETTINGS) показывает изменённые параметры сессии. На PG 11 и ниже — проверяйте параметры вручную через SHOW.

Резюме для Senior

  • Startup Cost критичен для LIMIT, Total Cost для полной выборки
  • BUFFERS = главный метрик I/O (hit vs read)
  • Generic vs Custom Plans — частая причина расхождений prod/dev
  • plan_cache_mode управляет поведением Prepared Statements
  • JIT ускоряет OLAP, замедляет OLTP → отключайте где не нужно
  • WAL (PG 13+) показывает нагрузку на репликацию
  • Estimated ≠ Actual rows → ANALYZE или SET STATISTICS
  • DML + EXPLAIN ANALYZE → всегда в BEGIN...ROLLBACK

🎯 Шпаргалка для интервью

Обязательно знать:

  • EXPLAIN: показывает план БЕЗ выполнения; EXPLAIN ANALYZE: выполняет + реальное время
  • Cost: startup..total в условных единицах (1 unit ≈ чтение 8KB страницы)
  • BUFFERS: shared hit (из кэша), shared read (с диска), written (запись)
  • Generic Plan (Prepared Statements): универсальный, может быть хуже для skewed данных
  • Custom Plan: знает конкретное значение, лучше для skewed данных
  • plan_cache_mode: auto (5 раз Custom → Generic), force_custom_plan, force_generic_plan
  • JIT: ускоряет сложные выражения/агрегаты, замедляет OLTP (< 1ms запросы)
  • Parallel Query: Workers Planned vs Launched → если Launched < Planned, проверьте настройки
  • Incremental Sort (PG 13+): данные частично отсортированы → сортируем остаток
  • WAL (PG 13+): Records, Bytes, FPI → оценка нагрузки на репликацию
  • DML + EXPLAIN ANALYZE → ВЫПОЛНЯЕТ запрос! Только в BEGIN…ROLLBACK

Частые уточняющие вопросы:

  • «Почему в приложении медленнее, чем в консоли?» → Generic Plan в Prepared Statements
  • «Как проверить Cache Hit Rate?» → BUFFERS: hit / (hit + read)
  • «Почему JIT замедляет?» → Компиляция 8ms > выполнения 1ms → отключите для OLTP
  • «Что значит Estimated rows ≠ Actual?» → Устаревшая статистика → ANALYZE

Красные флаги (НЕ говорить):

  • ❌ «EXPLAIN ANALYZE безопасен для DML» (ВЫПОЛНЯЕТ запрос!)
  • ❌ «Generic Plan всегда хорош» (для skewed данных — катастрофа)
  • ❌ «JIT всегда ускоряет» (замедляет OLTP!)
  • ❌ «Cost — это миллисекунды» (нет, условные единицы!)

Связанные темы:

  • [[Как оптимизировать медленные запросы]] → практическое применение EXPLAIN
  • [[Зачем нужен ANALYZE]] → Estimated ≠ Actual → ANALYZE
  • [[Что такое коррелированный подзапрос]] → поиск SubPlan в плане