Материалы nkhcode

Appearance

Подзапросы vs CTE ​

Оптимизация, читаемость и поддержка кода - при решении разных задач нужно четко понимать какой инструмент необходимо выбирать.

Когда вы начинаете работать с более сложными SQL-запросами, перед вами встает важный выбор: использовать подзапросы или CTE (Common Table Expressions, общие табличные выражения). Обе конструкции позволяют структурировать и оптимизировать сложные запросы, но делают это по-разному.

Подзапрос — это запрос, вложенный внутри другого запроса. Он может находиться почти в любой части основного запроса, в зависимости от того, что будет являться результатом подзапроса. Подзапросы бывают коррелирующими (зависящими от внешнего запроса) и некоррелирующими (независимыми). Напомню, что в большинстве случаев коррелирующие подзапросы являются антипаттерном.

CTE (WITH-запрос) — это именованный временный набор результатов, который существует только во время выполнения запроса. CTE определяется с помощью ключевого слова WITH и может ссылаться на себя (рекурсивные CTE) или на другие CTE в том же запросе.

Базовый пример: подзапрос в WHERE ​

Задача: нужно найти всех клиентов, которые арендовали фильмы чаще среднего.

sql
select customer_id, count(*) 
from rental
group by customer_id
having count(*) > (
    select avg(rental_count)
    from (
        select count(*) as rental_count
        from rental
        group by customer_id) sub);
text
customer_id|count|
-----------+-----+
         87|   30|
        273|   35|
        550|   32|
        ...
        
HashAggregate  (cost=794.81..803.80 rows=200 width=10) (actual time=3.921..3.977 rows=296 loops=1)

В данном запросе два уровня вложенности: внутренний подзапрос sub считает количество аренд для каждого клиента, подзапрос выше — вычисляет среднее значение, а внешний запрос — фильтрует клиентов. Такой код может стать сложным для чтения при увеличении уровня вложенности.

Тот же пример с использованием CTE ​

Та же задача, но с использованием общих табличных выражений. CTE позволяет разбить сложный запрос на логические блоки, что улучшает читаемость.

sql
with customer_rentals as (
    select customer_id, count(*) as rental_count
    from rental
    group by customer_id
),
average_rentals as (
    select avg(rental_count) as avg_count
    from customer_rentals
)
select customer_id, rental_count
from customer_rentals
where rental_count > (select avg_count from average_rentals);
text
customer_id|count|
-----------+-----+
         87|   30|
        273|   35|
        550|   32|
        ...

CTE Scan on customer_rentals  (cost=410.14..425.12 rows=200 width=10) (actual time=2.047..2.091 rows=296 loops=1)

Запрос стал более структурированным. Три этапа: расчет аренд по клиентам, расчет среднего значения и финальный отбор. Такой код легче поддерживать и модифицировать.
При этом, так как количество сканирований таблицы rental с агрегацией получаемых данных сократилось до одного, то получили оптимизацию ~ в 2 раза.

Ключевые различия между подзапросами и CTE ​

1. Читаемость и структурированность: CTE выигрывают, особенно в сложных запросах с множественными вычислениями. Они создают "строительные блоки", которые можно читать сверху вниз.

2. Возможность повторного использования: CTE можно ссылаться несколько раз в основном запросе, в то время как подзапрос придется дублировать. PostgreSQL может иногда оптимизировать дублирующие подзапросы, но это скорей редкие ситуации и дублирование кода — плохая практика.

3. Рекурсивные запросы: Только CTE поддерживают рекурсию (WITH RECURSIVE), что незаменимо для работы с иерархическими данными.

4. Материализация: PostgreSQL может материализовать CTE (сохранить во временной таблице), что может ускорить выполнение при многократном использовании, но может и замедлить, если данные небольшие. При этом если CTE не материализуется, то оно встраивается в основной запрос. Для подзапросов оптимизатор всегда встраивает их в основной запрос.

5. Порядок выполнения: Если CTE материализован (явно или по решению оптимизатора), он выполняется однократно, а подзапросы, если они коррелирующие, могут выполняться многократно, что приводит к повышенной нагрузке.

CTE с операторами модификации данных ​

Одно из важных преимуществ CTE — возможность использования с INSERT, UPDATE, DELETE. Это позволяет создавать сложные модификации данных в одном запросе. Рассмотрим пример: нам нужно архивировать старые аренды и удалить их из основной таблицы.

sql
-- использование cte с insert и delete в одном запросе
with old_rentals as (
    delete from rental
    where rental_date::date < '2005-07-01'
    returning *
)
insert into rental_archive
select * from old_rentals;

Этот запрос атомарен: либо выполнятся обе операции, либо ни одна. С подзапросами такой фокус не пройдет — для подобных операций пришлось бы использовать временные таблицы или несколько отдельных запросов в транзакции.

