Message-ID: <50AC135F.8030508@2ndQuadrant.com>
Date: Wed, 21 Nov 2012 07:33:51 +0800
From: Craig Ringer <craig@2ndQuadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121029 Thunderbird/16.0.2
MIME-Version: 1.0
To: Jon Nelson <jnelson+pgsql@jamponi.net>
CC: Merlin Moncure <mmoncure@gmail.com>,
	Peter Geoghegan <peter@2ndquadrant.com>,
	Andrew Dunstan <andrew@dunslane.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	David Greco <David_Greco@harte-hanks.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: SOLVED - RE: Poor performance using CTE
References: 
 <187F6C10D2931A4386EE8E58E13857F61291D897@BY2PRD0811MB415.namprd08.prod.outlook.com>
	<20462.1352910543@sss.pgh.pa.us>
	<50A442A8.10803@2ndQuadrant.com> <50A4497D.80902@dunslane.net>
	<CAEYLb_XLgrFBMM5Ce9ZBoa0K+NY1hpFXyvXeaf6yWbHOjn3-3g@mail.gmail.com>
	<CAHyXU0yPcfWQ58yU07vhit2PQ70kcxsDOr7QumZgvnKT=fcC-Q@mail.gmail.com>
	<CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
In-Reply-To: 
 <CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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