X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 96EF0475ADE for ; Wed, 23 Apr 2003 16:52:17 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id B64FE474E42 for ; Wed, 23 Apr 2003 16:52:16 -0400 (EDT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2983053; Wed, 23 Apr 2003 13:52:14 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "LORRAINE DEWEY" Subject: Re: Optomizing left outer joins Date: Wed, 23 Apr 2003 13:51:53 -0700 User-Agent: KMail/1.4.3 References: In-Reply-To: Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200304231351.53694.josh@agliodbs.com> X-Spam-Status: No, hits=-32.4 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/355 X-Sequence-Number: 13056 Lorraine, > Perhaps I should have said that not only is the column optional, the enti= re > row is optional. I am trying to join a master table (contains insurance= =20 claim > information) to an error table. If the claim in the master table doesn't= =20 have > any errors, there won't be a row for it in the error table. When I select > columns from both tables, I only get the errored claims. That's why I was > trying to use the outer join. Well, an outer join is appropriate in that case. However, a single outer= =20 join shouldn't be killing your queries -- in the example I gave, I eliminat= ed=20 19 outer joins to boost the client's performance. I think the lack of indexes is your real albatross.=20=20=20=20 > I like your advice about telling the customer about the workaround. I'll > definitely do that! The workaround is pretty awkward: two select stateme= nts > joined by "union all," then some subselects to pick out the unmatched=20 records. > It returns the correct data and it's faster than the left join, but it= =20 takes > a lot more code. Anyway, I guess I'm stuck with it. You probably are. But make sure you tell the client that the lack of inde= xes=20 is killing performance; even if it doesn't get fixed, your keister is=20 covered. --=20 -Josh Berkus Aglio Database Solutions San Francisco