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 have 30 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