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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
