Пошук уроків, статей та іншого контенту
З’ясуйте, як PostgreSQL формує альтернативні плани та обирає найвигідніший.
Планувальник запитів PostgreSQL, або query planner, визначає, як саме виконати SQL-запит.
Один і той самий запит може бути виконаний різними способами. Наприклад, для пошуку рядків PostgreSQL може:
прочитати всю таблицю;
скористатися індексом;
з’єднати таблиці через Nested Loop;
з’єднати таблиці через Hash Join;
виконати сортування в пам’яті або тимчасовому файлі.
Планувальник порівнює доступні варіанти, оцінює їхню вартість і обирає план із найменшою очікуваною вартістю.
Важливо: планувальник не виконує всі варіанти, щоб порівняти їх у реальності. Він будує оцінки на основі статистики та внутрішньої моделі витрат.
План виконання — це дерево операцій, які PostgreSQL виконає для отримання результату.
Подивитися план можна за допомогою EXPLAIN:
EXPLAIN
SELECT *
FROM products
WHERE category = 'books';Приклад результату:
Seq Scan on products (cost=0.00..18.50 rows=20 width=72)
Filter: (category = 'books'::text)Цей план означає:
Seq Scan — послідовне читання всієї таблиці;
cost=0.00..18.50 — оцінка початкової та загальної вартості;
rows=20 — очікувана кількість рядків результату;
width=72 — приблизний середній розмір одного рядка в байтах;
Filter — умова, яку PostgreSQL застосує під час читання.
EXPLAIN не виконує запит. Він лише показує план, який PostgreSQL збирається використати.
Для запиту PostgreSQL аналізує:
які таблиці та індекси потрібні;
які умови фільтрації можна виконати;
у якому порядку з’єднувати таблиці;
чи потрібне сортування;
який алгоритм з’єднання або агрегації вибрати;
скільки приблизно рядків буде оброблено на кожному кроці.
Наприклад, для умови:
WHERE category = 'books'можливими варіантами можуть бути:
прочитати всю таблицю і перевірити кожен рядок;
знайти потрібні рядки через індекс;
використати індекс для пошуку, а потім прочитати самі рядки з таблиці.
Планувальник не завжди обирає індекс. Якщо таблиця маленька або умова повертає більшу частину рядків, повне читання таблиці може бути дешевшим.
PostgreSQL використовує умовні одиниці вартості. Це не час у мілісекундах і не кількість процесорних операцій.
У плані:
cost=10.00..250.00перше число — приблизна початкова вартість, необхідна для отримання першого рядка.
Друге число — приблизна повна вартість, необхідна для отримання всіх рядків.
Планувальник порівнює ці значення між альтернативами. Менша оцінка зазвичай означає привабливіший план.
На оцінку впливають, зокрема:
кількість сторінок таблиці;
очікувана кількість рядків;
вартість читання з диска;
вартість використання процесора;
наявність індексів;
вибірковість умови;
статистика таблиці;
налаштування PostgreSQL.
Наведений приклад можна виконати в PostgreSQL. Він створює тимчасову таблицю, додає дані, створює індекс і показує план запиту.
CREATE TEMP TABLE products (
id integer,
name text,
category text,
price numeric
);
INSERT INTO products (id, name, category, price)
SELECT
number,
'Товар ' || number,
CASE
WHEN number <= 9900 THEN 'other'
ELSE 'books'
END,
(number % 100) + 10
FROM generate_series(1, 10000) AS numbers(number);
CREATE INDEX products_category_idx
ON products (category);
ANALYZE products;
EXPLAIN
SELECT *
FROM products
WHERE category = 'books';Умова category = 'books' відповідає приблизно 100 рядкам із 10 000. Це невелика частина таблиці, тому PostgreSQL може обрати індекс.
Можливий план матиме такий вигляд:
Bitmap Heap Scan on products
Recheck Cond: (category = 'books'::text)
-> Bitmap Index Scan on products_category_idx
Index Cond: (category = 'books'::text)Точні числа та навіть тип плану можуть відрізнятися залежно від версії PostgreSQL і налаштувань.
Тепер перевіримо умову, яка відповідає більшості рядків:
EXPLAIN
SELECT *
FROM products
WHERE category = 'other';У цьому випадку PostgreSQL може вибрати:
Seq Scan on products
Filter: (category = 'other'::text)Хоча індекс існує, його використання може бути невигідним: потрібно повернути майже всю таблицю. Послідовне читання всіх сторінок часто дешевше, ніж пошук через індекс і подальше читання великої кількості рядків.
Щоб оцінити план, PostgreSQL потрібна інформація про дані:
приблизну кількість рядків;
розподіл значень у стовпцях;
найпоширеніші значення;
кількість різних значень;
приблизний розмір таблиці.
Цю інформацію PostgreSQL зберігає у статистиці. Вона оновлюється командою ANALYZE.
ANALYZE products;Також ANALYZE автоматично запускається фоновим процесом autovacuum у звичайній базі даних.
Якщо таблиця істотно змінилася, а статистика ще не оновилася, планувальник може неправильно оцінити кількість рядків. Через це він може обрати невигідний план.
EXPLAIN показує оцінки. Щоб побачити, що відбулося під час реального виконання, використовують EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'books';Приклад фрагмента результату:
Bitmap Heap Scan on products
(cost=5.00..80.00 rows=100 width=40)
(actual time=0.030..0.080 rows=100 loops=1)Тут:
rows=100 — скільки рядків очікував планувальник;
actual ... rows=100 — скільки рядків було отримано насправді;
loops=1 — скільки разів операція виконувалася;
actual time — фактичний час виконання в мілісекундах.
EXPLAIN ANALYZE виконує запит. Для SELECT це зазвичай безпечно, але для INSERT, UPDATE або DELETE дані справді будуть змінені.
Для з’єднання таблиць PostgreSQL також має кілька альтернатив. Серед основних алгоритмів:
Nested Loop;
Hash Join;
Merge Join.
Наприклад, Nested Loop може бути вигідним, якщо зовнішній набір маленький, а для внутрішньої таблиці є індекс.
Hash Join часто підходить, коли PostgreSQL може побудувати хеш-таблицю для одного набору даних і швидко зіставити з ним інший набір.
Merge Join може бути вигідним, коли обидва набори вже впорядковані або їх недорого відсортувати.
Планувальник не обирає алгоритм за назвою запиту. Він порівнює очікувану вартість конкретних альтернатив для конкретних даних.
План для одного й того самого SQL-запиту може змінитися, якщо:
у таблицю додали багато рядків;
змінився розподіл значень;
створили або видалили індекс;
оновили статистику;
змінилися налаштування PostgreSQL;
змінився текст запиту;
змінилася кількість рядків, які повертає умова.
Наприклад, індекс може бути корисним для рідкісного значення, але не для дуже поширеного. Тому PostgreSQL може використовувати різні плани для умов над одним і тим самим стовпцем.
Планувальник працює з оцінками. Якщо статистика неточна або запит складний, фактичні витрати можуть відрізнятися від очікуваних.
Наприклад:
rows=10
actual rows=50000Така різниця означає, що планувальник очікував набагато менший результат, ніж отримав насправді. Це може призвести до невдалого вибору алгоритму.
Тому під час аналізу повільного запиту корисно порівнювати:
rows і actual rows;
оцінений план;
фактичний час;
кількість повторень операції через loops.
Для початкового аналізу можна діяти так:
Виконати EXPLAIN для запиту.
Знайти операції з найбільшою оціненою вартістю.
Виконати EXPLAIN ANALYZE.
Порівняти оцінену та фактичну кількість рядків.
Перевірити, чи актуальна статистика.
Оновити статистику через ANALYZE.
Повторно перевірити план.
Не варто оцінювати запит лише за наявністю індексу. Важливо, чи відповідає обраний план реальній кількості даних і селективності умови.
Індекс не є безумовно швидшим за послідовне читання. Якщо запит повертає значну частину таблиці, Seq Scan може бути кращим.
Значення cost — це внутрішня оцінка PostgreSQL, а не мілісекунди. Для фактичного часу потрібно використовувати EXPLAIN ANALYZE.
EXPLAIN ANALYZE для зміни даних без обережностіТакий запит реально виконується:
EXPLAIN ANALYZE
DELETE FROM products
WHERE category = 'books';Для перевірки плану операцій, які змінюють дані, потрібно враховувати наслідки виконання та за потреби працювати в транзакції.
Після значних змін у таблиці стара статистика може призвести до неправильних оцінок. У такій ситуації допомагає:
ANALYZE products;План залежить від розміру таблиці та розподілу значень. План для маленької таблиці може бути цілком правильним, але не підходити для великої.
Планувальник PostgreSQL шукає спосіб виконати запит із найменшою очікуваною вартістю.
Для одного запиту можуть існувати різні альтернативні плани.
Планувальник вибирає між послідовним читанням, індексами, різними алгоритмами з’єднання та іншими операціями.
EXPLAIN показує оцінений план і не виконує запит.
EXPLAIN ANALYZE виконує запит і додає фактичні показники.
Вибір плану залежить від статистики, розміру таблиць, індексів і вибірковості умов.
Індекс не завжди є найкращим варіантом.
Актуальна статистика допомагає планувальнику точніше оцінювати альтернативи.