Re: subselects vs WITH in views

On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
>>
>> Joe Van Dyk wrote:
>> > My assumption was that WITH acted just like subselects, but apparently
>> > they don't? Using WITH doesn't
>> > use the expected index.
>>
>> Currently WITH acts as an "optimization fence", that means
>> that means that the planner won't move conditions into or
>> out of the WITH query.
>
>
> Where's the best place to read up on this?

Unfortunately, the mailing list archives. Rightly or wrongly,
postgresql docs are exceptionally light in terms of performance
aspects of various SQL mechanisms.

(non-data modifying) WITH is basically formalization of technique: A
extract to temp table B query that table. Not the optimization fence
characteristic is an implementation detail and not future proofed but
is nevertheless widely replied upon.

merlin