From: Josh Berkus <josh@agliodbs.com>
To: Lorraine.Dewey@companiongroup.com
To: pgsql-sql@postgresql.org
Subject: Re: Optomizing left outer joins
Date: Wed, 23 Apr 2003 11:49:32 -0700
Message-ID: <200304231149.32514.josh@agliodbs.com> (raw)
In-Reply-To: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
References: <ebfd27c8.0304210714.66e3ae75@posting.google.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 --
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
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: josh@agliodbs.com, Lorraine.Dewey@companiongroup.com
Subject: Re: Optomizing left outer joins
In-Reply-To: <200304231149.32514.josh@agliodbs.com>
* 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