Материализация CTE ​

В PostgreSQL вы можете явно указать, нужно ли материализовать CTE. Это может влиять на производительность, как в положительную, так и в отрицательную сторону.

Задача: Нужно получить среднее время аренды дисков пользователями, которые арендовали фильмы более 30 раз.

sql
-- без материализации (по умолчанию в postgresql с 12 версии)
explain analyze
with film_stats as not materialized (
    select film_id, 
           count(*) as rental_count,
           avg(return_date - rental_date) as avg_rental_duration
    from rental r
    join inventory i on r.inventory_id = i.inventory_id
    group by film_id
)
select f.title, fs.rental_count, fs.avg_rental_duration
from film_stats fs
join film f on fs.film_id = f.film_id
where fs.rental_count > 30;
text
Hash Join  (cost=662.66..763.30 rows=319 width=39) (actual time=4.547..4.691 rows=16 loops=1)
sql
-- с явной материализацией
explain analyze
with film_stats as materialized (
    select film_id, 
           count(*) as rental_count,
           avg(return_date - rental_date) as avg_rental_duration
    from rental r
    join inventory i on r.inventory_id = i.inventory_id
    group by film_id
)
select f.title, fs.rental_count, fs.avg_rental_duration
from film_stats fs
join film f on fs.film_id = f.film_id
where fs.rental_count > 30;
text
Hash Join  (cost=763.59..785.98 rows=319 width=39) (actual time=4.888..5.064 rows=16 loops=1)

План запроса показывает, что материализация в данном случае дала небольшую дополнительную нагрузку.

MATERIALIZED заставляет PostgreSQL сохранить результат CTE во временной таблице. Это полезно, когда CTE используется несколько раз или содержит тяжелые вычисления. Однако для маленьких CTE это может создавать лишние накладные расходы.

NOT MATERIALIZED — противоположная подсказка, которая говорит оптимизатору "встроить" CTE в основной запрос, как если бы это был подзапрос.

В итоге, когда что использовать? ​

Используйте подзапросы, когда:

  • Запрос простой и имеет не более трех уровней вложенности
  • Нужен коррелирующий подзапрос (с четким пониманием для чего и как)
  • Оптимизатор показывает лучший план выполнения для подзапроса

Используйте CTE, когда:

  • Запрос сложный и требует разбивки на логические части
  • Одни и те же вычисления используются несколько раз в запросе
  • Нужен рекурсивный запрос (иерархии, графы)
  • Выполняете сложные модификации данных (INSERT/UPDATE/DELETE с RETURNING)
  • Хотите улучшить читаемость и поддерживаемость кода

Чем новее версия PostgreSQL, тем оптимизатор стал умнее работать с обоими подходами, поэтому часто на простых конструкциях выбор сводится к вопросу читаемости и личных предпочтений. А когда вопрос стоит в "специфике", то выбираете или CTE, или подзапрос, хотя никто не запрещает в одном запросе совмещать CTE с подзапросами.

Производительность и оптимизация ​

Важно: до 12 версии PostgreSQL CTE по умолчанию материализовались (как если бы стояло MATERIALIZED), что иногда приводило к неоптимальным планам выполнения. С 12 версии поведение изменилось — CTE по умолчанию не материализуются и оптимизатор может "проталкивать" условия из основного запроса в CTE и наоборот.

sql
explain analyze
with not_all_payments as (
	select *
	from payment)
select *
from not_all_payments
where amount > 5
text
QUERY PLAN                                                                                              |
--------------------------------------------------------------------------------------------------------+
Seq Scan on payment  (cost=0.00..319.61 rows=3957 width=26) (actual time=0.013..1.219 rows=3957 loops=1)|
  Filter: (amount > '5'::numeric)                                                                       |
  Rows Removed by Filter: 12092                                                                         |
Planning Time: 0.081 ms                                                                                 |
Execution Time: 1.318 ms                                                                                |

Фильтрация по размеру платежа происходила при сканировании таблицы с платежами внутри CTE.

Советы по оптимизации:

  • Всегда смотрите план выполнения (EXPLAIN ANALYZE) для сравнения вариантов
  • Для небольших CTE используйте NOT MATERIALIZED, чтобы избежать лишних операций с диском
  • Для тяжелых вычислений, используемых многократно, используйте MATERIALIZED
  • Избегайте излишне глубоких цепочек CTE — иногда временная таблица будет лучшим выбором
  • Помните, что коррелирующие подзапросы могут выполняться N раз (по строке внешнего запроса)

Заключение ​

Подзапросы и CTE — не конкуренты, а инструменты, каждый из которых хорош в своей нише. Подзапросы отлично подходят для простых фильтраций и условий с использованием того же EXISTS. CTE же незаменимы для структурирования сложных запросов, рекурсивных операций и комплексных модификаций данных.

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