They have an almost-negligible performance decrease in my experience. I worked with a client who was transitioning from their MySQL DB to a (mysql-flavored) AW

They have an almost-negligible performance decrease in my experience. I worked with a client who was transitioning from their MySQL DB to a (mysql-flavored) AWS Aurora DB, as they required high concurrency. In order to handle the throughput their requirement was for the queries to run in under 1 second. After getting down to ~1.1-1.3s on average, I had to figure out how to squeeze the last bits of optimization I could get. One thing I did was rewrite all the CTEs to subqueries, and if I recall correctly, it shaved something like a few percent off the query time (~.01-.05s). I'd call it negligible as the speed increase certainly isn't worth the decrease in readability and maintainability of the query.