Оптимізація запитів з використанням CTE

Сьогодні ми занурюємось у захопливий (і трохи лячний) світ оптимізації запитів з використанням CTE (Common Table Expressions). Якщо ти вже навчився створювати CTE (ми про це говорили на попередніх лекціях), то зараз саме час поговорити про тонкощі їхньої внутрішньої кухні, «підводні камені» і способи вижати з них максимум ефективності.

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

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

1. Обсяг даних у CTE величезний, а результат використовується лише частково.
2. CTE викликається занадто багато разів, що збільшує накладні витрати.
3. Ми створюємо зайві складні CTE, які насправді не потрібні.

Знайомимось з матеріалізацією

Матеріалізація — це процес, коли PostgreSQL зберігає результат CTE в пам’яті або на диску (залежно від розміру даних). Це означає, що дані витягуються лише один раз, але якщо ти використовуєш CTE тільки в одному місці, матеріалізація може бути зайвою. Наприклад:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

У цьому випадку PostgreSQL спочатку створює тимчасову таблицю з результатом повного CTE (grade > 60), а потім фільтрує рядки, де grade > 90. Це додає зайвий проміжний крок і впливає на продуктивність.

Як уникнути зайвої матеріалізації?

З PostgreSQL 12 з’явилась можливість уникати матеріалізації CTE, коли це не потрібно. Для цього юзається ключове слово MATERIALIZED (за замовчуванням) або NOT MATERIALIZED. Приклад:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Тут ми кажемо PostgreSQL не матеріалізовувати дані large_set, а вбудувати запит прямо в основне вираження. Це робить запит ефективнішим, бо проміжна таблиця не створюється.

Коли матеріалізація корисна?

Не думай, що матеріалізація — це завжди погано! Якщо дані CTE використовуються кілька разів у запиті або мають бути обчислені незалежно, матеріалізація може бути корисною. Приклад:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Тут матеріалізація дозволяє уникнути повторного обчислення фільтра grade > 60.

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

Щоб CTE працювали швидше, треба використовувати індекси в базових таблицях, з яких тягнуться дані. Наприклад:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

Індекс по стовпцю grade дозволяє PostgreSQL швидше витягувати рядки, які підходять під умову grade > 90. Це особливо важливо при роботі з великими таблицями.

Розбиття великих CTE на дрібніші

Якщо CTE повертає багато даних, які потім фільтруються або агрегуються, краще розбити його на кілька етапів. Замість одного складного CTE зручніше зробити кілька маленьких:

Погано (великий CTE):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Краще (розбиття на етапи):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

Такий підхід допомагає PostgreSQL краще оптимізувати виконання запитів.

Практичний приклад: аналіз структури та оптимізація

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

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Цей запит можна оптимізувати, якщо додати індекси до таблиць grades і enrollments, що прискорить фільтрацію і з’єднання.

Monitoring: аналіз продуктивності

Щоб зрозуміти, наскільки ефективний запит, юзай EXPLAIN або EXPLAIN ANALYZE. Наприклад:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Цей запит покаже, скільки часу займає виконання кожного кроку, і допоможе зрозуміти, де можна покращити продуктивність.

Детальніше EXPLAIN ANALYZE ми з тобою розберемо на наступних рівнях :P

Часті помилки при оптимізації CTE

1. Забули про індекси. Якщо фільтрація даних виконується в CTE, але в базовій таблиці немає індексу, продуктивність постраждає.
2. Використання занадто великих CTE. Якщо один запит робить занадто багато, це може призвести до матеріалізації великих обсягів даних.
3. Зловживання NOT MATERIALIZED. В деяких випадках матеріалізація все ж потрібна, щоб уникнути повторного виконання CTE.
4. Ігнорування моніторингу. Без аналізу через EXPLAIN ти можеш не помітити, що запити працюють повільно.

Тепер ти готовий оптимізувати запити з використанням CTE, уникаючи пасток і підвищуючи продуктивність! Пам’ятай, що CTE — це інструмент, а не панацея. Використовуй їх з розумом, і вони стануть твоїми найкращими друзями в PostgreSQL.