Проблемы с оптимизацией при использовании CTE в PostgreSQL могут возникать из-за материализации внутреннего выражения внутри конструкции WITH… AS. 1 Его вычисляют перед началом работы с остальной частью запроса, и планирование происходит без учёта остальной части. 1 Такое поведение называют барьером для оптимизации. 1

Кроме того, материализация требует ресурсов, и если выборка большая, то это может привести к проблемам. 1

До версии PostgreSQL 12 по умолчанию была материализация, но в новой версии по умолчанию её нет, и оптимизатор сразу видит весь запрос. 1

Чтобы оптимизировать запросы с CTE, можно попробовать следующие рекомендации:

• Установить индексы на столбцы, используемые в предикатах соединения (JOIN). 2
• Переписать запрос, чтобы в нём использовались простые подзапросы или внутренние объединения (INNER JOIN) вместо CTE. 2
• Убедиться, что статистика PostgreSQL актуальна для таблиц, используемых в запросе. 2
• Проверить план выполнения запроса с помощью команды EXPLAIN и определить, какие операции занимают больше всего времени. 2
• Увеличить объём доступной памяти для PostgreSQL, чтобы улучшить производительность запроса. 2