Почему нельзя заменить WITH (CTE) на подзапрос в SQL

Что проверяют

Отличаете ли вы синтаксис от плана выполнения. Вопрос снова с подвохом: в большинстве случаев CTE и подзапрос взаимозаменяемы, и ценность ответа в том, чтобы назвать ровно те случаи, где замены нет.

Как отвечать

Сначала перечислите, где подзапрос действительно не подходит:

• рекурсия. WITH RECURSIVE для обхода дерева или графа подзапросом не выражается вовсе;
• изменяющие данные операторы внутри запроса. Конструкция вида WITH ins AS (INSERT ... RETURNING id) позволяет вставить в две таблицы одним оператором, подзапрос так не умеет;
• повторное использование. Ссылка на материализованный CTE из нескольких мест считается один раз, а продублированный подзапрос будет посчитан столько раз, сколько его написали;
• барьер оптимизатора. Иногда расчёт нужно выполнить ровно однажды или запретить проталкивание условия внутрь, и материализация это единственный способ.

Дальше про план. В современных версиях PostgreSQL нерекурсивный CTE без побочных эффектов, использованный один раз, встраивается в запрос, и разницы с подзапросом не остаётся, раньше он всегда был непроницаемой границей. Управляют этим слова MATERIALIZED и NOT MATERIALIZED, а проверяют EXPLAIN с ANALYZE и BUFFERS. Из сервиса на Go это тем более важно: запрос уезжает строкой, и увидеть его стоимость можно только через план.

Где спотыкаются

Заявляют, что CTE всегда быстрее или всегда медленнее подзапроса. Скорость определяет план конкретной СУБД конкретной версии, а не выбранное ключевое слово.

Не знают про обратную сторону материализации. Она спасает от повторного расчёта, но мешает протолкнуть условие внутрь: вместо чтения по индексу СУБД честно считает весь CTE, а фильтр применяет уже снаружи.

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