I see frequent references to
WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common expressions up out of the CTE, etc. It's often claimed to be a behaviour required by the SQL standards.
CTEs are definitely an optimisation fence in PostgreSQL ... but is this required by the standard, or actually just implementation detail?
For example, these mailing list posts claim or suggest that it's standard:
After mentioning it in a comment I was asked where it's specified - and after a look at the only draft of SQL:2008 I have access to I'm not having much luck finding it.
I haven't yet intensively studied the standard, so I'm hoping for a suggestion from somebody who has: Is the optimisation fencing of CTEs in PostgreSQL actually required by the standard? And if so, where's it specified? Or are the statements on the Pg mailing list in error?
See also the thread CTE optimization fence on the todo list?.