Почему в PostgreSQL возникают проблемы с оптимизацией при использовании CTE?

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

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

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

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

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