Основні концепції плану виконання запиту: cost, rows, width

Коли ти пишеш SQL-запит, PostgreSQL не починає його виконувати одразу. Спочатку він вмикає свій "мозок" — оптимізатор запитів, який створює план виконання. Цей план — як маршрут на карті: PostgreSQL рахує, які дії й у якому порядку треба зробити, щоб успішно отримати дані.

Оптимізатор запитів оцінює всі можливі шляхи виконання твого запиту: послідовне сканування таблиці, використання індексів, виконання фільтрації та сортування і т.д. Він намагається знайти найдешевший (з точки зору ресурсів) спосіб виконати твій запит. Тобто, він шукає компроміс між часом виконання і ресурсами сервера.

Ключові параметри плану виконання

Ну що, переходимо до самої "жести" — розбору параметрів, які PostgreSQL показує тобі у результаті виконання команди EXPLAIN. Для початку візьмемо простий приклад:

EXPLAIN
SELECT * FROM students WHERE age > 20;

Ти отримаєш щось типу такого результату:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=72)
  Filter: (age > 20)

Давай розберемо ці загадкові слова і числа.

1. cost (витрати на виконання)

cost — це оцінка того, скільки ресурсів знадобиться для виконання запиту. Цей параметр складається з двох частин:

• Startup Cost: витрати на початок виконання операції (наприклад, підготовка індексу).
• Total Cost: сумарні витрати на виконання всієї операції.

Приклад:

cost=0.00..35.00

• 0.00 — це Startup Cost.
• 35.00 — це Total Cost.

Чим нижчі значення cost, тим кращий цей план для PostgreSQL. Але важливо пам’ятати, що cost — це відносна величина. Вона не вимірюється у секундах чи мілісекундах, а скоріше відображає внутрішню оцінку PostgreSQL.

2. rows (припустима кількість рядків)

rows показує, скільки рядків PostgreSQL очікує повернути або обробити на цьому етапі виконання запиту. У нашому прикладі:

rows=7

Це означає, що PostgreSQL припускає, що фільтр age > 20 поверне 7 рядків. Ці дані беруться зі статистики, яку PostgreSQL збирає про таблицю. Якщо статистика застаріла, прогноз може бути неправильним. Це може призвести до менш оптимального плану.

3. width (ширина рядка у байтах)

width — це середній розмір кожного рядка, що повертається на цьому етапі, виміряний у байтах. У нашому прикладі:

width=72

Це означає, що кожен повернутий рядок у середньому займає 72 байти. width враховує розмір даних у стовпцях і будь-які додаткові накладні витрати, такі як ідентифікатори рядків або службова інформація.

Це приблизно як із завантаженням додатку. Якщо його "вага" (аналог width) велика, тобі знадобиться більше часу для завантаження, навіть якщо у тебе швидкий інтернет (аналог cost).

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

Давай подивимось на реальний приклад. Припустимо, у нас є таблиця students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    major VARCHAR(50)
);

І ми виконуємо такий запит:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Результат може виглядати так:

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan: PostgreSQL виконує послідовне сканування таблиці students. Це означає, що він проходить по кожному рядку.
• cost=0.00..42.50: Витрати на виконання операції. Startup Cost становить 0.00, а загальні витрати — 42.50.
• rows=3: PostgreSQL очікує, що фільтр age > 20 AND major = 'CS' поверне 3 рядки.
• width=164: Кожен рядок займає у середньому 164 байти.

Тепер ти розумієш, як PostgreSQL приймає рішення, і можеш знаходити слабкі місця у запитах. Наприклад, якщо ти бачиш високий cost, це може бути ознакою того, що запит занадто важкий. Або, якщо ти бачиш велику кількість рядків у rows, тобі варто переглянути свій фільтр.

Як працює cost на практиці?

Давай додамо індекс на стовпець age:

CREATE INDEX idx_age ON students(age);

Тепер повторимо наш запит:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Результат може змінитися:

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Що змінилося?

• Замість Seq Scan тепер використовується Bitmap Heap Scan: PostgreSQL спочатку шукає підходящі рядки в індексі idx_age, а потім витягує їх із таблиці.
• cost суттєво знизився: тепер Startup Cost становить 4.37, а Total Cost — 20.50.
• Операція стала ефективнішою завдяки індексу.

Візуалізація: різниця між Seq Scan та Index Scan

Ось невеличка таблиця для порівняння, щоб було наочніше:

Підводні камені та типові помилки

Коли використовуєш параметри плану виконання, будь готовий до деяких сюрпризів. Наприклад, не завжди низький cost означає краще виконання. Якщо статистика бази даних застаріла (наприклад, після масового оновлення таблиці), план може бути недостатньо точним. Оновлюй статистику за допомогою команди ANALYZE. Детальніше про неї — у наступній лекції.

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