Re: CTE inlining

On 04/30/2017 06:28 AM, Tom Lane wrote:
> Craig Ringer <craig(dot)ringer(at)2ndquadrant(dot)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