From: | "Preeti Ambardar" <pambardar(at)networkprograms(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | SQL Optimization |
Date: | 2003-12-17 06:26:52 |
Message-ID: | 000c01c3c466$c247f5b0$5acb09c0@pambardar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I am working on a query qhich has lot of RIGHT and LEFT Outer joins. This takes almost 2 hours to execute.
SELECT mstcurrency.pkcurid, mstcurrency.curswift_code,
mstproducts.pkprdid, mstproducts.prdname,
mstproducts.fkcnvid_prdbaseuom,
cpuser.uspname || ' ' || cpuser.uspsurname as uspname,
cpuser.pkuspid, cpuser.uspnotify_number AS
cpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid,
mstcompanies.pkcmpid, mstcompanies.cmpname,
mstcompanies.cmpaccount_type,
mstfacilityviews.facname, mstfacilityviews.pkfacid
FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies
ON cpuser.fktrdid_usptradhouseid = mstcompanies.fktrdid_cmptradhouseid
AND cpuser.pkuspid = mstcompanies.cmpcontact_userid
LEFT OUTER JOIN mstcurrency
ON mstcompanies.fktrdid_cmptradhouseid = mstcurrency.fktrdid_curtradhouseid
AND mstcompanies.fkcurid_cmpcurid = mstcurrency.pkcurid
LEFT OUTER JOIN mstproducts
ON mstcompanies.fktrdid_cmptradhouseid = mstproducts.fktrdid_prdtradhouseid
AND mstcompanies.cmpmain_product = mstproducts.pkprdid
RIGHT OUTER JOIN mstuserprofiles
LEFT OUTER JOIN mstfacilityviews
ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid
ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty
AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid
WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-12-17 15:32:23 | Re: failed to build any 5-way joins |
Previous Message | Jonas Lindholm | 2003-12-16 18:55:24 | Problem with functions. |