Пошук уроків, статей та іншого контенту
Розберіть шлях запиту від розбору SQL до виконання обраного плану.
Коли клієнт надсилає PostgreSQL SQL-запит, база даних не виконує текст безпосередньо. Запит проходить кілька етапів:
Розбір синтаксису — PostgreSQL перевіряє, чи правильно записано SQL.
Семантичний аналіз — база перевіряє таблиці, стовпці, типи даних і права доступу.
Переписування запиту — застосовуються правила PostgreSQL, зокрема робота з представленнями.
Планування — PostgreSQL створює можливі плани виконання та обирає найвигідніший.
Виконання — виконавець проходить обраний план і формує результат.
Повернення результату — рядки або повідомлення про помилку надсилаються клієнту.
Спрощено цей процес можна подати так:
SQL-текст
↓
Розбір і перевірка
↓
Переписування
↓
Планування
↓
План виконання
↓
Виконання
↓
РезультатСпочатку PostgreSQL читає SQL-запит і перевіряє його синтаксис.
Наприклад, цей запит записано правильно:
SELECT name
FROM users
WHERE age >= 18;А в цьому запиті пропущено ключове слово FROM:
SELECT name users;Залежно від контексту PostgreSQL може трактувати такий запис інакше або повідомити про синтаксичну помилку. Явно неправильний SQL, наприклад із пропущеною дужкою, не перейде до наступних етапів:
SELECT *
FROM users
WHERE (age >= 18;На цьому етапі PostgreSQL працює переважно з формою запиту: ключовими словами, іменами, операторами та дужками. Він ще не вирішує, яким способом читати таблицю.
Після синтаксичного розбору PostgreSQL перевіряє значення об'єктів у запиті:
чи існує таблиця;
чи існують указані стовпці;
чи сумісні типи даних;
чи можна виконати використані оператори та функції;
чи має користувач необхідні права.
Наприклад:
SELECT full_name
FROM users
WHERE age >= 18;Якщо таблиця users існує, але стовпця full_name немає, запит завершиться помилкою ще до виконання:
column "full_name" does not existСхожа ситуація виникне, якщо таблиці users не існує:
relation "users" does not existНа цьому етапі PostgreSQL також уточнює типи значень. Наприклад, число 18 порівнюється зі стовпцем age, і база має визначити, які типи та оператор порівняння потрібно використати.
Далі PostgreSQL може переписати запит за внутрішніми правилами. Один із найпомітніших випадків — робота з представленнями (VIEW).
Якщо створено представлення:
CREATE VIEW adult_users AS
SELECT id, name, age
FROM users
WHERE age >= 18;і виконується запит:
SELECT name
FROM adult_users
WHERE age < 30;PostgreSQL має фактично працювати з визначенням представлення та умовою зовнішнього запиту. У спрощеному вигляді запит стає схожим на такий:
SELECT name
FROM users
WHERE age >= 18
AND age < 30;Це не означає, що PostgreSQL просто замінює текстовий рядок. Він працює з внутрішнім представленням запиту.
Для звичайних запитів без представлень цей етап може бути непомітним, але він усе одно є частиною загального процесу.
Після підготовки запиту PostgreSQL повинен вирішити, як саме отримати дані.
Для одного результату часто існує кілька способів:
прочитати всю таблицю;
скористатися індексом;
об'єднати таблиці різними способами;
відсортувати рядки до або після фільтрації;
виконати операції послідовно або паралельно.
Планувальник оцінює ці варіанти та обирає план із найменшою очікуваною вартістю.
Нехай потрібно знайти користувачів за ідентифікатором:
SELECT *
FROM users
WHERE id = 42;PostgreSQL може:
послідовно прочитати всі рядки таблиці;
використати індекс на id, знайти потрібний рядок і звернутися до таблиці.
Якщо таблиця велика, індекс зазвичай є вигіднішим. Якщо таблиця дуже маленька, послідовне читання може бути достатньо дешевим, тому планувальник не обов'язково використає індекс.
Важливо: PostgreSQL не вибирає план лише за наявністю індексу. Він оцінює очікувану вартість кожного варіанта.
Планувальник не запускає всі можливі плани, щоб порівняти фактичний час. Замість цього він використовує статистику та внутрішні оцінки.
На вибір плану можуть впливати:
приблизна кількість рядків у таблиці;
розподіл значень у стовпцях;
наявність індексів;
оцінка вартості читання даних;
умови фільтрації;
способи об'єднання таблиць;
сортування та групування;
налаштування PostgreSQL.
Вартість у плані — це не час у мілісекундах. Це умовне число, яке PostgreSQL використовує для порівняння планів між собою.
План виконання складається з вузлів. Кожен вузол виконує окрему операцію та передає результат вузлу вище.
Часто можна побачити такі вузли:
Seq ScanПослідовне читання таблиці:
Seq Scan on usersPostgreSQL читає таблицю від початку до кінця та перевіряє рядки.
Index ScanПошук за індексом із подальшим отриманням рядків таблиці:
Index Scan using users_pkey on usersIndex Only ScanPostgreSQL може отримати потрібні значення без повного звернення до таблиці, якщо всі необхідні дані доступні в індексі та умови видимості це дозволяють.
FilterУмова, яку потрібно перевірити для отриманих рядків:
Filter: (age >= 18)Рядки, що не проходять фільтр, відкидаються.
SortСортування результату:
Sort Key: nameAggregateАгрегатні операції, наприклад count, sum або avg:
SELECT count(*)
FROM users;JoinОб'єднання рядків із кількох таблиць. PostgreSQL може використовувати різні алгоритми:
Nested Loop;
Hash Join;
Merge Join.
Конкретний вибір залежить від даних і умов запиту.
EXPLAINКоманда EXPLAIN показує план, який PostgreSQL планує використати, але не виконує сам запит:
EXPLAIN
SELECT *
FROM users
WHERE age >= 18;Приклад можливого результату:
Seq Scan on users (cost=0.00..18.50 rows=300 width=48)
Filter: (age >= 18)Цей результат означає:
Seq Scan on users — таблиця users читатиметься послідовно;
cost=0.00..18.50 — початкова та загальна оцінка вартості;
rows=300 — очікувана кількість рядків результату;
width=48 — приблизний середній розмір рядка в байтах;
Filter — умова, яку PostgreSQL застосує до прочитаних рядків.
Оцінки rows і width не є фактичним результатом. Це прогноз планувальника.
EXPLAIN ANALYZEКоманда EXPLAIN ANALYZE не лише показує план, а й реально виконує запит:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE age >= 18;Приклад результату:
Seq Scan on users (cost=0.00..18.50 rows=300 width=48)
(actual time=0.020..0.140 rows=280 loops=1)
Filter: (age >= 18)
Rows Removed by Filter: 120
Planning Time: 0.120 ms
Execution Time: 0.180 msТут з'являються фактичні показники:
actual time — час роботи вузла;
rows — фактична кількість отриманих рядків;
loops — кількість виконань вузла;
Rows Removed by Filter — кількість відкинутих рядків;
Planning Time — час побудови плану;
Execution Time — час виконання.
У прикладі планувальник очікував 300 рядків, але фактично отримав 280. Невелика різниця є нормальною. Велика різниця може означати, що статистика застаріла або запит складно оцінити.
EXPLAIN ANALYZEвиконує запит насправді. ДляSELECTце зазвичай безпечно, але дляINSERT,UPDATEіDELETEоперація також буде виконана.
Наприклад, цей запит змінить дані:
EXPLAIN ANALYZE
DELETE FROM users
WHERE age < 18;Тому для запитів, які змінюють дані, потрібно бути особливо уважним. Для перегляду плану без виконання використовуйте звичайний EXPLAIN.
Після вибору плану PostgreSQL передає його виконавцю. Виконавець запускає вузли плану та передає рядки між ними.
Розглянемо запит:
SELECT name
FROM users
WHERE age >= 18
ORDER BY name;Спрощений план може виглядати так:
Sort
Sort Key: name
Seq Scan on users
Filter: (age >= 18)Виконання відбувається знизу вгору:
Seq Scan читає рядки таблиці users.
Filter залишає рядки, де age >= 18.
Sort сортує залишені рядки за name.
PostgreSQL повертає стовпець name клієнту.
Верхній вузол плану формує остаточний результат, а нижні вузли готують для нього дані.
Створимо невелику таблицю та виконаємо запит:
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL,
in_stock boolean NOT NULL
);
INSERT INTO products (id, name, price, in_stock)
VALUES
(1, 'Keyboard', 75.00, true),
(2, 'Mouse', 25.00, true),
(3, 'Monitor', 220.00, false),
(4, 'Web camera', 90.00, true);
CREATE INDEX products_price_idx
ON products (price);
EXPLAIN
SELECT name, price
FROM products
WHERE price >= 50
AND in_stock = true
ORDER BY price;Що відбувається:
PostgreSQL розбирає SELECT, FROM, WHERE та ORDER BY.
Перевіряє таблицю products і стовпці name, price, in_stock.
Знаходить можливий індекс на price.
Оцінює послідовне читання та використання індексу.
Обирає план із меншою оціненою вартістю.
Виконує фільтри.
Сортує результат за price.
Повертає назви й ціни товарів, які коштують щонайменше 50 і є в наявності.
Для такої маленької таблиці PostgreSQL може вибрати Seq Scan, навіть попри наявність індексу. Це очікувана поведінка: прочитати кілька рядків усієї маленької таблиці іноді дешевше, ніж звертатися до індексу.
План описує спосіб отримання даних, а не самі дані.
Наприклад, два плани можуть повертати однаковий результат:
Seq Scanабо:
Index ScanРезультат запиту може бути однаковим, але час виконання та кількість прочитаних сторінок — різними.
Так само однаковий SQL-запит може отримувати різні плани в різних ситуаціях:
після зміни кількості рядків;
після створення або видалення індексу;
після оновлення статистики;
за іншого значення параметра;
після зміни налаштувань PostgreSQL.
План не є постійною властивістю запиту.
Порівнювати оцінки з фактичними значеннями зручно за допомогою EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT name, price
FROM products
WHERE price >= 50
AND in_stock = true
ORDER BY price;Особливо звертайте увагу на:
сильну різницю між оціненим rows і фактичним rows;
вузли, які виконуються багато разів;
велику кількість відкинутих рядків;
дорогі сортування;
несподіване послідовне читання великої таблиці.
Сам факт використання Seq Scan не означає, що запит повільний. На маленькій таблиці це часто найкращий варіант. Оцінювати план потрібно разом із розміром таблиці та фактичним часом виконання.
Наявність індексу не змушує PostgreSQL використовувати його. Якщо потрібно прочитати значну частину таблиці, послідовне читання може бути швидшим.
cost із мілісекундамиЗначення cost — внутрішня оцінка для порівняння планів, а не час виконання в мілісекундах.
EXPLAIN ANALYZE виконує запитДля запитів, які змінюють дані, EXPLAIN ANALYZE не є лише переглядом. Він виконає операцію.
rows у звичайному EXPLAIN — прогноз. Фактичні рядки можна побачити під час EXPLAIN ANALYZE.
Потрібно дивитися на весь план: фільтри, сортування, об'єднання, кількість виконань і фактичний час.
Seq ScanПослідовне читання маленької таблиці може бути найефективнішим планом. Назва вузла сама по собі не визначає проблему.
PostgreSQL проходить шлях від SQL-тексту до результату через розбір, перевірку, переписування, планування та виконання.
Планувальник порівнює можливі способи роботи із запитом.
Обраний план складається з вузлів, які виконуються як дерево.
EXPLAIN показує прогнозований план без виконання запиту.
EXPLAIN ANALYZE показує фактичні показники, але виконує запит.
cost є умовною оцінкою, а не часом у мілісекундах.
Для розуміння ефективності потрібно порівнювати оцінені та фактичні значення й дивитися на весь план.