Аналіз продуктивності функцій та процедур: використання EXPLAIN ANALYZE

EXPLAIN ANALYZE допомагає зрозуміти, як PostgreSQL "думає", коли виконує твій запит:

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

Команда EXPLAIN ANALYZE реально виконує запит і показує, як PostgreSQL оптимізує виконання. Уяви, що ти розбираєш годинник, щоб зрозуміти, як працює його механізм. Те саме робить EXPLAIN ANALYZE, тільки з твоїми SQL-запитами.

Синтаксис EXPLAIN ANALYZE

Почнемо з простого. Ось базовий вигляд команди:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Цей запит виконає команду SELECT і покаже, як PostgreSQL обробляє дані.

Результат EXPLAIN ANALYZE складається з дерева виконання запиту. Кожен рівень дерева описує крок, який PostgreSQL виконує:

• Operation Type — тип операції (наприклад, Seq Scan, Index Scan).
• Cost — наскільки "дорого" PostgreSQL вважає виконання цієї операції.
• Rows — скільки рядків очікується і скільки реально отримано в результаті.
• Time — скільки часу зайняла операція.

Приклад виводу:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Зверни увагу на Seq Scan on students. Це означає, що PostgreSQL переглядає ВСІ рядки таблиці students. Якщо таблиця велика, це може бути ДУЖЕ ПОВІЛЬНО.

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

Давай розберемо кілька практичних прикладів, де ти навчишся знаходити і виправляти проблеми в запитах.

Приклад 1: повний перегляд таблиці

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Вивід:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

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

Рішення: створимо індекс по стовпцю age.

CREATE INDEX idx_students_age ON students(age);

Тепер виконай той самий запит:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Вивід:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Ми бачимо Index Scan замість Seq Scan. Ура, тепер запит літає!

Приклад 2: складний запит з JOIN

Уявімо, що у нас є дві таблиці: students і courses. Ми хочемо дізнатись імена студентів і назви курсів, на які вони записані.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Вивід може бути приблизно таким:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Як бачиш, у PostgreSQL все під контролем: використовуються індекси на таблицях enrollments і courses, а виконання відбувається швидко. Але якщо якогось індексу не вистачає, ти можеш побачити Seq Scan, що сповільнить виконання.

Оптимізація продуктивності функцій

Тепер уявімо, що у нас є функція, яка повертає список студентів старше певного віку:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Ми можемо аналізувати продуктивність цієї функції за допомогою EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Прискорення виконання функції

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

1. Переконайся, що стовпець, який використовується у фільтрах (age), проіндексований.
2. Перевір кількість рядків у таблиці і подумай про партиціонування, якщо даних забагато.

Вузькі місця і як їх фіксити

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

2. Велика кількість рядків у результуючому наборі. Якщо запит повертає мільйони рядків, подумай про додавання фільтрів (WHERE, LIMIT) або пагінацію (OFFSET).

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