Оптимизация запросов с использованием CTE

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

На первый взгляд, CTE кажутся идеальными: выглядят чисто, пишутся легко, позволяют разбивать код на логические блоки. Но есть один нюанс — стратегия выполнения CTE в PostgreSQL менялась.

До PostgreSQL 12 WITH всегда работал как «оптимизационный забор»: результат CTE сначала материализовался во временную таблицу, и только потом использовался в основном запросе.

С PostgreSQL 12+ поведение умнее: если у CTE нет побочных эффектов (это обычный SELECT) и он используется в основном запросе ровно один раз, планировщик встраивает его прямо в запрос (как подзапрос) — это поведение NOT MATERIALIZED по умолчанию. Если CTE используется два и более раз, или у него есть INSERT/UPDATE/DELETE, или он рекурсивный — он по-прежнему материализуется. Принудительно управлять этим можно ключевыми словами MATERIALIZED / NOT MATERIALIZED после имени 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.

2

Задача

SQL SELF, 28 уровень, 2 лекция

Недоступна

Комбинация нескольких CTE для сложного запроса

Комбинация нескольких CTE для сложного запроса

Открыть