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