CTE в PostgreSQL 12: читаемость и материализация
Сравниваем обычный CTE, MATERIALIZED и NOT MATERIALIZED в PostgreSQL 12 на одном запросе.
- WITHОписывает именованный подзапрос
- СтратегияВстраивание или материализация в зависимости от запроса
- EXPLAINПоказывает выбранный план и цену решения
WITH помогает разбить длинный SQL на понятные части. Но считать каждый CTE отдельной временной таблицей уже нельзя: в PostgreSQL 12 часть таких выражений может объединяться с основным запросом. Сравним три формы записи и посмотрим, где применяется фильтр и когда материализация полезна.
Одинаковая задача в трёх формах
CREATE TABLE demo_cte (id integer PRIMARY KEY, category integer NOT NULL);
INSERT INTO demo_cte SELECT n, n % 10 FROM generate_series(1, 1000) AS n;
WITH chosen AS (SELECT * FROM demo_cte)
SELECT count(*) FROM chosen WHERE category = 3;
WITH chosen AS MATERIALIZED (SELECT * FROM demo_cte)
SELECT count(*) FROM chosen WHERE category = 3;
WITH chosen AS NOT MATERIALIZED (SELECT * FROM demo_cte)
SELECT count(*) FROM chosen WHERE category = 3;Все три запроса должны вернуть сто для созданного набора. Однако совпадение результата не обязывает сервер использовать одинаковый способ вычисления. Обычный нерекурсивный CTE без побочных эффектов, используемый один раз, может быть объединён с внешним запросом. MATERIALIZED требует отдельного вычисления, а NOT MATERIALIZED разрешает объединение и при нескольких ссылках, когда это допустимо.
Где применяется фильтр
Добавьте EXPLAIN перед каждым вариантом и сравните, где применяется фильтр. Цель опыта — найти различие структуры плана и понять область действия подсказки. На тысяче строк нельзя надёжно выбирать производственный вариант по одному времени запуска: кэш, статистика и фоновые процессы меняют картину. В статье не приводятся измерения или обещания ускорения.
Когда явная материализация полезна
Если промежуточное выражение дорого вычислять, а результат используется несколько раз, отдельное вычисление может избежать повторной работы. Если внешний запрос выбирает небольшую часть большого набора, объединение иногда позволяет применить ограничения раньше. Решение зависит от реальной формы запроса, распределения данных и функций внутри выражения.
NOT MATERIALIZED не стоит добавлять повсюду. Для рекурсивных CTE и выражений с побочными эффектами действуют другие ограничения. Сначала сравните результаты, потом планы и время на похожей нагрузке. Рядом с выводами сохраните версию PostgreSQL: после обновления сервера старое поведение плана может измениться.