Thread: Joins and their efficiencies

Hi All,
    I was doing a query yesterday, and implemented it two ways to see if
there was a difference. The first was the more standard way of doing the
query, by specifying the tables, and then linking them with AND x.id =
y.object etc..... I ran this and there seemed to be a momentary pause of
around one to one-half seconds before the result set returned. I decided
to try the same query again, but this time using, LEFT JOIN to bring in
the extra tables I wanted, it still had a few ANDs in there but only
about half as much. I ran the query and the ResultSet returned
instantly!

Is it correct then to assume that a LEFT JOIN is more efficient than a
straight join?

Thanks.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

Hadley Willan <hadley.willan@deeperdesign.co.nz> writes:
> Is it correct then to assume that a LEFT JOIN is more efficient than a
> straight join?

Not at all.  I would imagine that the left join forced the planner to
adopt a different plan than it used otherwise (perhaps a different join
order), and that this other plan was much faster than the original,
whereas the planner's estimate had it slower (else it would have used
that plan to begin with).

It could also be that the change in the WHERE clauses had something to
do with it.

If you want to investigate further, make sure your ANALYZE stats are up
to date, then post EXPLAIN ANALYZE results for both queries.

            regards, tom lane