From: Dave Dutcher <dave@tridecap.com>
To: 'Chris' <dmagick@gmail.com>
To: 'i.v.r.' <ivanvega@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help understanding indexes, explain, and optimizing
Date: Tue, 7 Mar 2006 09:02:58 -0600
Message-ID: <003b01c641f8$39185a70$8300a8c0@tridecap.com> (raw)
In-Reply-To: <440CD673.8080208@gmail.com>
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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Chris
Sent: Monday, March 06, 2006 6:40 PM
To: i.v.r.
Cc: pgsql-performance@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, dmagick@gmail.com, ivanvega@gmail.com
Subject: Re: Help understanding indexes, explain, and optimizing
In-Reply-To: <003b01c641f8$39185a70$8300a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox