Re: Any optimizations to the join code in 7.1?

On Wed, 25 Apr 2001, Tom Lane wrote:

> Mike Mascari <mascarm@mascari.com> writes:
> > I have a particular query which performs a 15-way join;
> 
> You should read 
> http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html

I was recently poring over this page myself, as I've been working w/some
larger-than-usual queries.

Two questions:

1) it appears (from my tests) that SELECT * FROM
  CREATE VIEW joined as  SELECT p.id,         p.pname,         c.cname  FROM   p  LEFT OUTER JOIN c using (id)
  gives the same answer as SELECT * FROM
  CREATE VIEW nested  SELECT p.id,         p.pname,         (select c.cname from c where c.id = p.id)  FROM   p
  However, I often am writing VIEWs that will be used by developers  in  a front-end system. Usually, this view might
have30 items in the  select clause, but the developer using it is likely to only as for  four or five items. In this
case,I often prefer the  subquery form because it appears that
 
  SELECT id, pname FROM joined
  is more complicated than
  SELECT id, pname FROM nested
  as the first has to perform the join, and the second doesn't.
  Is this actually correct?

2) The explicit-joins help suggests that manual structuring and  experimentation might help -- has anyone written (or
could anyone write) anthing about where to start in guessing what  join order might be optimal?
 


-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington