Subject: Re: Optomizing left outer joins
From: Rod Taylor <rbt@rbt.ca>
To: Lorraine Dewey <Lorraine.Dewey@companiongroup.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
References: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-cYExNGy8XbmoYetaXXBk"
Organization: 
Message-Id: <1051120118.38778.34.camel@jester>
Mime-Version: 1.0
Date: 23 Apr 2003 13:48:38 -0400

--=-cYExNGy8XbmoYetaXXBk
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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.
>=20
> 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.

--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-cYExNGy8XbmoYetaXXBk
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+ptH16DETLow6vwwRAhVqAJ96Dai1rHf8BtA62g4mSWluaNMoYgCfcnxN
E7jDOvof2BfGRbWxbEBDX4Q=
=ZnsH
-----END PGP SIGNATURE-----

--=-cYExNGy8XbmoYetaXXBk--