Re: Common Table Expressions applied; some issues remain

> Tom Lane wrote:
>> that Oracle chooses to treat WITH-queries as if they were plain
>> sub-selects if they're non-recursive and only referenced once.
>> That is, Oracle would rewrite the above into
>>
>> SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>>
>> and then flatten the sub-select and optimize normally. It would
>> not be hard to make Postgres do the same, but then we would lose
>> some guarantees about predictable execution of volatile functions.
>>
>> I'm inclined to think that there is no reason to provide two
>> different syntaxes to do the same thing, and so having the WITH
>> syntax behave like this is okay. But it could well result in
>> performance surprises for people who are used to Oracle.
>>
>> Any thoughts on what to do? One possibility is to flatten only
>> if the subquery doesn't contain any volatile functions.

I think we should always inline the view if there's a single call site. If
people want to control the subsequent flattening they can do it the same way
they can do today for inline views using OFFSET 0.

The question in my mind is if we can do better for CTEs with multiple call
sites. If we have no volatile function calls in them then we should be free to
inline some or all call sites. I'm not sure we have enough information early
enough to make the decision though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!