CTE в PostgreSQL упрощают код, но могут снижать производительность. Разбираем 4 антипаттерна, примеры EXPLAIN ANALYZE и практические способы оптимизации.

В прошлой статье мы упоминали основные SQL‑антипаттерны, способные замедлять работу базы данных. Продолжаем тему — на этот раз про CTE. 

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

Такие запросы на первый взгляд выглядят правильными, но работают неэффективно, и проблема вылезает только в EXPLAIN ANALYZE (инструмент разбирали в прошлом гайде). Разберём четыре антипаттерна CTE, посмотрим планы выполнения и покажем, как переписать запрос. В конце — короткий чек-лист диагностики.

Эта статья может быть полезна начинающим разработчикам и аналитикам, которые уже полюбили синтаксис CTE, но хотят понять, что на самом деле происходит «под капотом» в PostgreSQL. 

Антипаттерн № 1: CTE как «оптимизационный забор» (ловушки LIMIT, ORDER BY и агрегаций)

Почему это антипаттерн

В современных версиях PostgreSQL (начиная с 12-й) при выполнении CTE планировщик по возможности встраивает сам CTE прямо в основной запрос. Этот процесс называют инлайнингом (inlining) — он позволяет оптимизатору проталкивать фильтры из внешнего запроса внутрь CTE, что помогает эффективнее использовать индексы и уменьшать объём обрабатываемых данных.

Однако некоторые конструкции внутри CTE действуют как барьеры для проталкивания предикатов (predicate pushdown). Они не дают планировщику протолкнуть фильтры из основного запроса внутрь CTE. Планировщик вынужден сначала выполнить операции над всей таблицей и только потом применить фильтр из внешнего запроса к уже готовому результату. Индексы при этом часто оказываются бесполезными, а план выполнения становится неоптимальным. К таким конструкциям относятся ORDER BY, LIMIT, DISTINCT, GROUP BY и оконные функции.

Пример SQL-запроса

Задача: Получить количество заказов и общую сумму по группе клиентов с id от 1000 до 1100.

WITH customer_stats AS (
	SELECT customer_id,
       	COUNT(*) AS order_count,
       	SUM(amount) AS total_amount
	FROM orders
	GROUP BY customer_id
)
SELECT c.id,
   	c.name,
   	cs.order_count,
   	cs.total_amount
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id
WHERE c.id BETWEEN 1000 AND 1100;

Смотрим в EXPLAIN

Планировщик начал с таблицы orders, прочитав из индекса 22 080 строк. Индекс был просканирован лишь до customer_id = 1100. Таким образом, был обработан не весь объём таблицы (2 млн строк), а лишь её префикс. Подробнее о работе индексов можно прочитать в предыдущей статье. 

В нашем случае GROUP BY внутри CTE выступил как «оптимизационный забор»: внешний предикат не был «протолкнут» внутрь, поэтому ранняя фильтрация orders не произошла. Все 22 тысячи строк были сгруппированы в 1 100 «корзин», и только затем этот результат соединялся с отфильтрованной таблицей customers с помощью Merge Join. Итоговая стоимость запроса составила 67 551,41, а время выполнения — 34,149 мс.

Способ оптимизации

Кажется, что проблему решит модификатор NOT MATERIALIZED: он заставит планировщик протолкнуть WHERE внутрь. Но на практике это не сработает так, как мы ожидаем. NOT MATERIALIZED лишь запрещает материализацию CTE во временную таблицу, но не влияет на стратегию соединения. В плане всё равно останется Merge Join — значит, планировщику придётся прочитать и сгруппировать все строки таблицы orders. Из-за сочетания операции GROUP BY и выбранной стратегии JOIN оптимизатор не смог протолкнуть (pushdown) предикат из внешнего WHERE на этап сканирования индекса.

Поэтому самый простой способ улучшить эффективность запроса — это перенести условие фильтрации в CTE. Изменённый запрос:

WITH customer_stats AS (
	SELECT customer_id,
       	COUNT(*) AS order_count,
       	SUM(amount) AS total_amount
	FROM orders
	WHERE customer_id BETWEEN 1000 AND 1100
	GROUP BY customer_id
)
SELECT c.id,
   	c.name,
   	cs.order_count,
   	cs.total_amount
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id;

В EXPLAIN ANALYZE видно, что планировщик заметил фильтр внутри CTE и применил его уже на этапе сканирования индекса. Благодаря Index Cond из таблицы orders было прочитано всего 2 092 строки — примерно в 10 раз меньше, чем в исходном варианте. Агрегация прошла быстро (примерно 0,4 мс), так как объём данных стал небольшим, и получилась 101 группа — ровно столько, сколько нужно. Поскольку результат CTE оказался небольшим, планировщик выбрал Nested Loop вместо Merge Join. Это позволило обратиться к таблице customers по первичному ключу. В итоге стоимость запроса снизилась до 3 643,68, а время выполнения — до 3,720 мс. Мы отсекли лишние данные на самом раннем этапе, поэтому разница с исходным запросом получилась многократной.

Выводы

Сочетание LIMIT, ORDER BY или GROUP BY внутри CTE с внешним WHERE — классическая ловушка производительности. Оптимизатор не может «протолкнуть» фильтр через эту границу, что вынуждает базу данных извлекать избыточные данные. Старайтесь сузить выборку фильтрами как можно раньше — до GROUP BY, ORDER BY и LIMIT. Перенос WHERE внутрь CTE до агрегации — не микрооптимизация: в нашем примере он ускорил запрос почти в десять раз. 

Антипаттерн № 2: Рекурсивные CTE без необходимости (или для больших деревьев)

Почему это антипаттерн

Рекурсивные CTE (WITH RECURSIVE) удобны для обхода иерархий, но могут обходиться дорого.

Дело в алгоритме. На каждом шаге PostgreSQL берёт текущий набор строк из внутренней временной таблицы (WorkTable) и соединяет его с основной таблицей, чтобы найти следующий уровень. Если в основной таблице нет индекса по колонке связи (например, parent_id), база просканирует таблицу целиком на каждом уровне рекурсии. Если узлов обхода много, то сама WorkTable быстро разрастается и может сбрасываться на диск (spill to disk), что снижает производительность.

Пример SQL-запроса

Задача: Подсчитать количество элементов иерархии в поддереве корневого элемента с id = 1, включая сам корневой элемент и все вложенные уровни.

WITH RECURSIVE item_tree AS (
	SELECT id
	FROM items
	WHERE id = 1


	UNION ALL


	SELECT i.id
	FROM items i
	JOIN item_tree it ON i.parent_id = it.id
)
SELECT COUNT(*)
FROM item_tree;

Смотрим в EXPLAIN

Наличие строк Recursive Union и WorkTable Scan подтверждает использование рекурсии. Проблема бросается в глаза — количество циклов «loops=100000». PostgreSQL не ищет подкатегории пакетами. Он берет каждый найденный на предыдущем шаге id и и отдельно идёт с ним в B-Tree-индекс по parent_id. В нашем случае — 100 000 отдельных обращений. Именно поэтому метрика shared hit (количество страниц, найденных в общем буферном кэше) доходит до 250 тысяч. База данных тратит существенные ресурсы на процессорные циклы и контекстные переключения.

Способ оптимизации

Если дерево большое, а потомков узла приходится искать часто — посмотрите в сторону расширения ltree. Расширение материализует путь к узлу в самих данных, позволяя находить всё поддерево за один проход по GiST-индексу. Однако у этого решения есть недостатки: дорогие массовые обновления при перемещении узлов, рост и обслуживание индексов, нюансы с нормализацией и чувствительностью к регистру. Подробнее о ltree — в документации PostgreSQL и на Habr.

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

SELECT COUNT(*) AS total_descendants
FROM items
WHERE path <@ (
	SELECT path
	FROM items
	WHERE id = 1
);

В плане выполнения мы видим Bitmap Index Scan вместо рекурсии. GiST-индекс за один проход находит все 100 000 потомков. Вместо 250 тысяч обращений к буферам запрос делает 4 707. Время выполнения падает с 212 мс до 38 мс, и в разы снижается нагрузка на подсистему памяти и ввода-вывода (I/O), что критически важно для высоконагруженных систем.

Выводы

Рекурсивный CTE — это скорее инструмент для одноразовых аналитических запросов, когда дерево неглубокое (<10 уровней) и небольшое (<10 тыс. узлов). Если в вашем случае требуется часто (на каждый запрос пользователя) искать всех потомков, подсчитывать их или выполнять иные операции над поддеревом, рекурсивный CTE может стать «узким местом». Тогда посмотрите на расширение ltree, денормализацию (Materialized Path) или Closure Table (таблицу замыканий) — но заранее прикиньте, как это скажется на обновлениях.

