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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Chris Sent: Monday, March 06, 2006 6:40 PM To: i.v.r. Cc: pgsql-performance@xxxxxxxxxxxxxx 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
