Ответ

Инструкция WITH (или CTE — Common Table Expression) временно создаёт результирующий набор данных, которым можно пользоваться в основном SELECT или даже рекурсивно. CTE делают запросы проще для понимания, повышают читаемость, позволяют переиспользовать промежуточные результаты.

Преимущества против обычных подзапросов:

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

Пример:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Вопрос с подвохом

“Важно ли указание MATERIALIZED/NOT MATERIALIZED при объявлении CTE? Как это может повлиять на производительность?”

Ответ: В PostgreSQL можно явно указать MATERIALIZED (всегда вычислять CTE один раз и хранить, даже если используется несколько раз) или NOT MATERIALIZED (CTE будет инлайниться в основной запрос). Обычно оптимизатор сам выбирает, стоит ли материализовывать CTE, но принудительное указание иногда существенно влияет на скорость.

Пример:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

История

В большом проекте аналитик заменил все подзапросы на CTE, не зная, что в PostgreSQL до версии 12 CTE всегда материализуются. Итог — запросы замедлились в 2-3 раза. После возврата к подзапросам или перехода на новые версии проблема ушла.

История

Отчёт с несколькими уровнями вложенных подзапросов получился нечитаемым для коллеги. Переписали на CTE с осмысленными именами — обсуждение и поддержка запросов значительно упростились.

История

Два CTE с одинаковыми названиями вызвали путаницу в большом скрипте: CTE использовался ещё до его объявления. В результате — ошибка компиляции и большая трата времени на поиск причины.

Что делает инструкция WITH (Common Table Expressions, CTE)? В чём преимущества использования CTE по сравнению с подзапросами? Приведите пример.

Ответ

Вопрос с подвохом