Пошук уроків, статей та іншого контенту
Дізнайтеся, коли PostgreSQL читає всю таблицю та як оцінити доцільність такого плану.
Sequential Scan — це послідовне сканування таблиці. PostgreSQL читає рядки таблиці один за одним і перевіряє, чи відповідають вони умові запиту.
Наприклад:
SELECT *
FROM orders
WHERE status = 'paid';Якщо для такого запиту PostgreSQL обере Sequential Scan, він приблизно виконає такі дії:
прочитає сторінки таблиці;
перевірить кожен рядок;
залишить рядки, для яких status = 'paid';
поверне результат.
У плані запиту цей спосіб позначається як:
Seq Scan on ordersПослідовне сканування не є помилкою або ознакою повільної бази даних. Для багатьох запитів це найефективніший план.
Створимо тимчасову таблицю замовлень і заповнимо її тестовими даними:
CREATE TEMP TABLE orders (
id integer,
customer_id integer,
status text,
total numeric
);
INSERT INTO orders (id, customer_id, status, total)
SELECT
number,
(number % 1000) + 1,
CASE
WHEN number % 2 = 0 THEN 'paid'
ELSE 'pending'
END,
(number % 500) + 10
FROM generate_series(1, 100000) AS number;
-- Оновлюємо статистику таблиці
ANALYZE orders;
EXPLAIN
SELECT *
FROM orders
WHERE status = 'paid';Оскільки для status немає індексу, PostgreSQL, імовірно, побудує план на основі Sequential Scan:
Seq Scan on orders
Filter: (status = 'paid'::text)Фактичне форматування та числові значення плану можуть відрізнятися залежно від версії PostgreSQL і налаштувань.
У цьому прикладі умові відповідає приблизно половина таблиці. Щоб знайти ці рядки за допомогою індексу, PostgreSQL усе одно мав би звернутися до великої кількості рядків. Послідовне читання всієї таблиці може бути дешевшим.
Команда EXPLAIN показує план, але не виконує запит:
EXPLAIN
SELECT *
FROM orders
WHERE status = 'paid';Команда EXPLAIN ANALYZE спочатку будує план, а потім реально виконує запит:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'paid';Вивід може мати приблизно такий вигляд:
Seq Scan on orders
(cost=0.00..1834.00 rows=50000 width=...)
(actual time=0.020..12.500 rows=50000 loops=1)
Filter: (status = 'paid'::text)
Rows Removed by Filter: 50000
Buffers: shared hit=584Основні частини цього виводу:
Seq Scan on orders — таблиця читається послідовно;
cost=... — оцінка вартості плану, а не час у мілісекундах;
rows=... — кількість рядків, яку очікує планувальник;
actual ... — фактичний час виконання;
rows=... після actual — фактично повернута кількість рядків;
Rows Removed by Filter — кількість рядків, відкинутих умовою;
Buffers — інформація про сторінки таблиці, які були прочитані або знайдені в кеші.
Значення cost потрібне PostgreSQL для порівняння різних планів між собою. Його не слід сприймати як тривалість виконання запиту.
Якщо таблиця містить небагато рядків, читання всієї таблиці може бути швидшим, ніж пошук через індекс.
Використання індексу також має витрати:
потрібно прочитати індекс;
знайти потрібні записи;
звернутися до сторінок самої таблиці;
зібрати результат.
Для маленької таблиці ці додаткові дії можуть бути дорожчими за одне послідовне читання.
Якщо умова повертає значну кількість рядків, Sequential Scan часто є хорошим вибором.
Наприклад, якщо таблиця має 100 000 рядків, а запит повертає 80 000 із них, індекс не обов’язково допоможе. PostgreSQL усе одно повинен отримати більшість даних.
Якщо потрібний стовпець не індексований, PostgreSQL може перевірити умову лише під час читання рядків таблиці:
SELECT *
FROM orders
WHERE status = 'paid';Без індексу на status планувальник не має окремої структури, яка швидко вкаже на потрібні рядки.
Запити без фільтра зазвичай природно виконуються через Sequential Scan:
SELECT *
FROM orders;Так само це може бути оптимальним для агрегатів над усією таблицею:
SELECT count(*)
FROM orders;Сам факт наявності Seq Scan не означає, що запит потрібно оптимізувати. Оцініть кілька показників.
Перевірте запит за допомогою:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'paid';Важливо дивитися на фактичний час, а не лише на назву операції.
Якщо план показує:
Rows Removed by Filter: 99000а повернуто лише 10 рядків, PostgreSQL прочитав багато непотрібних рядків. Для великої таблиці це може бути сигналом, що варто перевірити індексацію.
Порівняйте кількість повернутих рядків із загальним розміром таблиці:
кілька рядків із мільйонів — Sequential Scan може бути підозрілим;
сотні тисяч із мільйона — Sequential Scan може бути цілком логічним.
Порівняйте rows у плані з фактичною кількістю рядків у секції actual.
Якщо планувальник очікував 10 рядків, а фактично отримав 100 000, статистика може бути неактуальною або недостатньо точною.
Статистику можна оновити:
ANALYZE orders;Після цього PostgreSQL повторно оцінить розподіл значень у таблиці.
Параметр BUFFERS допомагає оцінити, скільки сторінок було опрацьовано:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'paid';Велика кількість прочитаних сторінок разом із великим часом виконання може вказувати, що послідовне сканування дороге для цього запиту.
Щоб перевірити, чи може індекс допомогти для вибіркового запиту, створимо індекс:
CREATE INDEX orders_status_idx
ON orders (status);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'paid';Навіть після створення індексу PostgreSQL може залишити Seq Scan. Це нормально: у тестових даних значення paid відповідає приблизно половині рядків, тому індекс може не давати переваги.
Для умови, яка повертає мало рядків, індекс має більше шансів бути корисним:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 777;У цьому випадку PostgreSQL може вибрати індексний план, оскільки потрібно знайти лише невелику частину таблиці.
Планувальник PostgreSQL приймає рішення на основі статистики:
кількості рядків;
розміру таблиці;
розподілу значень;
приблизної вибірковості умови;
наявних індексів;
очікуваної вартості читання даних.
Статистика не оновлюється після кожної зміни рядків миттєво. Якщо таблиця сильно змінилася, планувальник може мати неточне уявлення про її вміст.
Для ручного оновлення статистики використовується:
ANALYZE orders;Після оновлення статистики план запиту іноді змінюється.
EXPLAIN ANALYZEEXPLAIN ANALYZE виконує запит насправді. Для SELECT це зазвичай безпечно, але для команд зміни даних запит може змінити базу:
EXPLAIN ANALYZE
DELETE FROM orders
WHERE status = 'pending';Такий запит справді видалить рядки.
Під час перевірки операцій, які змінюють дані, потрібно бути обережним. Просте додавання EXPLAIN ANALYZE не перетворює команду на тестовий режим.
Sequential Scan може бути оптимальним для маленької таблиці або запиту, який повертає більшу частину даних.
Оцінювати потрібно час виконання, кількість рядків і використані ресурси.
Індекс корисний не для кожного запиту. Якщо потрібно повернути багато рядків, PostgreSQL може правильно обрати послідовне читання.
ANALYZEЯкщо статистика застаріла, планувальник може неправильно оцінити кількість результатів і вибрати невдалий план.
costcost — внутрішня оцінка PostgreSQL. Це не час у мілісекундах і не універсальна оцінка продуктивності.
Для перевірки фактичної поведінки використовуйте EXPLAIN (ANALYZE, BUFFERS).
Різниця між очікуваною та реальною кількістю рядків може бути важливішою за сам тип сканування:
rows=10
actual ... rows=100000Така різниця може призвести до невдалого вибору плану.
Sequential Scan читає таблицю послідовно та перевіряє рядки за умовою.
Він може бути найкращим планом для маленьких таблиць або запитів, які повертають багато рядків.
Відсутність індексу може бути причиною Sequential Scan, але наявність індексу не гарантує його використання.
Для перевірки реального виконання застосовуйте EXPLAIN (ANALYZE, BUFFERS).
Оцінюйте фактичний час, кількість повернутих і відкинутих рядків, прочитані сторінки та точність статистики.
Не потрібно оптимізувати кожен Sequential Scan: важливо зрозуміти, чи є він дорогим саме для конкретного запиту.