SQL Optimization - Mailing list pgsql-sql

From Preeti Ambardar
Subject SQL Optimization
Date
Msg-id 000c01c3c466$c247f5b0$5acb09c0@pambardar
Whole thread Raw
Responses Re: SQL Optimization
List 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_numberAScpusercontactdetails, 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.pkcuridLEFTOUTER JOIN mstproducts ON mstcompanies.fktrdid_cmptradhouseid =
mstproducts.fktrdid_prdtradhouseid AND mstcompanies.cmpmain_product = mstproducts.pkprdid RIGHT OUTER JOIN
mstuserprofilesLEFT 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


pgsql-sql by date:

Previous
From: sundaresan raman
Date:
Subject: ...
Next
From: "Dok, D. van"
Date:
Subject: Crosstable query