Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance.
[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...
[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...
Exceptions:
1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.
2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.
https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.
After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.
Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.