Антипаттерн № 3: Множественное сканирование одной таблицы через разные CTE

Почему это антипаттерн

Когда вы используете несколько CTE или многократно обращаетесь к одному и тому же CTE, легко не заметить повторное обращение. Плюс работает интуиция: раз в запросе несколько раз фигурирует одна и та же таблица, база наверняка достаточно умна, чтобы прочитать её один раз. Однако в реальности база данных выполняет независимые сканирования и вычисления для каждого обращения. На малых объёмах это незаметно, на больших — скрытые повторные проходы заметно нагружают систему.

Пример SQL-запроса

Задача: выбрать категорию клиентов с id в диапазоне 1–10000 и для каждого клиента собрать суммы по срезам заказов: завершённые заказы за последний год (status = 'completed' и created_at за последний год) и крупные заказы (amount > 500).

WITH orders_last_year AS (
	SELECT customer_id, amount
	FROM orders
	WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'
),
orders_completed AS (
	SELECT customer_id, amount
	FROM orders
	WHERE status = 'completed'
),
orders_high_value AS (
	SELECT customer_id, amount
	FROM orders
	WHERE amount > 500
)
SELECT c.id,
   	COALESCE(o1.o1_sum, 0)
   	+ COALESCE(o2.o2_sum, 0)
   	+ COALESCE(o3.o3_sum, 0) AS total
FROM customers c
LEFT JOIN (
	SELECT customer_id, SUM(amount) AS o1_sum
	FROM orders_last_year
	GROUP BY customer_id
) o1 ON o1.customer_id = c.id
LEFT JOIN (
	SELECT customer_id, SUM(amount) AS o2_sum
	FROM orders_completed
	GROUP BY customer_id
) o2 ON o2.customer_id = c.id
LEFT JOIN (
	SELECT customer_id, SUM(amount) AS o3_sum
	FROM orders_high_value
	GROUP BY customer_id
) o3 ON o3.customer_id = c.id
WHERE c.id BETWEEN 1 AND 10000;

Смотрим в EXPLAIN

В плане выполнения сразу бросается в глаза, что СУБД выполнила три независимых сканирования таблицы orders. Это привело к многократному росту операций ввода-вывода. В нашем случае общее число страниц, найденных в буферном кэше (shared hit), превысило 400 тысяч.

Вторая критическая проблема — нехватка памяти. Выделенной памяти PostgreSQL для операций хeширования (work_mem 8 МБ) не хватило, чтобы обработать три параллельные агрегации в оперативной памяти. База данных была вынуждена сбрасывать промежуточные результаты на диск (spill to disk). Работа с диском, даже при наличии быстрых SSD, в разы медленнее работы в RAM. Именно эти постоянные переключения между памятью и диском, а также дублирование чтений, стали главной причиной замедления. Итог: время выполнения составило 649 мс, стоимость плана — 176 647.

Способ оптимизации

Попробуем объединить агрегации в один подзапрос: все вычисления пройдут за один проход по таблице orders, число сканирований сократится, а риск spill to disk снизится. Наш оптимизированный запрос будет выглядеть следующим образом:

SELECT c.id,
   	COALESCE(s.last_year_sum, 0)
   	+ COALESCE(s.completed_sum, 0)
   	+ COALESCE(s.high_value_sum, 0) AS total
FROM customers c
LEFT JOIN (
	SELECT customer_id,
       	SUM(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '1 year'
                	THEN amount ELSE 0 END) AS last_year_sum,
       	SUM(CASE WHEN status = 'completed'
                	THEN amount ELSE 0 END) AS completed_sum,
       	SUM(CASE WHEN amount > 500
                	THEN amount ELSE 0 END) AS high_value_sum
	FROM orders
	WHERE customer_id BETWEEN 1 AND 10000
	GROUP BY customer_id
) s ON s.customer_id = c.id
WHERE c.id BETWEEN 1 AND 10000;

В отчете EXPLAIN видим один проход по orders с условной агрегацией и фильтром по customer_id. Это сократило количество обращений к буферному кэшу (shared hit) с ~416 тыс. до ~200 тыс., устранило запись временных файлов (temp written исчез), и снизило время выполнения с 649 мс. до 307 мс. Стоимость запроса также снизилась с 176 647 до 29 560. Все ключевые показатели улучшились в разы.

Выводы

