Використання EXPLAIN ANALYZE для вимірювання реального часу виконання запитів

Якщо команда EXPLAIN дозволяє тобі зазирнути у кришталеву кулю й побачити, як PostgreSQL “планує” виконати запит, то EXPLAIN ANALYZE перетворює тебе на справжнього детектива, який з'ясовує, що насправді відбулося.

Ключові відмінності EXPLAIN від EXPLAIN ANALYZE:

EXPLAIN – це теорія, яка показує, як PostgreSQL планує виконання запиту. Ти бачиш очікувані значення, такі як кількість рядків (rows) і вартість виконання (cost).

EXPLAIN ANALYZE – це практика. PostgreSQL реально виконує запит і показує:

• Фактичну кількість оброблених рядків на кожному етапі.
• Фактичний час виконання кожної операції.
• Порівняння з припущеннями плану (rows і cost).

Приклад: якщо твій запит очікує обробити 100 рядків, а реально обробляє 10 000 рядків, EXPLAIN ANALYZE одразу розкриє цей неохайний факт!

Базовий синтаксис і використання

Як і EXPLAIN, EXPLAIN ANALYZE юзати дуже просто. Просто додай слово ANALYZE до своєї команди EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ось що зробить PostgreSQL:

• Він виконає запит.
• Запише кожну операцію у плані виконання, включаючи реальні показники.
• Поверне повний опис процесу виконання запиту.

Які дані дає EXPLAIN ANALYZE?

Фактичний час виконання операцій:

• Actual Start Time: коли операція почалася.
• Actual End Time: коли операція завершилася.

Загальна кількість оброблених рядків:

Це допомагає оцінити, наскільки точні припущення плану (значення rows).

Інфа про буфери:

Як використовувалися дискові та пам'яті-орієнтовані буфери.

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

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

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

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Виконаємо запит для вибірки студентів старше 20 років:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

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

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Розбираємо результат:

• Seq Scan – каже, що PostgreSQL робить послідовне сканування таблиці.
• cost=0.00..14.00 – це очікувана вартість операції.
• rows=2 – PostgreSQL очікує, що запит поверне 2 рядки (і він не помилився!).
• actual time=0.025..0.026 – реальний час виконання операції (у мілісекундах).
• Rows Removed by Filter: 2 – два рядки були відфільтровані, бо не відповідали умові WHERE.

Порівняння теорії та практики

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

Давай подивимось більш складний приклад.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

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

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Що ми бачимо:

1. PostgreSQL виконав запит за 0.057 мілісекунди.
2. Тільки один рядок (rows=1) відповідає умовам WHERE.
3. Три рядки були відфільтровані (Rows Removed by Filter: 3).

Резюме

Використання EXPLAIN ANALYZE дозволяє знайти вузькі місця й зрозуміти, як оптимізувати запити. Наприклад:

• Якщо Seq Scan занадто "важкий", можливо, час додати індекс.
• Якщо припущення PostgreSQL сильно відрізняються від реальних даних, треба перевірити статистику таблиць (ANALYZE) або структуру індексів.