Важные изменения в работе CTE в PostgreSQL 12
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-подзапрос по умолчанию будет инлайниться, если его результат используется один раз. В противном случае будет как раньше материализовываться.
