Пошук уроків, статей та іншого контенту
Навчіться читати вузли плану, оцінки рядків, витрати та порядок виконання операцій.
План виконання — це спосіб, яким PostgreSQL збирається виконати SQL-запит.
Для кожного запиту PostgreSQL:
аналізує SQL;
оцінює кількість рядків і вартість різних способів виконання;
обирає один план;
виконує операції відповідно до цього плану.
Команда EXPLAIN показує обраний план, але не виконує сам запит:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;Команда EXPLAIN ANALYZE спочатку показує план, а потім справді виконує запит і додає фактичні результати:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;Це важлива відмінність: EXPLAIN ANALYZE може змінити дані, якщо запит є INSERT, UPDATE або DELETE.
Створимо тимчасову таблицю із замовленнями:
CREATE TEMP TABLE orders (
id integer,
customer_id integer,
amount numeric(10, 2),
created_at timestamp
);
-- Додаємо тестові замовлення
INSERT INTO orders (id, customer_id, amount, created_at)
SELECT
number,
(number % 1000) + 1,
(number % 200) + 10.00,
timestamp '2025-01-01 00:00:00' + number * interval '1 minute'
FROM generate_series(1, 10000) AS numbers(number);
-- Створюємо індекс для пошуку за клієнтом і сортування за датою
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
-- Оновлюємо статистику таблиці
ANALYZE orders;
EXPLAIN
SELECT id, amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 5;Результат може бути подібним до такого:
Limit (cost=0.29..8.42 rows=5 width=24)
-> Index Scan using orders_customer_created_idx on orders
(cost=0.29..16.55 rows=10 width=24)
Index Cond: (customer_id = 42)Конкретні числа та навіть тип вузла можуть відрізнятися залежно від версії PostgreSQL, даних, статистики та налаштувань.
План має деревоподібну структуру. У прикладі:
Limit
-> Index ScanLimit — батьківський вузол, а Index Scan — його дочірній вузол.
Дочірні вузли виконуються для отримання даних, які потрібні батьківському вузлу. Тому в цьому прикладі PostgreSQL спочатку знаходить рядки через Index Scan, а потім Limit залишає лише перші п’ять.
План зазвичай читають так:
знайти найглибші, відступлені вузли;
зрозуміти, звідки беруться рядки;
простежити, як батьківські вузли обробляють ці рядки;
дійти до верхнього вузла — кінцевого результату запиту.
Верхній вузол не обов’язково виконується першим. Він описує кінцевий результат роботи всього дерева.
Розглянемо цей рядок:
Index Scan using orders_customer_created_idx on orders
(cost=0.29..16.55 rows=10 width=24)Index Scan означає, що PostgreSQL читає таблицю за допомогою індексу.
Інші поширені вузли:
Seq Scan — послідовне читання всієї таблиці;
Index Scan — пошук через індекс із читанням відповідних рядків таблиці;
Index Only Scan — PostgreSQL може отримати потрібні дані лише з індексу;
Bitmap Index Scan і Bitmap Heap Scan — пошук великої кількості рядків через bitmap;
Sort — сортування рядків;
Aggregate — агрегатні операції, наприклад COUNT або SUM;
Hash Join, Merge Join, Nested Loop — способи об’єднання таблиць;
Limit — обмеження кількості результатів.
using orders_customer_created_idx on ordersЦя частина показує, який індекс і таблиця використовуються.
costcost=0.29..16.55Перше число — приблизна початкова вартість вузла, або startup cost.
Друге число — приблизна повна вартість отримання всіх рядків, або total cost.
Вартість:
не є часом у мілісекундах;
не є кількістю прочитаних рядків;
використовується планувальником для порівняння варіантів;
залежить від налаштувань PostgreSQL і статистики таблиць.
Наприклад, план із cost=10..100 не означає виконання за 100 мілісекунд. План cost=20..80 не обов’язково буде вдвічі швидшим за план cost=20..160 у реальному часі.
Важливо порівнювати витрати між альтернативними планами в однаковому середовищі, а не сприймати їх як точний прогноз часу.
rowsrows=10Це оцінка кількості рядків, які вузол поверне батьківському вузлу.
У звичайному EXPLAIN це прогноз планувальника, а не фактична кількість рядків.
Якщо оцінка дуже відрізняється від реальності, PostgreSQL може вибрати невдалий план. Наприклад, він може очікувати 10 рядків, хоча насправді потрібно прочитати 100 000.
widthwidth=24Це приблизний середній розмір одного рядка в байтах, який повертає вузол.
width рідше використовують під час початкового аналізу, але він впливає на оцінювання вартості передачі та обробки даних.
EXPLAIN ANALYZEДля порівняння прогнозу з реальністю використовуйте EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 5;Результат може виглядати так:
Limit (cost=0.29..8.42 rows=5 width=24)
(actual time=0.020..0.025 rows=5 loops=1)
-> Index Scan using orders_customer_created_idx on orders
(cost=0.29..16.55 rows=10 width=24)
(actual time=0.019..0.023 rows=5 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.120 ms
Execution Time: 0.040 msПісля cost, rows і width з’явилися фактичні значення:
actual time=0.019..0.023 rows=5 loops=1actual timeПерше число — час до отримання першого рядка.
Друге число — час до отримання всіх рядків цим запуском вузла.
Час вимірюється в мілісекундах.
actual rowsФактична кількість рядків, яку вузол повернув за один запуск.
У цьому прикладі Limit повернув 5 рядків.
loopsКількість запусків вузла.
Якщо:
actual rows=3 loops=10це означає, що вузол запускався 10 разів і в середньому повертав по 3 рядки за запуск. Загальна кількість рядків за всі запуски — приблизно 30.
Щоб порівняти оцінку rows із загальною фактичною кількістю рядків, потрібно враховувати loops.
Порівнюйте такі частини:
rows=10
actual ... rows=100Оцінка 10, а фактичний результат — 100. Така різниця може бути нормальною для невеликої похибки, але значні відхилення часто вказують на застарілу або недостатню статистику.
Наприклад:
Seq Scan on orders
(cost=0.00..180.00 rows=10 width=24)
(actual time=0.020..12.500 rows=5000 loops=1)Планувальник очікував лише 10 рядків, але отримав 5000. Через це він міг вибрати не найкращий спосіб виконання.
Для оновлення базової статистики таблиці виконайте:
ANALYZE orders;Після цього повторіть EXPLAIN ANALYZE.
Seq ScanSeq Scan on orders
(cost=0.00..180.00 rows=10 width=24)
Filter: (customer_id = 42)Seq Scan послідовно читає таблицю та перевіряє умову для кожного рядка.
Це не завжди погано. Послідовне читання може бути найкращим варіантом, якщо:
таблиця маленька;
запит повертає значну частину таблиці;
індексу немає;
читання всієї таблиці дешевше за звертання до індексу.
Index ScanIndex Scan using orders_customer_created_idx on orders
Index Cond: (customer_id = 42)Index Scan використовує індекс, щоб знайти відповідні записи.
Наявність індексу не гарантує, що PostgreSQL завжди його використає. Планувальник вибирає варіант із найменшою оціненою вартістю.
Index Cond і FilterУ плані можна побачити різні умови:
Index Cond: (customer_id = 42)
Filter: (amount > 100)Index Cond — умова, яку PostgreSQL використовує безпосередньо під час пошуку в індексі.
Filter — додаткова умова, яку перевіряють після отримання рядків.
Наприклад:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42
AND amount > 100;Якщо індекс містить лише customer_id, пошук можна виконати за клієнтом, а amount > 100 перевірити окремо.
Якщо в плані є:
Rows Removed by Filter: 95це означає, що вузол отримав рядки, але відкинув їх через фільтр. Велика кількість відкинутих рядків може бути ознакою того, що умову можна ефективніше підтримати індексом, але рішення потрібно приймати на основі всього плану.
Розглянемо план:
Limit
-> Sort
Sort Key: created_at DESC
-> Seq Scan on orders
Filter: (customer_id = 42)Логічний порядок роботи:
Seq Scan читає таблицю;
Filter залишає замовлення клієнта 42;
Sort сортує їх за created_at;
Limit залишає потрібну кількість рядків.
Тобто план читається зверху вниз як дерево, але фактична робота зазвичай починається з найглибших дочірніх вузлів.
Вузли можуть мати кілька дочірніх вузлів:
Hash Join
-> Seq Scan on customers
-> Hash
-> Seq Scan on ordersЩоб зрозуміти такий план, потрібно окремо розглянути кожну гілку та з’ясувати, які дані вона готує для батьківського вузла.
У плані можуть одночасно бути такі значення:
(cost=0.00..180.00)
(actual time=0.020..12.500)cost — оцінка планувальника, а actual time — виміряний час конкретного запуску.
На фактичний час можуть впливати:
дані, які вже є в кеші;
навантаження на сервер;
дискова підсистема;
паралельне виконання інших запитів;
обсяг результату;
мережеве передавання результату.
Тому EXPLAIN ANALYZE показує вимірювання конкретного запуску, а не універсальну гарантію часу.
Опція BUFFERS показує інформацію про сторінки даних:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;У результаті можна побачити, наприклад:
Buffers: shared hit=8 read=3hit — сторінку знайшли в кеші PostgreSQL;
read — сторінку довелося прочитати з диска або іншого сховища.
BUFFERS корисний для розуміння того, скільки даних запит реально прочитав. Але для початкового читання плану достатньо спочатку зосередитися на вузлах, rows, actual time і loops.
Під час аналізу запиту зручно діяти так:
Запустіть EXPLAIN ANALYZE.
Подивіться на верхній вузол і кінцевий час виконання.
Рухайтеся до найглибших вузлів.
Для кожного вузла визначте:
яку таблицю або індекс він читає;
скільки рядків очікувалося;
скільки рядків отримано фактично;
скільки разів вузол запускався.
Знайдіть вузли з великою різницею між rows та actual rows.
Зверніть увагу на повне читання таблиці, сортування, об’єднання та великі значення actual time.
Перевірте, чи проблема знаходиться в одному вузлі, чи накопичується на кількох рівнях дерева.
cost часом у мілісекундахcost=0.00..100.00не означає 100 мілісекунд. Це внутрішня оцінка вартості.
Для фактичного часу дивіться на actual time та Execution Time.
Seq Scan завжди поганийДля маленької таблиці або запиту, який повертає багато рядків, Seq Scan може бути найшвидшим варіантом.
Верхній вузол часто лише передає або обмежує результат. Основна робота може виконуватися глибоко в дереві — у скануванні, сортуванні чи об’єднанні таблиць.
loopsПри loops > 1 значення actual rows стосується одного запуску. Для загальної оцінки потрібно враховувати кількість запусків.
EXPLAIN ANALYZE для зміни даних без обережностіEXPLAIN ANALYZE виконує запит. Для перевірки UPDATE або DELETE це може призвести до реальних змін у таблиці.
Результат залежить від стану кешу та навантаження. Для початку шукайте стабільні закономірності: великі розбіжності в оцінках рядків, надмірне читання даних і дорогі вузли.
EXPLAIN показує план, але не виконує запит.
EXPLAIN ANALYZE виконує запит і показує фактичні вимірювання.
План має деревоподібну структуру з батьківськими та дочірніми вузлами.
Найглибші вузли готують дані, а батьківські вузли їх обробляють.
cost — внутрішня оцінка вартості, а не час у мілісекундах.
rows — очікувана кількість рядків.
actual time, actual rows і loops показують фактичну роботу вузла.
Значна різниця між оціненими та фактичними рядками може вказувати на проблеми зі статистикою.
Seq Scan не завжди є проблемою, а індекс не завжди є найкращим вибором.
План потрібно читати від найглибших вузлів до верхнього результату.