From: Lorraine.Dewey@companiongroup.com
To: pgsql-sql@postgresql.org
Subject: Optomizing left outer joins
Date: 21 Apr 2003 08:14:04 -0700
Message-ID: <ebfd27c8.0304210714.66e3ae75@posting.google.com> (raw)
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.
As a workaround, I'm doing something like this:
Select (list of all fields)
from ...
where...
join all
Select (list of all fields except the ones from tables that I can't
match)
from...
where key not in (select key from optional table)
This runs in the 5-10 second range, but it's going to be a nightmare
to code, especially the "where" clause.
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.
Thanks a lot for your help.
Lorraine
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: Lorraine.Dewey@companiongroup.com
Subject: Re: Optomizing left outer joins
In-Reply-To: <ebfd27c8.0304210714.66e3ae75@posting.google.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