Re: SOLVED - RE: Poor performance using CTE

On 11/21/2012 03:53 AM, Jon Nelson wrote:
> 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.
I agree, especially since that barrier isn't specified as standard, so
we're using a standard feature with a subtle quirk as a
database-specific optimisation trick. A hint, as it were, like OFFSET 0.

*(Dons asbestos underwear an dives for cover)*

My big problem with the status quo is that it breaks queries from other
databases, like MS SQL server, where CTEs are optimised. I see this
periodically on Stack Overflow, with people asking variants of "Why
does PostgreSQL take 10,000 times longer to execute this query"? (not a
literal quote).

I really want to see this formalized and made explicit with `WITH
tablename AS MATERIALIZE (SELECT)` or similar.

Right now I often can't use CTEs to clean up hard-to-read queries
because of the optimisation barrier, so I have to create a temporary
view, temporary table, or use nested subqueries in FROM instead. Ugly.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services