Що таке EXPLAIN і як з його допомогою аналізувати запити

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

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

Іншими словами, EXPLAIN дозволяє зрозуміти, чого чекати від виконання запиту: наскільки він «важкий», скільки рядків планується обробити і які ресурси при цьому будуть задіяні. Це незамінний інструмент, коли запит раптом починає гальмувати, а тобі треба з’ясувати — чому.

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

Синтаксис команди EXPLAIN

Давай подивимось на базовий синтаксис команди EXPLAIN:

EXPLAIN твій_SQL_запит;

Приклад запиту:

EXPLAIN SELECT * FROM students WHERE age > 20;

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

Ось приклад виводу:

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

Цей вивід може здатися страшним, але не переймайся — зараз розберемося з основними компонентами.

Розбір базового плану виконання

Давай розберемо той самий результат:

1. Seq Scan on students — це означає, що PostgreSQL буде сканувати таблицю students повністю (послідовне сканування). Це не завжди погано, але у великих таблицях Seq Scan може бути повільним.

2. cost=0.00..35.00 — це оцінка витрат на виконання операції:

   • Startup Cost: стартові витрати операції (у цьому випадку 0.00).
   • Total Cost: загальні витрати на завершення операції (тут 35.00).

3. rows=7 — PostgreSQL припускає, що умова age > 20 поверне 7 рядків. Це називається "кардинальністю" (cardinality). Якщо бачиш дивні оцінки, це може бути сигналом, що статистика твоєї таблиці застаріла.

4. width=37 — це середній розмір одного рядка у байтах.

5. Filter: (age > 20) — уточнює, що PostgreSQL застосує фільтр, перевіряючи кожен рядок.

Таким чином, вивід EXPLAIN дає тобі уявлення про стратегії та припущення PostgreSQL. Ти можеш використовувати цю інформацію для оптимізації.

Опції команди EXPLAIN

Хоча базовий вивід EXPLAIN вже корисний, ти можеш модифікувати його за допомогою наступних опцій:

ANALYZE

З цією опцією PostgreSQL не лише покаже план виконання, а й виконає запит, надавши фактичні дані. Приклад:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

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

VERBOSE

Показує додаткову деталізацію, корисну для глибокого аналізу. Приклад:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Показує використання буферів пам’яті при виконанні запиту. Застосовується разом з ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Якщо хочеш приховати або показати інформацію про витрати (cost), використовуй цю опцію:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

Вивід плану може бути представлений в інших форматах, таких як JSON або XML. Приклад:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Приклад використання EXPLAIN

Розглянемо базу даних university з таблицею students. Припустимо, ти хочеш знайти всіх студентів старше 20 років:

EXPLAIN SELECT * FROM students WHERE age > 20;

Вивід може бути таким:

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

Як ми вже згадували, це послідовне сканування Seq Scan, яке може бути неефективним для великих таблиць.

Тепер створимо індекс по стовпцю age і подивимось, чи зміниться план:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Вивід:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Тепер PostgreSQL використовує індексне сканування (Index Scan), що зазвичай швидше, особливо для великих таблиць.

Типові питання та помилки

Чому мій запит виконується повільно, незважаючи на індекс?

Можливо, запит повертає занадто багато рядків, що робить використання індексу менш вигідним. Індекс може бути неякісним або не оновленим.

Що якщо вивід EXPLAIN складно зрозуміти?

Почни з простих запитів і вивчай вузли плану виконання по одній операції.

Як дізнатися, чи застаріла статистика таблиці?

Виконай команду ANALYZE students

Коли використовувати EXPLAIN без ANALYZE?

Якщо хочеш побачити план без реального виконання (наприклад, для запитів, які модифікують дані).