X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 1F3DFD1CC76
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Dec 2003 06:27:21 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 73297-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Dec 2003 02:26:50 -0400 (AST)
Received: from mail.networkprograms.com (unknown [203.190.139.254])
	by svr1.postgresql.org (Postfix) with ESMTP id A28D6D1B8C0
	for <pgsql-sql@postgresql.org>; Wed, 17 Dec 2003 02:26:46 -0400 (AST)
Received: from pambardar ([192.9.203.90])
	by mail.networkprograms.com (8.12.9/8.12.9) with SMTP id hBH6XPeR014493
	for <pgsql-sql@postgresql.org>; Wed, 17 Dec 2003 12:03:25 +0530
Message-ID: <000c01c3c466$c247f5b0$5acb09c0@pambardar>
From: "Preeti Ambardar" <pambardar@networkprograms.com>
To: <pgsql-sql@postgresql.org>
Subject: SQL Optimization
Date: Wed, 17 Dec 2003 11:56:52 +0530
MIME-Version: 1.0
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Scanned-By: MIMEDefang 2.35
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Disposition: inline
X-Archive-Number: 200312/155
X-Sequence-Number: 16321

I am working on a query qhich has lot of  RIGHT and LEFT Outer joins. This =
takes almost 2 hours to execute.=20

SELECT  mstcurrency.pkcurid, mstcurrency.curswift_code,=20
 mstproducts.pkprdid, mstproducts.prdname,=20
 mstproducts.fkcnvid_prdbaseuom,=20
 cpuser.uspname || ' ' || cpuser.uspsurname as uspname,=20
 cpuser.pkuspid, cpuser.uspnotify_number AS
 cpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid,=20
 mstcompanies.pkcmpid, mstcompanies.cmpname,=20
 mstcompanies.cmpaccount_type,=20
 mstfacilityviews.facname, mstfacilityviews.pkfacid=20

FROM  mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies=20
 ON cpuser.fktrdid_usptradhouseid =3D mstcompanies.fktrdid_cmptradhouseid=
=20
  AND cpuser.pkuspid =3D mstcompanies.cmpcontact_userid=20
 LEFT OUTER JOIN mstcurrency=20
 ON mstcompanies.fktrdid_cmptradhouseid =3D mstcurrency.fktrdid_curtradhous=
eid=20
  AND mstcompanies.fkcurid_cmpcurid =3D mstcurrency.pkcurid
 LEFT OUTER JOIN mstproducts=20
 ON mstcompanies.fktrdid_cmptradhouseid =3D mstproducts.fktrdid_prdtradhous=
eid=20
  AND mstcompanies.cmpmain_product =3D mstproducts.pkprdid=20
 RIGHT OUTER JOIN mstuserprofiles=20
 LEFT OUTER JOIN mstfacilityviews=20
 ON mstuserprofiles.uspdefaultfacility =3D mstfacilityviews.pkfacid=20
 ON mstcompanies.pkcmpid =3D mstuserprofiles.uspdefaultcounterparty=20
  AND mstcompanies.fktrdid_cmptradhouseid =3D          mstuserprofiles.fktr=
did_usptradhouseid

WHERE mstuserprofiles.pkuspid =3D 101 AND mstuserprofiles.fktrdid_usptradho=
useid =3D 1