Множественные CTE с разными фильтрами на одну таблицу — это скрытое многократное сканирование. Избегайте множественных проходов по данным: объединяйте логику в одном шаге с помощью условной агрегации. Если данных действительно много, а результаты вычислений переиспользуются, материализуйте их в TEMP TABLE.

Антипаттерн № 4: Избыточное использование CTE на больших данных (Big Data)

Почему это антипаттерн

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

Пример SQL-запроса

Задача: собрать суммы заказов и транзакций по каждому клиенту за год и вывести топ-100.

WITH yearly_orders AS (
	SELECT customer_id, SUM(amount) AS order_sum
	FROM orders
	WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'
	GROUP BY customer_id
),
yearly_trans AS (
	SELECT user_id AS customer_id, SUM(amount) AS trans_sum
	FROM transactions
	WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'
	GROUP BY user_id
),
combined AS (
	SELECT COALESCE(o.customer_id, t.customer_id) AS customer_id,
       	COALESCE(o.order_sum, 0) AS order_sum,
       	COALESCE(t.trans_sum, 0) AS trans_sum
	FROM yearly_orders o
	FULL JOIN yearly_trans t ON o.customer_id = t.customer_id
)
SELECT customer_id,
   	(order_sum + trans_sum) AS total_volume
FROM combined
ORDER BY total_volume DESC
LIMIT 100;

Смотрим в EXPLAIN

Сразу обращает на себя внимание ресурсоёмкость операции объединения данных Hash Full Join. Она имеет стоимость (cost) 228 835 и выполняется примерно за две секунды. При этом итоговая стоимость всего запроса в узле Limit оценивается в 1,9 млн. В ходе объединения Hash Full Join обработал примерно по 100 000 строк из таблиц yearly_orders и yearly_trans. Существенный разрыв между стоимостью основного узла и суммарной стоимостью плана объясняется тем, что сортировка перед LIMIT работала с сильно завышенной оценкой: планировщик ожидал порядка 45 млн строк. Именно эта ошибка в оценке и раздула стоимость плана. Показатель в 2,7 миллиона shared hit говорит о том, что все страницы, затронутые запросом, были найдены в буферном кэше, и чтения с диска не потребовалось. Однако миллионы таких обращений в сумме дают ощутимую нагрузку на CPU, а также могут вытеснить из кэша данные других запросов. Итог: время выполнения — 3,2 секунды.

Важно отметить, что в этом случае «узким местом» является операция FULL JOIN больших таблиц, а не CTE. Сам процесс соединения потребляет много ресурсов. Материализация CTE может дополнительно увеличивать нагрузку, но не служит её первопричиной.

Способ оптимизации

Один из вариантов — заменить CTE подзапросом с UNION ALL:

SELECT customer_id,
   	SUM(amount) AS total_volume
FROM (
	SELECT customer_id, amount
	FROM orders
	WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'

	UNION ALL

	SELECT user_id AS customer_id, amount
	FROM transactions
	WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'
) combined_data
GROUP BY customer_id
ORDER BY total_volume DESC
LIMIT 100;

Смотрим в EXPLAIN ANALYZE альтернативного варианта. Подзапросы с UNION ALL могут использовать параллельные воркеры. В нашем случае были использованы 2 воркера для параллельного выполнения, в то время как в исходном запросе параллельное выполнение не использовалось, и запрос выполнялся одним процессом. Можно заметить ключевое различие в алгоритме планировщика — отказ от тяжелого Hash Full Join в пользу единой агрегации. Вместо двух отдельных группировок и последующего соединения, данные агрегируются один раз. Однако у такого подхода есть свой компромисс: мы объединяем «сырые» данные до агрегации. Из-за большого объема этого промежуточного потока мы получили spill to disk (work_mem 8 МБ) при сортировке и группировке, что добавило операций ввода-вывода. Несмотря на это, итоговое время выполнения сократилось с 3,2 до 0,89 секунды, а стоимость плана упала с 1 972 053 до 61 225.

Выводы

Если ваш пайплайн из CTE сводится к тому, что вы формируете агрегированные наборы объемом в миллионы строк, а потом соединяете их через JOIN, вы можете создать искусственное «узкое место». Рассмотрите альтернативы: UNION ALL с единой агрегацией, материализованные представления (MATERIALIZED VIEW) или временные таблицы (TEMP TABLE). При этом всегда используйте EXPLAIN: бывает, что причиной низкой производительности является не сам факт использования CTE, а дорогое соединение, отсутствие индексов или нехватка памяти (work_mem).

