On Wed, 25 Apr 2001, Tom Lane wrote:
> Mike Mascari <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
