Дивіться на тип доступу до таблиці, оцінку рядків проти фактичної кількості і найдорожчі вузли плану; розбіжність оцінки й факту вказує на застарілу статистику.
Як питають
Чим EXPLAIN ANALYZE відрізняється від EXPLAIN?
Що означає Seq Scan на великій таблиці?
Запит швидкий локально й повільний у продакшені: як зрозуміти чому?
EXPLAINплан запиту
Пояснення
EXPLAIN показує, як оптимізатор збирається виконати запит: дерево вузлів, для кожного оцінка вартості й очікувана кількість рядків. EXPLAIN ANALYZE виконує запит насправді й додає фактичний час і рядки на кожному вузлі. Саме порівняння оцінки з фактом дає найбільше: якщо оптимізатор очікував тисячу рядків, а отримав двісті тисяч, він побудував план під неправильні припущення, і лікується це ANALYZE або розширеною статистикою.
План читається від найглибших вузлів до кореня, і шукати треба той вузол, де витрачається час, а не загальний cost. Перше, на що дивляться, — тип доступу. Seq Scan або ALL у MySQL означає повне читання таблиці; на малій таблиці це нормально, на великій із селективною умовою сигнал, що індекс відсутній або незастосовний через функцію в умові. Index Scan читає індекс і таблицю, Index Only Scan лише індекс. Далі шукають сортування без індексу: Sort із великою кількістю рядків або Using filesort і Using temporary у MySQL. У PostgreSQL важливо множити на loops: вузол із rows=1 і loops=100000 виконався сто тисяч разів.
План залежить від даних. На порожній локальній базі оптимізатор обирає Seq Scan і Nested Loop, які в продакшені стають катастрофою, тому аналізувати треба на реалістичному обсязі, а повільні запити в продакшені ловити через auto_explain або slow query log.
КодSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;
-- Проблемний план (PostgreSQL): читаємо всю таблицю й сортуємо в памʼяті
-- Limit (actual time=812.4..812.5 rows=50 loops=1)
-- -> Sort (actual time=812.3..812.4 rows=50 loops=1)
-- Sort Method: top-N heapsort
-- -> Hash Join (actual rows=184213 loops=1)
-- -> Seq Scan on orders o (rows=184213 loops=1) <- повний скан
-- Filter: (status = 'paid' AND created_at > ...)
-- Rows Removed by Filter: 4815787 <- 96 % рядків відкинуто
-- -> Hash -> Seq Scan on users u
-- Ознаки проблеми: Seq Scan з великим Rows Removed by Filter, Sort без індексу,
-- estimated rows=1200 проти actual rows=184213 (стара статистика)
CREATE INDEX orders_status_created ON orders (status, created_at DESC);
ANALYZE orders;
-- Після: Index Scan по orders_status_created, Sort зник, actual time ~2 ms
Що хоче почути інтервʼюер
Що EXPLAIN показує план оптимізатора з оцінками, а EXPLAIN ANALYZE реально виконує запит і додає фактичний час і кількість рядків на кожному вузлі.
Що план читається від найглибших вузлів до кореня, і шукати треба вузол, де витрачається час, а не загальний cost.
Типи доступу: Seq Scan або ALL це повне читання таблиці, Index Scan пошук по індексу з читанням таблиці, Index Only Scan без таблиці, Bitmap Scan для великих діапазонів.
Що велика різниця між rows estimated і rows actual означає застарілу статистику або скорельовані колонки, і тоді оптимізатор обирає неправильний JOIN або порядок.
Що план залежить від даних: на порожній локальній базі Seq Scan нормальний, тому аналізувати треба на копії продакшн-обсягів.
Типові помилки
Дивитись лише на загальний cost і вважати, що EXPLAIN показує час.
Панікувати від Seq Scan на таблиці з тисячею рядків: для малих таблиць це найшвидший спосіб.
Запускати EXPLAIN ANALYZE на UPDATE або DELETE у продакшені без транзакції з ROLLBACK.
Не знати, що loops у PostgreSQL множить час і рядки вузла: actual rows=1 loops=100000 це сто тисяч виконань.
Ігнорувати Using filesort і Using temporary у MySQL або Sort з Disk у PostgreSQL, які показують сортування без індексу.
ПОРАДА
Скажіть, що читаєте EXPLAIN ANALYZE на копії продакшн-даних: на порожній локальній базі план буде іншим. Згадайте BUFFERS і auto_explain для пошуку повільних запитів у продакшені.
Додаткові питанняЗ ВІДПОВІДЯМИ
EXPLAIN лише будує план і показує оцінки оптимізатора: cost і очікувану кількість рядків. EXPLAIN ANALYZE виконує запит і додає фактичний час та рядки на кожному вузлі, тому саме він виявляє неправильні оцінки. Побічний ефект: DML-запити реально змінюють дані, тому їх аналізують у транзакції з ROLLBACK. Опція BUFFERS додає кількість прочитаних сторінок з кешу й з диска.
База читає таблицю цілком, бо або немає підходящого індексу, або оптимізатор вирішив, що умова поверне значну частину таблиці й індекс не окупиться, або функція чи приведення типу в умові робить індекс незастосовним, наприклад WHERE lower(email) = ? без індексу по lower(email). На великій таблиці з селективною умовою це сигнал додати індекс або переписати умову.
План залежить від статистики й обсягу. На малих даних оптимізатор обирає Seq Scan і Nested Loop, які на мільйонах рядків стають катастрофою. Також у продакшені інші налаштування памʼяті work_mem, інший кеш і паралельне навантаження. Тому план перевіряють на копії продакшн-даних або хоча б на базі, заповненій реалістичним обсягом, і після ANALYZE.
Спочатку ANALYZE таблиці, бо статистика могла застаріти після масового імпорту. Якщо не допомагає, збільшити default_statistics_target для колонки з нерівномірним розподілом або створити розширену статистику CREATE STATISTICS для скорельованих колонок, наприклад country і city. У MySQL це ANALYZE TABLE і гістограми через ANALYZE TABLE ... UPDATE HISTOGRAM.