Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Lorraine.Dewey@companiongroup.com (Lorraine Dewey),
	pgsql-sql@postgresql.org
Subject: Re: Optomizing left outer joins
Date: Wed, 23 Apr 2003 11:49:32 -0700
User-Agent: KMail/1.4.3
References: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
In-Reply-To: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304231149.32514.josh@agliodbs.com>

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 -=
-=20
often up to 5 times slower.   I'm not sure whether this is just the planner=
=20
restricitons inherent in an outer join, or whether this is something about=
=20
the required join algorithm itself.

The general solution is not to allow nulls in join columns.  For example, I=
=20
recently had to force one of my clients to add 0: Not Selected to all of=20
their reference lists to make those columns NOT NULL.   Their reports run 8=
0%=20
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 s=
ure=20
you tell them so ;->

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco