Sorry. When you said "really proud" and "so great", it was perhaps an emotive choice of words.
In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like
with x as (...)
update x set ...
I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work around.
Also IIRC PG has 'materialized' and 'recursive' keywords. No biggie.
So the semantics are substantially the same. Difference is, how the optimiser treats it. That means you will get the same results back but the time difference may be enormous. This explains it: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
In my previous work, the predicate pushdown (which is the posh term for the optimiser rewriting the query as the above link demonstrates) made CTEs usable. Without them, performance would have destroyed the company.
Can we get round it? Mostly, yes, but it would have been more work for us (meaning more human hours) which the DB optimiser should have saved us from.
The only unique thing CTEs bring to the party is recursion. Otherwise they are no more than a convenience, albeit a great one.
HTH
EDIT: MSSQL has a good optimiser when it works. I recently wrote a trivial CTE and had it take a minute to run, WTF? Looked at the query plan, something was very wrong there, pulled out the CTE part and put it into a temp table, ran in one second. 60x speedup. No idea why, was not impressed though.
In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like
I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work around.Also IIRC PG has 'materialized' and 'recursive' keywords. No biggie.
So the semantics are substantially the same. Difference is, how the optimiser treats it. That means you will get the same results back but the time difference may be enormous. This explains it: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
In my previous work, the predicate pushdown (which is the posh term for the optimiser rewriting the query as the above link demonstrates) made CTEs usable. Without them, performance would have destroyed the company.
Can we get round it? Mostly, yes, but it would have been more work for us (meaning more human hours) which the DB optimiser should have saved us from.
The only unique thing CTEs bring to the party is recursion. Otherwise they are no more than a convenience, albeit a great one.
HTH
EDIT: MSSQL has a good optimiser when it works. I recently wrote a trivial CTE and had it take a minute to run, WTF? Looked at the query plan, something was very wrong there, pulled out the CTE part and put it into a temp table, ran in one second. 60x speedup. No idea why, was not impressed though.