Антипаттерны CTE в PostgreSQL для баз 1С

После обновления PostgreSQL знакомый запрос с WITH внезапно получает другой план. В версии 11 и старше планировщик материализовал каждый CTE. Начиная с версии 12 простой CTE можно встроить в основной запрос. Изменение подробно описано в техническом разборе MonPG.

Администратор замечает результат без EXPLAIN: вчера отчёт 1С открывался, сегодня приходится ждать. В плане появился CTE Scan. PostgreSQL читает крупную таблицу, сохраняет промежуточный набор и лишь затем отбрасывает строки по внешнему фильтру.

Задача — найти эту границу в плане и убрать её без полной переделки запроса. Где-то поможет NOT MATERIALIZED, где-то — подзапрос. Но бывает и обратная ситуация: сохранённый результат избавляет PostgreSQL от повторных вычислений. Тогда стоит проверить MATERIALIZED или временную таблицу.

До PostgreSQL 12 CTE всегда отделён от основного запроса

Один и тот же SQL в PostgreSQL 11 и 12 может получить разные планы. Поэтому диагностику начинайте с версии сервера:

SELECT version();

В PostgreSQL 11 и старше CTE выполняется отдельным этапом. Внешний фильтр не проходит внутрь, даже если у исходной таблицы есть подходящий индекс. Эту особенность описывают MonPG и Bitsfolio.

В PostgreSQL 12 правило изменилось. Планировщик обычно встраивает нерекурсивный CTE без побочных эффектов, когда запрос обращается к нему один раз. Тогда же появились ключевые слова MATERIALIZED и NOT MATERIALIZED.

Само слово WITH ещё ничего не говорит о плане. Поведение зависит от версии PostgreSQL, свойств CTE и числа обращений к нему.

Внешний фильтр не должен ждать полной материализации

Запрос с неудачной материализацией выглядит безобидно:

WITH document_rows AS (
 SELECT
 id,
 period,
 organization_id,
 amount
 FROM accounting_rows
)
SELECT
 id,
 amount
FROM document_rows
WHERE organization_id = 42
 AND period >= DATE '2026-08-01';

Допустим, accounting_rows хранит записи за несколько лет. Отчёту нужны один месяц и одна организация. Если PostgreSQL материализует document_rows, он сначала соберёт весь набор. Фильтр по организации и периоду сработает позже.

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

Запрос особенно сильно страдает при сочетании трёх условий:

• CTE читает крупную таблицу;
• внешний фильтр оставляет малую часть строк;
• в исходной таблице есть индекс под этот фильтр.

Сам по себе индекс не спасёт. Пока условие находится снаружи CTE, планировщик не применяет его при первом чтении таблицы.

В PostgreSQL 12 и новее сравните исходный план с явным инлайнингом:

WITH document_rows AS NOT MATERIALIZED (
 SELECT
 id,
 period,
 organization_id,
 amount
 FROM accounting_rows
)
SELECT
 id,
 amount
FROM document_rows
WHERE organization_id = 42
 AND period >= DATE '2026-08-01';

NOT MATERIALIZED просит планировщик встроить тело CTE в родительский запрос. Условия по organization_id и period после этого могут перейти к сканированию accounting_rows.

PostgreSQL 11 этот синтаксис не понимает. Для него перепишите CTE как подзапрос в FROM:

SELECT
 document_rows.id,
 document_rows.amount
FROM (
 SELECT
 id,
 period,
 organization_id,
 amount
 FROM accounting_rows
) AS document_rows
WHERE document_rows.organization_id = 42
 AND document_rows.period >= DATE '2026-08-01';

В разборе Elysiate показано: простой CTE с одним обращением в PostgreSQL 12+ может получить тот же план, что и встроенный подзапрос. Поэтому замена WITH на скобки без проверки плана часто меняет лишь оформление SQL.

NOT MATERIALIZED вредит при повторных обращениях

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

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

