>>>>> "David" == David Fetter <david@fetter.org> writes:
>> Consider the difference between (in the absence of CTE inlining):
>>
>> -- inline subquery with no optimization barrier (qual may be pushed down)
>> select * from (select x from y) s where x=1;
David> ...and doesn't need to materialize all of y,
>> -- inline subquery with optimization barrier (qual not pushed down)
>> select * from (select x from y offset 0) s where x=1;
>>
>> -- CTE with materialization
>> with s as (select x from y) select * from s where x=1;
David> while both of these do.
The non-CTE one has to _evaluate_ the whole of the "s" subquery, but it
doesn't have to actually store the result, whereas the CTE version needs
to put it all in a tuplestore and read it back.
--
Andrew (irc:RhodiumToad)