MSSSQL people seem really proud of their CTEs. What is it that makes CTEs in MSSQL so great?
I wonder if that really disqualifies Postgres from the same task. Are MSSQL CTEs just the hammer for your proverbial nail? Can you use a different approach in Postgres to solve the problem by leveraging its strengths?
My day job is a lot of Redshift. We use CTEs and temp tables depending on what we need. It’s based on Postgres but not really comparable when talking about performance optimization.
Here's the last line of my post: "Not that I'm knocking PG, 'best' depends upon the problem" I did not want to get into a war with people emotionally involved in their favourite programming tool. The article said 'best'. By that measure PG was worse. Point is it doesn't matter, it's about what problem you are trying to solve and at what price. In many cases PG is best for that - do you understand? I knew people would see it as an attack on PG. It's so childish and predictable. I solve business problems, not cheerlead for a particular RDBMS.
As for what made CTEs so great in MSSQL, I just explained in my first post. Look for 'optimisation fence', and understand that a) PG has changed and b) was not a personal attack.
> I wonder if that really disqualifies Postgres from the same task.
If you have 100GB of data being materialised as a temp table under the hood instead of having the predicates being pushed down, what do you think?
> Are MSSQL CTEs just the hammer for your proverbial nail?
Jesus, they're just a tool to get a job done. As for is there a way round it, probably, it just means more work for the programmer, and more stress for the SQL optimiser which will at some point fail as complexity climbs.
> We use CTEs and temp tables depending on what we need
It was an honest question, professional to professional. I did not interpret your post as a personal attack nor did I intend my post as an attack on you.
The last time I used MSSQL in anger I was an intern and Michael Jackson was alive. It’s been a while.
My question is: “what is it about your work that makes (or made) MSSQL a better choice than Postgres or something else, specifically because of CTE differences.”
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.
You do realize you are the one getting emotional because you are clinging to an engine that consistently underperforms in benchmarks across the board and costs you a fortune right?
Hope the job security is worth being an expert in a bunch of defunkt practices in 10 years nobody will want.
> consistently underperforms in benchmarks across the board
Show me mssql vs PG speed comparison on a recent industry standard benchmark.
> and costs you a fortune right?
Are you witless enough to think I'm unaware of the wallet-raping, labyrinthine licensing of MSSQL?
> Hope the job security is worth...
Since you apparently can't understand, let me spell it out for you: I've recently installed PG to start learning it because, depending on the problem, it IS the best solution. I do hope you can stop feeling the need to protect your pet software package and undersand these are in the end just tools to solve problems.
I wonder if that really disqualifies Postgres from the same task. Are MSSQL CTEs just the hammer for your proverbial nail? Can you use a different approach in Postgres to solve the problem by leveraging its strengths?
My day job is a lot of Redshift. We use CTEs and temp tables depending on what we need. It’s based on Postgres but not really comparable when talking about performance optimization.