WITH prepared_rows AS MATERIALIZED (
 SELECT
 organization_id,
 amount
 FROM accounting_rows
 WHERE period >= DATE '2026-08-01'
)
SELECT
 organization_id,
 SUM(amount)
FROM prepared_rows
GROUP BY organization_id

UNION ALL

SELECT
 organization_id,
 COUNT(*)
FROM prepared_rows
GROUP BY organization_id;

Здесь MATERIALIZED закрепляет отдельное вычисление и повторное чтение сохранённых данных. MonPG и Elysiate относят такой запрос к случаям, где материализация убирает лишнюю работу.

При одном обращении тот же приём добавит ненужный этап. PostgreSQL подготовит и сохранит набор, который больше никто не прочитает.

Число обращений здесь важнее размера CTE. Крупное выражение с одним чтением иногда выгодно встроить. Небольшой общий результат — сохранить.

MATERIALIZED может служить границей для планировщика

Материализация нужна не только для повторного чтения. Иногда совместная оптимизация CTE и внешнего запроса приводит к неудачному плану. MATERIALIZED отделяет часть запроса с уже проверенным планом.

Используйте этот приём только после сравнения фактических планов. Иначе закрепите границу, которая задержит фильтр и помешает раньше обратиться к индексу.

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

План покажет, где PostgreSQL тратит работу

Перед сравнением обновите статистику таблиц:

ANALYZE accounting_rows;

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

Теперь снимите фактический план:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH document_rows AS (
 SELECT
 id,
 period,
 organization_id,
 amount
 FROM accounting_rows
)
SELECT
 id,
 amount
FROM document_rows
WHERE organization_id = 42
 AND period >= DATE '2026-08-01';

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

Одного названия узла мало. Смотрите, где сработал фильтр, сколько раз выполнилось поддерево и какую работу PostgreSQL проделал до сокращения набора.

Исправлять нужно не слово WITH. Ищите лишнее чтение, поздний фильтр или повторное вычисление.

Снимайте планы для одной формы запроса и с одинаковыми параметрами. Иначе вы сравните разные нагрузки, а разницу ошибочно припишете CTE.

Перед опытами на продуктивной базе проверьте резервное копирование PostgreSQL. Если дело уже не в запросе, а в выборе серверной СУБД, пригодится сравнение PostgreSQL и SQL Server для 1С.

Когда вместо CTE нужна временная таблица

CTE существует лишь внутри одного SQL-запроса. Временная таблица живёт до конца сессии. Для неё можно создать индексы, а PostgreSQL соберёт статистику. Эти различия приводит SQLQuest.

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

Цена такого выбора — отдельное создание, заполнение и обслуживание таблицы. Ради одного простого чтения код станет длиннее, но запрос от этого не выиграет. Сначала сравните CTE и подзапрос.

Временная таблица решает конкретную задачу: переиспользование данных между запросами. Универсальной заменой WITH она не станет.

Правило выбора за один проход по плану

Сначала узнайте версию PostgreSQL. В версии 11 и старше считайте каждый CTE границей оптимизации. Если внешний фильтр должен пройти внутрь, сравните исходный запрос с подзапросом.

В PostgreSQL 12+ посчитайте обращения к CTE. При одном обращении, чтении без побочных эффектов и строгом внешнем фильтре сравните обычный CTE с NOT MATERIALIZED. Если дорогой общий результат нужен несколько раз, снимите ещё один план с MATERIALIZED.

Рекурсивный CTE и CTE с изменением данных не пытайтесь встроить. PostgreSQL выполняет такие конструкции отдельно.

После правки снова запустите EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) и проверьте пять признаков:

1. Исчез ли лишний CTE Scan.
2. Перешёл ли фильтр к исходному сканированию.
3. Не стало ли одно поддерево выполняться несколько раз.
4. Не появился ли большой промежуточный результат.
5. Сократился ли набор операций, которые PostgreSQL выполнил до фильтрации.

CTE не тормозит запрос сам по себе. Задержка появляется, когда граница оптимизации стоит не там, где она нужна конкретному плану.