Розуміння планів виконання запитів

Як плани запитів впливають на продуктивність запитів

У нашій попередній публікації ми обговорювали, як використовувати оператор EXPLAIN, щоб перевірити, чи використовується індекс під час виконання запиту. Однак ми не занурювалися глибше в те, як працює EXPLAIN чи що означає його результат. Якщо ви пропустили ту публікацію, обов'язково ознайомтесь з нею для глибшого розуміння.

У сьогоднішній публікації ми більш детально розглянемо План виконання запиту та розберемо різні ключові аспекти, включаючи Index Scan, Gather, Seq Scan та важливі метрики, такі як cost, rows та інші. Розпочнемо!

У нас є стовпець id з індексом, оскільки id оголошено як Primary Key. Давайте витягнемо всіх користувачів, чиї id менші за 100.

EXPLAIN SELECT id, first_name, last_name, email, birthday, is_pro  
FROM users WHERE ID < 100;

План виконання запиту з використанням індексованого стовпця — id

+--------------------------------------------------------------------------+  
|QUERY PLAN |  
+--------------------------------------------------------------------------+  
|Index Scan using users_pkey on users (cost=0.42..11.30 rows=106 width=52)|  
| Index Cond: (id < 100) |  
+--------------------------------------------------------------------------+

Давайте розберемо результат, щоб зрозуміти, що відбувається "під капотом".

Розбір плану запиту

1. Index Scan using users_pkey on users:

• Index Scan: це означає, що база даних використовує індекс для знаходження рядків, що відповідають умові, а не виконує послідовне сканування всієї таблиці рядок за рядком. Index Scan зазвичай є більш ефективним для запитів, які можуть скористатися індексом.
• using users_pkey: це вказує на те, що база даних використовує індекс users_pkey для виконання сканування, який автоматично створюється, коли id оголошується як Primary Key.
• on users: це вказує на таблицю, до якої застосовується сканування — у даному випадку таблиця users.

2. (cost=0.42..11.30 rows=106 width=52):

• cost=0.42..11.30: це оцінений cost (витрати) виконання запиту, виражений в "довільних одиницях". Важливо зазначити, що цей cost не пов’язаний безпосередньо з часом чи використанням пам'яті, але є внутрішнім обчисленням, яке PostgreSQL використовує для визначення ефективності плану запиту.
• 0.42: це оцінка стартових витрат, або витрати на початок сканування індексу.
• 11.30: це загальний cost плану запиту, включаючи зчитування даних та завершення операції.
  База даних використовує ці оцінки витрат для порівняння різних можливих планів запиту та вибору найефективнішого. Нижчий cost вказує на більш ефективний план.
• rows=106: це оцінкова кількість рядків, яку база даних очікує отримати на основі плану запиту. У цьому випадку база даних очікує, що 106 рядків задовольнять умову id < 100.
• width=52: це середня ширина (в байтах) кожного рядка, який буде повернуто запитом. Це оцінений розмір кожного рядка в результатах.

3. Index Cond: (id < 100):

• Index Cond: Це стосується умови, яку індекс використовує для фільтрації рядків. У цьому випадку умовою є id < 100.
  Індекс (users_pkey) використовується для фільтрації рядків, де id менше 100.
  Це більш ефективно, ніж послідовне сканування, оскільки індекс відсортований, і база даних може швидко переходити до відповідних рядків, а не перевіряти кожен рядок у таблиці.

План виконання запиту з використанням неіндексованого стовпця — first_name

+-------------------------------------------------------------------------+  
|QUERY PLAN |  
+-------------------------------------------------------------------------+  
|Gather (cost=1000.00..19869.57 rows=328 width=52) |  
| Workers Planned: 2 |  
| -> Parallel Seq Scan on users (cost=0.00..18836.77 rows=137 width=52)|  
| Filter: (first_name = 'Jason'::text) |  
+-------------------------------------------------------------------------+

Розбір плану запиту

Gather (cost=1000.00..19869.57 rows=328 width=52)

• Gather: Вузол Gather вказує на те, що цей план запиту використовує паралельне виконання. У PostgreSQL оператор Gather відповідає за об'єднання результатів з кількох робочих процесів (у цьому випадку 2 робочих процеси) в один набір результатів.
• Parallel Query: Це означає, що база даних виконує кілька процесів (або робочих процесів) одночасно для пришвидшення виконання запиту. Кожен робочий процес сканує частину даних паралельно, а вузол Gather збирає результати з усіх робочих процесів.
• cost=1000.00..19869.57: Діапазон cost представляє оцінені стартові витрати та загальні витрати для операції Gather.
• 1000.00 — це стартові витрати для операції Gather.
• 19869.57 — це загальні витрати на запит (включаючи стартові та обробні витрати).
• rows=328: Це оцінена загальна кількість рядків, яку буде повернуто запитом після збору результатів з усіх робочих процесів. Планувальник запитів оцінює, що він поверне 328 рядків загалом.
• width=52: Це середня ширина (в байтах) кожного рядка в наборі результатів. Кожен рядок оцінюється в 52 байти.

2. Workers Planned: 2

• Ця частина вказує, що було заплановано 2 робочих процеси для виконання запиту паралельно. Ці робочі процеси одночасно сканують частини таблиці users, що може значно покращити продуктивність при скануванні великих наборів даних.

3. -> Parallel Seq Scan on users (cost=0.00..18836.77 rows=137 width=52)

• Parallel Seq Scan: Це показує, що база даних виконує послідовне сканування таблиці users, але робить це паралельно. Кожен з робочих процесів сканує різні частини таблиці. Хоча це все ще послідовне сканування (тобто читання таблиці рядок за рядком), воно виконується кількома робочими процесами одночасно.
• cost=0.00..18836.77: Це представляє діапазон витрат для Паралельного послідовного сканування:
• 0.00 — це стартові витрати для кожного робочого процесу.
• 18836.77 — це загальні витрати на сканування для кожного робочого процесу.
• rows=137: Це оцінена кількість рядків, які кожен робочий процес оброблятиме. Оскільки є 2 робочих процеси, загальна кількість оброблених рядків становить 137 рядків на кожного процеса, що дає оцінену загальну кількість 274 рядків (хоча остаточна кількість рядків у кроці Gather становить 328, що означає, що деякі рядки можуть бути оброблені другим робочим процесом або розподілені між робочими процесами).
• width=52: Це середня ширина кожного рядка в наборі результатів для послідовного сканування. Кожен рядок оцінюється в 52 байти.

4. Filter: (first_name = ‘Jason’::text)

• Filter: Це вказує на те, що до даних, які скануються, застосовуються умови фільтрації: запит шукає рядки, де стовпець first_name дорівнює 'Jason'.
• first_name = 'Jason': Ця умова фільтрує рядки на основі значення стовпця first_name.
  'Jason'::text: частина::textє перетворенням типу, що означає, що рядок'Jason'явно перетворюється в тип данихtext, хоча в цьому випадку це, ймовірно, зайве, оскільки'Jason'вже є значенням типуtext`.

Підсумок

Цей план запиту показує, що база даних виконує паралельне послідовне сканування таблиці users, де два робочих процеси сканують таблицю паралельно. Кожен робочий процес шукає рядки, де стовпець first_name дорівнює 'Jason'. Після сканування вузол Gather збирає результати з двох робочих процесів і повертає фінальний набір рядків.

Чому паралельне виконання?

Паралельне виконання може бути корисним при роботі з великими наборами даних, оскільки дозволяє системі обробляти кілька частин даних одночасно, що зменшує загальний час виконання запиту. Це особливо корисно для великих таблиць або запитів, що потребують сканування великої кількості даних.

У цьому випадку PostgreSQL вирішив, що паралельне послідовне сканування буде швидшим за інші потенційні стратегії (наприклад, сканування індексу), ймовірно, через те, що сканується велика частина таблиці, а умова фільтрації (first_name = 'Jason') застосовується до багатьох рядків.

Основні моменти, які варто пам'ятати:

• Gather: Об'єднує результати з кількох паралельних робочих процесів.
• Parallel Seq Scan: Сканує таблицю рядок за рядком, але робиться це паралельно кількома робочими процесами.
• Workers Planned: Для сканування даних використовуються два паралельних робочих процеси.
• Filter: Фільтрує рядки, де first_name = 'Jason'.

Розбір сканування індексу на індексованому стовпці

У першому прикладі результат запиту був наступним:

EXPLAIN SELECT id, first_name, last_name, email, birthday, is_pro  
 FROM users WHERE ID < 100;  

-- OUTPUT  

+-------------------------------------------------------------------------+  
|QUERY PLAN |  
+-------------------------------------------------------------------------+  
|Index Scan using users_pkey on users (cost=0.42..11.09 rows=94 width=52)|  
| Index Cond: (id < 100) |  
+-------------------------------------------------------------------------+

Але що станеться, якщо змінити умову? Припустимо, нам потрібно отримати всіх користувачів, чиї id > 100. Давайте перевіримо, що ми отримаємо в цьому випадку.

EXPLAIN SELECT id, first_name, last_name, email, birthday, is_pro  
 FROM users WHERE id > 100;  

-- Output  

+-------------------------------------------------------------+  
|QUERY PLAN |  
+-------------------------------------------------------------+  
|Seq Scan on users (cost=0.00..26054.85 rows=989813 width=52)|  
| Filter: (id > 100) |  
+-------------------------------------------------------------+

ОЙ! Що сталося з нашим запитом та індексом? Хоча індекс визначений для стовпця, PostgreSQL використав Seq Scan замість Index Scan.

У наступному пості ми детальніше розглянемо фактори, які впливають на це рішення, такі як кардинальність і селективність, і дослідимо, як вони впливають на план виконання, щоб краще зрозуміти продуктивність запиту.

У цьому пості ми дослідили, як PostgreSQL будує план виконання запиту на основі наявності та відсутності спеціалізованого індексу для стовпця. Також ми розглянули ключові аспекти виконання запиту, включаючи ролі Gather, cost, rows, width та інші важливі деталі.

Якщо вам сподобався цей пост, обов'язково ознайомтесь з іншими моїми статтями! Підпишіться, щоб бути в курсі нових публікацій. У майбутніх статтях ми продовжимо досліджувати Плани запитів та Індекси бази даних.

Щасливого кодування! 🧑‍💻👩‍💻👨‍💻

Перекладено з: Understanding Query Execution Plans