Thread: SQL Optimization

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

"Preeti Ambardar" <pambardar@networkprograms.com> writes:
> SELECT ...
> 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

Ugh :-(

7.4 will do a better job with this than previous releases, but you are
still going to be up against the fact that outer joins constrain the
join order.  See
http://www.postgresql.org/docs/7.4/static/explicit-joins.html

You may need to revise the query to do the joins in a more appropriate
order.  One trick to try is to reduce all the outer joins to plain inner
joins, then EXPLAIN that form of the query to see what order the planner
thinks the tables should be joined in.  Then see if you can revise the
query to do the outer joins in that order.

One thing I find particularly odd is that the query is phrased to cause
the entire join of mstuserprofiles and mstfacilityviews to be formed,
because the last few lines will be parsed as

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

Surely that ordering of the ON clauses was a thinko and should be
reconsidered.  And do you really want a RIGHT JOIN to the second
occurrence of mstuserprofiles?  Why?
        regards, tom lane