One thing to keep in mind: CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs. So CTEs have to always be resolved first.
If you later join that CTE against some other table, eliminating most of the rows fetched by the CTE, you will have wasted all the time for retrieving these rows.
This means that you might get a worse plan by using a CTE instead of a direct join.
It also means that you might convince the system to give you a much better plan by using a CTE (if you can limit the total amount of rows by the rows in the CTE instead of the tables you join it against).
Just keep that in mind when you're debugging query speed issues, but in general, CTEs are good for very readable queries that are much easier to understand and maintain than if you join inline.
The additional readability is for sure worth it to first try to solve the issue with a CTE and only if you run into above problem and if the performance is inacceptable to then convert the query to a more traditional (in a sense of "what the various open source RDBMs provided until two years ago when Postgres introduced CTEs") join.
CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs.
Wait, no. I know for a fact that SQL Server will happily optimize across CTEs. They are effectively syntactic sugar, and handled much like a view or derived table.
So, either the SQL standard defines this optimization boundary and SQL Server ignores it, or this is an idiosyncrasy of the Postgres implementation, or this is an idiosyncrasy of some other implementation that you have assumed applies to all implementations.
I honestly don't know. I wouldn't mind a syntactic construct that did define optimization boundaries, but to my mind CTEs are not that construct.
Unfortunately I haven't read the SQL standard(s) and I also seem to have trouble finding them in full on the internet.
However I was told on IRC in #postgres that not optimising across CTEs was something mandated by the standard.
Of course I might have been told something that's not quite correct or I might have misunderstood.
However in the context of postgres (which is what the article is talking about), I know for a fact that CTEs are first fully resolved. This is what I'm seeing in my query plans and it's what the manual says: http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)
If I'm wrong what the standard is concerned, then I apologise.
Yes, as far as the standard is concerned you are wrong. See http://dba.stackexchange.com/questions/27425/is-the-optimisa.... But it is a very common misconception you can hardly be faulted for because it is the way postgres does it and many people claims it is due to the sql standard.
Of the only annoying features in postgres, imho. You have a large query and you try to break it up into smaller self-contained cte parts and voila, performance goes crap.
As per discussons on the email lists, PostgreSQL does it this way because this is the safest way to adhere to the standard. Presumably over time cross-CTE optimizations will be added. The problem though is guaranteeing the stability the standard requires without doing so. That's a technical issue, not a standards issue though and you are quite right for pointing that out.
"Of course I might have been told something that's not quite correct or I might have misunderstood."
I think it was a partial truth. In many cases, you can't optimize because you need to produce the same results as if you had evaluated the CTE in full exactly once.
CTEs are also a convenient place to have an optimization fence, which are sometimes useful. Yes, this conflates the logical and the physical, but that's the way it is in postgres.
Yes, it's quite apparent in practice that WHERE constraints are not being propagated back into CTEs in cases where they clearly could be, so that's something to look out for.
"CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs."
No. You can optimize them however you want and still comply with the SQL standard; you just can't produce different results.
Different results are really only a problem when the CTE is non-deterministic or has side effects. If you know that the CTE is deterministic and has no side effects, you can just treat it like a macro.
> If you know that the CTE is deterministic and has no side effects, you can just treat it like a macro.
With PostgreSQL you can't really assume this. Consider this:
CREATE FUNCTION stable_random() returns double language sql as
$$ select random() $$ stable;
Now what I have done is wrapped a volatile function in a stable function. Now the planner thinks it is deterministic and will fold it into the plan in a different way. I could further mark it as immutable and allow the planner to run it once per query even if it occurs multiple times. Now: select my_random() will be treated as fully deterministic, so select my_random() a from my_table will return a list of the same singular random number (all values of a will be the same).
So how is the planner to know this? Obviously we could just let the planner decide based on function markers but then you'd run into the case where a stable function above would produce different results, while a volatile or an immutable one would produce the same result. That seems to make very little sense to me.....
I may have gotten folding wrong. The question is whether the subquery gets run once at execution time and folded in, or whether it gets run on every row. I will have to double check the specific behavior but it's a query folding issue not a plan vs execution time issue.
As I think about it I may have gotten it wrong. It might need to be in a subquery to fold in that way.
It's also worth noting that in systems that don't optimise across CTE boundaries (I believe Postgres is one of these), they usually do still optimise across view boundaries. CREATE TEMPORARY VIEW is your friend
... that seems thoroughly backwards to me, at least in terms of expectations. I mean, I know that views that were optimization boundaries wouldn't be useful, but just from an intuitive perspective I'd expect that the stand-alone database objects would be optimization boundaries while the query-supporting statements would be optimized as part of the query.
CTEs are defined as being optimisation boundaries and databases are not allowed to optimise across CTEs.
What sort of brain-dead language design would conflate a performance construct with a maintainability construct? Even C isn't that bad (though it sure comes close).
I, on the other hand, am perfectly capable of believing that SQL is a pretty terrible language that merely persists because every attempt to replace it has been even worse because they threw out the baby with the bath-water.
A data-storage language based on relational theory is a great idea. We should make a new one at some point.
Date and Darwen's rants were exactly what I had in mind when I posted that. Although I didn't know an actual implementation of D existed in the form of D4, which is interesting.
I wonder whether it would be possible to write a D interpreter as a front-end for an existing database? If everyone with a PostgreSQL installation suddenly found that Tutorial D was only a 'yum install' away, it might catch on.
I mean, strictly speaking, that would make PostgreSQL a noSQL database ...
Given that the language is different, you'd probably need a custom background listener and its own parser (and youd probably want to listen on a different port), but yes.
Someone already wrote a custom background listener that pretends to be mongodb, so I guess PostgreSQL is web-scale now ;-)
If you later join that CTE against some other table, eliminating most of the rows fetched by the CTE, you will have wasted all the time for retrieving these rows.
This means that you might get a worse plan by using a CTE instead of a direct join.
It also means that you might convince the system to give you a much better plan by using a CTE (if you can limit the total amount of rows by the rows in the CTE instead of the tables you join it against).
Just keep that in mind when you're debugging query speed issues, but in general, CTEs are good for very readable queries that are much easier to understand and maintain than if you join inline.
The additional readability is for sure worth it to first try to solve the issue with a CTE and only if you run into above problem and if the performance is inacceptable to then convert the query to a more traditional (in a sense of "what the various open source RDBMs provided until two years ago when Postgres introduced CTEs") join.