From: Lorraine.Dewey@companiongroup.com (Lorraine Dewey)
Subject: Optomizing left outer joins
Date: 21 Apr 2003 08:14:04 -0700
Organization: http://groups.google.com/
Lines: 34
Message-ID: <ebfd27c8.0304210714.66e3ae75@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable

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