Re: Optomizing left outer joins

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(at)rbt(dot)ca>

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