Java Interview Questions

Головна / Усі відповіді / Бази даних SQL PostgreSQL

Питання 20 · Розділ 1

Що таке explain plan?

4. Hash Left Join → з'єднав 5. HashAggregate → згрупував

Ukrainian Бази даних 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 у плані