Re: [HACKERS] CTE inlining

On 04/30/2017 06:28 AM, Tom Lane wrote:
> Craig Ringer <craig.ringer@2ndquadrant.com> writes:
>> - as you noted, it is hard to decide when it's worth inlining vs
>> materializing for CTE terms referenced more than once.
>
> [ raised eyebrow... ]  Please explain why the answer isn't trivially
> "never".
>
> There's already a pretty large hill to climb here in the way of
> breaking peoples' expectations about CTEs being optimization
> fences.  Breaking the documented semantics about CTEs being
> single-evaluation seems to me to be an absolute non-starter.
>

I'm not sure that's a universal expectation, though. I know there are 
people who actually do rely on that intentionally, no doubt about that. 
And we'd nee to make it work for them.

But I keep running into people who face serious performance issues 
exactly because not realizing this, and using CTEs as named subqueries. 
And when I tell them "optimization fence" they react "Whaaaaaaat?"

If I had to make up some numbers, I'd say the "Whaaaaat?" group is about 
10x the group of people who intentionally rely on CTEs being 
optimization fences.

FWIW I don't know how to do this. There were multiple attempts at this 
in the past, none of them succeeded. But perhaps we could at least 
propagate some of the CTE features, so that the outside query can 
benefit from that (e.g. when the CTE is sorted, we could set the 
sortkeys). That wouldn't break the fence thing, but it would allow other 
stuff.

regard

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services