Am I incorrect in thinking that "standard" CTEs don't have an impact on performance. When I saw this in the article and how it had no performance improvements it reaffirmed my assumption.
That SE answer is about MS SQL Server.
What I wrote was specific to PostgreSQL which does a specific weird thing using CTEs where they are materialized.
In SQL Server, CTEs are not materialized and are just used to organize the query, to make it more readable.
If I wrote about PostgreSQL heap organized tables, would you object and say I'm wrong because MS SQL Server tables are index-organized, clustered on the primary key?
Practically speaking, though, CTEs can be used to organize code, and query plan generators/optimizers may take that into account. Or, as someone else added, PostgreSQL will materialize the CTE as if it were a temporary table being used as an input.
Almost all RDBMS advice is implementation specific, sadly.
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.