Как диагностировать проблемы с CTE

Если CTE-запрос выполняется дольше ожидаемого, начните с плана выполнения. Используйте EXPLAIN (ANALYZE, BUFFERS). На то, что CTE стал «узким местом», могут указывать следующие признаки:

• Диспропорция времени. Узел CTE или его внутреннее сканирование занимает 80–90% времени выполнения (Execution Time). Это явный индикатор узкого места.

• Повторное чтение одной и той же таблицы. Наличие нескольких узлов сканирования (Seq Scan или Index Scan) одной таблицы в разных частях плана свидетельствует о проблемах с оптимизацией. Чаще всего это дублирование логики в CTE или фильтры, которые не удалось объединить в один проход.

• Большое количество Rows Removed by Filter. Если CTE возвращает миллионы строк, а внешний фильтр отбрасывает почти всё, значит, предикаты (условие WHERE) не были «протолкнуты» внутрь CTE и база зря читала и передавала большие объёмы ненужных данных.

• Temp Written больше нуля. Параметр Temp Written у узлов Materialize, HashAggregate или Sort означает сброс на диск из-за недостатка памяти (work_mem). Запись на диск резко снижает производительность.

• Большой WorkTable (>50 тыс. строк) при рекурсивных CTE. Если в рекурсивном CTE на последних итерациях WorkTable содержит десятки или сотни тысяч строк, то такой запрос плохо масштабируется и возникает риск spill to disk или бесконечных итераций.

• Многократное сканирование (СTE Scan c loops > 1). Это означает, что СTE выполнен несколько раз. Обычно это происходит в Nested Loop Join, когда планировщик недооценил количество строк из внешней таблицы. Проблема усугубляется при «дорогостоящих» CTE со сложными вычислениями и большими данными.

• Тяжёлые внешние операции после CTE. Дорогие Hash Join, Sort или HashAggregate с высоким actual time после узла CTE означают, что CTE вернул слишком большой или неупорядоченный набор данных, с которым теперь тяжело работать основной части запроса. Признак неэффективного запроса.

Если анализ плана (EXPLAIN) показывает неоптимальную работу с CTE — например, появляется узел CTE Scan с записью на диск (Temp Written > 0) при однократном использовании или, наоборот, CTE не материализуется, хотя используется многократно, — попробуйте следующие подходы (PostgreSQL 12+):

• NOT MATERIALIZED: если CTE используется однократно, а план показывает его материализацию и запись на диск, добавьте этот модификатор. Так можно протолкнуть предикаты (WHERE) и LIMIT из внешнего запроса внутрь, но только если внутри него нет барьеров вроде GROUP BY.

• MATERIALIZED: если CTE используется несколько раз (и вы видите повторные тяжелые сканирования одних и тех же таблиц) или если внутри CTE есть ресурсоемкие оконные функции / агрегации, результаты которых нельзя дублировать.

Стоит иметь в виду: сами модификаторы лишь управляют границей материализации. Решение о том, как именно строить план внутри или снаружи CTE, планировщик по-прежнему принимает на основе оценки стоимости. Всегда подтверждайте их эффект через EXPLAIN ANALYZE. Подробнее об особенностях модификаторов — в официальной документации PostgreSQL.

Если вы только начинаете улучшать производительность СУБД и не знаете, как настроить мониторинг проблемных запросов, ознакомьтесь с предыдущей статьёй: «Как найти медленный запрос в PostgreSQL: три инструмента мониторинга» — в ней подробно разбираются pg_stat_statements, auto_explain и log_min_duration_statement, а также приведены примеры настройки логирования медленных запросов.

Что запомнить

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

Если вы столкнулись с низкой производительностью CTE, проверьте вывод EXPLAIN (ANALYZE, BUFFERS) по чек-листу: он поможет локализовать причину, будь то один из разобранных антипаттернов или иная неоптимальность. Если проблема подтверждена, попробуйте переписать запрос. Эффект оценивайте на реальных объёмах данных и с учётом доступных ресурсов (CPU, RAM, дисковый I/O). Так оптимизация станет понятным циклом: диагностика → гипотеза → проверка. И каждая правка будет обоснованной. 

Автор текста Макаренков Вячеслав

НЛО прилетело и оставило здесь промокод для читателей нашего блога:
-15% на заказ нового VDS — HABRFIRSTVDS.

Положение об акции