From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
cc: Merlin Moncure <mmoncure@gmail.com>,
	Peter Geoghegan <peter@2ndquadrant.com>,
	Andrew Dunstan <andrew@dunslane.net>, Craig Ringer <craig@2ndquadrant.com>,
	David Greco <David_Greco@harte-hanks.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: SOLVED - RE: Poor performance using CTE
In-reply-to: 
 <CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
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>
Comments: In-reply-to Jon Nelson <jnelson+pgsql@jamponi.net>
	message dated "Tue, 20 Nov 2012 13:53:30 -0600"
Date: Tue, 20 Nov 2012 16:26:49 -0500
Message-ID: <15381.1353446809@sss.pgh.pa.us>

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> ... 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).

FWIW, it has nothing to do with materialization; it means that we don't
push conditions down into that subquery, nor pull subexpressions up out
of it, nor rearrange join order across the subquery boundary.  In short
the subquery is planned separately from the outer query.  But it could
then be run by the executor in the usual tuple-at-a-time fashion,
without materializing the whole subquery result.

It is true that CTEScan nodes materialize the subquery output (ie copy
it into a tuplestore), but that's to support multiple CTEScans reading
the same CTE.  One of the optimizations we *should* put in place
sometime is skipping the tuplestore if there's only one CTEScan on the
CTE.

			regards, tom lane