Пошук уроків, статей та іншого контенту
Порівняйте прогноз планувальника з фактичними часом і кількістю оброблених рядків.
EXPLAIN ANALYZEEXPLAIN показує план, який PostgreSQL планує використати для запиту.
EXPLAIN ANALYZE робить більше:
будує план;
реально виконує запит;
порівнює прогнозовані значення з фактичними;
показує час виконання та кількість оброблених рядків.
Загальний синтаксис:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;Результат містить план у вигляді дерева операцій: наприклад, послідовне сканування таблиці або пошук за індексом.
Створимо тимчасову таблицю замовлень. Вона існуватиме лише протягом поточного з'єднання з PostgreSQL.
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
amount numeric(10, 2) NOT NULL
);
INSERT INTO orders (id, customer_id, status, amount)
SELECT
id,
((id - 1) % 100) + 1,
CASE
WHEN id % 10 = 0 THEN 'cancelled'
WHEN id % 3 = 0 THEN 'processing'
ELSE 'completed'
END,
(id % 500) + 0.99
FROM generate_series(1, 10000) AS numbers(id);
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
-- Оновлюємо статистику для планувальника
ANALYZE orders;У таблиці буде 10 000 рядків і 100 різних клієнтів. Для кожного клієнта приблизно 100 замовлень.
Тепер виконаємо запит із вимірюванням:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;План може виглядати приблизно так:
Index Scan using orders_customer_id_idx on orders
(cost=0.29..8.04 rows=100 width=25)
(actual time=0.020..0.035 rows=100 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.120 ms
Execution Time: 0.060 msКонкретні числа залежать від версії PostgreSQL, обладнання та поточного стану бази даних.
У кожної операції є прогнозовані та фактичні показники.
(cost=0.29..8.04 rows=100 width=25)
(actual time=0.020..0.035 rows=100 loops=1)costcost — внутрішня оцінка вартості операції для планувальника.
У прикладі:
0.29 — приблизна вартість початку отримання першого рядка;
8.04 — приблизна загальна вартість отримання всіх рядків.
Це не час у мілісекундах. Значення cost використовуються для порівняння різних планів між собою.
Наприклад, планувальник може порівняти:
послідовне сканування таблиці;
пошук за індексом.
Він обере план із меншою прогнозованою вартістю.
rowsУ першій частині:
rows=100це прогнозована кількість рядків, яку поверне операція.
У другій частині:
actual ... rows=100це фактична кількість рядків, отримана під час виконання.
У цьому прикладі прогноз і факт збігаються.
widthwidth=25Це прогнозований середній розмір одного рядка в байтах. Він потрібен планувальнику для оцінювання вартості передавання та обробки даних.
actual timeactual time=0.020..0.035Це фактичний час у мілісекундах:
перше значення — час до отримання першого рядка;
друге значення — час до отримання всіх рядків цієї операції.
Це вже вимірювання реального виконання, а не прогноз.
loopsloops=1Операція виконувалася один раз.
Якщо операція є частиною вкладеного циклу, вона може виконуватися багато разів. У такому випадку actual time і actual rows зазвичай показуються для одного виконання, а loops показує загальну кількість повторень.
Наприкінці плану PostgreSQL може показати:
Planning Time: 0.120 ms
Execution Time: 0.060 msPlanning Time — час, потрібний для побудови плану.
Execution Time — час фактичного виконання запиту.
Для складних запитів час планування теж може бути помітним. Для простих запитів більшість часу зазвичай припадає на виконання або очікування введення-виведення.
Планувальник не виконує запит наперед, щоб дізнатися точну кількість рядків. Він використовує статистику таблиць.
Наприклад, статистика може бути застарілою після великої кількості вставок або видалень. Тоді PostgreSQL може неправильно оцінити кількість рядків:
Index Scan using orders_customer_id_idx on orders
(cost=0.29..8.04 rows=1 width=25)
(actual time=0.020..0.035 rows=100 loops=1)Тут:
планувальник очікував 1 рядок;
фактично отримав 100 рядків.
Велика різниця між rows і actual rows може призвести до невдалого вибору плану. Наприклад, PostgreSQL може вибрати індекс, очікуючи на кілька рядків, хоча насправді потрібно обробити значну частину таблиці.
Оновити статистику можна командою:
ANALYZE orders;Після цього варто повторити EXPLAIN ANALYZE і перевірити, чи став прогноз точнішим.
loopsРозглянемо запит із підзапитом:
EXPLAIN ANALYZE
SELECT
customer_id,
(
SELECT count(*)
FROM orders AS inner_orders
WHERE inner_orders.customer_id = outer_orders.customer_id
) AS order_count
FROM orders AS outer_orders
WHERE customer_id IN (1, 2, 3);У плані внутрішня операція може мати:
(actual time=0.010..0.011 rows=1 loops=300)Це означає, що:
за одне виконання операція повертала 1 рядок;
операція виконувалася 300 разів;
загальна кількість роботи залежить від усіх повторень.
Тому важливо дивитися не лише на один рядок плану, а й на його дочірні операції та значення loops.
EXPLAIN без виконання запитуЯкщо потрібно лише побачити прогнозований план, використовуйте EXPLAIN без ANALYZE:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;У цьому випадку запит не виконується, тому PostgreSQL не покаже:
actual time;
actual rows;
loops на основі реального виконання;
Execution Time.
Цей варіант безпечніший для запитів, які змінюють дані.
EXPLAIN ANALYZE реально виконує переданий SQL-запит.
Наприклад, цей запит справді видалить рядки:
EXPLAIN ANALYZE
DELETE FROM orders
WHERE status = 'cancelled';Для перевірки такого запиту в транзакції можна виконати:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM orders
WHERE status = 'cancelled';
ROLLBACK;ROLLBACK скасує виконане видалення. У робочій базі даних не слід бездумно запускати EXPLAIN ANALYZE для INSERT, UPDATE або DELETE.
EXPLAINДо EXPLAIN можна додати параметри в дужках:
EXPLAIN (ANALYZE, COSTS, TIMING)
SELECT *
FROM orders
WHERE customer_id = 42;Основні параметри:
ANALYZE — реально виконати запит і показати фактичні показники;
COSTS — показати прогнозовану вартість;
TIMING — вимірювати час окремих операцій.
Зазвичай достатньо:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;Під час вимірювань потрібно пам'ятати, що результат залежить від:
розміру таблиці;
кешу даних;
навантаження на сервер;
доступних індексів;
актуальності статистики;
версії PostgreSQL.
Одне виконання не завжди є достатнім для висновків про продуктивність.
cost як мілісекундиcost=0.29..8.04Це не означає 0.29 або 8.04 мілісекунди. Для фактичного часу потрібно дивитися на actual time і Execution Time.
Execution TimeЗагальний час важливий, але він не пояснює причину повільної роботи. Потрібно також перевірити:
типи операцій;
прогнозовану й фактичну кількість рядків;
loops;
дочірні вузли плану.
ANALYZEПісля значних змін у таблиці статистика може не відповідати реальним даним. Перед дослідженням плану можна оновити її:
ANALYZE orders;EXPLAIN ANALYZE не є режимом «лише перегляд». Він виконує запит, зокрема UPDATE або DELETE.
Для перевірки змін використовуйте транзакцію та ROLLBACK.
Час виконання може змінюватися між запусками. Краще повторити вимірювання та порівнювати плани в однакових умовах.
EXPLAIN показує прогнозований план запиту.
EXPLAIN ANALYZE додатково реально виконує запит.
rows у прогнозі — очікувана кількість рядків.
actual rows — кількість рядків, отримана фактично.
cost — внутрішня оцінка вартості, а не мілісекунди.
actual time і Execution Time показують виміряний час.
loops показує, скільки разів виконувалася операція.
Значна різниця між прогнозом і фактом може свідчити про застарілу статистику або нерівномірний розподіл даних.
EXPLAIN ANALYZE виконує запит, тому для операцій зміни даних потрібно діяти обережно.