WITH w AS  NOT MATERIALIZED (
    SELECT * 
    FROM very_very_big_table
)
SELECT * 
FROM w AS w1 
    JOIN w AS w2 
        ON w1.key = w2.ref
WHERE w2.key = 123;

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

Это войдет в PostgreSQL 12, и это big deal. Давайте рассмотрим, почему

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

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

Впрочем, бывают ситуации, когда такое отдельное выполнение работает во благо: есть такой оптимизационный трюк, когда часть сложного запроса лучше выполнить отдельно, а постгрес самостоятельно этого не понимает. Тогда мы выносим эту часть в CTE-подзапрос.

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

Более того, изменилось дефолтное поведение. Теперь CTE-подзапрос по умолчанию будет инлайниться, если его результат используется один раз. В противном случае будет как раньше материализовываться.