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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
