Re: subselects vs WITH in views

Hi Merlin,
So should I interpret this as: there is a potential gain from choosing
subqueries over with WITHs ?



On Tue, Feb 19, 2013 at 3:33 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

> On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote:
> > On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>