Re: SOLVED - RE: Poor performance using CTE

My perspective on this is that CTEs *should* be just like creating a
temporary table and then joining to it, but without the
materialization costs. In that respect, they seem like they should be
like nifty VIEWs. If I wanted the behavior of materialization and then
join, I'd do that explicitly with temporary tables, but using CTEs as
an explicit optimization barrier feels like the explaining away
surprising behavior.

As can be seen by the current conversation, not everyone is convinced
that CTEs ought to be an explicit optimization barrier, and setting
that behavior as somehow desirable or explicit (rather than merely an
implementation detail) feels shortsighted to me. I would be delighted
to find that in some future version of PostgreSQL, but if that is not
to be, at the very least, the verbiage surrounding CTEs might want to
include (perhaps prominently) something along the lines of "CTEs are
currently an optimization barrier, but this is an implementation
detail and may change in future versions". Perhaps even including a
small blurb about what an optimization barrier even means (my
understanding is that it merely forces materialization of that part of
the query).

That's just my perspective, coming at the use of CTEs not as a
PostgreSQL developer, but as somebody who learned about CTEs and
started using them - only to discover surprising behavior.

On Tue, Nov 20, 2012 at 1:22 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
>> On 15 November 2012 01:46, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> It cuts both ways. I have used CTEs a LOT precisely because this behaviour
>>> lets me get better plans. Without that I'll be back to using the "offset 0"
>>> hack.
>>
>> Is the "OFFSET 0" hack really so bad? We've been telling people to do
>> that for years, so it's already something that we've effectively
>> committed to.
>
> IMSNHO, 'OFFSET 0' is completely unreadable black magic. I agree with
> Andrew: CTEs allow for manual composition of queries and can be the
> best tool when the planner is outsmarting itself. In the old days,
> we'd extract data to a temp table and join against that: CTE are
> essentially a formalization of that technique. I like things the way
> they are; if CTE are hurting your plan, that's an indication you're
> using them inappropriately.
>
> merlin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

--
Jon