Re: subselects vs WITH in views

On Thu, Feb 21, 2013 at 4:31 AM, Seref Arikan <serefarikan(at)gmail(dot)com> wrote:
> Hi Merlin,
> So should I interpret this as: there is a potential gain from choosing
> subqueries over with WITHs ?

Well, potentially, yes. WITH is a mechanic to force iterative order
of evaluation on queries. This can be a good or bad thing naturally.
Subqueries can also do this, especially if you put them in the field
select list -- but WITH is more general. We also have an undocumented
hack that uses OFFSET 0 to force subquery evaluation. These are all
very dangerous tools because they tend to be very sensitive to data
inputs as you are bypassing database statistics effectively. The
other end of the spectrum is to use vanilla JOINs as much as possible
-- this releases the work of planning the query to the database.

Upcoming 9.3 LATERAL will remove one large class of cases where we
have to do this: joining against set returning functions with
non-constant inputs.

merlin