Which versions of Postgres have you been running your large CTE based queries with?

Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes.

OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.