From: Rod Taylor <rbt@rbt.ca>
To: Lorraine Dewey <Lorraine.Dewey@companiongroup.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optomizing left outer joins
Date: 23 Apr 2003 13:48:38 -0400
Message-ID: <1051120118.38778.34.camel@jester> (raw)
In-Reply-To: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
References: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
On Mon, 2003-04-21 at 11:14, Lorraine Dewey wrote:
> I'm writing an on-the-fly report program that generates and executes
> an SQL statement. The statement depends upon the choices users make
> when selecting from several hundred columns spread across > 90 tables.
>
> Since some of the data fields are optional and I won't be able to
> match across tables, I need to use left outer joins to make sure I
> don't drop rows. Unfortunately, execution time is somewhere around a
> minute (there's other stuff going on, but the majority of the time is
> in the execution). Way too long. For comparison, my queries run in
> about 2 seconds when I don't have to do outer joins.
Have you tried (or are able to) change the order the tables are joined
in? PostgreSQL runs outer joins in the order they are provided in,
which not not necessarily the best order to do so.
Try using EXPLAIN ANALYZE on your fast query for help on determining the
best join order.
Its up to you to ensure the results still apply. Switch the join order
may change the results depending on what you're doing.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.ascAttachments:
signature.asc (application/pgp-signature, 186B) download
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-sql@postgresql.org
Cc: rbt@rbt.ca, Lorraine.Dewey@companiongroup.com
Subject: Re: Optomizing left outer joins
In-Reply-To: <1051120118.38778.34.camel@jester>
* 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