Re: Any optimizations to the join code in 7.1?

On Wed, 25 Apr 2001, Tom Lane wrote:

> Mike Mascari <mascarm(at)mascari(dot)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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington