Re: Help understanding indexes, explain, and optimizing

Actually I think LEFT OUTER JOIN is equivalent to LEFT JOIN. The
Postgres manual says that the word OUTER is optional. Either way you
get "...all rows in the qualified Cartesian product (i.e., all combined
rows that pass its join condition), plus one copy of each row in the
left-hand table for which there was no right-hand row that passed the
join condition."

It sounds like the original posters problem was a less than optimal join
order, and from what I understand Postgres can't reorder left joins.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Chris
Sent: Monday, March 06, 2006 6:40 PM
To: i.v.r.
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Help understanding indexes, explain, and
optimizing

i.v.r. wrote:
> Hi everyone,
[Snip]
> So I'm wondering what I'm doing wrong. I migrated this database from
> MySQL, and on there it ran pretty fast.

Have you done an 'analyze' or 'vacuum analyze' over these tables?

A left outer join gets *everything* from the second table:

> LEFT OUTER JOIN groups ON groups.id = locations.group_id
> LEFT OUTER JOIN schools ON schools.location_id = locations.id

So they will load everything from groups and schools. Maybe they should
be left join's not left outer joins?

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings