Інтерпретація плану виконання: читання та аналіз вузлів (`Seq Scan`, `Index Scan`, `Hash Join`)

Сьогодні розберемося, що таке вузли плану виконання PostgreSQL, як їх читати і, найголовніше, як зрозуміти, що щось пішло не так. Ти дізнаєшся, чому твоя база даних іноді обирає ресурсоємний Seq Scan, хоча, здавалось би, індекс вже є, а може навіть і не один.

Коли PostgreSQL будує план виконання запиту, він розбиває його на етапи, які називаються вузлами. Кожен вузол — це свого роду "крок", який сервер бази даних виконує, щоб обробити твій запит. Основні типи вузлів включають:

Sequential Scan (Seq Scan)

Seq Scan або послідовне сканування — це найпростіший спосіб витягнути дані з таблиці. PostgreSQL буквально бере таблицю, читає її рядки одну за одною і перевіряє, чи підходять вони під умови твого запиту.

Коли використовується Seq Scan?
Seq Scan застосовується, якщо:

• У таблиці немає підходящого індексу для прискорення запиту.
• Умова фільтрації занадто загальна, щоб індекс був корисним (наприклад, вибірка більше 50% даних).
• PostgreSQL вважає, що послідовне читання таблиці швидше, ніж використання індексу (іноді так буває на дуже маленьких таблицях).

EXPLAIN SELECT * FROM students WHERE age > 18;

Приклад результату:

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Зверни увагу на Seq Scan on students — це PostgreSQL каже, що буде читати таблицю "students" повністю.

Проблеми з Seq Scan: Якщо таблиця велика, послідовне сканування може зайняти дуже багато часу.

Index Scan

Index Scan — це сканування даних з використанням індексу. Коли ти створюєш індекс у PostgreSQL, це приблизно як зробити зміст для своєї таблиці. Якщо запит може скористатися індексом, PostgreSQL обходить таблицю не повністю, а тільки потрібні частини.

Коли використовується Index Scan?

• У запиті вказані умови фільтрації для стовпця з індексом (наприклад, WHERE).
• Використовуються операції порівняння, такі як =, <, >, BETWEEN і т.д.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

Приклад результату:

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Тут Index Scan using idx_students_age показує, що PostgreSQL використовує індекс idx_students_age. Построчне читання таблиці замінюється на набагато швидший доступ через індекс.

Переваги Index Scan:

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

Проблеми Index Scan:
Якщо твій запит повертає занадто багато даних (наприклад, більше половини таблиці), використання індексу може бути навіть повільніше, ніж Seq Scan.

Hash Join

Hash Join використовується для об'єднання двох таблиць на основі умови з'єднання (наприклад, ON students.course_id = courses.id). PostgreSQL створює hash-таблицю для однієї з таблиць (меншої за розміром) і використовує її для пошуку збігів у другій таблиці.

Коли використовується Hash Join?

• При з'єднанні таблиць через INNER JOIN, LEFT JOIN і т.д.
• Коли PostgreSQL вважає Hash Join ефективнішим, ніж інші методи з'єднання.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

Приклад результату:

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Тут Hash Join з'єднує дві таблиці. Зверни увагу, що PostgreSQL спочатку виконує Seq Scan для обох таблиць, а потім будує hash-таблицю (Hash).

Переваги Hash Join:

• Швидка обробка для таблиць середнього розміру.
• Ефективний для з'єднання таблиць з великою кількістю рядків.

Проблеми Hash Join:
Якщо розмір hash-таблиці перевищує доступну пам'ять, PostgreSQL буде використовувати диск для її зберігання, що суттєво сповільнює з'єднання.

Приклад аналізу плану виконання

Давай розглянемо реальний приклад.

Запит:

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Результат:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Інтерпретація:

1. Hash Join: Головний вузол. PostgreSQL з'єднує таблиці students і courses.
   • actual time: від 1.00 до 2.50 мс.
   • rows=5: запит повернув 5 рядків.
2. Вкладені вузли:
   • Seq Scan on students: послідовно читає таблицю students і застосовує фільтр (age > 18).
   • Rows Removed by Filter = 3: 3 рядки не відповідали умові.
   • Hash: PostgreSQL створює hash-таблицю для таблиці courses.

Порівняння та вибір вузлів

Коли ти аналізуєш план виконання, головне — розуміти, чому PostgreSQL обрав саме такий спосіб обробки даних. Іноді треба втрутитися, щоб виправити ситуацію, наприклад, додати індекс або переписати запит. Ось кілька порад:

• Якщо бачиш Seq Scan на великій таблиці — подумай про індекси.
• Якщо Hash Join занадто повільний — перевір доступну пам'ять для PostgreSQL.
• Використовуй EXPLAIN ANALYZE, щоб порівнювати очікувані та фактичні значення метрик (rows, time).

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