Будет ли CTE материализован, если он используется только один раз для JOIN с итоговым результатом?

Не обязательно. В PostgreSQL 12+ простой нерекурсивный CTE без побочных эффектов при одном использовании обычно встраивается в основной запрос.

Решение зависит от СУБД, версии и содержимого CTE. MATERIALIZED может принудительно выделить вычисление, NOT MATERIALIZED — разрешить встраивание там, где это допустимо.

Рекурсия, модифицирующие запросы и volatile-функции меняют ситуацию. Сам факт JOIN с итоговым результатом ничего не гарантирует. Проверяйте EXPLAIN: временное имя в WITH — логическая организация запроса, а не обещание создать отдельную физическую таблицу.

Источник: www.postgresql.org.