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@xxxxxxxxx> wrote: > On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan <peter@xxxxxxxxxxxxxxx> wrote: >> On 15 November 2012 01:46, Andrew Dunstan <andrew@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
