Re: Optomizing left outer joins

Lorraine,

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

FWIW, outer joins are slower than regular joins on all RDBMSs I've tested --
often up to 5 times slower. I'm not sure whether this is just the planner
restricitons inherent in an outer join, or whether this is something about
the required join algorithm itself.

The general solution is not to allow nulls in join columns. For example, I
recently had to force one of my clients to add 0: Not Selected to all of
their reference lists to make those columns NOT NULL. Their reports run 80%
faster now.

> I never know which columns the users will pick so I can't really
> hardcode the queries. Everything is generated on the fly. I've been
> asked not to index any columns. Any ideas about how I can make this
> thing run faster so I can drop the workaround, or any alternative
> ideas? I think we're using version 7.3.2.

If the client is tying your hands, you'll have to use a workaround. Make sure
you tell them so ;->

--
-Josh Berkus
Aglio Database Solutions
San Francisco